Query using criteria from Form

[ramez75]

Hi,

I created a database to eventually replace the paper forms. So each record in the table (tbldata) represent a paper form completed by a USER.

Each record in tbldata is represented by a unique number for example 11-0001, 11-0002, etc where the first digit represent the year and the other 4 digits represent sequential numbering.

Since I have hundred of records, I wanted to make it easy for the user to find the appropriate record to print it out (I created a Report) to sign/date.

So currently I have a form (frmRecordSearch) which has a Subform (frmRecordSearchSubform) linked to a Query (qryRecordSearch) which is pulling info from tbldata

The USER usually opens frmRecordSearch which lists all the records whether Open or Close. Once they find what they are looking for they click on it (I have a vb code on double click) and it will open the form the USER will make the chnages and then print it to sign/date.

So to minimize the search for the USER I wanted to be able to exclude the closed records.

This what I did so far.
I added to "tbldata" a field (called status - data type: "number", can be 1 or 2). On the form (frmdata) I added a FRAME with two (2) checkboxes Open the defualt value and is invisible (Option Value=1) and Closed (Option Value=2) which is visible. So if the box Closed is checked then the record is closed (used AllowEdits.Enable).

On "frmRecordSearch" I added a Checkbox.

So here where I am stuck in the query (qryRecordSearch). I need to put something in the query. When the USER checks the box on "frmRecordSearch", then all closed records will disappear and not be shown.

Currently I do it manual, I open qryRecordSearch in design format and I add in the Criteria field of "status" - <>2.

Hopefully i am making sense.

Thanks in advance

RB

 

[DavidAtWork]

Does your status field (tbldata.status) get updated when user checks the closed checkbox - incidentally it would have been easier to use an Option frame if the status can only be one of 2 options, that option value can be bound to your status field and your query criteria based on the value in this field. Anyway you can still set your query criteria based on tbldata.status field if it is being populated
David

 

[ramez75]

Does your status field (tbldata.status) get updated when user checks the closed checkbox - incidentally it would have been easier to use an Option frame if the status can only be one of 2 options, that option value can be bound to your status field and your query criteria based on the value in this field. Anyway you can still set your query criteria based on tbldata.status field if it is being populated
David

Yes when the USER check the Closed checkbox (Option Value = 2) in the form (frmdata) the status field (tbldata.status) gets updated. But I need to configure the qryRecordSearch somehow that when the checkbox in frmRecordSearch is checked it will be equivalent as to me going into the query (qryRecordSearch) in design field and typing <>2 in the criteria field of the field "status" in the query.

Also to clarify I am using an option frame which houses 2 checkbox Open (invisible) is the default and Closed (visible).

But I want my query to be flexible to be able to allow the USER if need to see all records whether OPen or CLosed and at the same time the USER be able to only see the Open ones

 

[DavidAtWork]

The Option method uses option buttons, there would be one labelled 'Open' the other 'Closed'. An Option frame can be set so there is no default value and when the form opens, it will not have any value until a user clicks one option.
However using your existing method, when a user checks the Closed box and then opens frmRecordSearch, you need to set the recordsource according to the users choice like this:
If forms!frmData.Checkbox2=True Then
frmRecordSearch.Recordsource = "SELECT tbldata.* FROM tbldata WHERE tbldata.status=2"
ElseIf forms!frmData.Checkbox1=True Then
frmRecordSearch.Recordsource = "SELECT tbldata.* FROM tbldata WHERE tbldata.status=1"
Else
frmRecordSearch.Recordsource = "SELECT tbldata.* FROM tbldata"
Endif

This code is very simplified of course and should be tied to the forms (frmRecordSearch) load event. By doing it this way you control the forms record source by what the user chooses, not by a pre-define query, qryRecordSearch
David

 

[ramez75]

David, thank you for the response. I have attached a sample copy of the database I am working on. Its easier to explain.

If you open frmRecordSearchform you will see two record 12-0001 (Status=Open) and 12-0002 (Status=Closed).

I want the user to be able if they check the checkbox ("Check to show only open records") by Search button then 12-0002 should disappear and only 12-0001 will be shown. When reset button is clicked then 12-0002 will pop-up on the form again.

I just dont know how to get this to work. I was thinking i will need to add something in qryRecordSearch that will first of all tie qryRecordSearch.chkstatus to tbldata.status and then will look for the value of tbldata.status if it is 1 or 2 and based on that will either make closed records visible or not

Hope I am making sense.

Unless there is another way

Thanks again

 

[DavidAtWork]

I have solved your problem here. If you make a copy your query qryRecordSearch and call it qryRecordSearchOpen and in it set the criteria for status as 1, save and close.
Now go to the code behind your cmdsearch_Click, comment out any existing code and paste this code:

If Forms!frmRecordSearch.chkstatus = True Then

Forms![frmRecordSearch]![frmRecordSearchSubform].Form.RecordSource = "qryRecordSearchOpen"
Else
Forms![frmRecordSearch]![frmRecordSearchSubform].Form.RecordSource = "qryRecordSearch"
End If
Forms![frmRecordSearch]![frmRecordSearchSubform].Form.Requery

and also add it under your existing code behind cmdReset_Click

David

 

[ramez75]

I have solved your problem here. If you make a copy your query qryRecordSearch and call it qryRecordSearchOpen and in it set the criteria for status as 1, save and close.
Now go to the code behind your cmdsearch_Click, comment out any existing code and paste this code:

If Forms!frmRecordSearch.chkstatus = True Then

Forms![frmRecordSearch]![frmRecordSearchSubform].Form.RecordSource = "qryRecordSearchOpen"
Else
Forms![frmRecordSearch]![frmRecordSearchSubform].Form.RecordSource = "qryRecordSearch"
End If
Forms![frmRecordSearch]![frmRecordSearchSubform].Form.Requery

and also add it under your existing code behind cmdReset_Click

David

thank you David....

Totally make sense. I was trying to use one query to accomplish what I wanted but your idea is so simple ands effective.

Thanks works like a charm