Full Version: Error 3464
UtterAccess Forums > Microsoft® Access > Access Forms
Grover42
Hi all,
I'm trying to get a form to update when I change records using the "on_Current" event.
When it runs I get a Runtime error 3464. Data type mismatch in criteria expression.
Help reads: The criteria expression in a Find method is attempting to compare a field with a value whose data type does not match the field’s data type.
Here's my code:
CODE
Private Sub Form_Current()
Dim sqlstr As String
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim Callno As Integer
Set db = CurrentDb
Callno = [txtCallNo]
sqlstr = "SELECT qryCallMaster.*" & _
" FROM qryCallMaster" & _
" WHERE (((qryCallMaster.CallMasterNo)= '" & [txtCallNo] & "' ));"
Set rs = db.OpenRecordset(sqlstr, dbOpenDynaset)
Me.cboCaller.Value = rs!CallerName
Me.cboCallStatus.Value = rs!CallStatus
Me.cboCompany.Value = rs!ClientName
Me.cboCallType.Value = rs!CallType
Me.cboRep.Value = rs!Rep

Anyone know what I'm doing wrong?
txtCallNo is a call number that is an autonumber in the table, so it's always an integer.
If I take out txtCallNo (which for the first entry is 1) and put in a 1 instead of ' " & [txtCallNo] & " ' I get what I'm looking for.
I've tried saving the value of txtCallNo to a string and integer based variable, but I'm still coming up empty handed. Any help would be much appreciated.
RoyVidar
Single quotes are text delimiters, just remove them
CODE
" WHERE (((qryCallMaster.CallMasterNo)= " & [txtCallNo] & " ));"
Grover42
That did it!
Thanks a million! I knew it had to be some n00bie error wink.gif
Just so I know for future reference, could you provide a little more info on the syntax of including different parts of the form or just plain text in a SQL statement like this? Or, maybe you could shoot me a link on where I can read about it myself? I found an article on MSDN but that seemed to be for the DLookup Function.
Again, thanks a ton for your help!
RoyVidar
You're welcome!
Well, it goes for the domain aggregates in VBA as well as sql (the criteria part of the domain aggregate functions are technically an sql where condition without the keyword where).
For text fields, criteria need to be surrounded by single quotes
For numeric, no deleimiters
For dates, in access, hash (#) is used (when using SQL server, I think the date delimiters are single quotes)
Then there's the next element, what happens if you have a text/string criterion, where the criterion might contain single quotes? Some methods
CODE
" WHERE (((qryCallMaster.CallMasterNo)= """ & [txtSomeText] & """ ));"
" WHERE (((qryCallMaster.CallMasterNo)= '" & replace([txtSomeText],"'","''" & "' ));"

First one is often referenced as "double quoting", second replaces a single occurrance of single quote with two...
Grover42
Thanks again Roy!
Your help is very much appreciated!
RoyVidar
Ouch - now I see you're using Access 97, where I don't believe the replace function exists. Use double quoting until you upgrade ;-)
Grover42
Okay,
I've tried doing double quoting, single quotes, etc and still can't get this line quite right.
" WHERE (((qryRepInfo.Rep)= """ & [cboRep] & """ ));"
I've tried several variations of that, but here's where I'm stumped
This line here works perfect earlier in the code:
" WHERE (((qryRepTech.Rep)='" & [cboRep] & "'));"
When I replicate it to use another query
" WHERE (((qryRepInfo.Rep)='" & [cboRep] & "'));"
Oget an error 3061, too few parameters.
I'm using the same field, comparing the same value in a different query, and Access is balking at me.
I'm unsure how this is different than the query I'm running earlier in the code.
It looks just like this
CODE
  Set db = CurrentDb
sqlstr = "SELECT qryCallMaster.*" & _
" FROM qryCallMaster" & _
" WHERE (((qryCallMaster.CallMasterNo)= " & [txtCallNo] & " ));"
Set rs = db.OpenRecordset(sqlstr, dbOpenDynaset)
Me.cboCaller.Value = rs!CallerName
Me.cboCallStatus.Value = rs!CallStatus
Me.cboCompany.Value = rs!ClientName
Me.cboCallType.Value = rs!CallType
Me.cboRep.Value = rs!Rep
sqlstr = "SELECT qryRepTech.*" & _
" FROM qryRepTech" & _
" WHERE (((qryRepTech.Rep)='" & [cboRep] & "'));" 'works
Set rs = db.OpenRecordset(sqlstr, dbOpenSnapshot)
Me.cboZoneTech.Value = rs!ZoneTech
sqlstr = "SELECT qryCallerInfo.*" & _
" FROM qryCallerInfo" & _
" WHERE (((qryCallerInfo.CallMasterNo)= " & [txtCallNo] & " ));"
Set rs = db.OpenRecordset(sqlstr, dbOpenDynaset)
Me.txtCallerPh.Value = rs!ContactPh
Me.txtCallerExt.Value = rs!ContactExt
sqlstr = "SELECT qryRepInfo.*" & _
" FROM qryRepInfo" & _
" WHERE (((qryRepInfo.Rep)='" & [cboRep] & "'));" ' does not work
Set rs = db.OpenRecordset(sqlstr, dbOpenSnapshot)
RoyVidar
You are sure the Rep field is present in the query?
Would the qryRepInfo query by any chance contain parameters (references to forms)?
If the latter, try to use the whole query string (copy from the SQL view of the Query Builder), or far down this thread, among a lot of ADO discussion, there is a working DAO sample that you might try Set RS equal to Query. In both cases, it would probably need some tweaking.
Grover42
Okay,
I'm afraid most of that is greek to me. I did check the query, and it does have a criteria set to
[Forms]![frmCallMaster]![cboRep]
but the form is open when I'm running the query (which is obvious, since it's an On_Current event)
I think I see the light on how to pass those parameters, but I know I'm doing something wrong.
I tried pasting the SQL view, but that yeilded the same results.
I suppose what I need to know is where in the
Set rs =
line do the parameters need to go? or do they belong with the
sqlstr =
I checked the help for the Select and Recordset entries, and I didn't see anything really pertaining to passing the criteria.

Any help would be greatly appreciated.
RoyVidar
As I stated in that thread, DAO is not something I'm very fluent in. But I guess, since the criterion is alredy within the stored query, you could just copy paste the contents of that thread, and open the recordset based on that querydef. So instead of your last snippet, do
CODE
dim qd as dao.querydef
dim prm as dao.parameter
set qd=currentdb.querydefs("qryRepInfo")
for each prm in qd.parameters
  prm.value=eval(prm.name)
next prm
set rs=qd.openrecordset()
Grover42
That did the trick.
Thanks again! Now that I know it works, I'd love to know why.
could you maybe give me a quick synopsis of what each line does so I can see the logic behind it, as I'm sure I'll need to apply it elsewhere in my DB.
Thanks a million for your time, You have no idea how cool it is to finally see this all coming together! =)
RoyVidar
When a stored query contains parameters, VBA doesn't know anything about them, and we need to provide it somehow. Note again, I don't use DAO much, so others may perhaps elaborate more precisly on this.
e're kind of opening the query (assigning the querydef)
Then loop thru each parameter in the query
Per each parameter, issue the eval function, which will resolve (or collect, in lack of better words) the paremeter value.
Then open a recordset based on the querydef.
Allow me one additional comment, it seems your on current event is getting a bit "crowded". I'm not entirely sure of what you're doing, but it seems you're populating controls - they wouldn't be bound would they? Then it, at least to me would indicate there might be some more normalizing to do...
Grover42
I'm thinking I'm pretty well normalized, but I always welcome constructive criticism.
I'm just trying to do some off-the-wall stuff because I'd like to have everything working off of one main form. I've attached a screenshot of my relationships window. Main info goes into the frmCallMaster and gets written to the table. Subsequent calls go in the Call History subform, which is just windowed on a tab of the main form. When a new client or contact is entered into the combo boxes on the forms, a "not in list" event triggers their addition to their respective tables.
Kinda quirky, I know...but I got some quirky direction on how it needs to work wink.gif
I think I can only get it normalized to a point, since almost all of this information is subject to change at any time, but I need to capture data "as is" for historical reporting.
RoyVidar
Nah - I'll leave such comments to others wink.gif
etting around to understand the terms used (field names) is a bit to much for my Norwegian brain, but it seems there are some similar information stored in both tblCallHistory, tblCallMaster and tblContact - which seems redundant/stored in more than one table. What is the difference between CallerEmail and ContactEmail. Same question forCallerName, Caller and ClientName.
I'm kind of getting the impression perhaps the tblCallHistory would be better placed as a junction table between tblCallMaster and tblContacts.
But again, this is just based on a glance, and applying my Norwegian common sence (should such exist) on terms/field names I don't completely understand wink.gif
Grover42
HAHA!
The difference between CallerEmail and ContactEmail is the historical thing: CallerEmail would be the email the caller had when they called, that we sent info to at that time. ContactEmail is the latest value of CallerEmail, meaning, if I for some reason had to go back and e-mail them now, and it had changed, I would have their correct e-mail from the "Contacts" table, rather than what it was back when they called which is in the "CallMaster" table.
For the Caller in CallMaster, that would be who the main contact on the issue is(i.e. a project manager). We frequently speak with more than one person as we do more "project" based things. The CallHistory CallerName would be the name of the person who was spoken to on that particular call (i.e. a secratary, someone in security/finance/an exec, etc.). The name in Contacts is simply that persons name with all of their current, relevant information.
I suppose the field names are a little ambiguous, but if it ain't broke, don't fix it.
There will most likely be duplication for 99% of people...but it's the one time I don't have current info that's going to bite me in [censored], not the size or speed of the DB.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.