Docmd.openform with multiple items in the WHERE condition

[Margarita]

Hello,
I am confused by why my code is not picking up the where criteria in a docmd.openform line. I have a form based on a select statement (join two tables, Transactions and DocRec, and then select the records where Transactions.PRC2 is null). This is done for dates between two values that the user specifies. This form is opened in datasheet view from another form, call it MonthReportPreparation_OTPS. On MonthReportPreparation_OTPS, the user types the reporting start date into a text box and hits buttons to view different forms and reports. All the forms open using docmd.openform- but they are all listing only one item in the Where Condition. When I try to use the same approach for this PRC2 form that requires more than one where condition, the parameters are not picked up by the code and a paramter entry box is displayed for me to type them in manually.

This is what I have for the on click event of the problematic button:

DoCmd.OpenForm "Purchasing_PRC2_withoutInvoice_form", View:=acFormDS, wherecondition:="Transactions.PRC2DocID Is Null And DocRec.[Class Code]=100 and [Date of Record] between #" & Forms!MonthReportPreparation_OTPS.startdateparOTPS & "# and #" & EndOfMonth(Forms!MonthReportPreparation_OTPS.startdateparOTPS) & "#"

I tried putting the first two items in the where condition (the 'Transactions.PRC2DocID Is Null And DocRec.[Class Code]=100' parts) into the sql query itself, and leaving only the date criteria in this vb line, but that doesn't work either. All the field names exist and are spelled correctly.
Would anyone be able to tell me why docmd.openform works with a single where criteria but asks for parameters input with multiple criteria?
Thank you!

 

[jzwp22]

How about the following. I removed the "View:" and "Wherecondition:". To compensate for the removal of "Wherecondition:", I added a comma (shown in red). I also replaced the form reference with me. since you are running this from the MonthReportPreparation_OTPS form

DoCmd.OpenForm "Purchasing_PRC2_withoutInvoice_form", acFormDS, , "Transactions.PRC2DocID Is Null And DocRec.[Class Code]=100 and [Date of Record] between #" & me.startdateparOTPS & "# and #" & EndOfMonth(me.startdateparOTPS) & "#"


BTW, does the EndOfMonth() function work properly?

I also assume that date of record is defined as a date/time datatype and not text.

 

[Margarita]

Hi jzwp22, thanks for giving this a shot. I just copied and pasted your modified line into the code and it is still asking me for the Class Code and Date of Record parameters.
To answer your questions: Yes, Date of record is formatted as a date and so is the text box into which the user types in the chosen date. And the EndofMonth function also works ok- I am using it in several different modules and it's giving me the right results.
Thank you for your suggestions!

 

[Margarita]

Hi everyone,
I am still struggling with getting this seemingly simple thing to work. I read about using the openargs property to set multiple parameter criteria, but I haven't gotten that to work either. I don't understand the concept of openargs, even though I did quite a bit of reading on it yesterday. All the articles about openargs seem to make it more complicated than it is, but I don't understand how to simplify it and get it to work. Would anyone be able to provide an easy explanation of open args and how to use it for multiple parameters that would be accessible to someone who is not proficient at the technicalities of vba?
Thank you!

 

[jzwp22]

Is there any chance that you could zip and post a copy of your database with any sensitive data removed. It might be easier to troubleshoot seeing the actual application.

 

[Margarita]

Hello, attached is a simplified version of my databse. It cotains the report prep form, the two tables in question, and the form that is supposed to open when the button is question is clicked. I would be really grateful for any light you could shed on this openform problem.
Thanks!

 

[jzwp22]

How do the two tables relate to one another? I see no key fields defined in either table. I also do not see any established relationships.

 

[Margarita]

Sorry- I didn't set the relationship up when I pasted these tables in the sample db. The only field relating the two tables is Doc ID in Doc Rec and PRC2DocID in Transactions. The form that is supposed to show up when I click the button shows the recrds that have a Null PRC2DocID.

 

[jzwp22]

None of the values match in those fields. In the DocRec the two records are both TC while there are two different numbers in the PRC2DocID field of the transaction table. What is the relationship? For every DocRec can there be many related records in the transaction table?

 

[Margarita]

The relationship is one to many: for every PRC2 record (a PRC2 is a document which holds separate invoices), there can be many invoices in the Transactions table. The data I have entered right now- neither of the invoices correspond to either of the PRC2's that are currently in the DocRec table. I expect both records from Transactions to show up when I open the Purchasing_PRC2_withoutInvoice form- which they do successfully when I open the form itself from the database window. However, the problem is with the syntax of the vba that is supposed to open this form after the relevant button on the MonthReportPreparation_OTPS form (the third button from the top in section 2 of the form). The form asks for parameters Date of Record and Class Code. (Note: in vba I accidentally called it [Class Code]- the correct name is Closing Classification Code- but when I fixed the mistake in the vba, it still asks me for that parameter).

 

[jzwp22]

After changing the Class Code to Closing Classification Code, I also noticed that your date field was date of record, but in the query you gave it the alias [PRC2 Date of Record] so that is what must be used in the docmd. I also noticed that you had Closing Classification Code=11, but in your table the value is 100. So for test purposes, I changed it to 100. With that everything works as it should. DB attached.

 

[Margarita]

After changing the Class Code to Closing Classification Code, I also noticed that your date field was date of record, but in the query you gave it the alias [PRC2 Date of Record] so that is what must be used in the docmd. I also noticed that you had Closing Classification Code=11, but in your table the value is 100. So for test purposes, I changed it to 100. With that everything works as it should. DB attached.

Thank you so much! It worked! Here is what I did in the real database for anyone who might be browsing this thread for help:

I put the two non-date criteria in the where clause into the ORIGINAL query underlying the form that opens. Then I changed the date criteria in the vba to match the field name in the actual form, NOT the underlying query (so I put [PRC2 Date of Record], not [Date of Record]). What I was making the mistake in was thinking that I need to reference the field names of the underlying query, but what docmd.openform looks for are fields in the actual form, and since the form doesn't have fields Closing Calssification code and Date of Record, it was asking for them as parameters.
Thank you so, so much jzwp22!

 

[jzwp22]

You're welcome.