VBA - Open Form with Multiple Criteria (1 Viewer)

Advisedwolf · Mar 19, 2018

Hi

Sorry about this, but after a million variations of the below script and seeking online examples, I am seeking help with the below line of code.

The principle is to Open the Form "FRM_WorkRecord_Header", finding the appropriate record based on three criterias - taken from a listbox on the current user form.
Everywhich way I try this, I receive a data mismatch, or some other error code. I have failed to spot my error with the below.

Client Address = Text String
Client Number = Text String
ScheduledDateofJob = Date

DoCmd.OpenForm "FRM_WorkRecord_Header", acNormal, ,

("[ClientAddress]=" & "'" & [LST_Activities].Column(1) & "'") And ("[ClientNumber]=" & "'" & [LST_Activities].Column(3) & "'") And ("[ScheduledDateofJob]=" & "#" & [LST_Activities].Column(0) & "#")

thanks in advance

Gavin
:banghead:

isladogs · Mar 19, 2018

Here you go ....

Code:

DoCmd.OpenForm "FRM_WorkRecord_Header", acNormal, , "ClientAddress='" & LST_Activities.Column(1) & "' And ClientNumber='" & LST_Activities.Column(3) & "' And ScheduledDateofJob=#" & [LST_Activities].Column(0) & "#"

I've removed all superfluous quotes & []

This assumes all fields have no spaces as in your code - you used spaces when defining the field types

Advisedwolf · Mar 19, 2018

Colin - thank you very much!! I clearly was getting myself a little (or a lot) confused within the syntax. Much appreciated. Code implemented and working perfectly.

Mark_ · Mar 19, 2018

Personally I would use a variable to store your WHERE, then show it prior to opening the form.

Code:

DIM asWhere as STRING
asWhere = ("[ClientAddress]=" & "'" & [LST_Activities].Column(1) & "'") And ("[ClientNumber]=" & "'" & [LST_Activities].Column(3) & "'") And ("[ScheduledDateofJob]=" & "#" & [LST_Activities].Column(0) & "#")

MSGBOX "Opening form with: " & asWhere

DoCmd.OpenForm "FRM_WorkRecord_Header", acNormal, , asWhere

From there you can (hopefully) see what your error is.

Advisedwolf · Mar 19, 2018

Hi Mark

Thanks for that handy tip!! I just gave it a shot, and your point was clear... very easy to see and validate what's going on in those long/confusing statements... great approach, and one i'll be adopting in the future... appreciate the guidance

isladogs · Mar 19, 2018

I use a similar approach but prefer to use Debug.Print so the filter is displayed in the VBE immediate window (and easy to edit from there)

Code:

DIM strFilter as STRING
strFilter = "ClientAddress='" & LST_Activities.Column(1) & "' And ClientNumber='" & LST_Activities.Column(3) & "' And ScheduledDateofJob=#" & [LST_Activities].Column(0) & "#"

Debug.Print strFilter

DoCmd.OpenForm "FRM_WorkRecord_Header", acNormal, , strFilter

This is even more useful for solving issues with SQL statements as you can copy & paste the SQL output into the query designer for testing

Mark_ · Mar 19, 2018

Colin,

I got in the habit of using a message a loooong time ago. If the user does not have an "Immediate" window to show in, you can still have a flag in a configuration file set for "Debug" that allows the end user to see this. Different language, but same general purpose. Works WONDERS for finding strange things happening.

Funny, the actual way we did it was "If not set, don't do anything". If one value, show messages. If another, print to file. The messages were normally used when certain types of issues came up and we needed to find out what types of errors were generated when users did specific actions. Funny how rights issues or disk space limits (or invalid file names) can pop up at the strangest times...

isladogs · Mar 20, 2018

Hi Mark

Both methods are useful. Just a matter of personal preference really

As for error logging, I devised a system whereby each error was logged to file with details of who / what / where / when .... BUT the issue was of course that clients rarely pass on those details

So I modified the process so that an in addition to logging the errors, an email was automatically (and 'silently') sent to me as the developer with those details.

This was invaluable in fixing program errors - after the initial flurry of emails the programs became almost error free. The exception being one client where I was able to prove that the issues they had were due to frequent network interruptions

VBA - Open Form with Multiple Criteria (1 Viewer)

Advisedwolf

New member

isladogs

MVP / VIP

Advisedwolf

New member

Mark_

Longboard on the internet

Advisedwolf

New member

isladogs

MVP / VIP

Mark_

Longboard on the internet

isladogs

MVP / VIP

Attachments

Similar threads

Users who are viewing this thread