Using value on subform to find record on parent form

  • Thread starter Thread starter shaggles
  • Start date Start date
S

shaggles

Is it possible to use a combo box on a subform to locate a
record on the parent form? I have a check ordering form
on my database. The main form contains all the client
info and all the check info is on the subform. I want to
be able to locate a record by a field on the subform
(check number.) I tried 'Me" in the code with reference
to the subform like this -
Forms!Check_Order!Subform.Check_Sub!
Check_Number.RecordsetClone.FindFirst "[SS] = '" & Me!
[Combo12] & "'"
Forms!Check_Order!Subform.Check_Sub!
Check_Number.Bookmark = Me.RecordsetClone.Bookmark -
but it doesn't pull up the right record.
 
Is it possible to use a combo box on a subform to locate a
record on the parent form? I have a check ordering form
on my database. The main form contains all the client
info and all the check info is on the subform. I want to
be able to locate a record by a field on the subform
(check number.) I tried 'Me" in the code with reference
to the subform like this -
Forms!Check_Order!Subform.Check_Sub!
Check_Number.RecordsetClone.FindFirst "[SS] = '" & Me!
[Combo12] & "'"
Forms!Check_Order!Subform.Check_Sub!
Check_Number.Bookmark = Me.RecordsetClone.Bookmark -
but it doesn't pull up the right record.
Click to expand...

I can't quite figure whether you are trying to find a record in the main form
from a combobox selection on the subform, or, as your code indicates, find a
record in the subform from a combobox selection on the subform, so I'll simply
address your syntax based on your existing code. To provide a full reference to
a control on a subform, you need to use the following syntax:

Forms![MainFormName].[SubformControlName].Form.ControlName

Where "MainFormName" is the name of the main form, "SubformControlName" is the
name of the subform *control* on the main form (not the name of the subform,
although both may share the same name) and "ControlName" is the name of the
control on the subform. You can shorten your references to the form's
recordsetclone property using "With ... End With":

'***Modified version of your code
With Me.RecordsetClone
'Assuming the search value is a string
.FindFirst "[SS] = '" & Me![Combo12] & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
'***

If you are, in fact, searching the main form's recordset for the value selected
in the subform control, then you can replace the first and last instance of "Me"
in the above example (*don't* change that reference in the "FindFirst" line)
with "Me.Parent".
 
Thanks Bruce. I was trying to find a record on the parent
form. I'm just not very good with vb.
-----Original Message-----
Is it possible to use a combo box on a subform to locate a
record on the parent form? I have a check ordering form
on my database. The main form contains all the client
info and all the check info is on the subform. I want to
be able to locate a record by a field on the subform
(check number.) I tried 'Me" in the code with reference
to the subform like this -
Forms!Check_Order!Subform.Check_Sub!
Check_Number.RecordsetClone.FindFirst "[SS] = '" & Me!
[Combo12] & "'"
Forms!Check_Order!Subform.Check_Sub!
Check_Number.Bookmark = Me.RecordsetClone.Bookmark -
but it doesn't pull up the right record.
Click to expand...

I can't quite figure whether you are trying to find a record in the main form
from a combobox selection on the subform, or, as your code indicates, find a
record in the subform from a combobox selection on the subform, so I'll simply
address your syntax based on your existing code. To provide a full reference to
a control on a subform, you need to use the following syntax:

Forms![MainFormName].[SubformControlName].Form.ControlName

Where "MainFormName" is the name of the main
Click to expand...
form, "SubformControlName" is the
name of the subform *control* on the main form (not the name of the subform,
although both may share the same name) and "ControlName" is the name of the
control on the subform. You can shorten your references to the form's
recordsetclone property using "With ... End With":

'***Modified version of your code
With Me.RecordsetClone
'Assuming the search value is a string
.FindFirst "[SS] = '" & Me![Combo12] & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
'***

If you are, in fact, searching the main form's recordset for the value selected
in the subform control, then you can replace the first and last instance of "Me"
in the above example (*don't* change that reference in the "FindFirst" line)
with "Me.Parent".

--
Bruce M. Thompson, Microsoft Access MVP
(e-mail address removed) (See the Access FAQ at http://www.mvps.org/access)within the newsgroups so that all might benefit.<<


.
Click to expand...
 
Back
Top