VBA DAO.Recordset not returning same results as Access Query

[Chauncey7]

I have a form in which on a change it looks for a matching record in the same table. I created a query with the same parameters (the parameters are the same values as the variants I place in my SELECT statment). The query returns a record but the VBA does not. I've gone through every possible scenario I can think of and have no idea why this is not working. PLEASE HELP!

Below is my code:

Dim strDataType As Variant
Dim strVALVE_NO As Variant
Dim strLOCATION As Variant
Dim strSYSTEM As Variant
Dim strSERVICE As Variant
Dim rstCompare As DAO.Recordset

Set dbsname = CurrentDb
strDataType = Trim(Form_CompareFullSubform.DataType)
strVALVE_NO = Trim(Form_CompareFullSubform.VALVE_NO)
strSYSTEM = Trim(Form_CompareFullSubform.SYSTEM)
strLOCATION = Trim(Form_CompareFullSubform.Location)
strSERVICE = Trim(Form_CompareFullSubform.SERVICE)

Set rstCompare = CurrentDb.OpenRecordset(Name:="SELECT * FROM CompareData WHERE [DataType]<> '" & strDataType & "' AND [VALVE_NO] = '" & strVALVE_NO & "' AND [SYSTEM] = '" & strSYSTEM & "' AND [LOCATION] = '" & strLOCATION & "' AND [SERVICE] = '" & strSERVICE & "'")

 

[vbaInet]

Were you trimming the values in your query?

 

[Chauncey7]

Negative, I only put the trim in there because I thought the values had spaces, which they do not. This is actually a colleague's and at first he had it set up as:

Set rst= CurrentDB.OpenRecordset("tableName", dbOpenDynaset)

and he was using .FindFirst

I thought moving it to a SELECT statement would solve the problem but I obviously have no idea.

 

[spikepl]

Put your SELECT stuff in a string

DIm strSQL as String

strSQL= "SELECT .. .
print.debug strSQL

paste it into the SQL view of the query designer and run query
Then you have a 100% direct comparison

 

[boblarson]

And what is the rest of the code. How are you using the recordset that is opened? It doesn't show on your initial code.

 

[Chauncey7]

The initial code was this: The .FindFirst would not return anything and exit the if statment/procedure. If it did find a record it would change the record with the CDMD DataType and Delete the VHMP DataType.

Function UpdateConfigFull() As Integer
 
    Dim dbsname As Database
    Dim stdocname, strcriteria, strCDMD, strDC, strRemote, strRIN, strCDMD_DC As String
    Dim response As String
    Dim matched As Boolean
    Dim rstCompare As DAO.Recordset
 
    matched = False
 
    Set rstCompare = dbsname.OpenRecordset("CompareData", dbOpenDynaset)
 
    ' criteria string to find a duplicate record of a different datatype
    strcriteria = "[DataType] <> '" & Form_CompareFullSubform.DataType & "' AND " & "[VALVE_NO] = '" & Form_CompareFullSubform.VALVE_NO & "' AND " & "[SYSTEM] = '" & Form_CompareFullSubform.SYSTEM & "' AND " & "[LOCATION] = '" & Form_CompareFullSubform.Location & "' AND " & "[SERVICE] = '" & Form_CompareFullSubform.SERVICE & "'"
 
    ' criteria string to find the cdmd record that there was a match for
    strCDMD = "[DataType] = '" & "CDMD" & "' AND " & "[VALVE_NO] = '" & Form_CompareFullSubform.VALVE_NO & "' AND " & "[SYSTEM] = '" & Form_CompareFullSubform.SYSTEM & "' AND " & "[LOCATION] = '" & Form_CompareFullSubform.Location & "' AND " & "[SERVICE] = '" & Form_CompareFullSubform.SERVICE & "'"
 
    With rstCompare
        .FindFirst strcriteria
        strDC = ![DC_NO]
        strRemote = ![REMOTE]
        strRIN = ![RIN]
    End With
 
    With rstCompare
        .FindFirst strcriteria
 
        matched = Not .NoMatch
 
        If matched Then
            response = MsgBox("Valve found a CDMD match and will remove the matched VHMP record.", vbOKCancel, "CDMD Match Found")
 
            If response = vbCancel Then
                Exit Function
            Else
 
                If Form_CompareFullSubform.Dirty = True Then
                    Form_CompareFullSubform.Dirty = False
                End If
 
                If matched Then
                    With rstCompare
                        .FindFirst strCDMD
                        .Edit
 
                        If IsNull(strDC) And IsNull(strRemote) Then
                            ![REMARKS] = "MATCH FOUND."
                            ![VERIFIED] = -1
                        ElseIf IsNull(strDC) And Not IsNull(strRemote) Then
                            ![REMOTE] = strRemote
                            ![REMARKS] = "MATCH FOUND."
                            ![VERIFIED] = -1
                        ElseIf Not IsNull(strDC) And IsNull(strRemote) Then
                            ![DC_NO] = strDC
                            ![REMARKS] = "VERIFY DC NO."
                            ![VERIFY] = -1
                        ElseIf Not IsNull(strDC) And Not IsNull(strRemote) Then
                            ![DC_NO] = strDC
                            ![REMOTE] = strRemote
                            ![REMARKS] = "VERIFY DC NO."
                            ![VERIFY] = -1
                        End If
 
                        .Update
                    End With
 
                    Do
                        dbsname.Execute "DELETE FROM CompareData WHERE [DATATYPE] = '" & "VHMP" & "' AND " & "[VALVE_NO] = '" & Form_CompareFullSubform.VALVE_NO & "' AND " & "[SYSTEM] = '" & Form_CompareFullSubform.SYSTEM & "' AND " & "[LOCATION] = '" & Form_CompareFullSubform.Location & "' AND " & "[SERVICE] = '" & Form_CompareFullSubform.SERVICE & "'"
                        .FindNext strcriteria
                    Loop Until .NoMatch
 
                End If
            End If
            Form_CompareFullSubform.Refresh
        End If
 
    End With
 
    DoCmd.RunCommand acCmdSaveRecord
 
