date checker

[coyote69]

I am trying to perform a check of an appointment date field in an access database to verify that the user does not enter an overlapping appointment date and time. the database is bound to vb. I lock the txtbox field and force the user to use a cmd button to change the appointment. I am not tha good at this yet (obviously). I cannot get the record to update the date or display an error message if that date and time are taken
here is the mess I have so far.


Private Sub apptcheck()
'Defines variable used in date check
Dim apptdates As String
Dim newApptDate As String

'assigns value to from field for search
apptdates = datContactManager.Recordset("apptdate&quot.Value





'Calls input box to check new appointment time and assigns variable

newApptTime = InputBox("Enter desired Appointment time&quot

'checks to see if the criteria is meet
If datContactManager.RecordSource = "select * from contactmanager were [apptDate]= #" & apptdates & "# and were [Time]= #" & newApptTime & "#" Then

MsgBox "That time is already taken for that day"

Exit Sub

Else:
txtAppointmentDate.Locked = False

txtAppointmentDate.Text = newApptDate

End If
End Sub

 

[FancyPrairie]

There are several ways to do it. If you want to do it like you're attempting to, try this:

Me.RecordsetClone.FindFirst "apptdate = #" & newApptTime & "#"
if (Me.RecordsetClone.NoMatch) then
'**** don't need to unlock the control ****
txtAppointmentDate = newApptDate 'Without the ".Text"
else
msgbox "no match"
end if

 

[FancyPrairie]

By the way, this will work if you only have 1 user using the database at a time. However, if not, then this concept will not work, for several reasons. The main reason is that when you check it, it may not exist. But by the time you write it out to the table, someone else may have already written the same value to the table. Now you have a duplicate.

The best way to ensure that there are no duplicates is to define the field in the table as having a unique index.

 

[coyote69]

This is just for single user . I appreciate the advice. I tried that and get and error when it looks for recordsetclone. Is diplays a message the the method is not found and has the recordsetclone highlighted.

 

[FancyPrairie]

It should work, I just tested it. I'm assuming that you are selecting the command button from within a Access form. Can you show me your code?

 

[coyote69]

I am accessing it through vb form that is bound to the access database
this work sort of but when it updates the appointment date it updates the first record not the current




Dim newappt As String

newappt = InputBox("Enter new appointment date and time", " _/_/__, _ am/pm&quot




On Error GoTo errorhandler

datContactManager.Recordset.FindFirst "apptdate = #" & newappt & "#"
On Error GoTo errorhandler

If (datContactManager.Recordset.NoMatch = False) Then

'msgbox display if date is not available
MsgBox "That appointment time is not Available" 'Without the ".Text"

Exit Sub

Else
txtAppointmentDate.Locked = False


txtAppointmentDate = newappt


Exit Sub
End If
errorhandler:
Exit Sub


End Sub

 

[FancyPrairie]

Sorry, I thought you were working with an Access form. Not that familiar with vb forms. However, what you have listed looks good. Does everything work except this line of code "txtAppointmentDate = newappt"? If so, I would check to see what record you're actually referencing prior to issuing the Recordset.findfirst command and then checking to see what record you're referencing after the command.

 

[coyote69]

Yes it works fine except except it does not update the appointment date in the records that I am viewing. It updates the appointment date in the first record in that fields. How do I check to see what records i am referencing?

 

[FancyPrairie]

I sent the following message right after I sent you the last message. However, I sent it to the wrong person. Anyway...

Just remembered something. When you issue the Recordset.FindFirst command, your current record will become the record it found. If no record was found (NoMatch) then the 1st record will be the current record. That's why I used the RecordsetClone.FindFirst method for my search rather than the Recordset.FindFirst. Since you don't have the RecordsetClone method in vb forms, then you need to search for the information another way. Either create a different recordset to search on or do a DLookup or some other way to search the table for the data.

Note, I would still define the field in the Access database as a unqiue index. This would prevent other applications, etc. from writing a bad (duplicate) value to the record.

 

[coyote69]

Thanks, I know I could do it in access buyt wanted to get it to function using code.