[Resolved] Access VBA - Checking for entry on the fly

[c03cg]

Im writing a booking system for a school room in Access, as the school wants it done in an office package!

I need to check wether a booking for the date, time and area has already been made one the three relevant fields have been filled. (areaID [number] , bookingDate [short date], timeID [number]) what ever i do seems to not work.

Ive tried a "SELECT INTO" then "SELECT COUNT" SQL statement but had no joy with that!

Any ideas?

[Static]

If IsNull(Dlookup("AreaID","TABLE NAME","bookingDate=#" & dDate & ")) Then
'Date Not used
Else
'Date taken
End If

[c03cg]

Coolies... ill write some code then post it for marking (",)

[c03cg]

ok.... so it doesn't seem to work quite right.... if i edit a record its fine, but if im adding a record it lets me do any date/time/area combo.

Private Sub calBookingDate_Click()

'--- Set [bookingDate] to date selected on calendar ---'
bookingDate.Value = calBookingDate.Value

checkBooking bookingDate.Value, timeID.Value, areaID.Value

End Sub

Function checkBooking(dDate As Date, iTime As Integer, iArea As Integer)

'--- Check to see if date/area combo is not taken ---'
If IsNull(DLookup("areaID", "booking", "bookingDate=#" & dDate & "#")) Then
'--- Area is not being used on the selected date ---'

MsgBox "Date Not used"

Else
'--- Area is being used on the selected date ---'

MsgBox "Date taken"

'--- check to see if area/date/time combo is free ---'
If IsNull(DLookup("areaID", "booking", "timeID=" & iTime & "")) Then
'--- Area/date/tim is free ---'

MsgBox "Time Slot Not Taken"

Else
'--- Area/date/tim is taken ---'

MsgBox "Time Slot Taken"

End If

End If

End Function

[c03cg]

Right..... any one up for sorting this one out?:

If Not IsNull(DLookup("areaID", "booking", "areaID=" & areaID)) Then
'--- Area is not being used ---'

sError = "A"

End If

If Not IsNull(DLookup("bookingDate", "booking", "bookingDate=#" & dDate & "#")) Then
'--- Area and date are not being used ---'

sError = sError & "D"

End If

If Not IsNull(DLookup("timeID", "booking", "timeID=" & timeID)) Then
'--- Area, date and time are not being used ---'

sError = sError & "T"

End If

MsgBox sError

This is split apart so i could test it......but

The First and third work fine but the date bit only works on the first record in the table (currently 07/06/2006) but wont recognise the second (08/06/2006) why?!?!?!?!

[westconn1]

access database reqire date to be "mm/dd/yyyy", if you are trying to use "dd/mm/yyyy" you will get problems, you need to format your date to suit.

i can't really tell if this is you problem, just seems as if it might.

pete

[c03cg]

Yes.... that was the problem..... I'd just trawled thru googles results and found that out so ive done this:


Dim dDate As Date
Dim sDate() As String
Dim iTime As Integer
Dim sError As String

'--- Set [bookingDate] to date selected on calendar ---'
bookingDate.Value = calBookingDate.Value

dDate = bookingDate.Value
iTime = timeID.Value

sDate = Split(dDate, "/")
dDate = sDate(1) & "/" & sDate(0) & "/" & sDate(2)

[westconn1]

mydate = format(sdate, "mm/dd/yyyy")

should work i think

pete

[c03cg]

cool, that works even better

[c03cg]

Right the code all works and the result is:


If Not IsNull(DLookup("timeID", "booking", "timeID=" & iTime)) _
And Not IsNull(DLookup("bookingDate", "booking", "bookingDate=#" & dDate & "#")) _
And Not IsNull(DLookup("areaID", "booking", "areaID=" & iArea)) Then

sCode = DLookup("staffCode", "booking", "bookingDate = #" & dDate & "# AND timeID = " & iTime & " AND areaID = " & iArea)

sForename = DLookup("[staffForeName]", "staff", "[staffCode] = '" & sCode & "'")

sSurname = DLookup("staffSurName", "staff", "staffCode = '" & sCode & "'")

MsgBox "The booking has already been booked by " & sForename & " " & sSurname

End If