Access VBA Query not working properly

[BrotherRob]

All,

I have a table that stores email addresses. A client can have many email addresses but only one is selected as the default which is indicated by the boolean field 'DefaultFlag' being selected. I've created a query to do this using the query design method which works perfectly. But because I have to pass a variable into the query I've had to use SQL in VBA to achieve this. The problem that I have is that the same query doesn't work in VBA. It finds all records associated with the client but fails to pick up the default record even with the boolean field set to 'True'. Please see the attached text file

I can't understand why it won't work because it appears to be correct.

Thanks in anticipation of a response.

 

[Mr. B]

Try a little different approach to getting the count of the records that meet your criteria. I normally just do a "count" of the records that meet my criteria rather than try to return the recordset.

Here is some code that I have used to do what you are wanting to do. This code will return zero if there are no records that meet the criteria or the actual count of the number of records that do meet the criteria. You will need to change the references to match your form controls and your variables.

Private Sub cmdCheckStatus_Click()
Dim rs As DAO.Recordset
Dim lngVendorId As Long
Dim strSql As String
Dim varRecCnt

'read the Vendor ID
lngVendorId = Me.txtVendorID
'define the sql statement using the VendorID variable
strSql = "SELECT Count(tblVedors.VendorID) AS " _ 
       & "CountOfVendorID FROM tblVedors " _
       & "WHERE (((tblVedors.VendorID)=" & lngVendorId & ") " _
       & "AND ((tblVedors.Active)=True));"
'open the recordset with the count
Set rs = CurrentDb.OpenRecordset(strSql)
'read the record count returned 
varRecCnt = rs.Fields("CountOfVendorID").Value
'close the recordset
rs.Close
'destroy the recordset variable
Set rs = Nothing
'evaluate the value in the "varRecCnt" variable
If varRecCnt = 0 Then
    'do something here
ElseIf varRecCnt > 1 Then
    'do something else here
Else
    'do something else here
End If
End Sub

This is a much more reliable way of checking for records that meet your criteria.

 

[Galaxiom]

Your procedure is "shutting the gate after the horse has escaped".

A No Duplicates Composite Index should be included in the table on VendorID and the DefaultFlag fields. Then it will be absolutely impossible to enter multiple defaults for the same Vendor.

On the form, use VBA to uncheck all the other records when a Default is checked(assuming it only shows records for a particular vendor).

No need to test for problems then.

BTW "Chr(10) & Chr(13)" can be replaced with the rather more concise constant vbCrLf

 

[BrotherRob]

Guys, thanks for the replies but I'm curious. Why would a query created in query design work as opposed to the same SQL string scripted in VBA?