Syntax issue - when strlinkcriteria is a number

[computerider]

I have this code which works fine and will open the form to the correct record but it produces a "Type mismatch" msgbox on opening the record...

the stLinkCriteria consists of two values one of which is a number [AAID] - is the syntax causing this?

************************************************** ******************************************

Private Sub Command13_Click()
On Error GoTo Err_Command13_Click
 
    Dim stDocName As String
    stDocName = "frmAAResults"
    stLinkCriteria = "[AAID] =" & Me![lstEMPs].Column(0) & " AND [Department]='" & Me![lstEMPs].Column(1) & "'"
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
 
DoCmd.Close "frmSEARCH-Results"
Exit_Command13_Click:
    Exit Sub
 
Err_Command13_Click:
    MsgBox Err.Description
    Resume Exit_Command13_Click
 
End Sub
 

************************************************** ******************************************

[puppydogbuddy]

Yes, I believe that since AAID is numeric, you have to wrap it with the string (str) function as shown:

stLinkCriteria = Str("[AAID] =" & Me![lstEMPs].Column(0)) & " AND [Department]='" & Me![lstEMPs].Column(1) & "'"

[evn678]

Yes, I believe that since AAID is numeric, you have to wrap it with the string (str) function as shown:

stLinkCriteria = Str("[AAID] =" & Me![lstEMPs].Column(0)) & " AND [Department]='" & Me![lstEMPs].Column(1) & "'"

Yes, but wouldn't the syntax be:

stLinkCriteria = "[AAID] =" & str(Me![lstEMPs].Column(0)) & " AND [Department]='" & Me![lstEMPs].Column(1) & "'"

?

[computerider]

Puppydogbuddy – your code produces the error msgbox “Type mismatch” as soon as you click the button and doesn’t open the form

evn678 – your code does the same thing as my original code – it opens the form to the correct record but still produces the error msgbox…

???

[puppydogbuddy]

[font=Verdana][size=2]You can try it that way, but I think I probably should have wrapped the entire expression with the CStr function instead of the Str function as shown:[/size][/font]

stLinkCriteria = CStr("[AAID] =" & Me![lstEMPs].Column(0) & " AND [Department]='" & Me![lstEMPs].Column(1) & "'" )

[evn678]

Puppydogbuddy – your code produces the error msgbox “Type mismatch” as soon as you click the button and doesn’t open the form

evn678 – your code does the same thing as my original code – it opens the form to the correct record but still produces the error msgbox…

???

Go into your table definition and tell us the data types of .Column(0) and .Column(1)

[missinglinq]

I assume that AAID is an identification "number." If this is true, there's no valid reason for it data type to be numeric! You should only use a numeric data type for fields that are going to be used in some sort of mathematical calculations, not for fields that just happen to be made up of all "numbers." Tyr changing the data type and run your original code and see what happens.

[puppydogbuddy]

Are you referring to the last code I submitted, using the CStr function? That should work.

[computerider]

I think the number is the issue why I can't figure out BUT

AAID is a number because in the table it looks up to it is an auto number
Department is text

AAID + Dept is the composite key of the table we are searching for

The new code still produces the error msgbox...

[puppydogbuddy]

[color=black]

I think the number is the issue why I can't figure out ...

[/color]

[color=black] [/color]

[color=black]None of the syntaxes used previously were correct for combining text and numeric data types in one expression. Hopefully, we can get you the correct expression syntax.[/color]

[color=black] [/color]

[color=black]Let's try the following expression syntax: I simplified it by removing the Column(0) from the numeric component because column(0) is the bound (default) column in that expression and should not be needed. I also added variables for each component of the expression to be able to determine that is evaluated properly.[/color]

[color=black] [/color]

[color=black]Dim iNumericPart As Integer[/color]

[color=black]Dim strTextPart As String[/color]

[color=black] [/color]

[color=black]iNumericPart = [/color]"[AAID] = ” & Me![lstEMPs]

strTextPart = [color=black] “[/color][Department]= '" & Me![lstEMPs].Column(1) & "'"

[color=black] [/color]

stLinkCriteria = iNumericPart & “ AND “ & strTextPart



If the above doesn’t work, you need to go to the debug window and type in the variable name for each component of the expression by itself and let me know which component does not evaluate properly.

[computerider]

Thank You - I see you dim(ed) an integer AND a string which is what I thought the issue to be all along – so that makes sense – however, go figure - when I trashed the form and made a new one from scratch it works this way:
************************************************** **********************
Private Sub cmdOpenResults_Click()
On Error GoTo Err_cmdOpenResults_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmAAResults"

stLinkCriteria = CStr("[AAID] =" & Me![lstWS].Column(0) & " AND [Department]='" & Me![lstWS].Column(2) & "'")
DoCmd.OpenForm stDocName, , , stLinkCriteria

DoCmd.Close acForm, "frmSearchResults"


Exit_cmdOpenResults_Click:
Exit Sub

Err_cmdOpenResults_Click:
MsgBox Err.Description
Resume Exit_cmdOpenResults_Click

End Sub

I am learning sometimes access has gremlins…

But I wanted to know how to dim 2 separate data type variables and you showed me so thanks very much…

[puppydogbuddy]

Glad I could help. I am curious about one thing, though.
Your final expression:
stLinkCriteria = CStr("[AAID] =" & Me![lstWS].Column(0) & " AND [Department]='" & Me![lstWS].Column(2) & "'")

is the same as the one I recommended in one of my prior posts (post #5):
stLinkCriteria = CStr("[AAID] =" & Me![lstEMPs].Column(0) & " AND [Department]='" & Me![lstEMPs].Column(1) & "'" )

except that you are referencing a different listbox. Could this have been the problem all along?

[computerider]

I would have to say no, I changed the order several times, but if it had been the wrong column number it would not have been able to locate the correct record (which it always did)…

The column count determines which field to match against the form’s record, so I am thinking if I had the wrong one it would have crashed...correct?

[puppydogbuddy]

The column count determines which field to match against the form’s record, so I am thinking if I had the wrong one it would have crashed...correct?

Not neccessarily, but there would be a noticeable difference in the return values, i.e. the values returned wouldn't make sense in the context of what would be expected.

However, I am referring to the object name, not the order:
CStr("[AAID] =" & Me![lstWS]
.......................vs......................... ...............
CStr("[AAID] =" & Me![lstEMPs]

[computerider]

Sorry I missed that - yes you are right I did change the listbox name to the current example...so now I am curious, are you saying that the wrong name of the listbox might still have opened the form yet had a mismatch error? I don't think so, I believe it would have debugged and opened to the list box name in the VBE, right?

[puppydogbuddy]

Sorry I missed that - yes you are right I did change the listbox name to the current example...so now I am curious, are you saying that the wrong name of the listbox might still have opened the form yet had a mismatch error? I don't think so, I believe it would have debugged and opened to the list box name in the VBE, right?

Yes, it would have to be debugged. I was not referring to the listbox name in that discussion about crashing the system. In that context, I was talking about colums within the listbox.