Hi Katherine,
If the text box is bound (ie. has a control source) then you can use it's Before_Update event
procedure to run the following code. This sample is written to work in the Northwind sample
database, throwing up an error message if an existing OrderDate from the Orders table is entered
into a textbox named txtDate:
Private Sub txtDate_BeforeUpdate(Cancel As Integer)
On Error GoTo ProcError
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT OrderDate FROM Orders WHERE OrderDate = #" & Me.txtDate & "#"
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then ' We have found an existing date
MsgBox "The date you selected is not available" & vbCrLf _
& "Please select another date.", vbCritical, "Date selected has been booked..."
Me.Undo
End If
ExitProc:
' Cleanup
On Error Resume Next
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, , _
"Error in txtDate_BeforeUpdate event procedure"
Resume ExitProc
End Sub
I have a nice little calendar form that can be used to enter dates in any textbox on any form. I
usually call it using the Double_Click event procedure for the textbox. Shown below is basically
the same code, except that there are additional lines of code used to invoke the frmCalendar
form. I can send you a sample database that includes the test form and frmCalendar that I used
to provide this answer, if you send me a private e-mail message with a valid e-mail address.
Private Sub txtDate_DblClick(Cancel As Integer)
On Error GoTo ProcError
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim frmCal As New Form_frmCalendar
Dim varResponse As Variant
Dim strSQL As String
Set db = CurrentDb()
strSQL = "SELECT OrderDate FROM Orders WHERE OrderDate = #" & Me.txtDate.Text & "#"
' Optional parameter: Use "W" for Monday of the current week,
' "M" for first day of current month, and "Y" for first day of current
' year. If today's date is a Sunday, then the default for "W" will
' be the previous Monday.
varResponse = frmCal.ReturnDate(Me.txtDate, "w")
If Not varResponse = vbNullString Then
Me.txtDate = varResponse
End If
Me.txtHidden.SetFocus
Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then ' We have found an existing date
MsgBox "The date you selected is not available" & vbCrLf _
& "Please select another date.", vbCritical, "Date selected has been booked..."
Me.txtDate = ""
Me.txtDate.SetFocus
End If
ExitProc:
' Cleanup
On Error Resume Next
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, , _
"Error in txtDate_DblClick event procedure"
Resume ExitProc
End Sub
Tom
__________________________________
I'm completely new to coding in VB and I'm making a database. I am starting to pick up a few
things and am hoping someone can teach me something new here.
I have a text box in which the date is entered after it has been picked on a drop down calendar.
Before the user moves off from the date text box, I want to run a query that checks if the date
entered is a valid date by comparing it to a table of invalid dates. If it finds a match in the
query, I want to code it so that an error box pops up and says "Invalid date, pick another" and,
if the query produces no matches, I want the user to just be able to continue entering the record
as per usual. Please help
