How do I create a look up module?

[Guest]

I am trying to create a module in which when I type in the ID Number the
serial number and description automatically fills in. Here is the module I
wrote but it is not working - I am getting a compile error message.
Private Sub IDNo_AfterUpdate()
Dim varID, varSerialNo, varDescription As Variant
varIDNo = DLookup("IDNo", "tblIDNo", "IDNo =[IDNo] ")
varSerialNo = DLookup("SerialNo", "tblIDNo", "SerialNo =[SerialNo] ")
varDescription = DLookup("Description", "tblIDNo", "Description
=[Description] ")
If (Not IsNull(varIDNo)) Then Me![IDNo] = varIDNo
If (Not IsNull(varSerialNo)) Then Me![SerialNo] = varSerialNo
If (Not IsNull(varDescription)) Then Me![Description] = varDescription

 

[Guest]

There are some basic problems with the code. Although, in this case, it
would not be noticable or cause problems, your Dim statement is incorrect.
Any variable not specifically typed is established as a variant. I think you
are expecting that all variables in the Dim will have the type listed at the
end. That is not correct. Each has to be typed separately. For example, if
you coded:
Dim x, y, z as String
Only z would be a string. x and y would be variants. It is also a good
practice to dim each variable on a separate line. It is also a good idea to
indent your code so it is easier to read. Notice how I have reformatted it

Private Sub IDNo_AfterUpdate()
Dim varID As Variant
Dim varSerialNo As Variant
Dim varDescription As Variant

' In the lines below, the variable or field has to be outside the qoutes. I
have used the first line to give examples of different data types. First
example is for numeric data. It needs no delimiters. Second example is for
text data. It has to be enclosed in qoutes. Single qoutes will do in most
cases. Third example is for dates. The must be enclosed in #. Also using
brackets for field names is a good thing. Which [IDNo] is Which? duplicate
names confused both Access and anyone reading your code. If the IDNo you are
trying to match is a text box on your form, use Me![IDNo]

varIDNo = DLookup("[IDNo]", "tblIDNo", "[IDNo] = " & [IDNo] )
varIDNo = DLookup("[IDNo]", "tblIDNo", "[IDNo] = '" & [IDNo] & "'")
varIDNo = DLookup("[IDNo]", "tblIDNo", "]IDNo] = #" & [IDNo] & "#")

varSerialNo = DLookup("[SerialNo]", "tblIDNo", "[SerialNo] = " &
[SerialNo] )
varDescription = DLookup("[Description]", "tblIDNo", "[Description] = '"
& _
[Description] & "'")

If (Not IsNull(varIDNo)) Then
Me![IDNo] = varIDNo
End If
If (Not IsNull(varSerialNo)) Then
Me![SerialNo] = varSerialNo
End If
If (Not IsNull(varDescription)) Then
Me![Description] = varDescription
End If