Type mismatch

[Shawny]

I have a combo box, (text box), called fakemodel that queries the Catalog tbl for Model # s.
After update, I want to check the Catalog tbl to see if that Model # has been entered.
I get a type mismatch at the line:strID = Trim(Str(Me.fakemodel))
PS. I was not the one who named that field Model #, and it's way too late in the game to change it now.

If Not IsNull(Me.fakemodel) Then
strRst = "Catalog"
strID = Trim(Str(Me.fakemodel))
Set rstData = dbData.OpenRecordset(strRst, dbOpenDynaset)
strCriteria = "[Model #] = " & strID
rstData.FindFirst strCriteria
If Not rstData.NoMatch Then
MsgBox "a match"
Else
MsgBox "no match"
End If
Else

End If

 

[RV]

> strID = Trim(Str(Me.fakemodel))<

You're getting an Type mismatch when you're trying to compare different datatypes.
So that's what happening here.
I guess strID is an Integer whereas Trim(Str(Me.fakemodel)) rults in a Variant (String).

Try this:

strID = Cint(TRim(Me.fakemodel))

RV

 

[Shawny]

Nope. Didn't work. The Model # is text. It has both numbers and letters in it. Thanks for the idea tho.
Hmmm I am to put Ctext?

 

[DBL]

If model number is made up of text, this line

strCriteria = "[Model #] = " & strID

will need to be

strCriteria = "[Model #] = '" & strID &"'"

Not sure if that will fix you're overall problem

 

[Shawny]

Nope. Not working yet. Anybody else got an idea?

 

[RV]

In your After Update event, how did you define:

strCriteria,
strID

Post the full code of the event.
BTW, are you sure your reference to your Model # is OK?

Try this:

Create a hidden textbox which is bound to the combobox column representing your Model #.

Adapt your After Update event code using this new textbox, see what happens.

RV

 

[Shawny]

The entire code is in the first posting. It is already on an unbound combo box called fakeModel. The field is a key field for the tbl that is being queried if that makes any difference.
Kinda weird. It seems like such a simple thing.
Make a combo box based on a query of a field, check to see if the data that is entered has been entered before. I am looseing confidence in myself.
Thanks

 

[DALeffler]

Doesn't look like you can call the Str function on a string...

how'bout:

If Not IsNull(Me.fakemodel) Then
strID = me.fakemodel
If IsNumeric(strID) Then strID = Trim(Str(strID))
strRst = "Catalog"
Set rstData = dbData.OpenRecordset(strRst, dbOpenDynaset)
strCriteria = "[Model #] = " & strID
rstData.FindFirst strCriteria
If Not rstData.NoMatch Then
MsgBox "a match"
Else
MsgBox "no match"
End If
Else

End If

Doug.

 

[Shawny]

You folks willing to try one last time?
Unbound combo box (fakemodel), querying a primary key field (model #).
Want to detect if the entered data is in the dB or not.
Debugger yellows out the line:
rstData.FindFirst strCriteria

Private Sub fakemodel_AfterUpdate()

Dim dbData As Database, rstData As Recordset
Dim strRst As String, strCriteria As String, strID As String
Dim intResp As Integer

Set dbData = CurrentDb

'See if Model # is in Catalog tbl

If Not IsNull(Me.fakemodel) Then
strID = Me.fakemodel
If IsNumeric(strID) Then strID = Trim(Str(strID))
strRst = "Catalog"
Set rstData = dbData.OpenRecordset(strRst, dbOpenDynaset)
strCriteria = "[Model #] = " & strID
rstData.FindFirst strCriteria
If Not rstData.NoMatch Then
MsgBox "a match"
Else
MsgBox "no match"
End If
Else

End If

 

[DBL]

Lets try it from another angle

If Not IsNull (Me.fakemodel) then

If Not IsNull (DLookup("Model#","Catelogtbl","Model# ='" & Me.fakemodel &"'")) then
MsgBox "Match Found"
Else
MsgBox "No Match Found"
End if
End if

 

[DALeffler]

Now try changing

strCriteria = "[Model #] = " & strID
rstData.FindFirst strCriteria

to this:

strCriteria = "[Model #] = ' " & strID & " ' "
rstData.FindFirst strCriteria

There's spaces between the single and double quotes above to show which type quote goes where - don't include the spaces in your code module. DO keep the space between the equals sign and the first single quote...

HTWorks,

Doug.