Mike said:
Dirk,
Thanks for pinpointing the typo, that was the problem. If
I wanted to proceede with your suggestion on using
recordset, how would I need to go about and chang my
codes?
I note that John Viescas has also addressed this issue in a separate
thread, and made the very good suggestion of picking up extra columns
from a combo box. That's a better solution than doing a lookup, but for
completeness I'll give some example code to do what I was talking about:
'----- start of sample code -----
Private Sub ProductID_AfterUpdate()
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL =
"SELECT * FROM tblProductsIn " & _
"WHERE ProductID=" & Me.ProductID
Set rs = CurrentDb.OpenRecordset(strSQL)
With rs
If .EOF Then
Msgbox "Product ID not on file!"
Else
Me.ProductName = !ProductName
Me.ProductDescription = !ProductDescription
Me.Supplier= !Supplier
Me.UnitsIn = !UnitsIn
Me.DateReceived = !DateReceived
Me.UnitPrice = !UnitPrice
End If
.Close
End With
Set rs = Nothing
End Sub
'----- end of sample code -----
Note: the above code requires you to have a reference set to the DAO
object library. If you're using Access 97, you already have the
reference set by default. If you're using A2K or later, you may need to
click Tools -> References... in the VB Editor, locate Microsoft DAO 3.6
Object Library in the list, and put a check mark in the box next to it.
Note also that I moved the code from the Exit event to the AfterUpdate
event, which is a better choice for this sort of processing.
