Referencing subform during module code

[BerkshireGuy]

I have a form that contains two subforms. Both of these subforms use
the same function.

Rather than repeating the code, I want to be able to pass the form name
to the code. Right now, its a module, but I guess it could go at the
form level. I rather keep it in a module in case I ever have other
forms that would use the code.

The code is :

Public Function LookUpZip(strZip As String, strFormName)
Dim db As DAO.Database, rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT ZIP, CITY, STATE FROM tblZipCodes
WHERE ZIP = '" & strZip & "'")

If rs.RecordCount = 0 Then
MsgBox "No Matching Zip Codes Could Be Found. Please enter
information manually."
Forms(strFormName).txtContactCity.SetFocus
Else
Forms(strFormName).txtContactCity = rs("City")
Forms(strFormName).txtContactStateOrProvince = rs("State")
End If

Set rs = Nothing
Set db = Nothing
End Function

This would work at the form level, but not at the subform. I need to
reference the subform.

Could someone please help me out?

Thanks

[RoyVidar]

BerkshireGuy wrote in message
<11**********************@g47g2000cwa.googlegroups .com> :

I have a form that contains two subforms. Both of these subforms use
the same function.

Rather than repeating the code, I want to be able to pass the form name
to the code. Right now, its a module, but I guess it could go at the
form level. I rather keep it in a module in case I ever have other
forms that would use the code.

The code is :

Public Function LookUpZip(strZip As String, strFormName)
Dim db As DAO.Database, rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT ZIP, CITY, STATE FROM tblZipCodes
WHERE ZIP = '" & strZip & "'")

If rs.RecordCount = 0 Then
MsgBox "No Matching Zip Codes Could Be Found. Please enter
information manually."
Forms(strFormName).txtContactCity.SetFocus
Else
Forms(strFormName).txtContactCity = rs("City")
Forms(strFormName).txtContactStateOrProvince = rs("State")
End If

Set rs = Nothing
Set db = Nothing
End Function

This would work at the form level, but not at the subform. I need to
reference the subform.

Could someone please help me out?

Thanks

In stead of passing the form name - which might give a bit more
challenges, pass the form, and refer through it.

Public Function LookUpZip(strZip As String, frm as form)
....
frm!txtContactCity = rs("City")
....

Then call it with

Call LookUpZip(strYourZip, Me)

Also - since you're not returning anything, convert to a sub?

--
Roy-Vidar

[salad]

BerkshireGuy wrote:

I have a form that contains two subforms. Both of these subforms use
the same function.

Rather than repeating the code, I want to be able to pass the form name
to the code. Right now, its a module, but I guess it could go at the
form level. I rather keep it in a module in case I ever have other
forms that would use the code.

The code is :

Public Function LookUpZip(strZip As String, strFormName)
Dim db As DAO.Database, rs As DAO.Recordset

Set db = CurrentDb()
Set rs = db.OpenRecordset("SELECT ZIP, CITY, STATE FROM tblZipCodes
WHERE ZIP = '" & strZip & "'")

If rs.RecordCount = 0 Then
MsgBox "No Matching Zip Codes Could Be Found. Please enter
information manually."
Forms(strFormName).txtContactCity.SetFocus
Else
Forms(strFormName).txtContactCity = rs("City")
Forms(strFormName).txtContactStateOrProvince = rs("State")
End If

Set rs = Nothing
Set db = Nothing
End Function

This would work at the form level, but not at the subform. I need to
reference the subform.

Could someone please help me out?

Thanks

You might be better off passing the form instead of the name. Ex:
LookUpZip "12345", Me

You could have some code to determine if the form is a subform
Public Function IsSubform(frm As Form)
Dim strName As String
On Error Resume Next
' This action will trigger a runtime
' error if the form isn't loaded as a subform.
strName = frm.Parent.name
IsSubform = (Err = 0)
End Function

Public Sub LookUpZip(strZip As String, frm As Form)
Dim strName As String
Dim strSub As String
If IsSubform(frm) Then
strSub = frm.Name
strName = frm.Parent.Name
else
strName = frm.Name
endif
If strSub = "" Then
Forms(strName).txtFld = rs("City")
Forms(strName).txtState = rs("State")
Else
Forms(strName)(strSub).txtFld = rs("City")
Forms(strName)(strSub).txtState = rs("State")
Endif
End Sub

[Lyle Fairfield]

Any form used in anyway can be referenced as Form_formname if it has
its HasModule property set to true as in:
Form_formname.txtContactCity.Value=