Update boolean critera in a query using VBA - Access 2010

[avcastner]

I have written a database that randomly selects questions for a Bible Quiz tournament. However, we are limited to no more than 4 of one type of question per round. Until the 4th question is asked the criteria parameter remains blank. Once the 5th question has been asked, then the criteria needs to change to "no" or "0" or "false." I am not even sure which one I should use through VBA.

query name: qryStudyGuideQuestions
field to be updated: QTVorFTV

I am using Access 2010, so the solution using DAO.QueryDef as previously given does not work.

Any help would be greatly appreciated.

 

[JHB]

Are you asking for an answer before the next question is picked out, or do you pick them out all at once? And how do you do it, using a form or something else?
Hmm - reading you question again I'm not sure if you want to update a field in the table or to change the criteria!

 

[avcastner]

I use a form. The question and the answer are in the same table. I hide the answer and have a command button to unhide it (helps me review while I quiz with the kids). I want to change the criteria in the query, not update a field in the table or in the query. I would like to be able to change a YES/NO field from {nothing} to "no." I have the logic worked out for when it should happen, I just don't know how to get it to change the criteria in the query while I'm in the form. Thank you for your response.

 

[JHB]

... I just don't know how to get it to change the criteria in the query while I'm in the form.

Why not use a filter on the form instead?

  Me.Filter = "QTVorFTV=0"
  Me.FilterOn = True

Another way is to place an unbound invisible control on the form and then referred to that control in the query. So when you have got 4 of one type of question per round set the value of the control to 0 and do a form requery.
Below is the criteria in the query:

WHERE QTVorFTV=[Forms]![YourFormName]![YourControlName] OR [Forms]![YourFormName]![YourControlName] Is Null;

 

[avcastner]

I'll see if this works. I think it just might. Thank you so very, very much!

Allison

 

[avcastner]

Just tried it. It won't work because the randomization requeries and resets the filter. I think I really need to set the criteria in the query.

Can anyone show me how to do this specifically?

Thanks,

Allison

 

[JHB]

Just tried it. It won't work because the randomization requeries and resets the filter. I think I really need to set the criteria in the query.

Have you tried solution no. 2?
Another way is to place an unbound invisible control on the form and then referred to that control in the query. So when you have got 4 of one type of question per round set the value of the control to 0 and do a form requery.
Below is the criteria in the query:

  WHERE QTVorFTV=[Forms]![YourFormName]![YourControlName] OR [Forms]![YourFormName]![YourControlName] Is Null;

Else post a stripped version of you database with some sample data, (zip it).