Use VBA to set value of Text Box to Date()

[bconner]

I have a data entry form and I want to set the value of the 'Request Date' field to equal the Current Date Date() after the Form is Cleared for New Entry. Below is the code I am using however it enters 1/6/1900 as the 'Request Date' value....

Private Sub Command21_Click()
Dim Rs As Recordset
 
'Test to make sure Required Fields are Populated

If (IsNull(Me.[Request Batch].Value) = False And IsNull(Me.[Provider].Value) = False _
And IsNull(Me.[Requestor].Value) = False And IsNull(Me.[Patient or Client Name].Value) = False _
And IsNull(Me.[Lab Site Name].Value) = False And IsNull(Me.[Lab Site Name].Value) = False And _
IsNull(Me.[Account Number].Value) = False And IsNull(Me.[Refund To].Value) = False _
And IsNull(Me.[Refund Reason].Value) = False And IsNull(Me.[Invoice Number].Value) = False _
And IsNull(Me.[Refund Amount].Value) = False And IsNull(Me.[Date of Service].Value) = False) = True Then
 
 
'Updates Refund Letter Table
Set Rs = CurrentDb.OpenRecordset("Main Table", dbOpenDynaset)
Rs.AddNew
Rs![Request Date] = Me.[Request Date]
Rs![Request Batch] = Me.[Request Batch]
Rs![Account Number] = Me.[Account Number]
Rs![Invoice Number] = Me.[Invoice Number]
Rs![Refund To] = Me.[Refund To]
Rs![Refund Amount] = Me.[Refund Amount]
Rs![Group Number] = Me.[Group Number]
Rs![Date of Service] = Me.[Date of Service]
Rs![Provider] = Me.[Provider]
Rs![Requestor] = Me.[Requestor]
Rs![Patient or Client Name] = Me.[Patient or Client Name]
Rs![Lab Site Name] = Me.[Lab Site Name]
Rs![Invoice Balance] = Me.[Invoice Balance]
Rs![Other Balances] = Me.[Other Balances]
Rs![Refund Reason] = Me.[Refund Reason]
Rs![Address on File?] = Me.[Address on File?]
Rs![Other Address] = Me.[Other Address]
Rs![Comments] = Me.Comments
Rs.Update
Rs.Close
Set Rs = Nothing
 
 
'Clear Form for New Entry
Me.[Request Date].Value = vbDate
Me.[Request Batch].Value = Null
Me.[Account Number].Value = Null
Me.[Invoice Number].Value = Null
Me.[Refund To].Value = Null
Me.[Refund Amount].Value = Null
Me.[Group Number].Value = Null
Me.[Date of Service].Value = Null
Me.[Provider].Value = Null
Me.[Requestor].Value = Null
Me.[Patient or Client Name].Value = Null
Me.[Lab Site Name].Value = Null
Me.[Invoice Balance].Value = Null
Me.[Other Balances].Value = False
Me.[Refund Reason].Value = Null
Me.[Address on File?].Value = False
Me.[Other Address].Value = Null
Me.Comments.Value = Null
 
 

Else
MsgBox ("Please Review and Complete all Required Fields. All Blue fields are Required")
End If
End Sub

 

[boblarson]

Change this line:
Me.[Request Date].Value = vbDate

to this:

Me.[Request Date].Value = Date

vbDate = 7 which is, as you found out, 7 days past day 0 which day 0 is 12/30/1899

 

[vbaInet]

I'm guessing this form is unbound?

How about setting the Format property of the textbox to Short Date?

 

[boblarson]

Oh, and if just DATE doesn't work, it actually is

VBA.Date

not vbDate.

 

[Steve R.]

You don't need the code below "Clear Form for New Entry". Your form should be unbound. I have a similar style form. After updating to the new record by pressing a "Save" command, the existing form is zeroed except for things like the date. New data is then entered and the "Save" button re-executes the "addnew" code besides zeroing out the form.

I use a slightly different approach to your if statement. Each field has its own logical variable. In the end it still boils down to a large if statement. But it does allow me to isolate "bad" fields.

 

[bconner]

Thanks everyone for your help, I really appreciate it.....