Speedup Data Entry of City State Zip Code Using A Dlookup
August 8, 2009 | 9:54 amMichael Haag offers a tip this week on filling in the city and state after entering a zip code on a data entry MS Access form. Data is stored in a ‘Zipcodes’ table that contains 3 states for the New York area with a Dlookup function to retrieve the city and State.
Michael states “I think one of the main advantages is: The information doesn’t have to be stored in the “Customer” table, so it doesn’t take up space.” for Microsoft Access display. I think the main advantages are speed of data entry and elimination of spelling errors during data entry. MS Access allows for a Dlookup fuction to bring back data from a table or query.
He places in the control source property of the ‘City’ text box this:
=Dlookup(“[city]“,”zipcodes”,”zipcodes= ‘” & [Forms]![frmcustomer]![zipcode] & ” ‘ “)
He places in the control source property of the ‘State’ text box this:
=Dlookup(“[State]“,”zipcodes”,”zipcodes= ‘” & [Forms]![frmcustomer]![zipcode] & ” ‘ “)
An alternate approach would use the ‘After Update’ method of the ‘Zipcode’ form control with this code:
Private Sub ZipCode_AfterUpdate()
If IsNull(Me.City) Then
Me.City = Dlookup(“[city]“, “zipcodes”, “zipcodes= ‘” & [ZipCode] & ” ‘ “)
End If
If IsNull(Me.State) Then
Me.State = Dlookup(“[State]“, “zipcodes”, “zipcodes= ‘” & [ZipCode] & ” ‘ “)
End If
End Sub
This second solution would have the City and State form controls bound to the Customer table or the code could be placed in the On Current Event of the form to handle unbound controls for the City and State Microsoft Access display of data.
Both approaches still require the maintenance of the Zipcode table. Perhaps an easy to maintain solution would involve changing the Zipcode form control to a combo box which is feed by a Group By query (See below) that is based on all prior Zip codes, Cities, and States entered in the Customer table (Not limited to list.) This has the advantage of no maintenance of a Zipcode table and speed of data entry, however it can potentially have two or more Zip, City, State combinations of misspelled City or State names.
The combo box SQL would be:
SELECT Customer.ZipCode, Customer.City, Customer.State
FROM Customer
GROUP BY Customer.ZipCode, Customer.City, Customer.State;
The after update for the combo box would be changed to:
Private Sub ZipCode_AfterUpdate()
If IsNull(Me.City) Then
Me.City = ZipCode.column(1)
End If
If IsNull(Me.State) Then
Me.State = ZipCode.column(2)
End If
End Sub
Try out one of these solutions and please enter your feedback in the comments of this blog posting (Below).
Thank you Michael for your tip and article suggestion.
Great idea!
This makes it much easier to enter the correct address info.
Where did you find the data for the zip code table?
A client had a list limited to NY and I don’t know where he got the zip code data. One of the other methods I mentioned will start accumulating a list for you.
We use a combo box for CITY named CITYNAME that retrieves its information from a CITY table. Using the ‘On Not in List’ event triggers the following code.
Private Sub CityName_NotInList(NewData As String, Response As Integer)
Response = AddValue(NewData, “City”, “CityName”, “City”)
End Sub
which in turn calls the Function AddValue listed below.
Function AddValue(NewData As String, sTableName As String, sFieldName, sFormName As String, Optional sFieldName2 As String = “”, Optional sNewData2 As String = “”) As Integer
‘ Comments : adds a value to a table once the OnNotInList property has been flagged on Combo Box
‘ Parameters: NewData – Value user has entered that is not currently in the list
‘ sTableName – Table that the value should be entered in
‘ sFieldName – Field name for the particular table
‘ sFieldName2 – Field (second) name for the particular table
‘ sNewData2 – second value to add to the second field
‘ sFormName – Corresponding maintenance form name
‘ Returns : Response constant
‘ acDataErrAdded – the value has been added, requery the Combo Box
‘ acDataErrContinue – user must select an existing value or leave the control empty (null)
Dim db As Database
Dim rsRecSet As Recordset
Dim nAnswer As Integer
Dim sMsg As String
Dim nReply As Integer
Dim tdf As TableDef
Dim fld As Field
Const conErrFieldToSmall = 3163
On Error GoTo CheckError
nAnswer = OpenMsgBox(NewData & ” does not exist.” & Chr(10) & “Would you like to add ” & NewData & ” to the list?”, vbQuestion + vbYesNo)
If nAnswer = vbYes Then
Set db = CurrentDb
Set rsRecSet = db.OpenRecordset(sTableName, dbOpenDynaset, dbAppendOnly)
rsRecSet.AddNew
rsRecSet(sFieldName) = NewData
If sFieldName2 “” Then
rsRecSet(sFieldName2) = sNewData2
End If
rsRecSet.Update
rsRecSet.Close
DoCmd.OpenForm (sFormName), , , , , , NewData
DoCmd.FindRecord (NewData)
AddValue = acDataErrAdded
Else
AddValue = acDataErrContinue
End If
Err_Handling_Exit:
Exit Function
CheckError:
If (Err.Number = conErrFieldToSmall) Then
Set db = CurrentDb()
Set tdf = db.TableDefs(sTableName)
Set fld = tdf.Fields(sFieldName)
sMsg = “The value ” & NewData & ” is longer than the allowable number of characters and cannot be inserted into the field.” & vbCrLf & “Please enter a code which is at most ” & fld.Size & ” characters.”
nReply = OpenMsgBox(sMsg, vbCritical, “Data Error”)
Resume Err_Handling_Exit
Else
sMsg = “An Error Has Occured. A value cannot be added at this time.”
nReply = OpenMsgBox(sMsg, vbExclamation)
Resume Err_Handling_Exit
End If
End Function
This function asks if the user wants to add the new city to the CITY table.
Now this is the group interaction I like to see on these posting.
Thanks
Bob