Search Subform with combobox

[spartansFC]

Hi

im having a bit of trouble, in that i'm not sure how to do this. I've attached a screen shot of my form, basically, i've set up a combo box in the form header, and i've got it to list all the child's name via a select command:

SELECT ChildFirstName+" " & ChildLastname AS FullName FROM tblChild ORDER BY ChildFirstname, ChildLastname;

but i would like to be able to select that child from the combo box and then the form (all of the form, including subform) jumps to that record.

I've searched and googled around, i know you have to write some code in the afterupdate, and then setfocus, but i'm lost.

any help would be great.

thanks

Mikie

 

[pbaldy]

Try the combo box wizard, choosing the third option "Find a record...". That will build the code for you. You can then modify it to display the way you want.

 

[spartansFC]

Hi Pbaldy

i did what you said and i was able to create a combo box that searched the main form, and even jumped to that record, so then i tried to re-create that by modifying the code, to work on the subform, but it keeps coming up with an error, this is the code i get that the combo box wizard created, this code works as it's only looking on the main form:

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

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

End Sub

i've sort of modified the code, basically changing [ParentId] to [ChildId], it doesn't recognize the ChildId (which is the primary key), but this is stored on the subform, ive tried changing it to tblChild.ChildID, but again it doesn't recognize it, i know its something simple, i just can't see it. The error is a runtime error 13, Type mismatch.

Mikie

 

[boblarson]

If your subforms are linked to your main form properly using the Master / Child links then you don't need this for the subforms. If you move to the correct record on the main form the subforms will automatically follow.

 

[pbaldy]

Presuming the form/subform data is related appropriately, I would keep them in sync with each other using the master/child links, rather than trying to use code. Is that an option here? Is parent the "main" record, or child?

 

[spartansFC]

erm, you've lost me sort of, the relationships are all set up correctly as far as i know, but my database, the parents and the children, may have different surnames etc, so the user may only have the child's name to go off, so thats why i'm trying to set up a search combo box, to search the subform for the childsname. Ive set the row source up on the combo box as:

SELECT ChildFirstName+" " & ChildLastname AS FullName FROM tblChild ORDER BY ChildFirstname, ChildLastname;

so i'm not sure what you mean now, sorry, i'm no good at the technical side of access yet, how do i tell the combo box to search on the subform fields?

Mikie

 

[boblarson]

The LINK would be the field that ties the child together with the parent.

 

[spartansFC]

i've sorted it, sort of , i've got it to search the subform now.

i changed the rowsource on the combo box to:

SELECT tblChild.ChildID, tblChild.ChildFirstName, tblChild.ChildLastname FROM tblChild;

instead of

SELECT tblChildFirstName+" " & ChildLastname AS FullName FROM tblChild ORDER BY ChildFirstname, ChildLastname;

and it works, i've even tweaked the original row source line, so that it works with the original FullName SELECT command.

so that it looks like (if your interested)

SELECT tblChild.ChildId, tblChild.ChildFirstName+" " & tblChild.ChildLastname AS FullName FROM tblChild ORDER BY tblChild.ChildFirstname, tblChild.ChildLastname;

thanks again

Mikie

 

[spartansFC]

I knew i was getting excited too soon, a problem has come up. If a parent has 2 children, access doesn't link properly to that record, im so close to cracking this, below is what the data looks like on tblChild

Child ID ParentId Forename Surname
378...........45.............John.........Smith
45.............45............Jane..........Smith

how isn't it linking to the correct record, when there's 2 children

any ideas

Mikie

 

[spartansFC]

I know what access is doing, and i think i'm not using the correct field.

Child ID ParentId Forename Surname
378...........45.............John.........Smith
45.............45............Jane..........Smith

In the example above, if i was trying to select John Smith (who is a child), access would show me the record that relates to ParentId 378, so erm, what am i doing wrong?

Mikie

 

[spartansFC]

So has anyone got any ideas, if there are 2 children listed on tblChild, how to i set focus on the 2nd childs ChildID, as in the example below:

Child ID...ParentId......Forename.....Surname
378...........45.............John.........Smith
45.............45............Jane..........Smith

like i said, if i click on John Smith, who has a ChildID on 378, access takes me to record ParentId 378, is this a problem with the select statement, or the code in the afterevent?

Mikie

 

[spartansFC]

Come on guys, can anyone help me out with my subform search problem?

Mikie