Dual tables in form

[Karl H]

rHi,
I have a form with demographics based on one table, but I would like to
incorporate another table of user-defined city & zip codes so the
demographics form can "auto-fill" the state & Zip, but have the data entered
in the demographics table.

I thought I could put in some code, "after update" that looks for the city
on the current form and compares it to the city in tblCity. If the city is
found, it would fill in the State & Zipcode fields in the demographics form
with the corresponding data from tblCity.

Private Sub txtCity_AfterUpdate()
Dim cInt As Interger
If txtCity = Tables.tblCity.FindFirst.[City] Then
cInt = [ID] (of same recordset for this table)
txtState = Tables.tblCity.[State]
txtZip = Tables.tblCity.[Zip]
Else exit sub
End if
End Sub

I know I'm missing some important means of linking the recordset of the
second table.

Thank you in advance for your help!
Karl

 

[Douglas J. Steele]

You sure this'll work? I thought some cities had multiple zip codes...

If there was only one, you could use the following:

Private Sub txtCity_AfterUpdate()

Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT State, Zip FROM tblCity WHERE City = '" & txtCity & "'"
Set rsCurr = CurrentDb().OpenRecordset(strSQL)
If rsCurr.EOF = False Then
txtState = rsCurr!State
txtZip = rsCurr!Zip
End If

End Sub

 

[Karl H]

Hi Doug,
That looks good, but it didn't work. No error messages popped up, but the
other fields didn't populate. Do you see any faults in the code?

'Links some zip codes to cities
Private Sub txtCity_AfterUpdate()
Dim rsCurr As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT State, Zip FROM tblCity WHERE City = '" & txtCity & "'"
Set rsCurr = CurrentDb().OpenRecordset(strSQL)
If rsCurr.EOF = False Then
txtState = rsCurr!State
txtZip = rsCurr!Zip
End If
End Sub

Thank you!
Karl

You sure this'll work? I thought some cities had multiple zip codes...

If there was only one, you could use the following:

Private Sub txtCity_AfterUpdate()

Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT State, Zip FROM tblCity WHERE City = '" & txtCity & "'"
Set rsCurr = CurrentDb().OpenRecordset(strSQL)
If rsCurr.EOF = False Then
txtState = rsCurr!State
txtZip = rsCurr!Zip
End If

End Sub



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

rHi,
I have a form with demographics based on one table, but I would like to
incorporate another table of user-defined city & zip codes so the
demographics form can "auto-fill" the state & Zip, but have the data
entered
in the demographics table.

I thought I could put in some code, "after update" that looks for the city
on the current form and compares it to the city in tblCity. If the city is
found, it would fill in the State & Zipcode fields in the demographics
form
with the corresponding data from tblCity.

Private Sub txtCity_AfterUpdate()
Dim cInt As Interger
If txtCity = Tables.tblCity.FindFirst.[City] Then
cInt = [ID] (of same recordset for this table)
txtState = Tables.tblCity.[State]
txtZip = Tables.tblCity.[Zip]
Else exit sub
End if
End Sub

I know I'm missing some important means of linking the recordset of the
second table.

Thank you in advance for your help!
Karl

 

[Karl H]

My fault...You ARE THE MAN!
I didn't have my text fields labeled correct. I live in an area where the
cities are kind of small, but you bring up good point about the possible
futility of this procedure.

I get pretty confused about the quotes in SQL--do you have a good URL
reference for them?

Thanks again!
Karl

You sure this'll work? I thought some cities had multiple zip codes...

If there was only one, you could use the following:

Private Sub txtCity_AfterUpdate()

Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT State, Zip FROM tblCity WHERE City = '" & txtCity & "'"
Set rsCurr = CurrentDb().OpenRecordset(strSQL)
If rsCurr.EOF = False Then
txtState = rsCurr!State
txtZip = rsCurr!Zip
End If

End Sub



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

rHi,
I have a form with demographics based on one table, but I would like to
incorporate another table of user-defined city & zip codes so the
demographics form can "auto-fill" the state & Zip, but have the data
entered
in the demographics table.

I thought I could put in some code, "after update" that looks for the city
on the current form and compares it to the city in tblCity. If the city is
found, it would fill in the State & Zipcode fields in the demographics
form
with the corresponding data from tblCity.

Private Sub txtCity_AfterUpdate()
Dim cInt As Interger
If txtCity = Tables.tblCity.FindFirst.[City] Then
cInt = [ID] (of same recordset for this table)
txtState = Tables.tblCity.[State]
txtZip = Tables.tblCity.[Zip]
Else exit sub
End if
End Sub

I know I'm missing some important means of linking the recordset of the
second table.

Thank you in advance for your help!
Karl

 

[Douglas J. Steele]

Glad you've got it working.

Afraid I don't really have a good reference about quotes in SQL, although my
May, 2004 "Access Answers" column in Pinnacle Publication's "Smart Access"
may help a little. You can download the column (and sample database) for
free at http://www.accessmvp.com/DJSteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

My fault...You ARE THE MAN!
I didn't have my text fields labeled correct. I live in an area where the
cities are kind of small, but you bring up good point about the possible
futility of this procedure.

I get pretty confused about the quotes in SQL--do you have a good URL
reference for them?

Thanks again!
Karl

You sure this'll work? I thought some cities had multiple zip codes...

If there was only one, you could use the following:

Private Sub txtCity_AfterUpdate()

Dim rsCurr As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT State, Zip FROM tblCity WHERE City = '" & txtCity &
"'"
Set rsCurr = CurrentDb().OpenRecordset(strSQL)
If rsCurr.EOF = False Then
txtState = rsCurr!State
txtZip = rsCurr!Zip
End If

End Sub



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

rHi,
I have a form with demographics based on one table, but I would like to
incorporate another table of user-defined city & zip codes so the
demographics form can "auto-fill" the state & Zip, but have the data
entered
in the demographics table.

I thought I could put in some code, "after update" that looks for the
city
on the current form and compares it to the city in tblCity. If the city
is
found, it would fill in the State & Zipcode fields in the demographics
form
with the corresponding data from tblCity.

Private Sub txtCity_AfterUpdate()
Dim cInt As Interger
If txtCity = Tables.tblCity.FindFirst.[City] Then
cInt = [ID] (of same recordset for this table)
txtState = Tables.tblCity.[State]
txtZip = Tables.tblCity.[Zip]
Else exit sub
End if
End Sub

I know I'm missing some important means of linking the recordset of the
second table.

Thank you in advance for your help!
Karl