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.
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.