user defined type not defined error

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sleek - 24 Mar 2005 05:19 GMT

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.

Reply to this Message

Allen Browne - 24 Mar 2005 06:53 GMT

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

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Ok so Ive now got this code that i have written
>
[quoted text clipped - 25 lines]
> help please someone, Ive never used vb or vba before im having trouble
> finding documentation for it.

Reply to this Message

sleek - 29 Mar 2005 07:35 GMT

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.
>
[quoted text clipped - 33 lines]
> > help please someone, Ive never used vb or vba before im having trouble
> > finding documentation for it.

Reply to this Message

Allen Browne - 29 Mar 2005 08:30 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
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.
[quoted text clipped - 50 lines]
>> > help please someone, Ive never used vb or vba before im having trouble
>> > finding documentation for it.

Reply to this Message

Andreas - 29 Mar 2005 08:48 GMT

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.
[quoted text clipped - 49 lines]
>>>help please someone, Ive never used vb or vba before im having trouble
>>>finding documentation for it.

Reply to this Message

sleek - 30 Mar 2005 00:21 GMT

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
[quoted text clipped - 63 lines]
> >>>help please someone, Ive never used vb or vba before im having trouble
> >>>finding documentation for it.

Reply to this Message