I need some code help

[alpine82]

I have a form and a subform. On the subform I have some code which is supposed to be opening two tables and plugging in values using ItemNumber as the lookup. Here is the Code:

Private Sub ItemNumber_AfterUpdate()

'Declare DAO object variables and string variable.
Dim ThisDB As Database
Dim ProdList As Recordset
Dim ItemList As Recordset
Dim LookFor As String

If IsNull(Me!ItemNumber) Then
Exit Sub
End If

'If not blank, look it up.
'First define DAO object variables
Set ThisDB = CurrentDb()
Set ProdList = ThisDB.OpenRecordset("Envelope Detail File")
Set ItemList = ThisDB.OpenRecordset("Item File")
LookFor = Me![ItemNumber]
ProdList.Seek "=", LookFor

If ProdList.NoMatch Then
Beep
Else
[ItemList]![Status] = [ProdList]![Status]
[ItemList]![Date] = [ProdList]![Date]
[ItemList]![DriverID] = [ProdList]![DriverID]
End If

[ProdList].Close
[ItemList].Close

End Sub

Now I know this is the general idea of what I need to do but it is not working as of now. Any suggestions. What I need is, I need the code to lookup what is being entered in the form (whatever the data is called when the form hasn't been exited yet, I don't know if that is DAO), and then lookup a record in another table using ItemNumber and then set values in that table. (e.g. ----->
[ItemList]![Status] = [ProdList]![Status]
[ItemList]![Date] = [ProdList]![Date]
[ItemList]![DriverID] = [ProdList]![DriverID]

just updating values.

 

[alpine82]

I am not sure why it is giving me an error, but on the line

Dim ThisDB As Database

when the code tries to execute I get a compiler error, saying "User-defined type not defined"

I have no idea why that is. The code is right.

I also changed it to ____ Dim db As DAO.Database
and I still get the error.

 

[Mile-O]

Your database is not set to use DAO (the old way) and is set to use ADO (the future)


Open a module, goto Tools -> References

Look though the list for Microsoft Data Access Objects 3.6 and check it.

Move its priority above Microsoft ActiveX Data Objects.

Change your linem Dim db As Database, to: Dim db As DAO.Database

 

[Martyh]

How are you supposed to know this ??

Mile-O-Phile:

Who has told you you or how do you know this. In what book is this written? I would love to read it so that each time I wouldn't have to bother you guys!!



Can you please elucidate the rest of us "unwashed" people !!

If such a book does not exist could you write one --

"MS-Access for those people who know enough Access to get into trouble over details like this !!"

I bow down to kiss the "ACCESS RING" which should be on your finger !!!

Marty H.

 

[Mile-O]

Re: How are you supposed to know this ??

Mile-O-Phile:

Who has told you you or how do you know this. In what book is this written? I would love to read it so that each time I wouldn't have to bother you guys!!

I don't know of any books that are explicit in mentioning these details. As for how I know it: I read the Help files and read through old posts.