Data type mismatch in criteria expression

[ezechiel]

Hello,

I know others have the same problem here, I tried (a lot! ) different solutions but still can't get around the problem.. :s

Here's the code:

Private Sub cb_swedit_sopi_AfterUpdate()
Dim qry_upd_sopi As Variant
 
On Error GoTo Err_sopi_AfterUpdate
 
    MsgBox (Me.RN_EQU)
    MsgBox (Me.cb_swedit_sopi.Column(0))
 
    Me.txt_swedit_sopi_nr = Me.cb_swedit_sopi.Column(2)
 
    qry_upd_sopi = "UPDATE [Equipment and Software] SET [Equipment and Software].SOP_install = '& Me.cb_swedit_sopi.Column(0) &' WHERE [Equipment and Software].RN_EQU = ' & Me.RN_EQU.Value &';"
    DoCmd.RunSQL qry_upd_sopi
 
Err_sopi_AfterUpdate:
    MsgBox Err.Description
 
End Sub
 

cb_ is a ComboBox

Do I need " DoCmd.GoToRecord , , acGoTo " ?

Besides this, the error is data type mismatch in criteria expression
(Where did he say??)

So I guess there is a missmatch between the type of value I get from the ComboBox
and the type of value of the SOP_install field (number).
The error is still there even if I add ".ToNumber" or not to cb_blabla.Column(0)

RN_EQU: primary key and autonumber (long integer)
SOP_install: number (long integer) in both tables..

The Message Boxes display the correct numbers. Either with .Value or not.

Can anyone tell me what is going wrong here?
If more info needed, don't hesitate to ask!

[MikeTheBike]

hI

May be this is the solution (your query is just a literal string) the quotation marks delimiters cause the variable/control values to be returned as part of the query string.

qry_upd_sopi = "UPDATE [Equipment and Software] SET [Equipment and Software].SOP_install = '" & Me.cb_swedit_sopi.Column(0) & "' WHERE [Equipment and Software].RN_EQU = '" & Me.RN_EQU.Value & "';"

??

MTB

[ezechiel]

So,

when I put double quotes, it still doesn't change a thing.

But when I add " .ToNumber " to cb_xx.Column(0)
It now says : "Object required".

I don't know what it means, so you can help me again :)
But I'll try also to look on my side..

[ADezii]

@ezechiel

Dim qry_upd_sopi As String
 
If IsNull(Me![RN_EQU]) Then Exit Sub
 
DoCmd.SetWarnings False
 
qry_upd_sopi = "UPDATE [Equipment and Software] SET [Equipment and Software].SOP_install = '" & _
                Me.cb_swedit_sopi.Column(0) & "' WHERE [Equipment and Software].RN_EQU = '" & _
                Me.RN_EQU.Value & "';"
 
DoCmd.RunSQL qry_upd_sopi
 
DoCmd.SetWarnings True

[ezechiel]

Hi,

thanks for the help, but still have a "data type mismatch" mith the code above.

If I put .ToNumber or .ToLongInteger I get object required again..

additional info:
-the cb's control source is empty -> unbound
- row source type is a query:
SELECT SOP.SOP_id, SOP.SOP_name, SOP.SOP_nr FROM SOP WHERE SOP_domain = "soft" AND (SOP_type = "I" OR SOP_type = "MI")

I created an application before and worked with buttons to insert and delete.
But here I use the AfterUpdate property to UPDATE. I see that updates in access cause a lot of trouble to people. Never had any problem before with a real SQL base and php for example.. So this is really confusing me.

Did I forget any other commands or something? I will lose my hair with this :)

[ADezii]

@ezechiel
Assuming everything else is correct, namely: Table Field Names are correct, Form Field is correct, Column Reference is valid, etc., there can be 1 of 4 possible SQL Statements depending on the Data Types of the 2 Fields ([SOP_install] and [RN_EQU]). They are:

 '[SOP_install] and [RN_EQU] are STRINGS
qry_upd_sopi = "UPDATE [Equipment and Software] SET [Equipment and Software].SOP_install = '" & _
                Me.cb_swedit_sopi.Column(0) & "' WHERE [Equipment and Software].RN_EQU = '" & _
                Me.RN_EQU.Value & "';"
 
 '[SOP_install] and [RN_EQU] are NUMERIC
qry_upd_sopi = "UPDATE [Equipment and Software] SET [Equipment and Software].SOP_install = " & _
                Me.cb_swedit_sopi.Column(0) & " WHERE [Equipment and Software].RN_EQU = " & _
                Me.RN_EQU.Value & ";"
 
 '[SOP_install] is a STRING and [RN_EQU] is NUMERIC
qry_upd_sopi = "UPDATE [Equipment and Software] SET [Equipment and Software].SOP_install = '" & _
                Me.cb_swedit_sopi.Column(0) & "' WHERE [Equipment and Software].RN_EQU = " & _
                Me.RN_EQU.Value & ";"
 
 '[SOP_install] is NUMERIC and [RN_EQU] is A STRING
qry_upd_sopi = "UPDATE [Equipment and Software] SET [Equipment and Software].SOP_install = " & _
                Me.cb_swedit_sopi.Column(0) & " WHERE [Equipment and Software].RN_EQU = '" & _
                Me.RN_EQU.Value & "';"
 

[ChipR]