End Function

 

[boblarson]

FYI when posting code, use code tags please (especially for large code blocks):

 

[boblarson]

Part of your problem may be in how you are referencing the form.

This syntax:

Form_CompareFullSubform.Location

is NOT correct and can cause unintended side-effects.

The CORRECT way to refer to it is:

Forms!FormName.ControlName

or

Forms("FormName").ControlName

or if it is a subform that you are referring to on the main form's code:

Me.SubformControlName.Form.ControlName

(the SubformControlName is the name of the control which displays/houses the subform on the parent form and is NOT the name of the subform itself unless the subform control and the subform have the same exact names)

And the .Form part needs to be in there unless the two are named exactly the same.

 

[Chauncey7]

I realize the form reference isn't correct but it still gives the right values when I have Form_frmName, so I don't think that's the problem.

 

[vbaInet]

You've just been given expert advice that Form_FormName is the wrong way to reference a form object. Form_FormName refers to the class of the form, not the object itself. Yes it may work but don't always count on it. Use the other variations suggested by Bob.

Do a simple FindFirst with just one criteria and check the result, then go from there.

Have you stepped through your code?

 

[boblarson]

I realize the form reference isn't correct but it still gives the right values when I have Form_frmName, so I don't think that's the problem.

(15 years of Access experience against how many???)

Okay, let's say that isn't a problem in this instance - you still need to fix them as I said, using those can cause problems.

Next, I'll iterate through what else needs fixing and perhaps by fixing them, you can get it to work properly (although you really do need to fix those form references).

1. This line does not do what you think it does:

Dim stdocname, strcriteria, strCDMD, strDC, strRemote, strRIN, strCDMD_DC As String

In VBA, if you do it that way, all of the variables EXCEPT strCDMD_DC are going to be VARIANTS instead of strings. If you want them to be strings, you have to say so

Dim stdocname As String, strcriteria As String, strCDMD As String, strDC As String, strRemote As String, strRIN As String, strCDMD_DC As String

VBA doesn't work the same as VB6 where you can set a bunch of variables with the datatype at the very end.


2. This code:

    With rstCompare
        .FindFirst strcriteria
        strDC = ![DC_NO]
        strRemote = ![REMOTE]
        strRIN = ![RIN]
    End With

Needs to use .NoMatch before trying to set the values. If it doesn't find a match it will just update the one it is on.

Corrected code:

    With rstCompare
        .FindFirst strcriteria
           [B][COLOR=red]If Not .NoMatch Then
[/COLOR][/B]              strDC = ![DC_NO]
              strRemote = ![REMOTE]
              strRIN = ![RIN]
          [B][COLOR=red]End If[/COLOR][/B]
    End With

 

[boblarson]

Oh, and I did forget to add that you declared

dbsname as Database

but you never set it

Set dbsname = CurrentDb

(at least that I could see)

It appears to be in your first code you posted but not in the second set and in fact, the second set doesn't appear to have what you had at first. What's up with that?

 

[Chauncey7]

First off thank you all for the help. I made all of the suggested changes:

Forms!FrmCompare!CompareFullSubform.Form.Control

I also made all of the changes in the last couple posts and it still does not return a record. I took parameters out and the only parameter that causes problems is the SERVICE. Big problem that I have is finding any similarities between the failed procedures that don't return records and procedures that run correctly. It only happens on certain records and I have no idea why. Any ideas? quotes? commas? "/"??? I have no idea.

 

[Chauncey7]

The second set of code I posted was the original. The first is what I tried changing it to but still does not seem to work.

 

[boblarson]

Is SERVICE a combo box? Check to see that the value of Service is returning is the actual value for with it needs to search. Sometimes the bound column returns one thing but you wanted another. For example if SERVICE is returning the ID value instead of the TEXT, you would need to either change the bound column property of the combo or if it really should be searching for the ID then it shouldn't have quotes around it.

And this brings up the question - do you have lookups defined at table level? If so, you should remove them as they can cause problems like this (if it is a field with a lookup defined on the lookup tab directly in the table).

 

[Chauncey7]

Negative, SERVICE is a textbox and there are no lookups defined at the table level. This database is not the best I've seen, I'm trying to help out a friend.

 

[boblarson]

Negative, SERVICE is a textbox and there are no lookups defined at the table level. This database is not the best I've seen, I'm trying to help out a friend.

But if you take SERVICE out of the mix then it works? Hmm...

 

[Chauncey7]

Yup. I took the values from the text boxes and put them in a query and it worked perfectly. So it's something with VBA, maybe I don't have a reference checked or something? I don't know?:banghead:

 

[boblarson]

Yup. I took the values from the text boxes and put them in a query and it worked perfectly. So it's something with VBA, maybe I don't have a reference checked or something? I don't know?:banghead:

AHA!!!! I think you just said something which may have solved the mystery. You said:

I took the values from the text boxes

Does it work if you put a single value in?

 

[Chauncey7]

I wish it were that easy. All of my parameters in the openrecordset statement are generated from textboxes. There is only one value for each textbox, right? I tried simulating the same parameters in a query, thats all. And it worked for the query! So the records are there! its just not pulling it in VBA