By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,654 Members | 1,824 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,654 IT Pros & Developers. It's quick & easy.

Mainform and multiple subforms search...

P: 50
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"
Apr 28 '09 #1
Share this Question
Share on Google+
64 Replies


Expert 100+
P: 1,287
Hi lovelydan,
If you are setting the .SourceObject in code, you might as well set the .RecordSource also like:
Expand|Select|Wrap|Line Numbers
  1. Dim strSQL as String
  2. strSQL = "SELECT * FROM tblMyTable WHERE CompanyName = '" & cmbNameCombo.column(0) & "'"
  3. Me.Subform.FORM.RecordSource = strSQL
  4.  
Apr 28 '09 #2

P: 50
thanks ChipR

i wil try this and give u my response..

regards, lovely
Apr 28 '09 #3

NeoPa
Expert Mod 15k+
P: 31,712
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.
Apr 28 '09 #4

P: 50
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.
Apr 29 '09 #5

P: 50
NeoPa

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

regards..lovelyDan
Apr 29 '09 #6

P: 50
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
Apr 29 '09 #7

P: 50
chipR & NeoPa

i wanted to paste a snapshot of my main form and subform looks like it cant go through...
Apr 29 '09 #8

NeoPa
Expert Mod 15k+
P: 31,712
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.
Apr 29 '09 #9

P: 50
NeoPa
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmbMinistryName_AfterUpdate()
  2. 'With Me
  3.  '   If IsNull(.cmbMinistryName) Then
  4.     '    MsgBox "My goodness Tan, is this the best you can think of..."
  5.   '     Else
  6.    ''         With Me!SubForm
  7.      ''               Call .SetFocus
  8.        ''             Call DoCmd.GoToControl _
  9.          ''           ("minName")
  10.            ''         'May possible to replace with:
  11.                         'Call .minName.setfocus
  12.             ''End With
  13.             ''Call DoCmd.FindRecord(.cmbMinistryName)
  14.     ''End If
  15. ''End With
  16. Dim strFilter As String
  17.  
  18.     ' This assumes Handle is a text field
  19.     strFilter = "subform![minname] LIKE '" & Me.cmbMinistryName & "*'"
  20.     Me.SubForm.Form.Filter = strFilter
  21.     Me.SubForm.Form.FilterOn = True
  22.  
  23. 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:
Expand|Select|Wrap|Line Numbers
  1. Dim strFilter as string
  2.  
  3. if me!cmbMinistryName >"" Then
  4.      strFilter = strFilter & _
  5.      " AND ([minName]) = " & _
  6.      me!cmbMinistryName & ")"
  7.  
  8.        me.subform.form.recordsource = strFilter
  9.       ' me!Subform.form.filterOn=True
  10. end if
  11.  
  12. 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
Apr 29 '09 #10

Expert 100+
P: 1,287
Something is a little mixed up. It looks like you have
Expand|Select|Wrap|Line Numbers
  1. Me.Subform.Form.RecordSource = strFilter
You have to set the .RecordSource with a select or query statement, then set the .Filter separately.
Apr 29 '09 #11

P: 50
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
Apr 29 '09 #12

