Use combo box to select record on main and sub forms

G

Guest

Using Access 2003, I created a main form (frmMainForm) that displays “Momâ€
data pulled from tblParent. The form has a subform (subfrmChild) that lists
her children pulled from tblChild. The subform’s default view is set to
“single formâ€. The subform has 5 tabs with some of the tabs containing
subforms that display data per the selected child. TblParent and tblChild
are related through the MomID.

In the Form Header of frmMainForm, I inserted a combo box using the wizard
and chose “Find a record on my form based on…†and just chose MomID, the
primary key from tblParent. After the combo box was created, I clicked the
build button on the Row Source property and added tblChild to the grid. From
there I added the ChildID, ChildDOB, and a calculated field that combines the
ChildLastName and ChildFirstName fields.

When I select a name from this combo box, it displays the correct Mom in
frmMainForm, but it always goes to her first child record in subfrmChild. So
I always have to then go to the subform and select the child I want. I am
looking for code that will not only select the Mom but also display the
correct child record in the subform. Is that possible?

The After Update event procedure on the Combo Box has the following code (I
added Combo55=Ҡto clear the name out of the combo box once the record has
been chosen):

Private Sub Combo55_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MomID] = " & Str(Nz(Me![Combo55], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Combo55 = ""
End Sub
 
S

strive4peace

Hi Connie,

firstly, change
Combo55 = ""
to
Combo55 = null

to find the record in the subform, try this:

'---------------------
dim mChildID as long
mChildID = me.combo55.column(#)
Me.subform_controlname.form.RecordsetClone.FindFirst
"[ChildID] = " & mChildID

If Not Me.subform_controlname.form.RecordsetClone.NoMatch Then
Me.subform_controlname.form.Bookmark =
Me.subform_controlname.form.RecordsetClone.Bookmark
Exit Function
End If
'---------------------

where # is the column number -1 since column indexing starts
with 0, not 1

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)
 
G

Guest

Crystal,
Thank you so much. That worked perfectly.
Connie

strive4peace said:
Hi Connie,

firstly, change
Combo55 = ""
to
Combo55 = null

to find the record in the subform, try this:

'---------------------
dim mChildID as long
mChildID = me.combo55.column(#)
Me.subform_controlname.form.RecordsetClone.FindFirst
"[ChildID] = " & mChildID

If Not Me.subform_controlname.form.RecordsetClone.NoMatch Then
Me.subform_controlname.form.Bookmark =
Me.subform_controlname.form.RecordsetClone.Bookmark
Exit Function
End If
'---------------------

where # is the column number -1 since column indexing starts
with 0, not 1

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)

Using Access 2003, I created a main form (frmMainForm) that displays “Momâ€
data pulled from tblParent. The form has a subform (subfrmChild) that lists
her children pulled from tblChild. The subform’s default view is set to
“single formâ€. The subform has 5 tabs with some of the tabs containing
subforms that display data per the selected child. TblParent and tblChild
are related through the MomID.

In the Form Header of frmMainForm, I inserted a combo box using the wizard
and chose “Find a record on my form based on…†and just chose MomID, the
primary key from tblParent. After the combo box was created, I clicked the
build button on the Row Source property and added tblChild to the grid. From
there I added the ChildID, ChildDOB, and a calculated field that combines the
ChildLastName and ChildFirstName fields.

When I select a name from this combo box, it displays the correct Mom in
frmMainForm, but it always goes to her first child record in subfrmChild. So
I always have to then go to the subform and select the child I want. I am
looking for code that will not only select the Mom but also display the
correct child record in the subform. Is that possible?

The After Update event procedure on the Combo Box has the following code (I
added Combo55=Ҡto clear the name out of the combo box once the record has
been chosen):

Private Sub Combo55_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MomID] = " & Str(Nz(Me![Combo55], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Combo55 = ""
End Sub
Click to expand...
Click to expand...
 
S

strive4peace

you're welcome, Connie ;) happy to help

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)

Crystal,
Thank you so much. That worked perfectly.
Connie

:

Hi Connie,

firstly, change
Combo55 = ""
to
Combo55 = null

to find the record in the subform, try this:

'---------------------
dim mChildID as long
mChildID = me.combo55.column(#)
Me.subform_controlname.form.RecordsetClone.FindFirst
"[ChildID] = " & mChildID

If Not Me.subform_controlname.form.RecordsetClone.NoMatch Then
Me.subform_controlname.form.Bookmark =
Me.subform_controlname.form.RecordsetClone.Bookmark
Exit Function
End If
'---------------------

where # is the column number -1 since column indexing starts
with 0, not 1

Warm Regards,
Crystal
MVP Microsoft Access

remote programming and training
strive4peace2006 at yahoo.com
*
Have an awesome day ;)

Using Access 2003, I created a main form (frmMainForm) that displays “Momâ€
data pulled from tblParent. The form has a subform (subfrmChild) that lists
her children pulled from tblChild. The subform’s default view is set to
“single formâ€. The subform has 5 tabs with some of the tabs containing
subforms that display data per the selected child. TblParent and tblChild
are related through the MomID.

In the Form Header of frmMainForm, I inserted a combo box using the wizard
and chose “Find a record on my form based on…†and just chose MomID, the
primary key from tblParent. After the combo box was created, I clicked the
build button on the Row Source property and added tblChild to the grid. From
there I added the ChildID, ChildDOB, and a calculated field that combines the
ChildLastName and ChildFirstName fields.

When I select a name from this combo box, it displays the correct Mom in
frmMainForm, but it always goes to her first child record in subfrmChild. So
I always have to then go to the subform and select the child I want. I am
looking for code that will not only select the Mom but also display the
correct child record in the subform. Is that possible?

The After Update event procedure on the Combo Box has the following code (I
added Combo55=Ҡto clear the name out of the combo box once the record has
been chosen):

Private Sub Combo55_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[MomID] = " & Str(Nz(Me![Combo55], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Combo55 = ""
End Sub
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need combo box to refresh when moving to new record 2017 1
How to set different combo box values for new record? 6
Main form Combobox and subform 2
Filter subform with combo box on main form 1
Change Combo Box on Sub 9
Prevent duplicate selection from combo box on continuous form 4
Combo Box to select a record and populate fields in a form 2
Select Multiple columns in combo box 1

Top