Results 1 to 10 of 10
  1. #1
    michaeljohnh is offline More Human than Human
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    61

    Increment a value on button click

    I am making a database that has a table consisting of 3 columns: problems, corresponding solution, and the amount of time that problem/solution has been selected. I made a form with a combo box that allows the user to select the problem and the corresponding solution is displayed. What I want to do is add a button that when clicked will increment the Usage field of the corresponding record that has been selected by the user. This will assist in tracking the most common problems being reported. I tried writing some VB for the On Click function of the button to increment Usage by one but cant seem to get it right.



    Any help greatly appreciated.

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What do you have so far?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    michaeljohnh is offline More Human than Human
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    61
    So far I have made the table with the 3 fields, Problem, Solution, Usage.
    Its name is Table. I have made the form and made its source be Table. The form is named Form. The form has the combo box and Solution field from Table. I added a button and in the On Click event put in [Usage]=[Usage] + 1 , since that format works in other versions of BASIC. But when I click on the button, Usage does not increase.

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    It doesn't sound like the usage field is represented on the form? If you add a textbox for usage it should work (it can be hidded). I'd use Me:

    Me.UsageTextboxName = Me.UsageTextboxName + 1
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    I am not sure if the combo box will be synchronized with the textbox.

    But you can use update query to perform the increment

    private sub Command10_click()
    currentdb.execute "update tablename set usage=usage+1 where solution='" +combo1 + "'"
    end sub

  6. #6
    michaeljohnh is offline More Human than Human
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    61
    "It doesn't sound like the usage field is represented on the form? If you add a textbox for usage it should work (it can be hidded). I'd use Me:

    Me.UsageTextboxName = Me.UsageTextboxName + 1 "


    "Usage" field is on the form, I forgot to mention it though. Can the above be adjusted accordingly?

  7. #7
    michaeljohnh is offline More Human than Human
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    61
    Quote Originally Posted by weekend00 View Post
    I am not sure if the combo box will be synchronized with the textbox.

    But you can use update query to perform the increment

    private sub Command10_click()
    currentdb.execute "update tablename set usage=usage+1 where solution='" +combo1 + "'"
    end sub
    I did try this but the corresponding Usage field did not increase. I changed "tablename" to "Table", was there any other adjustments I needed to make?

  8. #8
    michaeljohnh is offline More Human than Human
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    61
    Looks like Im making progress. Not sure what I did wrong before but its working almost as intended.

    Here's what I got:

    Private Sub Button_Click()
    [Usage] = [Usage] + 1
    DoCmd.Close acForm, "Form", acSaveNo
    End Sub

    So the Usage field is being updated on the form but the Usage field in the source table named Table was not updating until the form closed. So, how do I get Usage on the source table to update when I click the button w/o having to close the form? At that point Ill be able to remove the DoCmd.

    Thank you.

  9. #9
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    In my way, you may try following:
    private sub Command10_click()
    currentdb.execute "update tablename set usage=nz(usage,0)+1 where solution='" +combo1 + "'"
    form.refresh
    end sub

    In PBALDY's way, I know that changes will not be saved until the focus leave current record in datasheet view. I think this happens to a form too. So you need to force it to save, you may try:
    Private Sub Button_Click()
    [Usage] = [Usage] + 1
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord
    End Sub

  10. #10
    michaeljohnh is offline More Human than Human
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    61
    Thank you very much for everyone's help. It is working as intended now. I learned a lot. Already working on the next iteration of the Db.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. On Click Event For Button On Form
    By Desstro in forum Forms
    Replies: 3
    Last Post: 08-09-2010, 02:36 PM
  2. Replies: 1
    Last Post: 03-03-2010, 07:29 PM
  3. VB coding for saving when click on save button in form
    By cwwaicw311 in forum Programming
    Replies: 1
    Last Post: 02-04-2010, 11:11 PM
  4. How to Increment A Number Field
    By Advanced in forum Programming
    Replies: 3
    Last Post: 01-27-2010, 02:36 PM
  5. Replies: 2
    Last Post: 12-08-2009, 01:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums