Making a Public Function more flexible

[sjs94704]

In my database I have a table of 42,000 ZIP codes with City and State included. Basically, the idea is that once the user selects a ZIP code that the database populates the City and State text boxes with the matching information.

As you can see in the following function, the form name Forms!CarRentalContractF is hard coded into this function.

My desired result is to change this form name into a variable of some kind so that I can use this function anywhere in the database I might need it. Please provide your instructions with your suggestions on how I can alter this function to accomplish my goal.


Public Function DisplayCity()

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * from ZIPCodeQ WHERE ZIPCodeID=" & Forms!CarRentalContractF!ZIPCodeCombo)
Forms!CarRentalContractF!State = rs!StateAbreviation
Forms!CarRentalContractF!City = rs!City
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing


End Function

[RuralGuy]

Perhaps this suggestion from MVP Allen Browne will be useful: http://allenbrowne.com/ser-16.html

[ssanfu]

Since you are writing code, you should learn about passing arguments (variables) to procedures.

http://msdn.microsoft.com/en-us/libr...=vs.60%29.aspx
http://www.cpearson.com/excel/byrefbyval.aspx



Here is the subroutine I came up with:

Public Sub GetCityState(pstrForm As Form, pZip As Long)
   Dim db As DAO.Database
   Dim rs As DAO.Recordset
   Dim frm As Form

   Set db = CurrentDb
   Set frm = pstrForm

   Set rs = db.OpenRecordset("SELECT City, StateAbreviation from ZIPCodeQ WHERE ZIPCodeID=" & pZip)
   If (rs.BOF And rs.EOF) Then
      MsgBox "Zip code - " & pZip & " not found"
   Else
      frm!StateAbreviation = rs!StateAbreviation
      frm!City = rs!City
   End If

   rs.Close
   db.Close
   Set rs = Nothing
   Set db = Nothing
   Set frm = Nothing

End Sub

You call it like this:

Private Sub Combo3_AfterUpdate()
   Call GetCityState(Me, Combo3)
End Sub