How to get default values from a table

[charles.kendricks]

This seems like a simple matter but I am still having trouble. I have
a form that is used to input customer personal data (frmCustInput). I
also have a table which I acquired which lists city, state, county,
area code, etc. for every zip code in the U.S (tblDistinctZipCodes).
Zipcode is the primary key in this table. I want to be able to enter
the zip code for a new customer in the form and have his city, state,
and area code automatically populate their respective fields in the
form. The data from the form is then saved to the customer table
(tblCustomer).

 

[Tom Wickerath]

Hi Charles,

Zipcode is the primary key in this table.

Not sure how that can be, since a given zip code can represent more than one
city. The method I outline includes ZipCode indexed with duplicates OK. Note:
This example is patterned somewhat after the demo database available here:

http://www.fmsinc.com/free/demos/index.html#TotalZipCodeDatabase

however, it is not using the copyright protected code.

Try this procedure as the After_Update event procedure for the zip code text
box. An assumption is made that tblDistinctZipCodes includes a boolean
(Yes/No) field named Primary, which designates the primary city for a given
zip code. Where a given zip code includes more than one city, for example:

ZipCode Primary City State
20024 X Washington DC
20024 Fort Lesley J McNair DC
20024 Fort McNair DC

Only one 20024 record is marked as primary. This becomes the default city.


Option Compare Database
Option Explicit

Private Sub txtZipCode_AfterUpdate()
On Error GoTo ProcError

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb()

strSQL = "SELECT City, State, County " _
& "FROM tblDistinctZipCodes " _
& "WHERE ZipCode = '" & Me.txtZipCode & "' " _
& "ORDER BY Primary DESC;"

Set rs = db.OpenRecordset(strSQL, dbReadOnly)

If rs.RecordCount > 0 Then 'One or more matching records found
Me.cboCity = rs("City")
Me.cboState = rs("State")
Me.cboCounty = rs("County")
End If

ExitProc:
'Cleanup
On Error Resume Next
rs.Close: Set rs = Nothing
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure txtZipCode_AfterUpdate..."
Resume ExitProc

End Sub


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

Hi Charles,

Zipcode is the primary key in this table.

Not sure how that can be, since a given zip code can represent more than one
city. The method I outline includes ZipCode indexed with duplicates OK. Note:
This example is patterned somewhat after the demo database available here:

http://www.fmsinc.com/free/demos/index.html#TotalZipCodeDatabase

however, it is not using the copyright protected code.

Try this procedure as the After_Update event procedure for the zip code text
box. An assumption is made that tblDistinctZipCodes includes a boolean
(Yes/No) field named Primary, which designates the primary city for a given
zip code. Where a given zip code includes more than one city, for example:

ZipCode Primary City State
20024 X Washington DC
20024 Fort Lesley J McNair DC
20024 Fort McNair DC

Only one 20024 record is marked as primary. This becomes the default city.


Option Compare Database
Option Explicit

Private Sub txtZipCode_AfterUpdate()
On Error GoTo ProcError

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb()

strSQL = "SELECT City, State, County " _
& "FROM tblDistinctZipCodes " _
& "WHERE ZipCode = '" & Me.txtZipCode & "' " _
& "ORDER BY Primary DESC;"

Set rs = db.OpenRecordset(strSQL, dbReadOnly)

If rs.RecordCount > 0 Then 'One or more matching records found
Me.cboCity = rs("City")
Me.cboState = rs("State")
Me.cboCounty = rs("County")
End If

ExitProc:
'Cleanup
On Error Resume Next
rs.Close: Set rs = Nothing
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure txtZipCode_AfterUpdate..."
Resume ExitProc

End Sub


PS. I suggest that you not include your real e-mail address in newsgroup
posts. Doing so will only invite the unwanted attention of spammers.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Tom Wickerath]

Hi Charles,

Zipcode is the primary key in this table.

Not sure how that can be, since a given zip code can represent more than one
city. The method I outline includes ZipCode indexed with duplicates OK. Note:
This example is patterned somewhat after the demo database available here:

http://www.fmsinc.com/free/demos/index.html#TotalZipCodeDatabase

however, it is not using the copyright protected code.

Try this procedure as the After_Update event procedure for the zip code text
box. An assumption is made that tblDistinctZipCodes includes a boolean
(Yes/No) field named Primary, which designates the primary city for a given
zip code. Where a given zip code includes more than one city, for example:

ZipCode Primary City State
20024 X Washington DC
20024 Fort Lesley J McNair DC
20024 Fort McNair DC

Only one 20024 record is marked as primary. This becomes the default city.


Option Compare Database
Option Explicit

Private Sub txtZipCode_AfterUpdate()
On Error GoTo ProcError

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String

Set db = CurrentDb()

strSQL = "SELECT City, State, County " _
& "FROM tblDistinctZipCodes " _
& "WHERE ZipCode = '" & Me.txtZipCode & "' " _
& "ORDER BY Primary DESC;"

Set rs = db.OpenRecordset(strSQL, dbReadOnly)

If rs.RecordCount > 0 Then 'One or more matching records found
Me.cboCity = rs("City")
Me.cboState = rs("State")
Me.cboCounty = rs("County")
End If

ExitProc:
'Cleanup
On Error Resume Next
rs.Close: Set rs = Nothing
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure txtZipCode_AfterUpdate..."
Resume ExitProc

End Sub


PS. I suggest that you not include your real e-mail address in newsgroup
posts. Doing so will only invite the unwanted attention of spammers.


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________