AddNew

D

- Dean -

I'm having problems trying to add a new record to a table
called "Data".

The information is entered into a form called "Main" then
when the OK button is pressed it checks the info and sends
it to a form called "Confirm"

When the OK button on the "Confirm" form is clicked I want
to add the info to a new record in the "Data" table.

I'm trying to use the following but getting an error on
the line indicated with ***s (Dim dbS As Database) which
says:
Compile Error:
User-defined type not defined

I'm using Access 2000 and am sure similar code worked in a
previous database created in 97 - has something changed?

Any help appreciated as I'm only a beginner at this and
it's pretty complex!!

TIA

btw It's a database for entering Food Sample results taken
for microbilogical analysis.


Private Sub ConfirmOK_Click()

'Ok button pressed, Enter a record in the Data Table

'***********************
Dim dbS As Database
'***********************
Dim rcdData As Recordset

Set dbS = CurrentDb()
Set rcdData = dbS.openrecordset("Data")

Dim name As String ' Name of officer
Dim area As String ' Area
Dim Sample As String ' Sample Number
Dim sdate As String ' Sample Date
Dim Cat2 As String ' Catagory Name
Dim tvc2 As String ' TVC Name
Dim result2 As String ' Result Name
Dim TVC As String ' TVC group ID number
Dim result As String ' Result group ID number
Dim cat As String ' Catagory group ID number
Dim txt As String ' Text Field

'Allocates values to varibles defined above

name = Forms![main]![Officer]
area = Forms![main]![area]
Sample = Forms![main]![Sample]
sdate = Forms![main]![Date]
cat = Forms![main]![Catagory]
TVC = Forms![main]![TVC]
result = Forms![main]![result]
If IsNull(Forms![main]![Notes]) Then
txt = ""
Else:
txt = Forms![main]![Notes]
End If

'Sets Cat2 varible to the correct Catagory
'name depending on the cat group ID number
If cat = 1 Then
Cat2 = "Meat Products"
ElseIf cat = 2 Then
Cat2 = "Confectionary"
ElseIf cat = 3 Then
Cat2 = "Sandwiches"
ElseIf cat = 4 Then
Cat2 = "Ready Meals"
ElseIf cat = 5 Then
Cat2 = "Seafood"
ElseIf cat = 6 Then
Cat2 = "Ice Cream"
ElseIf cat = 7 Then
Cat2 = "Dairy Products"
ElseIf cat = 8 Then
Cat2 = "Other Foods"
ElseIf cat = 9 Then
Cat2 = "Swabs"
ElseIf cat = 10 Then
Cat2 = "Lacors"
Else: Cat2 = "Error"
End If

'Sets TVC2 varible to the correct TVC
'name depending on the TVC group ID number
If TVC = 1 Then
tvc2 = "<10e3"
ElseIf TVC = 2 Then
tvc2 = "10e3 - 10e4"
ElseIf TVC = 3 Then
tvc2 = "10e4 - 10e5"
ElseIf TVC = 4 Then
tvc2 = "10e5 - 10e6"
ElseIf TVC = 5 Then
tvc2 = "10e6 - 10e7"
ElseIf TVC = 6 Then
tvc2 = "10e7 - 10e8"
ElseIf TVC = 7 Then
tvc2 = ">10e8"
Else: tvc2 = "Error"
End If

'Sets result2 varible to the correct Result
'name depending on the result group ID number
If result = 1 Then
result2 = "Satisfactory"
ElseIf result = 2 Then
result2 = "Acceptable"
ElseIf result = 3 Then
result2 = "Unsatisfactory"
ElseIf result = 4 Then
result2 = "Unacceptable"
Else: result2 = "Error"
End If

rcdData.AddNew

rcdData![Date] = sdate
rcdData![initials] = name
rcdData![area] = area
rcdData![sampleno] = Sample
rcdData![cat] = Cat2

If TVC = 1 Then rcdData![tvc1] = True Else rcdData!
[tvc1] = False
If TVC = 2 Then rcdData![tvc2] = True Else rcdData!
[tvc2] = False
If TVC = 3 Then rcdData![tvc3] = True Else rcdData!
[tvc3] = False
If TVC = 4 Then rcdData![tvc4] = True Else rcdData!
[tvc4] = False
If TVC = 5 Then rcdData![tvc5] = True Else rcdData!
[tvc5] = False
If TVC = 6 Then rcdData![tvc6] = True Else rcdData!
[tvc6] = False
If TVC = 7 Then rcdData![tvc7] = True Else rcdData!
[tvc7] = False

If Forms![main]![bug1t] = True Then rcdData![bug1t] =
True Else rcdData![bug1t] = False
If Forms![main]![bug2t] = True Then rcdData![bug2t] =
True Else rcdData![bug2t] = False
If Forms![main]![bug3t] = True Then rcdData![bug3t] =
True Else rcdData![bug3t] = False
If Forms![main]![bug4t] = True Then rcdData![bug4t] =
True Else rcdData![bug4t] = False
If Forms![main]![bug5t] = True Then rcdData![bug5t] =
True Else rcdData![bug5t] = False
If Forms![main]![bug6t] = True Then rcdData![bug6t] =
True Else rcdData![bug6t] = False
If Forms![main]![bug7t] = True Then rcdData![bug7t] =
True Else rcdData![bug7t] = False
If Forms![main]![bug8t] = True Then rcdData![bug8t] =
True Else rcdData![bug8t] = False
If Forms![main]![bug9t] = True Then rcdData![bug9t] =
True Else rcdData![bug9t] = False
If Forms![main]![bug10t] = True Then rcdData![bug10t]
= True Else rcdData![bug10t] = False

