Sytax help for refering to control on subform

[Kranman]

Hi All,

Love this site, have gotten a lot from it. This is my first time
posting though, so forgive me for any errors.

I have an Access 2000 db where I have a main form of Contractors and on
the main form is a subform of bonds carried by those contractors. I am
trying to reference a field named "Bond" located on the subform in
code that is run from the mainform and am having trouble with the
syntax for this. Below is a sample of just some of the code that I
have tried.

If Me.FraSearch.Value = "1" Then
strField =
"[Forms]![frmCustomerBonds]![frmBondsSubform]![BondNum] = '" &
Me![txtLookup] & "'"
ElseIf Me.FraSearch.Value = "2" Then
strField = "[LiscNum] = '" & Me![txtLookup] & "'"
Else
strField = "[LastName] = '" & Me![txtLookup] & "'"
End If

By the way the strField references in the 2nd and 3rd then statement
of the above code work, it is the first Then statement I am having
trouble with. Would be glad for any assistance as I am loosing hair by
the handful on this one.

[Kranman]

It may help if I mention that strField is used as the criteria of
FindFirst, code as follows
rs.FindFirst strField

Thanks again in advance

[Larry Linson]

The syntax for referencing a Control in a Form embedded in a Subform Control
from VBA code attached to the main form is:

Me!SubformControlName.Form!ControlNameinSubform

That does not appear to be what you are using. Note that you do not refer to
the Form in the Subform Control, but to the Form _property_ of the Subform
Control.

That will access whichever is the currently-selected Record in the Form
embedded in the Subform Control. That may not be the one you want and also
may not be obvious if it is in continuous-form view with multiple records
displayed. Thus, if you need to access something other than the
currently-selected Record, or if the user might possibly click and select a
different Record, then you may want to rethink your approach to the problem.

Larry Linson
Microsoft Access MVP

"Kranman" <st***@calaoms.org> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Hi All,

Love this site, have gotten a lot from it. This is my first time
posting though, so forgive me for any errors.

I have an Access 2000 db where I have a main form of Contractors and on
the main form is a subform of bonds carried by those contractors. I am
trying to reference a field named "Bond" located on the subform in
code that is run from the mainform and am having trouble with the
syntax for this. Below is a sample of just some of the code that I
have tried.

If Me.FraSearch.Value = "1" Then
strField =
"[Forms]![frmCustomerBonds]![frmBondsSubform]![BondNum] = '" &
Me![txtLookup] & "'"
ElseIf Me.FraSearch.Value = "2" Then
strField = "[LiscNum] = '" & Me![txtLookup] & "'"
Else
strField = "[LastName] = '" & Me![txtLookup] & "'"
End If

By the way the strField references in the 2nd and 3rd then statement
of the above code work, it is the first Then statement I am having
trouble with. Would be glad for any assistance as I am loosing hair by
the handful on this one.

[Kranman]

Larry,

Thanks for the reply it helps a little. As I mentioned above I had
tried several different ways to structure the syntax for this. I
believe that I even tried what you have provided (which I had found
while searching different strings prior to posting). After setting the
syntax back to your example, I still receive an error when running the
code that states " The Microsoft Jet database engine does not
recognize 'Me!SubBonds.Form!BondNum' as a valid field name or
expression". So I set up a button on the form to check the syntax by
setting the BondNum control's visible property to False on the
subform. As I suspected the button works, so the syntax is correct.
So what I thought was a syntax error, is now a problem with my code. So
I am providing the entire procedure below in the hope that my error is
spotted.

What I would like is for the user to click in a search box , after
select the License check box to find a contractor by license number,
then clear out the search box input a bond number and find a bond on
the sub form related to the contractor( as some contractors have
numerous bonds). Once again the procedure works for fields that are
located on the main form. The problem arises when searching for a
record on the subform.

Private Sub btnFind_Click()

' Find the record that matches the control.
Dim rs As Object
Dim strField As String
' Determine Field to Search By
If Me.FraSearch.Value = "1" Then
strField = "Me!subBonds.Form![BondNum] = '" & Me![txtLookup] &
"'"
Set rs = Me!subBonds.Form.Recordset.Clone
ElseIf Me.FraSearch.Value = "2" Then
strField = "[LiscNum] = '" & Me![txtLookup] & "'"
Set rs = Me.Recordset.Clone
Else
strField = "[LastName] = '" & Me![txtLookup] & "'"
Set rs = Me.Recordset.Clone
End If

' Search Customer Bond Form for select value
If IsNull(Me.txtLookup.Value) Then
MsgBox "You have not entered a value to search by",
vbExclamation, "CBS Search Error"
Me.txtLookup.SetFocus
Exit Sub
Else
rs.FindFirst strField
If rs.NoMatch Then
MsgBox "No Record Found for " & Me.txtLookup,
vbExclamation, "CBS Serch Error"
Else
Me.Bookmark = rs.Bookmark
End If
End If

End Sub