Results 1 to 2 of 2
  1. #1
    Join Date
    May 2010
    Posts
    23

    Unanswered: Retrieving text box value from a Form

    Hi there,

    I have an "Orders" form for my users to use when we receive an order. In this form I have and Order_ID text box in the top corner (it's locked) to show the user what the order number is.

    My goal is to timestamp the recording of each order as soon as it's saved by the users (i.e. I want to know what time the order is entered into the system).

    I'm not sure the best way to do this, but right now I have a hidden control on the form named "Date_Processed" bound to the date processed field in my Orders Table.

    I have written the following VBA code, but no data is being recorded into my date fields in the Orders table.

    Code:
    Private Sub Form_AfterInsert()
        Dim MySQL As String
        
        'Insert Order Processed Date Upon Save
        If Forms!Orders!Date_Processed.Value = Null Then
            MySQL = "UPDATE [Orders] SET [Date_Processed] = date() WHERE ((Order_ID = Forms!Orders!Order_ID.Value))"
            DoCmd****nSQL MySQL
        Else
        End If
    End Sub
    Last edited by sirrip; 05-22-10 at 03:52.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    First off, I'd move the code to the Form_BeforeUpdate event. And since I assume this code is appearing in the form's own code module, you can replace the cumbersome

    Forms!Orders!Date_Processed

    with the shorter

    Me.Date_Processed

    Also, you cannot check for Nulls in VBA code the way you have.

    Try this

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    
    Dim MySQL As String
    
        'Insert Order Processed Date Upon Save
        If Nz(Me.Date_Processed,"") = "" Then
            MySQL = "UPDATE [Orders] SET [Date_Processed] = date() WHERE ((Order_ID = Forms!Orders!Order_ID.Value))"
            DoCmd****nSQL MySQL
        End If
    End Sub
    Due to a quirk in this site, DoCmd lines are modified by inserting a series of asterisks, so simply insert your actual line in the code above for

    DoCmd****nSQL MySQL
    Hope this helps!

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

    All posts/responses based on Access 2003/2007

Posting Permissions

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