update query SQL Using VBA not working!!! please help!!!!!!!!!!!!!!

[rajgoyal00]

HI,

I am using an update query in access and its working, but the same thing in access is giving me problems.
I am getting the error type mismatch on the RED criteria.
Those are check boxes in the table and the form..

Thanks for the help in advance....

strSQL = "UPDATE (Products_Tbl INNER JOIN [Part Types] ON Products_Tbl.[Item Code] = [Part Types].[Part ID]) INNER JOIN [Car_Parts] ON [Part Types].[Part Type] = [Car_Parts].[Part Type]" & _
"SET Products_Tbl.[Qty on Hand] = [Qty on hand]-[Car_Parts].[Qty]" & _


"WHERE ((([Car_Parts].[Product Code]) = [Forms]![Sales_Entry_Form]![Prod_Code_Text]) And" & _
"((Products_Tbl.[Item Code]) = [Part Types].[Part ID])) And (([Car_Parts].[Basic Package]) Like IIf([Forms]![Sales_Entry_Form]![Basic_Package] = False, " * "," & _
"[Forms]![Sales_Entry_Form]![Basic_Package]))" & _
"And (([Car_Parts].[Luxury Package]) Like IIf([Forms]![Sales_Entry_Form]![Luxury_Package] = True, " * "," & _
"[Forms]![Sales_Entry_Form]![Luxury_Package]))"
DoCmd.RunSQL strSQL

[ItsMe]

Your instant ifs do not make any sense to me. Is Sales_Entry_Form a bound form? Are Basic_Pack and Luxury_Package yes no fields?

[rajgoyal00]

It's not a bound form. And yes basic and luxury are yes no fields.

[ItsMe]

Recomend setting the default value for the two checkbox controls to = 0

This is the same as false

Then the following code should work. It will look for a field that is either as False or True. If there are Nulls in your yes no fields in the table, then you will need to adjust the following code. See what the default is in the table.

strSQL = "UPDATE (Products_Tbl INNER JOIN [Part Types] ON Products_Tbl.[Item Code] = [Part Types].[Part ID]) INNER JOIN [Car_Parts] ON [Part Types].[Part Type] = [Car_Parts].[Part Type]" & _
"SET Products_Tbl.[Qty on Hand] = [Qty on hand]-[Car_Parts].[Qty] " & _
"WHERE [Car_Parts].[Product Code] = [Forms]![Sales_Entry_Form]![Prod_Code_Text] And " & _
"Products_Tbl.[Item Code] = [Part Types].[Part ID] And " & _
"[Car_Parts].[Basic Package] = " & [Forms]![Sales_Entry_Form]![Luxury_Package] & " And " & _
"[Car_Parts].[Luxury Package] = " & [Forms]![Sales_Entry_Form]![Basic_Package]
DoCmd.RunSQL strSQL

[ssanfu]

I have to agree... the Immediate If's don't make sense. It doesn't make sense to use "Like" for at Boolean value.

I massaged the SQL string:

    strSQL = "UPDATE (Products_Tbl"
    strSQL = strSQL & " INNER JOIN [Part Types] ON Products_Tbl.[Item Code] = [Part Types].[Part ID]) "
    strSQL = strSQL & " INNER JOIN [Car_Parts] ON [Part Types].[Part Type] = [Car_Parts].[Part Type]"
    strSQL = strSQL & " SET Products_Tbl.[Qty on Hand] = [Qty on hand]-[Car_Parts].[Qty]"
    strSQL = strSQL & " WHERE [Car_Parts].[Product Code] = '" & [Forms]![Sales_Entry_Form]![Prod_Code_Text] & "'"
    strSQL = strSQL & " And Products_Tbl.[Item Code] = [Part Types].[Part ID]"

    If [Forms]![Sales_Entry_Form]![Basic_Package] = True Then
        strSQL = strSQL & " And [Car_Parts].[Basic Package] = TRUE"
    End If
    If [Forms]![Sales_Entry_Form]![Luxury_Package] = False Then
        strSQL = strSQL & " And [Car_Parts].[Luxury Package] = " & [Forms]![Sales_Entry_Form]![Luxury_Package]
    End If

    strSQL = strSQL & ";"

    Debug.Print strSQL

    DoCmd.RunSQL strSQL    '<<< I would use   CurrentDb.Execute strSQL

Does the SQL string look correct? (in the immediate window)


Aren't the "Basic Package" and "Luxury_Package" options mutually exclusive?

Could you explain in English what you are trying to do?

Something like: " If [Forms]![Sales_Entry_Form]![Luxury_Package] is TRUE, I want to see all of the Luxury Package options"

[ItsMe]

Aren't the "Basic Package" and "Luxury_Package" options mutually exclusive?

Ditto......

[June7]

If Basic and Luxury are mutually exclusive (each record can be only one type) then there should be only one field for package type. Are there other types?

Yes/No fields are notoriously difficult (if not impossible) to build dynamic parameterized queries with. Use conditional code structure as shown by Steve to build the SQL string. Another example http://allenbrowne.com/ser-62code.html

Multiple yes/no fields often an indication of non-normalized data, especially when the fields are for the same data and are mutually exclusive.

[rajgoyal00]

