user defined type not defined error

[Guest]

Ok so Ive now got this code that i have written

It produces a "user defined type not defined" error and stops at the *

*Private Sub Item_No_AfterUpdate()

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordSet("item_incrementer")

If Not rs.EOF Then
rs.MoveFirst
Me.Supplier = rs![Supplier]
Me.Quantity = rs![Quantity]
Me.Status = rs![Status]
Me.Due_Date = rs![Due Date]
Me.Category = rs![Category]
Me.W_O_No = rs![W/O No]
Me.P_O_No = rs![P/O No]
Me.P_O_Line_No = rs![P/O Line No]
End If

rs.Close
End Sub

help please someone, Ive never used vb or vba before im having trouble
finding documentation for it.

 

[Allen Browne]

From the code window, choose References on the Tools menu.

Check the box beside:
Microsoft DAO 3.6 Library.

More info on references:
http://allenbrowne.com/ser-38.html

 

[Guest]

I did that and now its getting a run time error 3061 error missing 7
parameters.

the query that the code opens extracts 7 fields of data from the form in
question, is that what it is refering to? how do i get it to run the query
correctly?

basicly if i just run the query from the db window (with the correct form
open and containing the required data to run the query) it will run the query
correctly and place the required data in the fields of the query.

but when i try and run the query via vba code it get the above error.

HELP
as it really seems i didnt need to add in the dao stuff explicitly.

From the code window, choose References on the Tools menu.

Check the box beside:
Microsoft DAO 3.6 Library.

More info on references:
http://allenbrowne.com/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ok so Ive now got this code that i have written

It produces a "user defined type not defined" error and stops at the *

*Private Sub Item_No_AfterUpdate()

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordSet("item_incrementer")

If Not rs.EOF Then
rs.MoveFirst
Me.Supplier = rs![Supplier]
Me.Quantity = rs![Quantity]
Me.Status = rs![Status]
Me.Due_Date = rs![Due Date]
Me.Category = rs![Category]
Me.W_O_No = rs![W/O No]
Me.P_O_No = rs![P/O No]
Me.P_O_Line_No = rs![P/O Line No]
End If

rs.Close
End Sub

help please someone, Ive never used vb or vba before im having trouble
finding documentation for it.

 

[Allen Browne]

Access contains a thing called the Expression Service (ES).
When you run a query directly from the Database window, the ES resolves
references such as:
Forms!Form1!Textbox1
The ES is not available in the context of DAO code.

Your choices are therefore:
a) Build up a SQL string yourself, by concatenating the values from the
controls on the form into the string, or
b) Explicitly supply each parameter.

Solution a) is the more flexible one, and what I personally always use. You
can use BuildCriteria() to help you concatenate the values into the string
with the correct delimiters.

Solution b) involves declaring a QueryDef, and explicitly assigning each
paramter to the value in the control on the form.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I did that and now its getting a run time error 3061 error missing 7
parameters.

the query that the code opens extracts 7 fields of data from the form in
question, is that what it is refering to? how do i get it to run the query
correctly?

basicly if i just run the query from the db window (with the correct form
open and containing the required data to run the query) it will run the
query
correctly and place the required data in the fields of the query.

but when i try and run the query via vba code it get the above error.

HELP
as it really seems i didnt need to add in the dao stuff explicitly.

From the code window, choose References on the Tools menu.

Check the box beside:
Microsoft DAO 3.6 Library.

More info on references:
http://allenbrowne.com/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ok so Ive now got this code that i have written

It produces a "user defined type not defined" error and stops at the *

*Private Sub Item_No_AfterUpdate()

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordSet("item_incrementer")

If Not rs.EOF Then
rs.MoveFirst
Me.Supplier = rs![Supplier]
Me.Quantity = rs![Quantity]
Me.Status = rs![Status]
Me.Due_Date = rs![Due Date]
Me.Category = rs![Category]
Me.W_O_No = rs![W/O No]
Me.P_O_No = rs![P/O No]
Me.P_O_Line_No = rs![P/O Line No]
End If

