Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Form Question

  1. #1
    Join Date
    Sep 2011
    Posts
    48

    Unanswered: Form Question

    Hi,

    I am making a form for users to enter data. They have to enter a total wire amount, and then enter entries in a field journal entry amount that will add up to the total wire amount. Is there a way on the form so that it won't let them close the form unless the sum of journal entry amount = total wire amount.?

    I would but it in the code builder under the form, and I assume under the "on close" section, but I can't figure out how to build the code properly.

    Any help would be appreciated.

    thanks,

    Kevin Kristensen

  2. #2
    Join Date
    Jan 2012
    Posts
    97
    Hi Kevin,

    I would use a command button to close the form rather than using the OnClose or OnUnload event of the form and then disable the 'X' in the top right hand corner (you can find out how to do this by searching Google). This gives you more customised control over how the user navigates around you DB.

    Code to run OnClick of command button:

    Code:
    If Me.yourcontrolname.Value = me.yourcontrolname.value Then
    DoCmd.Close acForm, "formname"
    Else
    Msgbox ("Values not equal, do something...")
    Me.yourcontrolname.setfocus
    End If
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  3. #3
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    In order to make sure that the calculation for the summing of the journal entry amounts includes the amount for the Current Record, I think you need to precede Dave's code with code to explicitly Save the Current Record, such as

    If Me.Dirty Then Me.Dirty = False

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  4. #4
    Join Date
    Sep 2011
    Posts
    48
    Hi Guys,

    Thanks for the help so far, but it gives me a runtime error. This is what I have.

    Private Sub CloseFormbutton_Click()
    If Me.Dirty Then Me.Dirty = False
    If DSum(nonclaims.jrnl_entry_amt) = DSum(total_nonclaim.Total_Wire) Then
    DoCmd.Close acForm, "NonClaims"
    Else
    MsgBox ("Values not equal, do something...")
    End If

    End Sub

    It says the part bolded is the problem. I am not sure if I am doing it correctly. I need the sum of both of those fields to match.

    Any help would be appreciated,

    thanks,

    Kevin Kristensen

  5. #5
    Join Date
    Jan 2012
    Posts
    97
    If your using the DSum function you need the following syntax:

    DSum(expr, domain, [criteria])

    So for example to sum the field freight in an Orders table where the ship country is the UK use:
    DSum("[Freight]", "Orders", "[ShipCountry] = 'UK'"

    I would say use the names of the controls that hold the values, so rather than DSum(nonclaims.jrnl_entry_amt), use 'me.somecontrolname.value'
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  6. #6
    Join Date
    Sep 2011
    Posts
    48
    Quote Originally Posted by dave0810471 View Post
    If your using the DSum function you need the following syntax:

    DSum(expr, domain, [criteria])

    So for example to sum the field freight in an Orders table where the ship country is the UK use:
    DSum("[Freight]", "Orders", "[ShipCountry] = 'UK'"

    I would say use the names of the controls that hold the values, so rather than DSum(nonclaims.jrnl_entry_amt), use 'me.somecontrolname.value'
    Hi,

    So for the example above, what happens if you want it to sum the whole field freight and not just the ones in UK?

    thanks,

    Kevin Kristensen

  7. #7
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by dave0810471 View Post

    ...I would say use the names of the controls that hold the values, so rather than DSum(nonclaims.jrnl_entry_amt), use 'me.somecontrolname.value'
    Afraid not! DSum() is run against a Field in a Domain (Table or Query) not against a Control on an Form! Which is exactly what you displayed, with

    DSum("[Freight]", "Orders", "[ShipCountry] = 'UK'"

    except for omitting the closing parens.

    And as to Kevin's question, to include all [Freight], not just those for the UK, you simply omit the Criteria, so simply

    DSum("[Freight]", "Orders")

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  8. #8
    Join Date
    Jan 2012
    Posts
    97
    My apologies, quite right about closing parenthesis.

    I had assumed that the DSum function was the control source of a control on a form and therefore using me.somecontrolname.value would provide the value of the DSum function also.
    Hope this helps,

    Dave.

    _____________

    Access 2000 User

  9. #9
    Join Date
    Sep 2011
    Posts
    48
    Quote Originally Posted by Missinglinq View Post
    Afraid not! DSum() is run against a Field in a Domain (Table or Query) not against a Control on an Form! Which is exactly what you displayed, with

    DSum("[Freight]", "Orders", "[ShipCountry] = 'UK'"

    except for omitting the closing parens.

    And as to Kevin's question, to include all [Freight], not just those for the UK, you simply omit the Criteria, so simply

    DSum("[Freight]", "Orders")

    Linq ;0)>
    oh thanks!! I tried that but it didn't work, but I had forgot to put the "" around the second part.

    thanks a ton guys!

    I still have a small problem. It was what Missinglinq was elluding too. When I enter new entries it says that the values don't match when I go to close to form. If however I leave and come back, the button will work as the entries do match. its like the form is being updated right away.

    Well I was able to figure out my above problem, but I have a new smaller problem.

    In this form there is also code that makes them not able to leave selected fields blank such as:

    If Nz(Me.risk_terr_abbr, "") = "" Then
    MsgBox "Risk Territory Must Not Be Left Blank!"
    Cancel = True
    Exit Sub
    End If

    If they leave this blank, it gives them the error message, but then it brings them the debug error and brings up the form coding screen. Is there a way of stopping this? It is due to the If Me.Dirty Then Me.Dirty = False part i think..

    thanks,

    kevin Kristensen
    Last edited by grooner10; 04-26-12 at 14:59.

  10. #10
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by dave0810471 View Post

    ...I had assumed that the DSum function was the control source of a control on a form and therefore using me.somecontrolname.value
    As I said, you can't run DSum, or any Aggregate Function, for that matter, against a Control, it has to be run against a Field. And FYI, when using a Control name in a Control Source, you cannot refer to it as

    Me.ControlName

    you have to enclose the name in Square Brackets, like

    [ControlName]

    BTW, The same rule applies when referring to Controls when using Conditional Formatting. Just another of the Access Gnomes' little tricks to keep things confusing!
    Quote Originally Posted by grooner10 View Post
    ...When I enter new entries it says that the values don't match when I go to close to form...If I leave and come back... the entries do match
    I'm not really sure exactly what you are doing, now. What code are you using and how/where are you totaling up the 'journal entry amounts?' As I said before, in order to make sure that the calculation for the summing of the journal entry amounts includes the amount for the Current Record, you need to explicitly Save the Current Record, using something such as

    If Me.Dirty Then Me.Dirty = False

    to insure that the last journal entry amount is included in the total. The fact that the numbers don't match when you check them, after making journal entries, but do match after closing then re-opening the Form, would suggest that this isn't happening, i.e. the amount of the last entry, before closing the Form, isn't included at the time you're initially checking it.
    Quote Originally Posted by grooner10 View Post

    ...In this form there is also code that makes them not able to leave selected fields blank such as:

    If Nz(Me.risk_terr_abbr, "") = "" Then
    MsgBox "Risk Territory Must Not Be Left Blank!"
    Cancel = True
    Exit Sub
    End If

    If they leave this blank, it gives them the error message, but then it brings them the debug error and brings up the form coding screen. Is there a way of stopping this?
    Where, exactly, do you have this Validation code for the Control named risk_terr_abbr? What 'debug error' is being displayed and what line is highlighted when the code window comes up? There are only two things that I can see that could cause an error, in this little bit of code. One would be if Access couldn't find a Control named risk_terr_abbr, and the fact that the Messagebox is being displayed (I assume that's what you mean by "If they leave this blank, it gives them the error message") would indicate that this is not the problem. The other would be if the event that you have the code in is one that cannot be Canceled.

    Since the Messagebox is being displayed, the error has to be caused by something that comes after this code, perhaps the error in using Cancel = True, or perhaps something else. As I said before, where, exactly, do you have this Validation code for the Control named risk_terr_abbr?

    Linq ;0)>
    Last edited by Missinglinq; 04-27-12 at 02:14.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  11. #11
    Join Date
    Sep 2011
    Posts
    48
    Quote Originally Posted by Missinglinq View Post
    As I said, you can't run DSum, or any Aggregate Function, for that matter, against a Control, it has to be run against a Field. And FYI, when using a Control name in a Control Source, you cannot refer to it as

    Me.ControlName

    you have to enclose the name in Square Brackets, like

    [ControlName]

    BTW, The same rule applies when referring to Controls when using Conditional Formatting. Just another of the Access Gnomes' little tricks to keep things confusing!

    I'm not really sure exactly what you are doing, now. What code are you using and how/where are you totaling up the 'journal entry amounts?' As I said before, in order to make sure that the calculation for the summing of the journal entry amounts includes the amount for the Current Record, you need to explicitly Save the Current Record, using something such as

    If Me.Dirty Then Me.Dirty = False

    to insure that the last journal entry amount is included in the total. The fact that the numbers don't match when you check them, after making journal entries, but do match after closing then re-opening the Form, would suggest that this isn't happening, i.e. the amount of the last entry, before closing the Form, isn't included at the time you're initially checking it.

    Where, exactly, do you have this Validation code for the Control named risk_terr_abbr? What 'debug error' is being displayed and what line is highlighted when the code window comes up? There are only two things that I can see that could cause an error, in this little bit of code. One would be if Access couldn't find a Control named risk_terr_abbr, and the fact that the Messagebox is being displayed (I assume that's what you mean by "If they leave this blank, it gives them the error message") would indicate that this is not the problem. The other would be if the event that you have the code in is one that cannot be Canceled.

    Since the Messagebox is being displayed, the error has to be caused by something that comes after this code, perhaps the error in using Cancel = True, or perhaps something else. As I said before, where, exactly, do you have this Validation code for the Control named risk_terr_abbr?

    Linq ;0)>
    Hi Linq,

    Thanks for all the help so far! Ill provide screen shots as to what is happening.

    Attached is a word document with what is wrong, it seems to do with the

    If Me.Dirty Then Me.Dirty = False

    But it could be because of other stuff this isn't working.
    Attached Files Attached Files

  12. #12
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    That probably is the wrong spot for the code to force a save of the Record, and hence the error, but very little can really be done based on screen shots. Dave and I have been responding with general answers, because you've only been giving us a general idea of what you're doing, without the specifics of how you're doing it. We're at a point here where you really need to Zip your file up and attach it to a post so that myself and perhaps others can take a look at it.

    When attaching a database file

    • Zip the file and attach it to a post
    • Give the File Format, i.e. the Access version it was created in
    • Give the name of the Form(s) or other Objects involved in the problem
    • Give a brief explanation as to how to recreate the problem

    I'll be happy to take a look at the file this weekend and see if I can figure out what's going on, and perhaps others will, as well.


    Linq ;0)>
    Last edited by Missinglinq; 04-27-12 at 10:45.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  13. #13
    Join Date
    Sep 2011
    Posts
    48
    Quote Originally Posted by Missinglinq View Post
    That probably is the wrong spot for the code to force a save of the Record, and hence the error, but very little can really be done based on screen shots. Dave and I have been responding with general answers, because you've only been giving us a general idea of what you're doing, without the specifics of how you're doing it. We're at a point here where you really need to Zip your file up and attach it to a post so that myself and perhaps others can take a look at it.

    When attaching a database file

    • Zip the file and attach it to a post
    • Give the File Format, i.e. the Access version it was created in
    • Give the name of the Form(s) or other Objects involved in the problem
    • Give a brief explanation as to how to recreate the problem

    I'll be happy to take a look at the file this weekend and see if I can figure out what's going on, and perhaps others will, as well.


    Linq ;0)>
    that would be most helpful

    Attached is the database, I am looking at the form Total_NonClaim.

    I am basically trying to make sure that the totals match before they can exit. Also some of the stuff in the database might be done very wrong, I am not very experience at Access and did things at the best of my knowledge.

    It access 2003.

    thanks,

    Kevin Kristensen
    Attached Files Attached Files

  14. #14
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Quote Originally Posted by Missinglinq View Post
    • Give a brief explanation as to how to recreate the problem
    You left out this step! When coming here for help, you have to remember that the chances of someone here understanding the particular business/industry you're in are slim. We have to rely on you to bring us up to speed, in order to help you. You need to walk us through reproducing your problem. We need to know things like

    • What Form and Control are used to enter the total wire amount?
    • What Form and Control are used to enter the field journal entry amounts?

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  15. #15
    Join Date
    Sep 2011
    Posts
    48
    Quote Originally Posted by Missinglinq View Post
    You left out this step! When coming here for help, you have to remember that the chances of someone here understanding the particular business/industry you're in are slim. We have to rely on you to bring us up to speed, in order to help you. You need to walk us through reproducing your problem. We need to know things like

    • What Form and Control are used to enter the total wire amount?
    • What Form and Control are used to enter the field journal entry amounts?

    Linq ;0)>
    oops sorry about that, I guess I was unclear.

    I was able to figure out what I was doing wrong over the weekend.

    Thanks for all of the help, in the end I was able to do what you told me to do, just correctly this time

Posting Permissions

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