If Forms![main]![bug1d] = True Then rcdData![bug1d] =
True Else rcdData![bug1d] = False
If Forms![main]![bug2d] = True Then rcdData![bug2d] =
True Else rcdData![bug2d] = False
If Forms![main]![bug3d] = True Then rcdData![bug3d] =
True Else rcdData![bug3d] = False
If Forms![main]![bug4d] = True Then rcdData![bug4d] =
True Else rcdData![bug4d] = False
If Forms![main]![bug5d] = True Then rcdData![bug5d] =
True Else rcdData![bug5d] = False
If Forms![main]![bug6d] = True Then rcdData![bug6d] =
True Else rcdData![bug6d] = False
If Forms![main]![bug7d] = True Then rcdData![bug7d] =
True Else rcdData![bug7d] = False
If Forms![main]![bug8d] = True Then rcdData![bug8d] =
True Else rcdData![bug8d] = False
If Forms![main]![bug9d] = True Then rcdData![bug9d] =
True Else rcdData![bug9d] = False
If Forms![main]![bug10d] = True Then rcdData![bug10d]
= True Else rcdData![bug10d] = False

rcdData![result] = result2
rcdData![Notes] = txt

rcdData.Update

End Sub
 
N

Nikos Yannacopoulos

Dean,

I didn't read all of your code, but I think I can get you past the first
problem: A2K doesn't recognize recordsets this way, you need to add
Microsoft DAO 3.6 Object Library to your references (Tools > References in
the VBE window), and then change the database and recordset object
declarations to:

Dim dbS As DAO.Database
Dim rcdData As DAO.Recordset

Regrettably this is not documented in A2K VBA help, which has examples in
the A97 way!

HTH,
Nikos

- Dean - said:
I'm having problems trying to add a new record to a table
called "Data".

The information is entered into a form called "Main" then
when the OK button is pressed it checks the info and sends
it to a form called "Confirm"

When the OK button on the "Confirm" form is clicked I want
to add the info to a new record in the "Data" table.

I'm trying to use the following but getting an error on
the line indicated with ***s (Dim dbS As Database) which
says:
Compile Error:
User-defined type not defined

I'm using Access 2000 and am sure similar code worked in a
previous database created in 97 - has something changed?

Any help appreciated as I'm only a beginner at this and
it's pretty complex!!

TIA

btw It's a database for entering Food Sample results taken
for microbilogical analysis.


Private Sub ConfirmOK_Click()

'Ok button pressed, Enter a record in the Data Table

'***********************
Dim dbS As Database
'***********************
Dim rcdData As Recordset

Set dbS = CurrentDb()
Set rcdData = dbS.openrecordset("Data")

Dim name As String ' Name of officer
Dim area As String ' Area
Dim Sample As String ' Sample Number
Dim sdate As String ' Sample Date
Dim Cat2 As String ' Catagory Name
Dim tvc2 As String ' TVC Name
Dim result2 As String ' Result Name
Dim TVC As String ' TVC group ID number
Dim result As String ' Result group ID number
Dim cat As String ' Catagory group ID number
Dim txt As String ' Text Field

'Allocates values to varibles defined above

name = Forms![main]![Officer]
area = Forms![main]![area]
Sample = Forms![main]![Sample]
sdate = Forms![main]![Date]
cat = Forms![main]![Catagory]
TVC = Forms![main]![TVC]
result = Forms![main]![result]
If IsNull(Forms![main]![Notes]) Then
txt = ""
Else:
txt = Forms![main]![Notes]
End If

'Sets Cat2 varible to the correct Catagory
'name depending on the cat group ID number
If cat = 1 Then
Cat2 = "Meat Products"
ElseIf cat = 2 Then
Cat2 = "Confectionary"
ElseIf cat = 3 Then
Cat2 = "Sandwiches"
ElseIf cat = 4 Then
Cat2 = "Ready Meals"
ElseIf cat = 5 Then
Cat2 = "Seafood"
ElseIf cat = 6 Then
Cat2 = "Ice Cream"
ElseIf cat = 7 Then
Cat2 = "Dairy Products"
ElseIf cat = 8 Then
Cat2 = "Other Foods"
ElseIf cat = 9 Then
Cat2 = "Swabs"
ElseIf cat = 10 Then
Cat2 = "Lacors"
Else: Cat2 = "Error"
End If