rs.Close
End Sub

help please someone, Ive never used vb or vba before im having trouble
finding documentation for it.

 

[Andreas]

What are you actually doing?
Are you on an existing record, running this code after someone changes
the item or are you going to a new record?
If the query "item_incrementer" works with you sitting on an existing
record but asks for 7 parameters when opened in code, it seems to me
that you may be on a new, blank record at the time the code runs and
there is no data for the query to use.

And whatever you do, do not remove your reference to DAO.
Incidentally, what references have you got?

Regards,
Andreas

I did that and now its getting a run time error 3061 error missing 7
parameters.

the query that the code opens extracts 7 fields of data from the form in
question, is that what it is refering to? how do i get it to run the query
correctly?

basicly if i just run the query from the db window (with the correct form
open and containing the required data to run the query) it will run the query
correctly and place the required data in the fields of the query.

but when i try and run the query via vba code it get the above error.

HELP
as it really seems i didnt need to add in the dao stuff explicitly.

:

From the code window, choose References on the Tools menu.

Check the box beside:
Microsoft DAO 3.6 Library.

More info on references:
http://allenbrowne.com/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Ok so Ive now got this code that i have written

It produces a "user defined type not defined" error and stops at the *

*Private Sub Item_No_AfterUpdate()

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordSet("item_incrementer")

If Not rs.EOF Then
rs.MoveFirst
Me.Supplier = rs![Supplier]
Me.Quantity = rs![Quantity]
Me.Status = rs![Status]
Me.Due_Date = rs![Due Date]
Me.Category = rs![Category]
Me.W_O_No = rs![W/O No]
Me.P_O_No = rs![P/O No]
Me.P_O_Line_No = rs![P/O Line No]
End If

rs.Close
End Sub

help please someone, Ive never used vb or vba before im having trouble
finding documentation for it.

 

[Guest]

im generating a new record. basicly a revised item arrives, that item needs
to be entered. but the major details will always be the same, i.e supplier
etc so basicly when all the primary key fields have been entered it will look
up the previous current item and copy certian fields of data to the new
record im generating.

i think i will go with Allens answer. That is exactly the info i wanted to
know allen, thank you.

Now that i know that I will get on with doing things the right way. I was
just hoping it would be something small, like an error in the code.

What are you actually doing?
Are you on an existing record, running this code after someone changes
the item or are you going to a new record?
If the query "item_incrementer" works with you sitting on an existing
record but asks for 7 parameters when opened in code, it seems to me
that you may be on a new, blank record at the time the code runs and
there is no data for the query to use.

And whatever you do, do not remove your reference to DAO.
Incidentally, what references have you got?

Regards,
Andreas

I did that and now its getting a run time error 3061 error missing 7
parameters.

the query that the code opens extracts 7 fields of data from the form in
question, is that what it is refering to? how do i get it to run the query
correctly?

basicly if i just run the query from the db window (with the correct form
open and containing the required data to run the query) it will run the query
correctly and place the required data in the fields of the query.

but when i try and run the query via vba code it get the above error.

HELP
as it really seems i didnt need to add in the dao stuff explicitly.

:

From the code window, choose References on the Tools menu.

Check the box beside:
Microsoft DAO 3.6 Library.

More info on references:
http://allenbrowne.com/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


Ok so Ive now got this code that i have written

It produces a "user defined type not defined" error and stops at the *

*Private Sub Item_No_AfterUpdate()

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordSet("item_incrementer")

If Not rs.EOF Then
rs.MoveFirst
Me.Supplier = rs![Supplier]
Me.Quantity = rs![Quantity]
Me.Status = rs![Status]
Me.Due_Date = rs![Due Date]
Me.Category = rs![Category]
Me.W_O_No = rs![W/O No]
Me.P_O_No = rs![P/O No]
Me.P_O_Line_No = rs![P/O Line No]
End If

rs.Close
End Sub

help please someone, Ive never used vb or vba before im having trouble
finding documentation for it.