Software

Ask something
Discussion Locked

Access database form design

0 helpful
9 replies

by red dragoon

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
I have a job order database that I designed in Access 2000 to help me keep track of my helpdesk trouble tickets. I used the form design wizard to create an entry form. The last field is Closed, and it is in a Yes/No format. I want to know how I can tell Access to remove a yes record from the Forms entry so only the open calls remain. I still want to be able to view the Yes and No entries in the Table.

9 replies

by bob sellman

Create a query based on the table used as the record source for the form. The query should include all columns in the table. Include in the query a criteria for your Closed field that the value must equal False.

Change the record source for your form to this query. The form will now only display the open records.

If you change your mind and decide to also display the closed tickets, but do not want to permit changes to be made to closed records, modify the form in the following manner (I'm assuming the Yes/No box on the form is called chkClosed):

Set the form properties of AllowEdits (and AllowDeletions) to False.

In the OnCurrent property of the form, put the following code:

IF me.chkClosed = False THEN
me.AllowEdits = True
ELSE
me.AllowEdits = False
End If

For this you will use the record source you currently use, since all records (open and closed) should be available.

The one problem with this method is that if you enable users to select records by entering search criteria in the header or footer of the form, whenever you are on a closed record the user will be unable to enter data in the search field(s) (since edits are not allowed when the current record is closed).

by red dragoon

I have very little experience getting into the guts of Access. I need more specific instructions. I have started a query with all the fields of the table, but I don't understand what you mean by "Include in the query a criteria for your Closed fieldthat the value must equal False". Spell it out and I'll give you the points. Thanks.

by sl-campbell

Simply go to the design view of the form.
Then, when in design view click the menu:
View|Test Window (or Debug window)

Paste this code in:
Private Sub Form_Current()
Me.Filter = "CLOSED = FALSE"
Me.FilterOn = True
End Sub

by sl-campbell

Sorry, I mis-spoke. It should read:

Simply go to the design view of the form.
Then, when in design view click the menu:
View|Code

You want to go to the code window, not the test window.

After doing this, save the form and open it in normal view.

by sl-campbell

In the above example that I gave you need to put the code in the Form_Open event and not the Form_Current event.

By the way, instead of hard coding the parameter:
Me.Filter = "CLOSED = FALSE"
You could assign it to a variable, or the value of a check box. Then it is dynamic and you are then able to view at will whether to show closed records, open records or both.
This could be done by creating a module level variable, and the under the Key_Down event of the form, capture what key combinations were pressed and if the right combination is pressed, (possible only a combination that you know), then the variable is set to True or False, and also the filter. This could also be done with a visible check box or option buttons - depending on the state of the check box or option buttons the filter could be set.

Therefore, in this case, remove the code above for the Form_Open event, create a check box on the form (I have named it "chkbxOpenRecdsOnly") and then in the Code window pastethe following:

Private Sub chkbxOpenRecdsOnly_Click()
Me.Filter = "CLOSED = " & CStr(Not chkbxOpenRecdsOnly.Value)
Me.FilterOn = True
End Sub

Each time the check box is clicked the filter is changed to the value of the check box (0 for False and -1 for true which are equal to False and True), the query is re-executed, and you see the records accordingly.

Answer 3 is also 100% good if you will always look at the records in a certain view (only Opened records in this case). Youcould also create a parameter query, where the query it self ask?s, each time the form is opened, if closed or open records should be shown.

by bob sellman

Since you say you've already built a query based on the table that is currently the record source for the form, all you have to do is in the query design grid labeled "Criteria", under the column Closed put the following:
=False

False is the same as putting No (which would also work). You will probably find that after you enter the above and go to another cell in the query design grid the equal sign will disappear.

To see the results before you save the query, just click on the run icon(!) to see if you get the results you want.

Save the query with an appropriate name, such as qselOpenTickets, and change the record source of your form to be qselOpenTickets.

This discussion is closed to new replies.