[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