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
 
Ad

Advertisements

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
 
Ad

Advertisements

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
 

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

Top