Results 1 to 10 of 10

need help getting a value on form stored to 1st record of a table

  1. #1
    24t42 is offline Novice
    Windows XP Access 2010 (version 14.0)
    Join Date
    Nov 2010
    Posts
    6

    need help getting a value on form stored to 1st record of a table

    I have a form with a field named ID. I need to store the value of that ID in a table called tblID in the field SID. tblID only has one record as it is temporary storage and gets replaced often.



    Any help in writing the vba would be most appreciated.

  2. #2
    24t42 is offline Novice
    Windows XP Access 2010 (version 14.0)
    Join Date
    Nov 2010
    Posts
    6

    Almost working...

    My code is working somewhat. It works the 1st time I use the code. However, on the 2nd try it add a new record instead of replacing the value currently stored. I need it to replace the value SID. What am I doing wrong?

    Code:
    DoCmd.OpenTable "tblID"
    CurrentDb.Execute "INSERT INTO tblID (SID) VALUES (" & ID.Value & ")"

  3. #3
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,620
    Sounds like you want an update query instead of an append query.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    24t42 is offline Novice
    Windows XP Access 2010 (version 14.0)
    Join Date
    Nov 2010
    Posts
    6
    What changes do I need to make to the code for an update query. I am very unfamiliar with VBA

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,620
    Not really a VBA change, just an SQL change.

    "UPDATE TableName SET FieldName = " Me.iD
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    24t42 is offline Novice
    Windows XP Access 2010 (version 14.0)
    Join Date
    Nov 2010
    Posts
    6

    Still confused

    Thank you for the suggestion but I do not know what to do with the code. I want the code to run when I click a command button. So exactly how and where do I use the code. Sorry to be so dense!

    Code:
    Private Sub btn_printreceipt_Click()
    
    ???????
    
    End Sub

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,620
    That was a different query:

    CurrentDb.Execute "UPDATE TableName SET FieldName = " Me.iD
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,608
    Code:
    Private Sub btn_printreceipt_Click()
    
      Currentdb.execute "Update tblID set SID = " & me![ID]
    End Sub
    will update the table as you want it to.

    (But you'll need more code to print anything...)

  9. #9
    24t42 is offline Novice
    Windows XP Access 2010 (version 14.0)
    Join Date
    Nov 2010
    Posts
    6
    Thank you John and Paul. It now works perfectly. Such a simple answer.

    Thanks again.
    Judy

  10. #10
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,620
    No problem.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 1
    Last Post: 08-05-2014, 03:36 AM
  2. Replies: 1
    Last Post: 11-15-2012, 09:16 AM
  3. Replies: 5
    Last Post: 05-17-2011, 11:02 AM
  4. Use VBA to call stored SQL from table
    By hcruz in forum Programming
    Replies: 2
    Last Post: 05-02-2011, 08:26 AM
  5. combo box selection stored in table
    By combine21 in forum Forms
    Replies: 1
    Last Post: 12-02-2010, 09:57 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
  •  
Tech Forums: Microsoft Office Forums