Since you specified that the table fields are Long Integer, you would use ADezii's 2nd query, at line 7 of the previous post.
If you start a string with a quotation, you need to close it with another quotation before you can append the value of a variable. You can't mix " and ' as in the original post.

[ezechiel]

Wouhouou!! It works! Thanks to you all.

And saying it was so simple... and that I tried with simple quotes, a mix of simple and double. But double only was the only thing that I didn't tried! Shame on me o_0; !!!


So it's working, my selection is recorded in the table. But now I have two other questions..

When I reopen the form, the cb's starting value is empty, what's normal.
In order to load the stored value, do I need to put:

Private Sub Form_Load()
    sql blabla select...  
End Sub

Or is it something else to display the stored value in the cb?

The other question:
When I've selected my value in the cb, a messagebox is coming up, but it is empty (no text). Should there be something like "1 record updated"? If so,
how do I make it display in the msgbox.
If not, what could it be?

[ChipR]

If you want to get a stored value out of a table, you can use the DLookup function. It's not quite clear to me the way this form functions, or why you wouldn't just use a form bound to a recordset.
You have two MsgBox statements in your code, so you're going to have to be more specific about the other question.

[ezechiel]

Well, the access application is one mess (not mine ^^). The database is not ACID at all! So I' m improving it a bit, but it's not that easy.

This is probably a form created with the wizard. Every field is linked to a table.field trough record source. So Access creates the record once the form is closed. Since my cboxes are not 'record sourced' they do not function like the rest of the form. (and this creates other problems..) And I can't make them 'record sourced' since this would not display the right information or put the wrong information in the table. I tried with record source, but I'm unable to make it work liek I want it to..

The disadvantage (or advantage for some) are the 'automatic' jobs of Access.
Like: there is a form to add new software (autonumber ID). The field for this ID has a control source. You fill in the fields of the different tabs in the form, close the form and everything is saved in the new record (like an insert into..)

nb: procedure = SOP

TABLE (RN_EQU(strange name for software id...), other stuff, SOP_use (foreign key), SOP_maint (fk), etc..)

The problem I have here is with my comboboxes (for selecting SOP). I can't set a recordsource since I don't want to show an id number in the field. So I use vb (at least, with vb, you know how it works).

On the first tab, selecting the type (here "software"), triggers the creation of a new record in the table, the ID exists, but the record isn't created yet.
So when I choose a SOP with cb that triggers an update, the update doesn't work because the new record is in memory only. So now I'm searching how to arrange this problem... (maybe create a new post for this one.. form_onClose)

The field to fill in (in software table) with cb (insert or update) is an id which is a foreign key to another table. The information displayed in cb is the info from the other table SOP(id, name, and nr (not id)). This is done trough row source type: query and a SELECT query from the SOP table with some WHERE clauses.



It's kind a mess to explain because it is a mess in fact..
Here's the code that works for cb afterupdate and with which (is this English?) I have a msgbox displayed with no text..

 
Private Sub cb_swnew_sopu_AfterUpdate()
Dim qry_upd_sopu As String
On Error GoTo Err_sopu_AfterUpdate
 
    If IsNull(Me![RN_EQU]) Then Exit Sub
    DoCmd.SetWarnings False
 
    Me.txt_swnew_sopu_nr = Me.cb_swnew_sopu.Column(2)
 
    qry_upd_sopu = "UPDATE [Equipment and Software] SET [Equipment and Software].SOP_use = " & _
    Me.cb_swnew_sopu.Column(0) & " WHERE [Equipment and Software].RN_EQU = " & _
    Me.RN_EQU.Value & ";"
    DoCmd.RunSQL qry_upd_sopu
    DoCmd.SetWarnings True
 
Err_sopu_AfterUpdate:
    MsgBox Err.Description
 
End Sub

So I have no idea what Access is trying to say and why.. (1 record updated, perhaps?)
(I'll take a look at dlookup)

[ChipR]

Hah, your error code is being executed every time throught the function.

[ChipR]

Function ...
On Error GoTo ErrorHandler
 
    ...
    ...
 
ExitCode:
    Exit Function
 
ErrorHandler:
    ...
    Resume ExitCode
End Function

[ezechiel]

Hi,

thanks, it works (when changing Function to Sub of course).
But how does it come that it is executed every time?
I picked up the 'old' error handling code somewhere else, used it in an other access prog and never had an issue like this..


What concerns Dlookup, I think I'll rather use something like

form_onLoad:
 cb = select x, y, z from SOP where (match current software record and sop_id) 

This is not correct syntax, but I guess you get the idea?
Or is this a bad idea?

[ChipR]

The error handling code you had was executed because you were missing the Exit. The label on the code doesn't prevent it from being executed if you allow it to get there.
Are you talking about the source for the combo or the value for the current record? If you want to set it to the value for the current record, you need to get the value for the bound field and set it equal to that.

[ezechiel]

Well, my cb is like this:
control source: empty
Row Source:
SELECT SOP.SOP_id, SOP.SOP_name, SOP.SOP_nr
FROM SOP
WHERE SOP_id = 1 OR ((SOP_domain IN ("soft","all")) AND (SOP_type IN ("U", "UM")))
Bound Column: 1

And I only display SOP_name and SOP_nr

So I want the value for the current record to be displayed..
I don't understand this really much.. :s

you need to get the value for the bound field and set it equal to that.

Control source is still a bit confusing to me (after reading explanation on M$ site..)