Trouble auto populating field using DLookUp

  • Thread starter Thread starter bcp1961 via AccessMonster.com
  • Start date Start date
B

bcp1961 via AccessMonster.com

I am trying to auto populate a field in a subform.

I have 3 tables:

schools
TOWNS
StudentsAttending


I have a form with the source being the tbl schools and a subform with the
source being tbl Students Attending.
StudentsAttending is updated from the subform with the number of students
from each town.

I am trying to use the DLookup function with the AfterUpdate event to
populate the towns geocode into a text box on the subform and then into the
tbl StudentsAttending, but am having a great deal of difficulty. I am Very
New to data base workings so I would appreciate any help ...

Here is what I've done:

I've established relationships between my 3 tables.
The form I created is named schools with StudentsAttending Subform as the sub
form in which to update the
number of students from each town attending the specific school in the main
form.

The subform contains 3 fields,

Students
Town - combo box w/rowsource to TOWNS.Town
Geocode

I looked up the Northwind sample db and tried to copy from their example this
AfterUpdate event, but I am getting
an error msg "Run-time error '3464': Data type mismatch in criteria
expression." and this is highlighted in the code builder " Me!Geocode =
DLookup("GEOCODES", "TOWNS", strFilter)"

Here is the code I used....

Private Sub Town_AfterUpdate()

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "Town = " & Me!Town

' Look up Towns GEOCODE and assign it to Geocode control.
Me!Geocode = DLookup("GEOCODES", "TOWNS", strFilter)

Exit_Town_AfterUpdate:
Exit Sub

End Sub
 
I forgot to mention that I did check the data types in my tables and the
TOWNS.GEOCODES field is data type of number - long integer and no default
setting... StudentsAttending.geocode field is the same.
 
You state that the datatype is an integer. Why are you declaring you criteria
as a string then? Try declaring it as an integer and try again or better yet
try the following:

Me!Geocode = DLookup("GEOCODES", "TOWNS", "Town= " & me.town)

hth
 
Maurice,

Thank you so much! I tried the line of code you suggested and it worked
perfectly!
You saved the day!
 
Back
Top