Re: Referencing subform during module code

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


.

Relevant Pages

  • Re: INSERT INTO statement
    ... Dim StrSQL as String ... Dim strName As String ... O Wilson wrote: ...
    (microsoft.public.access.gettingstarted)
  • Re: INSERT INTO statement
    ... get treated as if it were a division problem. ... Dim StrSQL as String ... Dim strName As String ...
    (microsoft.public.access.gettingstarted)
  • Re: make backup of table before edit
    ... Assuming the table you want to copy is named "Employees", ... Dim strName as String ...
    (microsoft.public.access.modulesdaovba)
  • Re: Name checking
    ... Dim strName As String ... MsgBox strname & " not found!" ...
    (microsoft.public.excel.programming)
  • Re: Check last character
    ... I am assuming that you are checking the last character of the docVariable ... Dim strName As String ...
    (microsoft.public.word.vba.general)