How to Open Main and Sub in VBA

[Guest]

Hi,

I'm trying every which was to search for a particular participant on my sub
form and am getting close, but...

The code below is executed after typing in the ppt_no in a small form and
clicking the Search button. It opens the subform I'm looking for, but what
I'd like it to do is open not just the sub-form, but the main form in which
it is usually housed.

The main form has the house address, etc. and the subform has the people who
live in the houses. I have been unable to search for a participant in
particular because it only searches through the people in the currently
displayed house.

I had tried doing something based on a parameter query that contains the
dwellingID and the particpant number (upon which I base the parameter), but
can't figure out how to take the results and say - display that dwelling.
That would be easy, because I would now the ppt is in that dwelling, so could
just scroll through the ppts.

In any case, this is my code for the other way of doing it via a form that
opens other form(s) based on what I input.

Thanks!

rivate Sub cmdSearch_Click()
Dim varWhere As Variant

varWhere = Null

If Not IsNull(Me.txt_ppt_no) Then
varWhere = "[ppt_no] LIKE '" & Me.txt_ppt_no & "*'"
End If

If IsNull(varWhere) Then
MsgBox "You must enter at least one search criteria.",
vbInformation, gstrAppTitle
Exit Sub
End If

If IsNull(DLookup("ppt_no", "tbl_participants", varWhere)) Then
MsgBox "No participant number found.", vbInformation, gstrAppTitle
Exit Sub
End If

DoCmd.OpenForm "sfrm_dwelling_ppts", WhereCondition:=varWhere
' Done
DoCmd.Close acForm, Me.Name

End Sub

 

[Guest]

Dee,

I take it you want to use your search form, to enter a last name, or a first
name, and then figure out which address that applies to, and change current
record of the main form to that house, is that accurate?

What type of control are you using for your search, textbox, combo box,
listbox?
I would recommend a textbox, and would then use a SearchQuery that includes
the address_ID, address fields, and the occupants names. I would filter this
query based on the input in your search text field, and would display the
results (Names and addresses) in a listbox. Once the user selects an item
from the list, I would use the address_ID to find the appropriate address in
the main form and make that the current record.

If you would like additional instructions on how to implement moving to the
correct record or any other aspect of this, post back.

HTH
Dale