05-22-10, 03:15 #1Registered User
- Join Date
- May 2010
Unanswered: Retrieving text box value from a Form
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.
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.
05-22-10, 10:39 #2Moderator
Provided Answers: 19
- Join Date
- Jun 2005
- Richmond, Virginia USA
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
with the shorter
Also, you cannot check for Nulls in VBA code the way you have.
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
DoCmd****nSQL MySQLHope this helps!
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007