One Form 2 purposes. DE and Qry

[RustyRick]

I'm using Access 2010 on Wind 7.

I have a Db with 2 tables. the form duplicates each field in the Tbl.

Its a "Data Entry" recording of hard copies of many peoples records. Sometimes they make mistakes. So I have to enter as reported, and fix mistakes at a later time. So I have a "Yes/No" check box to identify which record are "Right" and which are "Wrong".

I would like 1 Form to do 2 things.

1) Let me populate data into the table.
While doing that I identify which records are in error with the "check box" control. (un-bound) This works fine.

2) I'd like to be able to open the same form (showing all the records) and then click a button and run a Query to select just the erroneous records.

My button works fine too, but opens the "Data Sheet" view of the table.

I'd like to remain in the "Form View" so I can correct the records.

One of the Form users recommended I enter "QueryName" in the RecordSource spot in Properties but that opens the "Data Sheet" view after the "Query" has run and I need it to open the "Form" view. :banghead:

 

[Beetle]

While doing that I identify which records are in error with the "check box" control. (un-bound)

An unbound check box is not going to work for what you want to do here. You would need to add a Yes/No field to the table, then you could apply (or remove) a filter to your form (in the click event of the command button) to display only the records that are "in error" (or all records when the filter is removed).

 

[RustyRick]

I think it would be called a "Bound" check box as it is a "Yes/no" field in the table with a default setting set to "0"?

 

[Beetle]

Not sure I'm following you. In your first post you said you were using an unbound check box. Are you now saying that is actually a bound check box?

 

[RustyRick]

Ya sorry, I was wrong. It is bound. I'm looking at the filter options. But. I want to publish in a stand alone program and don't want employees trying to learn how to filter. That's why I thought a simple button would be idiot proof.

 

[Beetle]

I want to publish in a stand alone program and don't want employees trying to learn how to filter.

The only thing they would need to know is how to click on a command button. You would code the filtering in the click event of the button This air code example is for using one button and manipulating the caption property to determine if the filter should be applied or removed;

Private Sub cmdYourButton_Click ()

    If Me.cmdYourButton.Caption = "Show Errors" Then
        Me.Filter = "[YourYesNoField] = True"
        Me.FilterOn = True
        Me.cmdYourButton.Caption = "Show All"
    Else
        Me.FilterOn = False
        Me.cmdYourButton.Caption = "Show Errors"
    End If

End Sub

 

[RustyRick]

I'm sure your right on, and I will try it. Coding / VBA scares me. I'm an Old Cowboy.

I don't ever need them to find the records that are correct or "checked" TRUE. Just the erroneous or FALSE ones.

 

[RustyRick]

Private Sub Command86_Click()
Private Sub cmdYourButton_Click()
If Me.cmdYourButton.Caption = "Show Errors" Then
Me.Filter = "[YourYesNoField] = True"
Me.FilterOn = True
Me.cmdYourButton.Caption = "Show All"
Else
Me.FilterOn = False
Me.cmdYourButton.Caption = "Show Errors"
End If

 

[RustyRick]

Doesn't like the red print?

 

[Beetle]

You've go the opening procedure line (Private Sub....) in there twice, plus, you need to substitute the actual names of your command button and your Yes/No field in place of my example names (cmdYourButton and YourYesNoField). Delete what you've got there now, then start over. Open the properties for your command button, go to the Events tab, select the On Click event and click the elipse (...) to the right. Your command button appears to be named Command86 (you really should name it something more meaningful) so when the code window opens, the following two lines should already be there (this is assuming you leave the name of the button as Command86);

Private Sub Command86_Click()

End Sub

In between those lines place the following code;

If Me.Command86.Caption = "Show Errors" Then
    Me.Filter = "[[COLOR="Red"]YourYesNoField[/COLOR]] = True"
    Me.FilterOn = True
    Me.Command86.Caption = "[COLOR="Green"]Show All[/COLOR]"
Else
    Me.FilterOn = False
    Me.Command86.Caption = "[COLOR="green"]Show Errors[/COLOR]"
End If

You need to replace the red highlighted text in the above with the actual name of your Yes/No field. The text in green above you can change if you want the caption of the button to be something different.

 

[RustyRick]

Thank you, oh thank you. I'm 65+ and trying to develop a application to obtain an office job so I don't have to drive truck anymore. My bones ack too much for that!

I've go the following working (with your help) - sort of.

Private Sub LBErrors_Click()
If Me.LBErrors.Caption = "Show Errors" Then
Me.Filter = "[RecOK] = False"
Me.FilterOn = False
Me.LBErrors.Caption = "Show All"
Else
Me.FilterOn = True
Me.LBErrors.Caption = "Show Errors"
End If

End Sub

When I initially open the form it shows all records. Click the button and it shows the "OK" records, Click again the "Error" records, Click again All the records, and cycles back and forth from the Total to the Errors. And that's OK. I just don't want the first view to show the OK records. Is that possible?

 

[Beetle]

Just to make sure I understand your data entry process, if the record has no errors (i.e. it's OK) then the check box should be checked (true) and if the record has errors then the check box should be unchecked (false), is that correct?

Also, when applying a filter, the FilterOn property should be set to True and when removing it it should be set to False (you've got that reversed in your code sample).

If Me.LBErrors.Caption = "Show Errors" Then
    Me.Filter = "[RecOK] = False"
    Me.FilterOn = False [COLOR="Red"]'<< Should be True[/COLOR]
    Me.LBErrors.Caption = "Show All"
Else
    Me.FilterOn = True [COLOR="red"]'<< Should be False[/COLOR]
    Me.LBErrors.Caption = "Show Errors"
End If

 

[RustyRick]

Don't know what I did but it works fine. thank you so much.