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

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?

VB Code:

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

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

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.

VB Code:

Private Sub calBookingDate_Click()
    
    '--- Set [bookingDate] to date selected on calendar ---'
    bookingDate.Value = calBookingDate.Value
    
    checkBooking bookingDate.Value, timeID.Value, areaID.Value
    
End Sub

VB Code:

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

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

VB Code:

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?!?!?!?!

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

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

VB Code:

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)

VB Code:

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

should work i think

pete

cool, that works even better

Right the code all works and the result is:

VB Code:

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