Module need to apply for a text field on the form, please help

[khicon73]

I had a module and named “modCheckAddress”.
Below is the code:

Option Compare Database
Option Explicit
Private rst As ADODB.Recordset
Private con As ADODB.Connection
Private strCon As String

Function CheckAddress(strAddress As String, strCity As String, strZip As String) As String
On Error GoTo ErrorFunction
Set con = New ADODB.Connection
strCon = "Provider=sqloledb;Data Source=HCWebSQL; Initial Catalog=AnimalControl;User ID=AnimalControl;Password=sswbi62;"
strCon = "DRIVER={SQL Server};SERVER=tijghis;DATABASE=t9eo;UID=vghfj;PWD=jjgfk"
con.Open strCon
Set rst = New ADODB.Recordset
Set rst.ActiveConnection = con
rst.Source = "EXEC proc_ValidateAddress '" & strAddress & "', '" & strCity & "', '" & strZip & "'"
rst.CursorType = adOpenStatic
rst.LockType = adLockReadOnly
rst.Open
CheckAddress = ""
Do While rst.EOF = False
CheckAddress = rst!Map
rst.MoveNext
Loop
rst.Close
con.Close
ExitFunction:
Set rst = Nothing
Set con = Nothing
Exit Function
ErrorFunction:
CheckAddress = ""
GoTo ExitFunction
End Function

This is my query viewed in “SQL view”:

SELECT tblTable.CaseID, tblTable.Date_Inserted, tblTable.Address, tblTable.City, tblTable.Zip, CheckAddress([Address],IIf([City] Is Null,"",[City]),IIf([Zip] Is Null,"",[Zip])) AS OriginalAdd
FROM tblTable;

The module “modCheckAddress” is a module use the code for the query above. It worked and applied for all records in that query. How can I use something similar to update a field on a form for a single record?

I have a form named “frmCheckAddress” and text fields named “txtCaseID”, “txtAddress”, “txtCity”, “txtZip”, all these text fields need to be filled in and required. “CaseID” is a unique key in the table.
Please help, I’m very appreciated. Thank you very much.

 

[PHV]

Have a look at the DLookUp and Nz functions.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886