Hi there,

In my db which is almost complete now, thanks to some brilliant people in here, I have a function which I'd like to add.

I've got a many to many relationship between equipment and visit. We're an acoustics consultancy which make visits to site with equipment. this is a whole load of equipment which of course is re-usable as they're sound level meters etc. So there needs to be a many to many relationship due to equipment can be used on many different visits but one visit can require more than one bit of equipment.

So now the problem arises of double booking... Therefore I'm trying to create, in VBA, a query which will count the number of records where the visit date and the equipment are the same.

I need to do this query using visit date and equipment id, so what I've done in the many-many table is create the following fields:

lngzVisit
lngzEquipment


lngzVisit is a lookup to the visit table and the lookup combo box has 3 columns of idsVisitID, dtmVisitDate, dtmVisitTime and it is set so that column(0) is set to 0cm so that it shows the visit date.

lngzEquipment is a lookup to the equipment query which includes the following fields:
idsEquipmentID,
Equipment (an Alias of chrEquipmentBrand, chrEquipmentModelName, chrEquipmentSerialNo and memEquipmentComments)
chrEquipmentBrand
chrEquipmentModelName
chrEquipmentType
chrEquipmentSerialNo
dtmEquipmentCalDate
dtmEquipmentCalDue
dtmDateEquipmentBought


The alias "equipment" field is the one that is showing in the lookup field, as idsEquipmentID is set to zero length in the combo box.

the form looks like this:

http://i6.photobucket.com/albums/y218/Mr_Si/VisitEquipment.jpg


My Code for this little function is as follows but it is returning 0 results when there are actually 3 listings for this equipment on the same date according the the tblVisitEquipment:



Option Compare Database
Option Explicit

Private Sub lngzEquipment_AfterUpdate()

Dim rst As dao.Recordset
Dim BeginningSQL As String
Dim WhereSQL As String
Dim FinalSQL As String

If IsNull(Me.lngzEquipment) Then
MsgBox "No Equipment Entered.", vbInformation
Exit Sub

Else

'the following SQL is copied direct from the query builder in access
BeginningSQL = "SELECT Count(*) As NumRecords FROM tblVisitEquipment WHERE "

'where the date field is equal to the one entered by the user AND where the equipmentID exists in a different
'entry for the same date, tell the user that the equipment is not available for that day.
'SQL uses the US date format, so the "get date" bit in the where clause needs to format it as long date
'before the SQL will work in the UK

WhereSQL = "tblVisitEquipment.lngzVisit = #" & Format(Me.lngzVisit.Column(1), "MM/DD/YYYY") & "# AND " _
& "tblVisitEquipment.lngzEquipment = " & Me.lngzEquipment & ";"
FinalSQL = BeginningSQL & WhereSQL
MsgBox FinalSQL


Set rst = CurrentDb.OpenRecordset(FinalSQL)
MsgBox "This is the result of the query:" & rst("NumRecords")
If (rst("NumRecords") >= 1) Then
If vbYes = MsgBox("You already have that equipment booked in for a job on " _
& Me.lngzVisit.Column(1) & ", Please choose another", vbExclamation) Then

Me.lngzEquipment = ""

Else
MsgBox "Please choose another bit of equipment", vbOKOnly
Me.lngzEquipment = ""
Me.lngzEquipment.SetFocus

End If

Else
Exit Sub

End If


End If

End Sub



The Full SQL statement is:

http://i6.photobucket.com/albums/y218/Mr_Si/VisitEquipmentQuery.jpg


The Result is:

http://i6.photobucket.com/albums/y218/Mr_Si/VisitEquipmentResult.jpg


The table is:

http://i6.photobucket.com/albums/y218/Mr_Si/tblVisitEquipment.jpg

I was wondering if anyone could shed some light on why I might be getting a "zero" result...?

Does tblVisitEquipment.lngzVisit hold the Time as well as the date. If so that is why you are not retrieving any records.

Also it doesn't look like lngzEquipment = 1. This too would lead to no data being retrieved.

As a temporary diagnostic I would advise removing the lngzEquipment part of the Where clause.

Had a brain wave after struggling over this all morning and the posting it.

I changed some references in my query so that it includes a couple of different tables. It now references the date from the parent subform and the equipment ID from the sub subform and it works.

My new code was this (if anyone was wondering):



Option Compare Database
Option Explicit

Private Sub lngzEquipment_AfterUpdate()

Dim rst As dao.Recordset
Dim BeginningSQL As String
Dim WhereSQL As String
Dim FinalSQL As String

If IsNull(Me.lngzEquipment) Then
MsgBox "No Equipment Entered.", vbInformation
Exit Sub

Else

'the following SQL is copied direct from the query builder in access
BeginningSQL = " SELECT (Count([qryVisit.dtmVisitStartDate] & [tblVisitEquipment.lngzEquipment])) AS NumRecords " _
& "FROM tblVisitEquipment INNER JOIN qryVisit ON tblVisitEquipment.lngzVisit = qryVisit.idsVisitID WHERE "

'where the date field is equal to the one entered by the user AND where the equipmentID exists in a different
'entry for the same date, tell the user that the equipment is not available for that day.
'SQL uses the US date format, so the "get date" bit in the where clause needs to format it as long date
'before the SQL will work in the UK

WhereSQL = "qryVisit.dtmVisitStartDate = #" & Format(Me.lngzVisit.Column(1), "MM/DD/YYYY") & "# AND " _
& "tblVisitEquipment.lngzEquipment = " & Me.lngzEquipment & ";"
FinalSQL = BeginningSQL & WhereSQL
MsgBox FinalSQL


Set rst = CurrentDb.OpenRecordset(FinalSQL)
MsgBox "This is the result of the query:" & rst("NumRecords")
If (rst("NumRecords") >= 1) Then
If vbYes = MsgBox("You already have that equipment booked in for a job on " _
& Me.lngzVisit.Column(1) & ", Please choose another", vbExclamation) Then

Me.lngzEquipment = ""

Else
MsgBox "Please choose another bit of equipment", vbOKOnly
Me.lngzEquipment = ""
Me.lngzEquipment.SetFocus

End If

Else
Exit Sub

End If


End If

End Sub