Mainform and multiple subforms search...

[lovelydan]

hi all

i need someone to help me with a code that will enable me filter records in a subform form through a combo box attached to a mainform-that has a main menu that controls all the sub form that runs under sourceObject on the mainform unbound frame.

like, when i a company name on mainform combo box then the subform shows only records for that company. eg like how it works when filtering a query..

regards. lovelydan

P.S. subforms runs through a code; Me.Subform.Sour ceObject=""

e.g. Me.Subform.Sour ceObject="Ässet s"

[ChipR]

Hi lovelydan,
If you are setting the .SourceObject in code, you might as well set the .RecordSource also like:

Dim strSQL as String
strSQL = "SELECT * FROM tblMyTable WHERE CompanyName = '" & cmbNameCombo.column(0) & "'"
Me.Subform[B].FORM.[/B]RecordSource = strSQL

[lovelydan]

thanks ChipR

i wil try this and give u my response..

regards, lovely

[NeoPa]

Check out Example Filtering on a Form for how best to use Access to do the work for you.

You can change the RowSource of a form retrospectively , and this can be extremely useful. In most cases though, simply changing the filtering should be all that's required. I suspect this to be so in your case.

[lovelydan]

ChipR

i tried the code and it opens the subform with no records...i tried to look into my tables and they seem to look fine. i dont know where im going wrong...?

what might be the problem.

[lovelydan]

NeoPa

thanks for ur call up...im still looking at ur solution, will get back to you when im done...

regards..lovely Dan

[lovelydan]

NeoPa;

i looked at your post, but after trying it i still get an error message"

The record source ' and([minName]) ' specified on this form or report does not exist.

Run-time error '2580'

here is my scenario again, i have a query; qryEquipmentDet ails and qrylocation with fields as follows;

1. qryLocation
minName(PKey in a table)
district
facility
department
office number

2. qryEquipmentDet ails
serialNumber(PK ey in a table)
category
status
model
make
minName(FKey in a table)

then i have other tables and queries of this nature that i create my other subforms from. i have a main form called frm_MainMenu that has combo boxes; cmbMinistryName s, cmbDistrict, cmbFacility and cmbDepartments. and unbound subform frame that runs subforms as e.g. .sourceObject=" frmSubAssets"
see picture below...



if i chose a search term on the first cmbMinistryName after update the subform
that acts like the source object by that time should show only records for that
search term chosen. e.g in this case if i chose minName form the query the vb code should go to qryEquipmentDet ails and filter records for that minName and opens the subform under source object with only records filtered.

can you help..i can send a sample DB for u to look at if needed.

regards...lovel yDan

[lovelydan]

chipR & NeoPa

i wanted to paste a snapshot of my main form and subform looks like it cant go through...

[NeoPa]

It might be more helpful to post what you're currently using that isn't working.

Just knowing that you have been influenced by some code is not enough to work from. We need code and the line the code failed on with the error message. Without this we really don't know what you're doing so we're not in any position to say what may be going wrong.

[lovelydan]

NeoPa

Private Sub cmbMinistryName_AfterUpdate()
'With Me
 '   If IsNull(.cmbMinistryName) Then
    '    MsgBox "My goodness Tan, is this the best you can think of..."
  '     Else
   ''         With Me!SubForm
     ''               Call .SetFocus
       ''             Call DoCmd.GoToControl _
         ''           ("minName")
           ''         'May possible to replace with:
                        'Call .minName.setfocus
            ''End With
            ''Call DoCmd.FindRecord(.cmbMinistryName)
    ''End If
''End With
Dim strFilter As String
  
    ' This assumes Handle is a text field
    strFilter = "subform![minname] LIKE '" & Me.cmbMinistryName & "*'"
    Me.SubForm.Form.Filter = strFilter
    Me.SubForm.Form.FilterOn = True

End Sub

The above code does not give me an error but it returns the sub form with no records as if i called the gotoAddNewRec command.

i tried both of the codes above then i also tried the following:

Dim strFilter as string

if me!cmbMinistryName >"" Then
     strFilter = strFilter & _
     " AND ([minName]) = " & _
     me!cmbMinistryName & ")"

       me.subform.form.recordsource = strFilter
      ' me!Subform.form.filterOn=True
end if

end sub

This code gives me the following error:

The record source ' and([minName]) ' specified on this form or report does not exist.

Run-time error '2580'


i hope this will make senses to you...

regards...lovel ydan

[ChipR]

Something is a little mixed up. It looks like you have

Me.Subform.Form.RecordSource = strFilter

You have to set the .RecordSource with a select or query statement, then set the .Filter separately.

[lovelydan]

ChipR

may you show me how to do that, just an example, then i can pick it from there

like as if you are amending the code your self, i seem not not get it...i lost. help.


regards..lovely d

[ChipR]

I'm going to assume for now that all of your subforms already are bound to RecordSets (or the same RecordSet), and have the field that you are filtering on, so we'll just set the Filter.

Private Sub cmbMinistryName_AfterUpdate()
  If cmbMinistryName.ListIndex < 0 Then
    Exit Sub
  End If
  Me.SubForm.Form.Filter = "minName = """ & cmbMinistryName & """"
  Me.SubForm.Form.FilterOn = True
End Sub

[lovelydan]

this is what i tried before and it does not give me an error but returns the subform with no records..in addNewRec status..

[lovelydan]

chipR

and i fail to understand were i go wrong...

please help..


lovelydan