Expert 100+
P: 1,287
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmbMinistryName_AfterUpdate()
  2.   If cmbMinistryName.ListIndex < 0 Then
  3.     Exit Sub
  4.   End If
  5.   Me.SubForm.Form.Filter = "minName = """ & cmbMinistryName & """"
  6.   Me.SubForm.Form.FilterOn = True
  7. End Sub
Apr 29 '09 #13

P: 50
this is what i tried before and it does not give me an error but returns the subform with no records..in addNewRec status..
Apr 29 '09 #14

P: 50
chipR

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

please help..


lovelydan
Apr 29 '09 #15

NeoPa
Expert Mod 15k+
P: 31,712
@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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmbMinistryName_AfterUpdate()
  2. Dim strFilter As String
  3.  
  4.     With Me.SubForm.Form
  5.         ' This assumes Handle is a text field
  6.         strFilter = "[MinName] Like '" & Me.cmbMinistryName & "*'"
  7.         .Filter = strFilter
  8.         .FilterOn = True
  9.         Call .Requery
  10.     End With
  11.  
  12. 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.
Apr 30 '09 #16

P: 50
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)
Apr 30 '09 #17

NeoPa
Expert Mod 15k+
P: 31,712
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 :
Expand|Select|Wrap|Line Numbers
  1. 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.
Apr 30 '09 #18

NeoPa
Expert Mod 15k+
P: 31,712
@lovelydan
I'm not sure if this means you've tried my latest suggestion or not?
Apr 30 '09 #19

P: 50
NeoPa;

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

my regards,
Apr 30 '09 #20

P: 50
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
Apr 30 '09 #21

P: 50
NeoPa
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Dim iBackupFrequency As Integer
  5.  
  6. '<@STRINGRESOURCE MODULE="DHIS_CORE.mdb/Form_BackupReminderOptions"> - Do not edit by hand
  7. Private srRes1 As New StringResource   ' For loading resource strings
  8. Private Const strBackupAccordingToGlobalOptionsCaption As Long = 5081
  9. Private Const strBackupReminderNextTimeUserQuitDHISCaption As Long = 5082
  10. Private Const strRunBackupCaption As Long = 5077
  11. Private Const strSkipAndSwitchOffReminderTillNextMonthCaption As Long = 5079
  12. Private Const strSkipBackupUntilNextCaption As Long = 5076
  13. Private Const strThisWillSkipTheBackupReminderFor1Caption As Long = 5080
  14. '</@STRINGRESOURCE>
  15.  
  16. Private Sub Form_Load()
  17. On Error GoTo ErrHandler
  18.  
  19.     If g_bMultiLanguageActivated Then InitialiseMLstrings
  20.  
  21.     fmBackupOptions_AfterUpdate
  22.  
  23. ExSub:
  24.     Exit Sub
  25. ErrHandler:
  26.     MsgBox Err.Description
  27.     Resume ExSub
  28.  
  29. End Sub
  30.  
  31. Sub InitialiseMLstrings()
  32.  
  33.     Dim sPeriodType As String
  34.  
  35.     Me.lblSkipBackupThisPeriod.Caption = srRes1(strSkipBackupUntilNextCaption)
  36.  
  37.     iBackupFrequency = GetDataFileProperty("BackupProcedureFrequency", "EntryNumber")
  38.  
  39.     Select Case iBackupFrequency
  40.         Case 1
  41.             sPeriodType = srGlobal(strGDayCaption)
  42.         Case 2
  43.             sPeriodType = srGlobal(strGWeekCaption)
  44.         Case 3
  45.             sPeriodType = srGlobal(strGMonthCaption)
  46.     End Select
  47.  
  48.     Me.lblSkipBackupThisPeriod.Caption = Me.lblSkipBackupThisPeriod.Caption & " " & sPeriodType
  49.  
  50.    Me.fmBackupOptions.StatusBarText = ""
  51.    Me.fmBackupOptions.ControlTipText = ""
  52.    Me.optRun.StatusBarText = ""
  53.    Me.optRun.ControlTipText = ""
  54.    Me.lblRunBackup.Caption = srRes1(strRunBackupCaption)
  55.    Me.optSkipNow.StatusBarText = ""
  56.    Me.optSkipNow.ControlTipText = ""
  57.    Me.lblSkipBackupThisSession.Caption = srGlobal(strGSkipCaption)
  58.    Me.optSkipTillNextPeriod.StatusBarText = ""
  59.    Me.optSkipTillNextPeriod.ControlTipText = ""
  60.    Me.lblSkipBackupThisPeriod.Caption = srRes1(strSkipAndSwitchOffReminderTillNextMonthCaption)
  61.    Me.cmdOK.Caption = srGlobal(strGContinueString)
  62.    Me.lblDescription.Caption = ""
  63.  
  64. End Sub
  65.  
  66. Private Sub fmBackupOptions_AfterUpdate()
  67. On Error GoTo ErrHandler
  68.  
  69.     Select Case Me.fmBackupOptions
  70.  
  71.         Case 1
  72.             Me.lblRunBackup.FontBold = -1
  73.             Me.lblSkipBackupThisPeriod.FontBold = 0
  74.             Me.lblSkipBackupThisSession.FontBold = 0
  75.             Me.lblDescription.Caption = srRes1(strBackupAccordingToGlobalOptionsCaption)
  76.         Case 2
  77.             Me.lblRunBackup.FontBold = 0
  78.             Me.lblSkipBackupThisPeriod.FontBold = 0
  79.             Me.lblSkipBackupThisSession.FontBold = -1
  80.             Me.lblDescription.Caption = srRes1(strBackupReminderNextTimeUserQuitDHISCaption)
  81.         Case 3
  82.             Me.lblRunBackup.FontBold = 0
  83.             Me.lblSkipBackupThisPeriod.FontBold = -1
  84.             Me.lblSkipBackupThisSession.FontBold = 0
  85.             Select Case iBackupFrequency
  86.                 Case 1
  87.                     Me.lblDescription.Caption = srRes1(strThisWillSkipTheBackupReminderFor1Caption) & " " & srGlobal(strGDayCaption)
  88.                 Case 2
  89.                     Me.lblDescription.Caption = srRes1(strThisWillSkipTheBackupReminderFor1Caption) & " " & srGlobal(strGWeekCaption)
  90.                 Case 3
  91.                     Me.lblDescription.Caption = srRes1(strThisWillSkipTheBackupReminderFor1Caption) & " " & srGlobal(strGMonthCaption)
  92.             End Select
  93.  
  94.     End Select
  95.  
  96. ExSub:
  97.     Exit Sub
  98. ErrHandler:
  99.     MsgBox Err.Description
  100.     Resume ExSub
  101.  
  102. End Sub
  103.  
  104. Private Sub cmdOK_Click()
  105. On Error GoTo ErrHandler
  106.  
  107.     If Me.fmBackupOptions = 1 Then
  108.         RunBackupProcedure
  109.     ElseIf Me.fmBackupOptions = 3 Then
  110.         SetDataFileProperty "DateOfLastBackupProcedure", "EntryDate", "#" & Now() & "#"
  111.     End If
  112.  
  113.     DoEvents
  114.  
  115.     DoCmd.Close acForm, Me.Name, acSaveNo
  116.  
  117. ExSub:
  118.     Exit Sub
  119. ErrHandler:
  120.     MsgBox Err.Description
  121.     Resume ExSub
  122.  
  123. 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
Apr 30 '09 #22

NeoPa
Expert Mod 15k+
P: 31,712
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.
Apr 30 '09 #23

P: 50
NeoPa
Expand|Select|Wrap|Line Numbers
  1. Sub SetActiveFormLabel()
  2.  
  3. 'This will set the "title" of the form using the "Assets" form caption
  4.     If Len(Nz(Me.SubForm.Form.Caption, "")) > 0 Then
  5.         Me.lblSubFormTitle.Caption = Me.SubForm.Form.Caption
  6.     Else
  7.         On Error Resume Next
  8.         Me.lblSubFormTitle.Caption = Screen.ActiveControl.Caption
  9.         On Error GoTo 0
  10.     End If
  11.  
  12.     Me.Caption = Me.lblSubFormTitle.Caption
  13.  
  14. End Sub
the first error message comes from this code...

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

regards..lovely
Apr 30 '09 #24

Expert 100+
P: 1,287
Is SubForm actually the name of your subform control?
Apr 30 '09 #25

NeoPa
Expert Mod 15k+
P: 31,712
@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.
Apr 30 '09 #26

P: 50
sory for the wrong way of posting im not yet familier with how im surpose to paste the codes...
Apr 30 '09 #27

NeoPa
Expert Mod 15k+
P: 31,712
@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.
Apr 30 '09 #28

NeoPa
Expert Mod 15k+
P: 31,712
CODE TAGS

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

P: 50
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
Apr 30 '09 #30

NeoPa
Expert Mod 15k+
P: 31,712
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.
Apr 30 '09 #31

P: 50
NeoPaim trying to attach but its getting blocked and have to notifiy the adminstrator..?
Apr 30 '09 #32

P: 50
NeoPa

here is an attachment of the db..
Apr 30 '09 #33

P: 50
NeoPa

did you get it...
Apr 30 '09 #34

NeoPa
Expert Mod 15k+
P: 31,712
I'll get to look at this at home if I get the chance Dan. I never download from at work.
Apr 30 '09 #35

P: 50
NeoPA

okay will talk more on monday, have to run to the cattle Post...have a brilliant day...
Apr 30 '09 #36

NeoPa
Expert Mod 15k+
P: 31,712
That's fine Dan. We have a holiday (Bank Holiday) on Monday but I'll see what can be done before then anyway.
Apr 30 '09 #37

NeoPa
Expert Mod 15k+
P: 31,712
@lovelydan
I have downloaded this now at home so I deleted the attachment.
May 1 '09 #38

NeoPa
Expert Mod 15k+
P: 31,712
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 :
Expand|Select|Wrap|Line Numbers
  1. 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.
May 1 '09 #39

NeoPa
Expert Mod 15k+
P: 31,712
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.
May 1 '09 #40

P: 50
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.
May 1 '09 #41

P: 50
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
May 1 '09 #42

NeoPa
Expert Mod 15k+
P: 31,712
@lovelydan
No.

Until the project compiles there is really no point in looking at any other problems.
May 1 '09 #43

NeoPa
Expert Mod 15k+
P: 31,712
@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.
May 1 '09 #44

P: 50
NeoPa..

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

regards
May 1 '09 #45

NeoPa
Expert Mod 15k+
P: 31,712
OK Dan. Whenever you're ready is fine.
May 1 '09 #46

P: 50
NeoPa

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

regards, daniell
May 1 '09 #47

P: 50
NeoPA

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

LovelyDan
May 1 '09 #48

NeoPa
Expert Mod 15k+
P: 31,712
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.
May 1 '09 #49

P: 50
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
May 1 '09 #50

64 Replies

Post your reply

Sign in to post your reply or Sign up for a free account.