Can't Emulate a Saved Query with VBA (CurrentDb command)

[Shinta]

Greetings:

Perhaps I'm violating some good practices / forum norms, for the title of the post: please let me know how can I improve it

My issue I ask for help, is of a saved query which I would like to emulated with VBA.

The whole idea is to update a set of a table's fields based in the status of a control at a form; when the condition is met, the query takes the value of another field for doing the updates.

The original saved query is: UPDATE EntradaPatiosDetalle SET EntradaPatiosDetalle.precio = IIf([Forms]![EntradaPatios]![patioCheckBox]=True,[precio_patio],[precio_pyme])
WHERE (((EntradaPatiosDetalle.idEntrada)=[Forms]![EntradaPatios]![idEntrada]));

The closest approach I can get to at VBA (which fails ), is: CurrentDb.Execute "UPDATE EntradaPatiosDetalle SET [precio] = " & IIf([Forms]![EntradaPatios]![patioCheckBox] = True, [precio_patio], [precio_pyme]) & ", [mayoreoCheckBox] = Null WHERE [idEntrada] = " & Me.idEntrada


I'm sure that the problem is at the code maked in bold..

Any ideas?

Thanks a lot in advanced; regards

 

[Galaxiom]

The closest approach I can get to at VBA (which fails ), is: CurrentDb.Execute "UPDATE EntradaPatiosDetalle SET [precio] = " & IIf([Forms]![EntradaPatios]![patioCheckBox] = True, [precio_patio], [precio_pyme]) & ", [mayoreoCheckBox] = Null WHERE [idEntrada] = " & Me.idEntrada


I'm sure that the problem is at the code maked in bold..

Why so sure?

This expression would be a problem because it will always return False.

[mayoreoCheckBox] = Null

Null cannot be tested with the equals operator.

Try

IsNull(Me[mayoreoCheckBox])

or

[mayoreoCheckBox] Is Null

 

[Shinta]

Greetings Galaxiom:

Thanks a lot for passing by and give a hint about this issue; mm.. you are right, I got a mistake, if the real original query is:

UPDATE EntradaPatiosDetalle SET EntradaPatiosDetalle.precio = IIf([Forms]![EntradaPatios]![patioCheckBox]=True,[precio_patio],[precio_pyme])
WHERE (((EntradaPatiosDetalle.idEntrada)=[Forms]![EntradaPatios]![idEntrada]));

The approach I'm willing to get would be:

CurrentDb.Execute "UPDATE EntradaPatiosDetalle SET [precio] = " & IIf([Forms]![EntradaPatios]![patioCheckBox] = True, [precio_pyme], [precio_patio]) & " WHERE [idEntrada] = " & Me.idEntrada

Which fails... so, you don't thing that the part marked in bold is the one creating the failure? My guess is that VBA can't "see" the fields from the table in the same way that the saved query can... still, I'm not the expert.

Thaks again for the suggestion

 

[Galaxiom]

IIf([Forms]![EntradaPatios]![patioCheckBox] = True, [precio_pyme], [precio_patio])

That expression will be looking for [precio_pyme] and [precio_patio] as controls or fields on the form.

 

[pbaldy]

This expression would be a problem because it will always return False.

[mayoreoCheckBox] = Null

Null cannot be tested with the equals operator.

To clarify this point, I don't think Galaxiom noticed this was part of the SET clause, where it should be fine. You can set a field to Null, presuming the underlying table allows it.

 

[Galaxiom]

Try this:

CurrentDb.Execute "UPDATE EntradaPatiosDetalle SET [precio] = " & IIf([Forms]![EntradaPatios]![patioCheckBox], "[precio_pyme]", "[precio_patio]")  & " WHERE [idEntrada] = " & Me.idEntrada

 

[Shinta]

To clarify this point, I don't think Galaxiom noticed this was part of the SET clause, where it should be fine. You can set a field to Null, presuming the underlying table allows it.

Greetings pbaldy, thank you for passing by and give your view:

It is important to now that I'm able to set a value to Null, previously knowing that the field is configured to accept it...

Regards

 

[Shinta]

Try this:

CurrentDb.Execute "UPDATE EntradaPatiosDetalle SET [precio] = " & IIf([Forms]![EntradaPatios]![patioCheckBox], "[precio_pyme]", "[precio_patio]")  & " WHERE [idEntrada] = " & Me.idEntrada

Hey, that worked!! Thanks a lot Galaxiom for giving yourself the chance for helping..

This forum is truly helpful and professional

My best wishes... and thanks again

 

[pbaldy]

Greetings pbaldy, thank you for passing by and give your view

Hola y de nada! (butchered Spanish from many, many moons ago when I could speak passable Spanish)

 

[Galaxiom]

To clarify this point, I don't think Galaxiom noticed this was part of the SET clause, where it should be fine. You can set a field to Null, presuming the underlying table allows it.

Yes Paul is correct. I misread the code.

It is much easier to read code if it is put into code tags. Even starting the code on a new line would have helped.

If code is particularly long then it is a good idea to format it with line continuations.

BTW The reason my code worked is because you needed to pass the name of the field in the table as a string. Your code was trying to pass the values from controls or fields on the form.

 

[Shinta]

Hola y de nada! (butchered Spanish from many, many moons ago when I could speak passable Spanish)

lol n.n... actually, your sentence was properly expressed and has the whole sence.

I appreciate deeply for the great community you have got: its quite harsh to have doubts in this issues and have no place to run for help..

Gracias

 

[Shinta]

Yes Paul is correct. I misread the code.

It is much easier to read code if it is put into code tags. Even starting the code on a new line would have helped.

If code is particularly long then it is a good idea to format it with line continuations.

BTW The reason my code worked is because you needed to pass the name of the field in the table as a string. Your code was trying to pass the values from controls or fields on the form.

Thanks for the tips Galaxiom: I'll try to be more careful with the formatting, so it'll improve the way to present things

And about this specific subject... yes, I'd no idea how could I tell to the VBA that I was referring to a field and not a control of the present form; so, your knowlegde and experience did the trick: this is the reason why we seek for the experts

My gratitude for your concern about my doubts