Am I making it too complicated..?

[Nightowl4933]

I have a form with 6 unbound fields on it to enable users to search for records meeting one or any of the criteria they've entered data for. There are a few 'requirements', as identified below:

fldA can be used on it's own
fldB can be used on it's own
fldC can only be used with fldB, if it's used at all, and never with fldF
fldD can be used on it's own
fldE can be used on it's own
fldF can be used on it's own, but never with fldC

Any combination of fldA, fldB, fldC, fldD, fldE or fldA, fldB, fldC, fldE, fldF can be used or each one individually.

I currently use If, ElseIf, End If statements for some criteria and was thinking this might be the way forward, starting with the 6 criteria options and working down to the singles, but this does seem to be quite a lot of typing!

Or is there a batter way of doing it?

I've attached a screenshot of the form, to give an idea of what I'm would like to do.

Thanks

 

[Nightowl4933]

Hmm. 120 combinations might make this a bit too cumbersome!

 

[plog]

Need more context. What's being opened and how? Here's what I would do:

Create a report based on everything you might want to see (unfiltered)
Add a 'Open' button on the form.
When the button is clicked it runs code that ultimately executes a DoCmd.OpenReport (https://msdn.microsoft.com/en-us/library/office/ff192676.aspx)

DoCmd.OpenReport allows you to pass a filter string to the report and open it to just the records you want. I would dynamically create that filter string based on your inputs. That way, you only need about 6 lines of code to build your filter.

 

[DonkeyKong]

Loop a Select Case...

For Each cntl in Me.controls

Select Case cntl.name

Case '1st Name'

Case '2nd Name'
if fldThatCantBeUsedWith2ndNameField <> "" _
and fld2ndName <> "" then
msgbox "you can't do that."
exit sub
end if
..................
end select
next

 

[Nightowl4933]

Hi plog.

A user can enter data in any combination of the fields in the form I uploaded a screenshot of. They then click a commnd button that opens another form with the results meeting the criteria they selected.

I've managed to write the code with a few of the combinations and where a single criteria is chosen, but I hadn't taken account of the number of 'in between' combinations there could be. I guess it's a bit like an inverted pyramid of combinations:

All the 5's (x2)
All the 4's (x4)
All the 3's (x6)
All the 2's (x10)
All the 1's (x5)

That doesn't seem too many, probably because two fields can't be used in the same filter, but it does seem a bit too few!

The reason I can't use a report is that this form opens another continuous form with the results. From there, users can view or edit a selected record, if there are more than one.

 

[Nightowl4933]

Loop a Select Case...

Good idea. I've used this as an alternative...

If chkLocation.Value = True And chkLocation2.Value = True Then
rResponse = MsgBox("...click OK to return to the search screen", vbOKOnly, "You can't use that combination...")
GoTo NoSearchString
End If

Using this kicks them out at the beginning and returns them to the search form.

Thanks, though

Pete

 

[plog]

Docmd.open form allows you to apply filter as well. You need to dynamically build your filter string based on what criteria are used. No need for more than 1 if statement per criteria.


HEre's some psuedo code:

strFilter="(1=1)"

if criteria1 used then strFilter=strFilter & " AND (Criteria1=" & Input1Value & ")"
if criteria2 used then strFilter=strFilter & " AND (Criteria2=" & Input2Value & ")"
...

Docmd.OpenForm, "YourForm", strFilter

 

[Nightowl4933]

Docmd.open form allows you to apply filter as well. You need to dynamically build your filter string based on what criteria are used. No need for more than 1 if statement per criteria.


Here's some psuedo code:

strFilter="(1=1)"

if criteria1 used then strFilter=strFilter & " AND (Criteria1=" & Input1Value & ")"
if criteria2 used then strFilter=strFilter & " AND (Criteria2=" & Input2Value & ")"
...

Docmd.OpenForm, "YourForm", strFilter

I see what you mean.

Would this work if multiple options were selected, though?

Pete

 

[plog]

Yes. Each criteria has its own If statement and adds to the filter only if it is used.

 

[Nightowl4933]

Cool. I've used the following (longest) code to start the If, Then, ElseIf statement. Would I be correct in thinking this could be split in to 6 lines and replace everything I've written?

If strReference <> "" And strAuthority <> "" And strLocation <> "" And Not IsNull(dStart) And Not IsNull(dEnd) And strPostCode <> "" Then

strSearchString = "[Initial Planning Application Reference] Like '*" & strReference & "*'" & " AND [Authority] = '" & strAuthority & "' AND [Town] = '" & strLocation & "' AND [Post Code] Like '*" & strPostCode & "*'" & " AND [Date of Application] BETWEEN #" & Format(dStart, "mm/dd/yyyy") & "# And #" & Format(dEnd, "mm/dd/yyyy") & "#"
...

Pete

 

[Nightowl4933]

I've had a go at using your code, but I'm getting a bit stuck with understanding this bit:

strFilter = "(1=1)"

When I use this:
If strAuthority <> "" Then strFilter = strFilter & " AND ([Authority] = '" & strAuthority & "')"

I get the following value for strFilter: (1=1) AND ([Authority] = 'Whatever Council') and every record is returned, i.e. the filter doesn't, erm, filter.

Have I misinterpreted something?

Thanks

 

[plog]

(1=1) AND ([Authority] = 'Whatever Council')

That looks valid to me. Perhaps you aren't putting it in the right argument when using DoCmd.OpenForm. Also, is [Authority] a field in the underlying datasource the form you are opening is based on?

My advice is to get DoCmd.OpenForm to work--forget about constructing your string for now and hard code something in there just so you know you are doing it right. Then once you have that you can construct your string and debug using it.

 

[Nightowl4933]

Sorted! I had the Filter string in the wrong place because I missed a comma!

Brilliant, plog, thank you.

Pete

 

[Nightowl4933]

OK, so this has developed a bit of a bug, and I don't know how...

When the database was 'released' it was split, with the back end on a server. I'm pretty sure it all worked wonderfully, but playing with it recently identified a flaw in my cunning plan, in that when one of the search criteria is selected (strAuthority), I get a parameter pop-up, but all the other search criteria work fine - unless strAuthority frms part of that search.

I'm using your brilliant solution, plog, but I don't understand why this part doesn't work when all the others do, especially as it does work with exactly the same code on earlier versions!

Basically (and I could include all the code, if this would help), this is what I've got:

Dim strAuthority As Variant
Dim strFilter As Variant

strAuthority = [comAuthority].[Column](1)


If strAuthority <> "" Then strFilter = strFilter & " AND [PlanningAuthority] = " & strAuthority

...and it fails here:

Set rst = CurrentDb.OpenRecordset("SELECT * FROM tblAllApplications WHERE " & strFilter)

    If rst.RecordCount < 1 Then
      rResponse = MsgBox("...click OK to return to the search screen and try again.", vbOKOnly, "The Search Criteria returned no records...")
      GoTo NoSearchString
    Else
       [COLOR=Red]DoCmd.OpenForm "frmApplicationSearchResult", , , strFilter[/COLOR]
    End If

The pop-up box prompts 'Enter Parameter Value' for 'PlanningAuthority', which is a field name in 'tblAllApplications' and a Datatype of Number (Long Integer), although it has text in it (this is the same in both the working and not working databases, but I can't remember, for the life of me, why I did that!), but it's a Lookup from another table using this statement: 'SELECT [tblPlanningAuthority].ID, [tblPlanningAuthority].Authority FROM tblPlanningAuthority ORDER BY [Authority];'

The value of strFilter is '(1=1) AND [PlanningAuthority] = 12', which is no different to when I run this in an earlier version!

Can anyone tell me why this is happening, and what to do to resolve it, please?

Thanks.

 

[Nightowl4933]

A bit of an update...

I've tried to run a simple query based on the same criteria, and it seems to work. I've used:

SELECT tblAllApplications.PlanningAuthority FROM tblAllApplications WHERE (((tblAllApplications.PlanningAuthority)=1));

...so I'm going to try to step this up to the full string and see how I get on.