Conditional cascading query

[michal79]

Hi there,

I'm new to this forum and I hope someone will be able to help. I've built a search form and a query that looks for data in a lessons learned database called LLIS. I've attached a screenshot of the form.

I've designed the query so that you can choose one or more fields and make your own search criteria.

As an axample the row source for the Field Name combo box is:
SELECT DISTINCT LLIS.[Field Name] FROM LLIS ORDER BY LLIS.[Field Name];

What I would like is a cascading query meaning that when I choose a specific Field, the number of wells displayed in the Well Name combo will be limited to the ones related to the specific field chosen... and that is not a problem, that part works. I use a following code on After Update (example from Field Name combo limiting choices on Well Name):

Private Sub CBFieldName_AfterUpdate()
On Error Resume Next
CbWellName.RowSource = "Select distinct LLIS.[Well Name] " & _
"FROM LLIS " & _
"WHERE LLIS.[Field Name] = '" & CBFieldName & "' " & _
"ORDER BY LLIS.[Well Name]"
End Sub

My problem is that I'd like to be able to see all Well Names if the Field Name combo is left blank... and all Sections if the Field Name & Well Name combos are left blank ... and so on. I don't know how to design that condition and where to place it,.. on which event?

I'm new to Access and not a VBA expert. I'd appreciate your help on this!

Thanks in advance,
Michal

 

[Alansidman]

This will prove helpful, I am sure.

http://www.datapigtechnologies.com/flashfiles/searchform.html

Alan

 

[michal79]

Allan,

Thanks a lot for you reply. That link is pretty useful but it is not exactly what I'm looking for. My form is based on comboboxes which link to fields in my database and retrieve distinct entries e.g. for Field Names, Well Names etc. I've managed to make them dependent on one another i.e. if a certain Field Name is chosen from a list it will limit the amount of wells available in the Well name combo which are associated to the specific Field that was selected. What I want is a condition, that if the Field Name is left blank... the user will be able to see all the Wells in my Well Name combobox. The same principle applies to all the other comboboxes in my form. I.e. if a user chooses a specific Field, then he can choose only the Wells associated to that field and then subsequently he can choose only those Sections which are associated to that specific Well etc., however if Field and Well names are left blank, the user should be able to choose between all the sections available in the Lessons Learned database.

I'd really appreciate help on that one.

Thanks,
Michal

 

[Alansidman]

Are you using these combo boxes to generate a query and thus see the results?
Suggest you post your codes that create the conditionals for this so that we can better understand how to provide you with the correct solution. I am guessing (read guessing) that you will need an If-Then in your code to make this happen.

 

[michal79]

Yes, in a way... when choosing options from combos you create a query... the point is that you can search after just one record or a combination of records. I attach the screenshot of the query design... it is 2 files as it's too big to show it on one screen... but I hope it'll give you an idea. It just allows to search after on sepcific record or a combination of records.

The 'cascading' effect is created by a VBA code which I posted together with my question, and I'm guessing that in that code I need as you say some sort of If / Then statement that would allow me to see ALL the distinct records in a combobox if the preceeding combobox is left blank.

Hope that helps.

Cheers,
Michal

 

[michal79]

Hi,

I still haven't solved my problem Is anyone able to help me out with the issue in this post... I got stuck and I'm not sure what I'm doing wrong. I'm trying to implement some if / then condition into my VBA but I'm not able to make it work.

I'd very much appreciate your help on this one.
Thanks!

M.