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.SourceObject=""

e.g. Me.Subform.SourceObject="Ässets"

[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.FORM.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..lovelyDan

[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; qryEquipmentDetails and qrylocation with fields as follows;

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

2. qryEquipmentDetails
serialNumber(PKey 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; cmbMinistryNames, 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 qryEquipmentDetails 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...lovelyDan

[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...lovelydan

[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..lovelyd

[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

[NeoPa]

@lovelydan
Both sets of code are unentered (not taken from your project). Please ensure that you enter the code into your project first before copying it in here. You're clearly not experienced enough to type straight from your head and get it in a reliable form, and this can waste a lot of our time (and ultimately yours too of course). Furthermore, I asked you to post what you're currently using. As this is just some stuff you've typed out it is clearly not what was requested. However, I'll comment on what's here as it may even help (I can certainly see some problems with it).

Your first bit of code is mainly comment lines, so that can be ignored.

The second bit is trying to set the filter relative to the form though. This is a misunderstanding. It needs to refer to the recordsource that the form is built on (bound to). Generally though, it can remain unqualified (See suggested code below). It also needs a .Requery when it's set (I believe). If the field in the recordsource is [MinName], then it should be something like :

Private Sub cmbMinistryName_AfterUpdate()
Dim strFilter As String
 
    With Me.SubForm.Form
        ' This assumes Handle is a text field
        strFilter = "[MinName] Like '" & Me.cmbMinistryName & "*'"
        .Filter = strFilter
        .FilterOn = True
        Call .Requery
    End With
 
End Sub

In the second bit of code you're setting the record source to the Filter value. This makes no sense.

Try the suggested code and see what you get.

[lovelydan]

NeoPa

i have used this example the way it is from your first suggestion about "example Filtering a form"

it also does not give an error but it returns the subform with no records...

hey sorry bout unentered codes...im using 2 computers, the laptop im using for programming is not connected to the internet/network but the other PC is and it does not have applications i need for my day to day work so im sorry for that, will try something better next tym.

regards, LovelyDan(Africa-Botswana)

[NeoPa]

That sounds like a good reason Dan. It's unfortunate, but understandable.

Let me add a tip that I couldn't find yesterday. This may help you, but also anyone else reading the thread.

It is always a good idea to ensure that variable name checking is enabled, AND your code compiles (at least compilation has been attempted), before submitting a question.

This avoids asking questions which are much more easily resolved on your own PC than on a forum.

To ensure variable name checking is enabled for all new modules, go to - Tools / Options / Editor (from the VBA Editor window) and set Require Variable Declaration to True (checked). For existing modules, ensure that the Option lines at the very top include :

Option Explicit

To compile your project, select (again from the VBA Editor window) Debug / Compile Project Name.

We ARE generally happy to help with compilation problems too (If you find an error reported and you can't resolve it, let us know), but we do expect members to have tried compiling before submitting a question. That way we have a better idea of the sort of problem we're looking at.

[NeoPa]

@lovelydan
I'm not sure if this means you've tried my latest suggestion or not?

[lovelydan]

NeoPa;

Yah, i have tried your latest i even found out that the "End with" was missing..

my regards,

[lovelydan]

NeoPa

eeeeeeyeyeyeye, ayayayayaya!! trouble is begining to start after i ran a few things you suggested and debug i got an error..and now the db can not open the welcome sourceObject subform...im still copying the whole code from my laptop and in few seconds i will paste it... i think im seeing dust now, but i wont give up

im realy thankful 4 ur help guys!

regards..lovely daniell

[lovelydan]

NeoPa

Option Compare Database
Option Explicit
 
Dim iBackupFrequency As Integer
 
'<@STRINGRESOURCE MODULE="DHIS_CORE.mdb/Form_BackupReminderOptions"> - Do not edit by hand
 Private srRes1 As New StringResource   ' For loading resource strings
Private Const strBackupAccordingToGlobalOptionsCaption As Long = 5081
Private Const strBackupReminderNextTimeUserQuitDHISCaption As Long = 5082
Private Const strRunBackupCaption As Long = 5077
Private Const strSkipAndSwitchOffReminderTillNextMonthCaption As Long = 5079
Private Const strSkipBackupUntilNextCaption As Long = 5076
Private Const strThisWillSkipTheBackupReminderFor1Caption As Long = 5080
'</@STRINGRESOURCE>
 
Private Sub Form_Load()
On Error GoTo ErrHandler
 
    If g_bMultiLanguageActivated Then InitialiseMLstrings
 
    fmBackupOptions_AfterUpdate
 
ExSub:
    Exit Sub
ErrHandler:
    MsgBox Err.Description
    Resume ExSub
 
End Sub
 
Sub InitialiseMLstrings()
 
    Dim sPeriodType As String
 
    Me.lblSkipBackupThisPeriod.Caption = srRes1(strSkipBackupUntilNextCaption)
 
    iBackupFrequency = GetDataFileProperty("BackupProcedureFrequency", "EntryNumber")
 
    Select Case iBackupFrequency
        Case 1
            sPeriodType = srGlobal(strGDayCaption)
        Case 2
            sPeriodType = srGlobal(strGWeekCaption)
        Case 3
            sPeriodType = srGlobal(strGMonthCaption)
    End Select
 
    Me.lblSkipBackupThisPeriod.Caption = Me.lblSkipBackupThisPeriod.Caption & " " & sPeriodType
 
   Me.fmBackupOptions.StatusBarText = ""
   Me.fmBackupOptions.ControlTipText = ""
   Me.optRun.StatusBarText = ""
   Me.optRun.ControlTipText = ""
   Me.lblRunBackup.Caption = srRes1(strRunBackupCaption)
   Me.optSkipNow.StatusBarText = ""
   Me.optSkipNow.ControlTipText = ""
   Me.lblSkipBackupThisSession.Caption = srGlobal(strGSkipCaption)
   Me.optSkipTillNextPeriod.StatusBarText = ""
   Me.optSkipTillNextPeriod.ControlTipText = ""
   Me.lblSkipBackupThisPeriod.Caption = srRes1(strSkipAndSwitchOffReminderTillNextMonthCaption)
   Me.cmdOK.Caption = srGlobal(strGContinueString)
   Me.lblDescription.Caption = ""
 
End Sub
 
Private Sub fmBackupOptions_AfterUpdate()
On Error GoTo ErrHandler
 
    Select Case Me.fmBackupOptions
 
        Case 1
            Me.lblRunBackup.FontBold = -1
            Me.lblSkipBackupThisPeriod.FontBold = 0
            Me.lblSkipBackupThisSession.FontBold = 0
            Me.lblDescription.Caption = srRes1(strBackupAccordingToGlobalOptionsCaption)
        Case 2
            Me.lblRunBackup.FontBold = 0
            Me.lblSkipBackupThisPeriod.FontBold = 0
            Me.lblSkipBackupThisSession.FontBold = -1
            Me.lblDescription.Caption = srRes1(strBackupReminderNextTimeUserQuitDHISCaption)
        Case 3
            Me.lblRunBackup.FontBold = 0
            Me.lblSkipBackupThisPeriod.FontBold = -1
            Me.lblSkipBackupThisSession.FontBold = 0
            Select Case iBackupFrequency
                Case 1
                    Me.lblDescription.Caption = srRes1(strThisWillSkipTheBackupReminderFor1Caption) & " " & srGlobal(strGDayCaption)
                Case 2
                    Me.lblDescription.Caption = srRes1(strThisWillSkipTheBackupReminderFor1Caption) & " " & srGlobal(strGWeekCaption)
                Case 3
                    Me.lblDescription.Caption = srRes1(strThisWillSkipTheBackupReminderFor1Caption) & " " & srGlobal(strGMonthCaption)
            End Select
 
    End Select
 
ExSub:
    Exit Sub
ErrHandler:
    MsgBox Err.Description
    Resume ExSub
 
End Sub
 
Private Sub cmdOK_Click()
On Error GoTo ErrHandler
 
    If Me.fmBackupOptions = 1 Then
        RunBackupProcedure
    ElseIf Me.fmBackupOptions = 3 Then
        SetDataFileProperty "DateOfLastBackupProcedure", "EntryDate", "#" & Now() & "#"
    End If
 
    DoEvents
 
    DoCmd.Close acForm, Me.Name, acSaveNo
 
ExSub:
    Exit Sub
ErrHandler:
    MsgBox Err.Description
    Resume ExSub
 
End Sub

here is the code with an error and it points to the first bold line
message is; user-defined type not defined.

and the when i close and try to open the DB i get the following message as it tries to load the subform

message; method "form' of object' _subform failed

if the is a way to send you a sample of my DB please let me knw. the DB is smaller than 1MB. and only has four forms

regards, lovely

[NeoPa]

I think I will need to see your RecordSource then.
Is [MinName] even a field in there?
@lovelydan
Good spot. I even mucked up the indenting horribly. Sorry about that.
I've fixed that in the original now.

[lovelydan]

NeoPa

Sub SetActiveFormLabel()
 
'This will set the "title" of the form using the "Assets" form caption
    If Len(Nz(Me.SubForm.Form.Caption, "")) > 0 Then
        Me.lblSubFormTitle.Caption = Me.SubForm.Form.Caption
    Else
        On Error Resume Next
        Me.lblSubFormTitle.Caption = Screen.ActiveControl.Caption
        On Error GoTo 0
    End If
 
    Me.Caption = Me.lblSubFormTitle.Caption
 
End Sub

the first error message comes from this code...

message is; method "form' of object '_ subform' failed

regards..lovely

[ChipR]

Is SubForm actually the name of your subform control?

[NeoPa]

@lovelydan
I can't help much here. I don't know what a StringResource object is. It's not standard Access as far as I'm aware.

I can only assume (as the compiler has) that it is a user-defined class. You need to ensure that it is defined somewhere, and that if it is outside of your project, then a Reference exists for it.
@lovelydan
This looks like you have saved the project without trying to compile it. This is not a good idea. See post #18 for how to ensure the basics are done before posting. It is also a good idea to follow these steps when developing a database.
@lovelydan
To attach items to one of your posts you need to scroll down the page a little when posting (either originally or in an advanced edit screen) to the button that saya Manage Attachments. Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.

For a database, please ensure you do a Compact & repair on it first, before ZIPping it into a .ZIP file. Only attach this .ZIP file.

[lovelydan]

sory for the wrong way of posting im not yet familier with how im surpose to paste the codes...

[NeoPa]

@lovelydan
There is no .Caption property for a Form control as far as I'm aware. What are you trying to reference with this line of code?

NB. Please remember that using the [ CODE ] tags is not optional. Please ensure all further posts have all code posted in the tags. Thank you.

[NeoPa]

CODE TAGS

To add the tags simply select the code part of your post, then click on the button that looks like a hash (#).

[lovelydan]

NeoPa

if u go to form properties in design view under format u will find caption...whatever name typed there, there code picks it and throws the name into the unbound subform lbl for caption...it works

[NeoPa]

You're quite right Dan. My bad.

Looking back to the error message it seems clear that the problem is, as ChipR said, related to the Me.subForm item not being recognised as a Subform control.

[lovelydan]

NeoPaim trying to attach but its getting blocked and have to notifiy the adminstrator..?

[lovelydan]

NeoPa

here is an attachment of the db..

[lovelydan]

NeoPa

did you get it...

[NeoPa]

I'll get to look at this at home if I get the chance Dan. I never download from at work.

[lovelydan]

NeoPA

okay will talk more on monday, have to run to the cattle Post...have a brilliant day...

[NeoPa]

That's fine Dan. We have a holiday (Bank Holiday) on Monday but I'll see what can be done before then anyway.

[NeoPa]

@lovelydan
I have downloaded this now at home so I deleted the attachment.

[NeoPa]

Right. This is a bit unfortunate.

Before posting it is always a good idea to ensure the database passes the basic tests. See this post I've made before for the things to consider.

It is always a good idea to ensure that variable name checking is enabled, AND your code compiles (at least compilation has been attempted), before submitting a question.

This avoids asking questions which are much more easily resolved on your own PC than on a forum.

To ensure variable name checking is enabled for all new modules, go to - Tools / Options / Editor (from the VBA Editor window) and set Require Variable Declaration to True (checked). For existing modules, ensure that the Option lines at the very top include :

Option Explicit

To compile your project, select (again from the VBA Editor window) Debug / Compile Project Name.

We ARE generally happy to help with compilation problems too (If you find an error reported and you can't resolve it, let us know), but we do expect members to have tried compiling before submitting a question. That way we have a better idea of the sort of problem we're looking at.

In your case it is also a good idea to list the references used Tools / References... from the VBA window.

I'm stuck as the database you attached is not compiled, and I cannot compile it here as I don't know which references are missing.

[NeoPa]

I did try to dig further. Sometimes you can find your way even through uncompiled code if you're careful.

What I found was that the code you posted was in the Form_frm_MainMenu form. This does indeed include a Subform control called SubForm.

However, this Subform object has no form associated with it in the design (IE. SubForm.SourceObject has not been set, at least in the design). When it hits that line of code (Line #5 of Post #24) it quite understandably complains that it recognises no Form of SubForm. There's nothing set after all.

Let me know if this clarifies the problem.

[lovelydan]

NeoPa

okay! i get u, even though im a bit confussed whr to set or whr not to set the subform.sourceObject. my codes are always trial and error and some tyms they work and sometyms i give up. okay, here is what i have done, i deleted the welcome subform and re-designed it again and it works i dont know whether its going to give me problems again after compiling the sytem.

about the search or filtering of subforms have you looked at the reason why it can't filter.

today is a May Day holiday here...so im in a holiday..been trying to connect all this mornig and the network for wireless is a bit low, but now its better.

[lovelydan]

NeoPA

i tried to compile the db and im getin an error from line seven the bolded line in the code above but does not corrupt the db. do you want me to attach this one or you will jst work with that one i sent yesterday...

regards..Daniell

[NeoPa]

@lovelydan
No.

Until the project compiles there is really no point in looking at any other problems.

[NeoPa]

@lovelydan
I wouldn't just yet. I can see the line you're referring to, and it's clear why it's not compiling. You're referring to a class (StringResource) that doesn't exist.

[lovelydan]

NeoPa..

okay i will crack my mind and get back to you as soon as possible..

regards

[NeoPa]

OK Dan. Whenever you're ready is fine.

[lovelydan]

NeoPa

i finally managed to compile the DB, can i send it..

regards, daniell

[lovelydan]

NeoPA

there you go, i finally won my own fight...

LovelyDan

[NeoPa]

That's fine, but what am I looking at now? What needs resolving?

PS. Assumed you wanted db removed after download. Let me know if not.

[lovelydan]

NeoPa

now whats needs resolving is a code under the combo box for By Ministry search under afterUpdate control...on frm_MainMenu..the search for subforms..thats what was always the case here..

after opening the db please click on the assets button on the main menu on the left then try to select a seach word from the first left combo box and observe the result. i have data on two search term "MOH and MOE" search using those from the combo box

regards