Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Sep 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

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

    The database I知 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池e in, which then sets a global variable called 舛hosenState.

    The next form that pops up allows them to click on a button from where they can see a summary of 祖lients in their state. I知 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致e chosen. Is this the correct way to do this, or should I be using a filter in the 前nOpen event of the 素rmclientsummary form?

    The fields on the 素rmclientsummary 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 喪efreshes itself to show the updated list of clients (which is what I want). The issue I have is that this 喪efresh doesn稚 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 素rmclientsummary form doesn稚 refresh to show this new client. I致e added me.requery and me.repaint lines to the 賎otFocus event of the 素rmclientsummary form, but it still doesn稚 work. Is anybody able to help with this?

    Cheers,
    Jason

  2. #2
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    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

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.
    Regards
    John



  4. #4
    Star Lounger
    Join Date
    Sep 2009
    Posts
    58
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi John,

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

    Cheers,

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    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.

    [attachment=90169:Expressionbuilder.gif]

    test that the query works, with different choices in the combo box.
    Attached Images Attached Images
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •