Form filters and refreshing forms

Topic

New Reply

Hi,

I’m quite new to Access so am after some help and input to see if I’m doing this the correct way.

The database I’m using will be accessed by users in different areas (states). The first form that pops up to the user asks them to select which state they’re in, which then sets a global variable called ‘ChosenState’.

The next form that pops up allows them to click on a button from where they can see a summary of ‘clients’ in their state. I’m calling the opening of this form from a button, using the following code:
DoCmd.OpenForm “frmClientSummary”, acNormal, , “[State]=” & ChosenState

This works ok and shows only clients that exist under the state they’ve chosen. Is this the correct way to do this, or should I be using a filter in the ‘OnOpen’ event of the ‘frmclientsummary’ form?

The fields on the ‘frmclientsummary’ form are set to not allow additions, modifications or deletions – they have to click on separate buttons to modify or delete current clients or add a new client.
For example, the delete button runs the following code:

ClientID = txtTFN

Response = MsgBox(“Are you sure you want to delete this client?”, vbOKCancel, “Delete client?”)

If Response = vbOK Then
DoCmd.SetWarnings False
DoCmd.RunSQL “DELETE FROM tblClient WHERE TFN = ” & ClientID & “;”

DoCmd.SetWarnings True
MsgBox “Client has been deleted.”

Me.Requery
Me.Refresh

End If

When they delete a client, the form ‘refreshes’ itself to show the updated list of clients (which is what I want). The issue I have is that this ‘refresh’ doesn’t happen if I add a new client. For example, if I click on another button on the form to add a client, it opens up to a new form from where the user can input the client details. On the save button of this form, the following code runs:

RunCommand acCmdRecordsGoToNew
RunCommand acCmdSaveRecord

This successfully adds a client to the table, but the ‘frmclientsummary’ form doesn’t refresh to show this new client. I’ve added me.requery and me.repaint lines to the ‘GotFocus’ event of the ‘frmclientsummary’ form, but it still doesn’t work. Is anybody able to help with this?

Cheers,
Jason

Reply | Quote

Replies

using the criteria when you open the form is the best way to do this, so stick with what you have. I assume [State] is a long integer? Just a tip but to make your code easier to understand you should be a bit more descriptive with your Field names eg State_ID

in the code you use to add the record add this line –

Code:

RunCommand acCmdRecordsGoToNew
RunCommand acCmdSaveRecord
Forms("frmclientsummary").Requery

That should refresh the form to show the new record

Reply | Quote

I agree with kentg, but just want to add one more thing.

Are there security issues associated with users seeing data from other states?
The reason I ask is that it is pretty easy for a user to remove a filter (applied via the criteria used to open the form) and so reveal data from other states.
If you did not want them to be able to do that, you could base the form on a query that uses the State Combo as a parameter.

Reply | Quote

Hi John,

Yes, I want it so that the user cannot view clients from other states. I’ve actually got the form already based on a query, but how do I modify it so that the query looks up the ‘ChosenState’ from what the user selected?

Cheers,

Reply | Quote

With the menu form open, and a State Selected, open your query in Design View.
On the criteria line, under State, right click and choose build.
Double click Forms, then Loaded Forms.
In the list of forms displayed, double click the one that contains the state combo.
In the centre panel of the Expression Builder, will be a list of all the controls on that form. double click the Combo state, and you will see an expression written into the top panel.
click OK

Here is an example.

test that the query works, with different choices in the combo box.

Reply | Quote