Results 1 to 3 of 3
  1. #1
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows 7 64bit Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202

    Question Update single record via forum, not entire table?

    Hello all I am trying to make a script work the way that I want it too but it's giving me a hard time. I am very new to scripting and I am relying a lot on samples to piece together what I want to do.

    The main problem that I am having right now is that the script that I have created will update all records in a table with the given input, but I only want it to update the record that I am currently viewing in the form.

    Here is my code....

    Code:
    Function UpdateTransactionDate() As Boolean
        Dim db As Database
        Dim LUpdate As String
        Dim LMsg As String
        Dim LTransactionDt As Date
        On Error GoTo Err_Execute
        'Query user for Date of R
        LMsg = "Enter the Date of R Form __/__/____"
        LMsg = LMsg & Chr(10) & Chr(10) & "Format date as:   mm/dd/yyyy"
        LTransactionDt = InputBox(LMsg)
        Set db = CurrentDb()
        'Re-Assign Date to TransactionDate
        LUpdate = "update [Tasks]"
        LUpdate = LUpdate & " set [UpdateDate] = #" & Format(LTransactionDt, "mm/dd/yyyy") & "#"
        db.Execute LUpdate, dbFailOnError
        Set db = Nothing
        MsgBox "Changing the Dates was Successful."
        UpdateTransactionDate = True
        On Error GoTo 0
        Exit Function
    Err_Execute:
        MsgBox "Updating the dates failed, you will need to enter each date individually."
        UpdateTransactionDate = False
    End Function
    What do I need to change to have it only work with the selected record?

    TIA for any help you can give me, this is driving me nuts.



    Ultimately I would like to have a button that when pressed will copy a current field to another field on the same record, then prompt for a new ticket number, and a new update date. But I am trying to take baby steps at first and just do one thing per module and then hopefully be able to build a module that will do all of those things in one run.

  2. #2
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    if you want to update the current record on you form, update the fieldname on you form, don't use update statement:

    Function UpdateTransactionDate() As Boolean
    Dim LMsg As String
    Dim LTransactionDt As Date
    On Error GoTo Err_Execute
    'Query user for Date of R
    LMsg = "Enter the Date of R Form __/__/____"
    LMsg = LMsg & Chr(10) & Chr(10) & "Format date as: mm/dd/yyyy"
    LTransactionDt = InputBox(LMsg)
    'Re-Assign Date to TransactionDate
    UpdateDate = cdate(LTransactionDt)
    UpdateTransactionDate = True
    MsgBox "Changing the Dates was Successful."
    On Error GoTo 0
    Exit Function
    Err_Execute:
    MsgBox "Updating the dates failed, you will need to enter each date individually."
    UpdateTransactionDate = False
    End Function

  3. #3
    10 Gauge's Avatar
    10 Gauge is offline Getting the hang of it...
    Windows 7 64bit Access 2007
    Join Date
    Feb 2011
    Location
    Fredericksburg, VA
    Posts
    202
    Thanks for the speedy response. I tried that and I didn't get an error message but the date didn't update with what I entered in the message box.

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

Similar Threads

  1. Expand entire row not just single cell in a Report
    By CityOfKalamazoo in forum Reports
    Replies: 2
    Last Post: 07-28-2016, 09:22 AM
  2. Replies: 5
    Last Post: 03-23-2011, 10:39 AM
  3. Replies: 7
    Last Post: 11-13-2010, 08:08 AM
  4. Replies: 1
    Last Post: 07-21-2010, 07:27 AM
  5. How to loop code to run through entire table
    By kmajors in forum Reports
    Replies: 9
    Last Post: 04-23-2010, 09:27 AM

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