'Sets TVC2 varible to the correct TVC
'name depending on the TVC group ID number
If TVC = 1 Then
tvc2 = "<10e3"
ElseIf TVC = 2 Then
tvc2 = "10e3 - 10e4"
ElseIf TVC = 3 Then
tvc2 = "10e4 - 10e5"
ElseIf TVC = 4 Then
tvc2 = "10e5 - 10e6"
ElseIf TVC = 5 Then
tvc2 = "10e6 - 10e7"
ElseIf TVC = 6 Then
tvc2 = "10e7 - 10e8"
ElseIf TVC = 7 Then
tvc2 = ">10e8"
Else: tvc2 = "Error"
End If

'Sets result2 varible to the correct Result
'name depending on the result group ID number
If result = 1 Then
result2 = "Satisfactory"
ElseIf result = 2 Then
result2 = "Acceptable"
ElseIf result = 3 Then
result2 = "Unsatisfactory"
ElseIf result = 4 Then
result2 = "Unacceptable"
Else: result2 = "Error"
End If

rcdData.AddNew

rcdData![Date] = sdate
rcdData![initials] = name
rcdData![area] = area
rcdData![sampleno] = Sample
rcdData![cat] = Cat2

If TVC = 1 Then rcdData![tvc1] = True Else rcdData!
[tvc1] = False
If TVC = 2 Then rcdData![tvc2] = True Else rcdData!
[tvc2] = False
If TVC = 3 Then rcdData![tvc3] = True Else rcdData!
[tvc3] = False
If TVC = 4 Then rcdData![tvc4] = True Else rcdData!
[tvc4] = False
If TVC = 5 Then rcdData![tvc5] = True Else rcdData!
[tvc5] = False
If TVC = 6 Then rcdData![tvc6] = True Else rcdData!
[tvc6] = False
If TVC = 7 Then rcdData![tvc7] = True Else rcdData!
[tvc7] = False

If Forms![main]![bug1t] = True Then rcdData![bug1t] =
True Else rcdData![bug1t] = False
If Forms![main]![bug2t] = True Then rcdData![bug2t] =
True Else rcdData![bug2t] = False
If Forms![main]![bug3t] = True Then rcdData![bug3t] =
True Else rcdData![bug3t] = False
If Forms![main]![bug4t] = True Then rcdData![bug4t] =
True Else rcdData![bug4t] = False
If Forms![main]![bug5t] = True Then rcdData![bug5t] =
True Else rcdData![bug5t] = False
If Forms![main]![bug6t] = True Then rcdData![bug6t] =
True Else rcdData![bug6t] = False
If Forms![main]![bug7t] = True Then rcdData![bug7t] =
True Else rcdData![bug7t] = False
If Forms![main]![bug8t] = True Then rcdData![bug8t] =
True Else rcdData![bug8t] = False
If Forms![main]![bug9t] = True Then rcdData![bug9t] =
True Else rcdData![bug9t] = False
If Forms![main]![bug10t] = True Then rcdData![bug10t]
= True Else rcdData![bug10t] = False

If Forms![main]![bug1d] = True Then rcdData![bug1d] =
True Else rcdData![bug1d] = False
If Forms![main]![bug2d] = True Then rcdData![bug2d] =
True Else rcdData![bug2d] = False
If Forms![main]![bug3d] = True Then rcdData![bug3d] =
True Else rcdData![bug3d] = False
If Forms![main]![bug4d] = True Then rcdData![bug4d] =
True Else rcdData![bug4d] = False
If Forms![main]![bug5d] = True Then rcdData![bug5d] =
True Else rcdData![bug5d] = False
If Forms![main]![bug6d] = True Then rcdData![bug6d] =
True Else rcdData![bug6d] = False
If Forms![main]![bug7d] = True Then rcdData![bug7d] =
True Else rcdData![bug7d] = False
If Forms![main]![bug8d] = True Then rcdData![bug8d] =
True Else rcdData![bug8d] = False
If Forms![main]![bug9d] = True Then rcdData![bug9d] =
True Else rcdData![bug9d] = False
If Forms![main]![bug10d] = True Then rcdData![bug10d]
= True Else rcdData![bug10d] = False

rcdData![result] = result2
rcdData![Notes] = txt

rcdData.Update

End Sub
Click to expand...
 
M

Marshall Barton

- Dean - said:
I'm having problems trying to add a new record to a table
called "Data".

The information is entered into a form called "Main" then
when the OK button is pressed it checks the info and sends
it to a form called "Confirm"

When the OK button on the "Confirm" form is clicked I want
to add the info to a new record in the "Data" table.

I'm trying to use the following but getting an error on
the line indicated with ***s (Dim dbS As Database) which
says:
Compile Error:
User-defined type not defined

I'm using Access 2000 and am sure similar code worked in a
previous database created in 97 - has something changed?
Click to expand...


The thing that changed in A2K is that it defaults to the ADO
library (which doesn't have a Datanase object) instead of
the DAO library.

From the VB design window, use Tools - References to uncheck
the reference to ADO and check the the DAO library.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Update a date field in sub-form from dates fields on main form 1
Error 3048 "Cannot open any more databases" 7
One or More Criteria 1
Run-Time Error 2501 The OpenForm action was cancelled 1
Need help in printing vba code 0
Syntax error in Update statement 3
A Better Way 4
Complex If Statement 6

Top