when basic field has the value True , it will show only basic package values,
But when luxury is selected It will show all whats included in basic and luxury.

I was thinking of an if statement that if Basic is true in the form that run a certain update query and and if luxury is true run a different one. But this criteria worked in an update query in access, I figured I could do it in VBA.

[June7]

If the values are mutually exclusive, should be one field and everything will be much simpler.

[ItsMe]

You could use an option group control in your unbound form to keep it intuitive for the user. The value of the option group could determine the value in a single yes/no field. If you already have many records in an already developed database, I would probably just leave it the way it is. How far along you are and how much further there is to go in the development of the DB would be a deciding factor for me.

I would be most concerned if you have default values for your yes no fields set in your table(s). If not, an update query to change nulls to 0 and a default value of 0 would make things easier going forward.

[rajgoyal00]

HI,
Thanks for your response. I know for sure I dont have any NULL records in any field.
I am adjusted the query with if and else. I am trying to run an update statement where I am updating a luxury package. Where it is to include all basic (where basic is false) and all luxury ( where luxury is true)


strSQL = "Update[Products_Tbl] INNER JOIN [Parts Types] ON [Products_Tbl].[Item Code] = [Parts Types].[Parts ID]" & _
"INNER JOIN [Car_Parts] ON [Parts Types].[Parts Type] = [Car_Parts].[Parts Type]" & _
"SET Products_Tbl.[Qty on Hand] = [Qty on hand]-[Car_Parts].[Qty]" & _
"WHERE [Car_Parts].[Product Code] ='" & [Forms]![Sales_Entry_Form]![Prod_Code_Text] & "'" & _
"And [Products_Tbl].[Item Code] = '" & [Parts Types].[Parts ID] & "'" & _
"And [Car_Parts].[Basic Package] = False" & _
"And [Car_Parts].[Luxury Package]= True"



I get an error saying Microsoft cant find the referred field in your expression. and points to the last line. When i eliminated the Last Line

strSQL = "Update[Products_Tbl] INNER JOIN [Parts Types] ON [Products_Tbl].[Item Code] = [Parts Types].[Parts ID]" & _
"INNER JOIN [Car_Parts] ON [Parts Types].[Parts Type] = [Car_Parts].[Parts Type]" & _
"SET Products_Tbl.[Qty on Hand] = [Qty on hand]-[Car_Parts].[Qty]" & _
"WHERE [Car_Parts].[Product Code] ='" & [Forms]![Sales_Entry_Form]![Prod_Code_Text] & "'" & _
"And [Products_Tbl].[Item Code] = '" & [Parts Types].[Parts ID] & "'" & _
"And [Car_Parts].[Basic Package] = False"



The same error moved to the second last line (which is the last line now), when I eliminated the NEW last line.

strSQL = "Update[Products_Tbl] INNER JOIN [Parts Types] ON [Products_Tbl].[Item Code] = [Parts Types].[Parts ID]" & _
"INNER JOIN [Car_Parts] ON [Parts Types].[Parts Type] = [Car_Parts].[Parts Type]" & _
"SET Products_Tbl.[Qty on Hand] = [Qty on hand]-[Car_Parts].[Qty]" & _
"WHERE [Car_Parts].[Product Code] ='" & [Forms]![Sales_Entry_Form]![Prod_Code_Text] & "'" & _
"And [Products_Tbl].[Item Code] = '" & [Parts Types].[Parts ID] & "'"


The same error moved further up
I removed another criteria.


strSQL = "Update[Products_Tbl] INNER JOIN [Parts Types] ON [Products_Tbl].[Item Code] = [Parts Types].[Parts ID]" & _
"INNER JOIN [Car_Parts] ON [Parts Types].[Parts Type] = [Car_Parts].[Parts Type]" & _
"SET Products_Tbl.[Qty on Hand] = [Qty on hand]-[Car_Parts].[Qty]" & _
"WHERE [Car_Parts].[Product Code] ='" & [Forms]![Sales_Entry_Form]![Prod_Code_Text] & "'"

It gave me runtime error 3075

I believe the problem is upnorth in the joins or syntax...

I am almost at the end of the DB. This has caused a few days delay. I am really hoping, you guys can help me with this. Please reply with all suggestions..


THANK YOU

[orange]

When I look at the strSQL, it appears there are no spaces at the ends of the individual lines.

You should do a debug.print strSQL to see the rendering that is done. I don't see any spaces between the parts shown in red.

strSQL = "Update[Products_Tbl] INNER JOIN [Parts Types] ON [Products_Tbl].[Item Code] = [Parts Types].[Parts ID]" & _
"INNER JOIN [Car_Parts] ON [Parts Types].[Parts Type] = [Car_Parts].[Parts Type]" & _
"SET Products_Tbl.[Qty on Hand] = [Qty on hand]-[Car_Parts].[Qty]" & _
"WHERE [Car_Parts].[Product Code] ='" & [Forms]![Sales_Entry_Form]![Prod_Code_Text] & "'"

[rajgoyal00]

I am sorry but I dont know how to do debug.print

[ItsMe]

While in design view of your form, press Ctrl+G on your keyboard.

[orange]

check out the info here http://www.cpearson.com/excel/debug.htm