Checking staff against booking date?

[bazzanoid]

Afternoon,

An extra feature I need to add to my database is puzzling me a little, yet I know it can be done! Access 2007...

When I create a booking, i want to make sure that the staff member i book onto it isn't already allocated to another booking, and alert me with a pop up that says something along the line of "[EAStaff] is already booked on this date for [Park]" if they are. Trouble is, it's a multiple-value combo box, so it needs to interrogate the individual entries and return just that value. Having some trouble with that elsewhere... but that's another story!

So, I need to get it to check the other bookings on that particular date for that staff member. Relevant fields, all in the 'Bookings' table, are:

VisitDate
EAStaff
Park

I know it's a fairly straightforward compare, but I can't figure it out! I'm having a headache day today....

Thanks!

Barry

 

[bazzanoid]

Still can't solve this one!!! Any thoughts?

 

[CyberLynx]

Use either the DLoopUp function or the DCount function. Either of those can tell you what you need. See Access Help with regards to these two functions.

.

 

[bazzanoid]

Use either the DLoopUp function or the DCount function. Either of those can tell you what you need. See Access Help with regards to these two functions.

.

For some reason, I really can't wrap my head round these.... help!

 

[bazzanoid]

Having read the Help file, it appears to be something like this, but it doesn't appear to work, and i'm not sure what it would say if there's a conflict:

=DLookUp([EAStaff1] & [EAStaff2] & [EAStaff3],[Bookings],[Bookings].[VisitDate]=[VisitDate])

 

[Rabbie]

That doesnt look right. i would expect to see the field names in quotes in a Dlook up statement.

In order to help solve your problem what actually is held in field EAStaff? Is it the actual name or is it the link to another table holding the name?

 

[bazzanoid]

That doesnt look right. i would expect to see the field names in quotes in a Dlook up statement.

In order to help solve your problem what actually is held in field EAStaff? Is it the actual name or is it the link to another table holding the name?

The fields EAStaff1/2/3 hold the actual name, selected on the form looked up from table EAStaff, field [Staff Member]. I need to check the selected staff after update to make sure they're not already booked elsewhere on that date, hence needing to check all 3 fields as there can be up to 3 members of staff per booking.

In english: Select name of EAStaff 1/2/3 for this booking from the list on the form (a lookup from EAStaff.[Staff Member]). DLookup checks the VisitDate for other bookings that contain the same value as the just-selected staff member to avoid duplicate bookings, returns an error if there is a conflict.

Hope that makes sense!

 

[CyberLynx]

As you have already gathered, you are going about this a wee bit backwards. If you have multiple Table Fields to check across then use those Fields within the criteria of the DLookUp function. Mind you his wouldn't help you a whole lot in this particular situation because you want to know which name is not available. Always remember, the fields that go into DLookUp are "Table" Fields.

You obviously have a Date entry Text Box located on your Form. For the sake of argument, let's say it is named the same as the Table's Date Field which is VisitDate. Now use the DLookUp Function to check against the Date from that in Table and the date on Form and see if any of the three names in the Text Boxes on Form are already scheduled. You need to lace this into the Forms code Module under the BeforeUpdate event to as to catch the issue before the record is saved (if Form is bound to Table).

Private Sub Form_BeforeUpdate(Cancel As Integer)
   Dim VDate As Variant
   Dim MsgHead As String, MsgTail As String
   Dim MemItem As Integer

   VDate = Me.VisitDate
   If IsNull(VDate) Then 
      MsgBox "You need to supply a Date before selectng a Staff Member!", _
             vbExclamation, "No Date Available"
      Cancel = True
      Me.VisitDate.SetFocus
      Exit Sub
   End If

   MsgTail = " is already booked on " & me.VisitDate & "." & vbCr & "Please select some other Staff Member."

   If Nz(DLookUp("[Staff Member]", "[Bookings]", "[VisitDate]=#" & Me.VisitDate & _
                 "# AND [Staff Member]='" & Nz(Me.EAStaff1, "") & "'"), "") <> "" Then
      Msg = "I'm afraid " & Me.EAStaff1 
      MemItem = 1
   ElseIf Nz(DLookUp("[Staff Member]", "[Bookings]", "[VisitDate]=#" & Me.VisitDate & _
                     "# AND [Staff Member]='" & Nz(Me.EAStaff1, "") & "'"), "") <> "" Then
      Msg = "I'm afraid " & Me.EAStaff2  
      MemItem = 2
   ElseIf Nz(DLookUp("[Staff Member]", "[Bookings]", "[VisitDate]=#" & Me.VisitDate & _
                     "# AND [Staff Member]='" & Nz(Me.EAStaff1, "") & "'"), "") <> "" Then
      Msg = "I'm afraid " & Me.EAStaff3
      MemItem = 3
   End If

   If Msg <> "" Then 
      Msg = Msg & MsgTail
      MsgBox Msg, vbEclamation, "Member Already Booked"
      Cancel = True
      Me.Controls("EAStaff" & MemItem).SetFocus
   End If
End Sub

pffft....And you thought it was confusing. Is it now?

There are several other ways to accomplish this task. For example You could use the DCount Function as well in a similar fashion.

Please keep in mind that the code above is air code (off the top of my head) and not tested.


.

 

[bazzanoid]

Actually makes sense now, I really should take a course in 'pure' VBA as i cobble coding like that together from various sources and it ends up looking so messy!!! I've separated the MsgHead & MsgTail DIM declarations to individual lines, for some reason access complained about them on the same line.

Yours looks great and from first glance should work, but there's the small issue of

   If Nz(DLookup("[Staff Member]", "[Bookings]", "[VisitDate]=#" & Me.VisitDate & _
                 "# AND [Staff Member]='" & Nz(Me.EAStaff1, "") & "'"), "") <> "" Then

giving me a 2471 runtime error!

"The expression you entered as a query parameter produced this error: '[Staff Member]'

Looks fine to me, any thoughts?

 

[CyberLynx]

I've separated the MsgHead & MsgTail DIM declarations to individual lines, for some reason access complained about them on the same line.[/quote

This is because you can not have two Dim statements in the same line unless the line is separated with a colon (:). It should have been:

Dim MsgHead As String, MsgTail As String

As for the second item, is there in fact a field in Table named Staff Member?

If not then what is the field in Table named which holds the Staff Member name?

If there is and it is spelled correctly then what is the Data Type of the Staff Member field? Is it Text or Number?

If it is indeed a Text Data Type, can it contain nothing (is the Required property set to Yes)?

.