Querying a many to many relationship table not working

[Mr_Si]

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:

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:

The Result is:

The table is:

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

 

[Rabbie]

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

 

[Rabbie]

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.

 

[Mr_Si]

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