docmd.execute "update..." problem

[Angi]

This code works great, acts just like an option group for cts forms.
Except one small problem...it updates all the records in the table
instead of the ones just for the current CoID. I've tried adding a
Where statement but then it says I have too few parameters. Expected
1. What am I supposed to do here?

TIA!

Private Sub Default_BeforeUpdate(Cancel As Integer)
Dim sql As String
sql = "SELECT ShipToMain.*, ShipToMain.CoID FROM ShipToMain WHERE
(((ShipToMain.CoID)=me.CoID)AND ((ShipToMain.Default)=Yes));"

If Me.Default = True And Not IsNull(sql) Then
CurrentDb.Execute "Update shiptomain Set shiptomain.default =
no;", dbFailOnError
End If

End Sub

 

[Ken Snell [MVP]]

Concatenate the value from the form's control into the string, not the name
of the control:

sql = "SELECT ShipToMain.*, ShipToMain.CoID FROM ShipToMain " & _
"WHERE (((ShipToMain.CoID)=" & Me.CoID & ") AND " & _
"((ShipToMain.Default)=Yes));"

 

[Angi]

Ken,
Thanks for the quick reply! Changed the sql statement. It's still
doing exactly the same thing. I'm having a similiar problem with
another line in the code (different If) that I'm hoping will work right
once I get the correct sql string. What should I try next?

thx,
ang

 

[Angi]

just another thought...

how does the execute know which records?

CurrentDb.Execute "Update shiptomain Set shiptomain.default =
no;", dbFailOnError

 

[Ken Snell [MVP]]

If it's updating all records, then something isn't working correctly with
your SQL statement. and I see why -- you are building an SQL statement, and
then you are not using it in the Delete query run!

Private Sub Default_BeforeUpdate(Cancel As Integer)
Dim sql As String
sql = "SELECT ShipToMain.*, ShipToMain.CoID FROM ShipToMain WHERE
(((ShipToMain.CoID)=me.CoID)AND ((ShipToMain.Default)=Yes));"

If Me.Default = True And Not IsNull(sql) Then
CurrentDb.Execute sql, dbFailOnError
End If

End Sub

 

[Angi]

Ken,
The sql string is just a select query to tell me if there are records
that match what I need (basically a count with criteria). I've created
another called sqlUpdate and used an Update sql statement. Now it
works fine. Hope this was the right way to do it!

If I may...one more question regarding the same code in a
Form_BeforeUpdate event:

All of this works:

Dim sql As String
Dim sqlUpdate As String
sql = "SELECT ShipToMain.*, ShipToMain.CoID FROM ShipToMain " & _
"WHERE (((ShipToMain.CoID)=" & Me.CoID & ") AND " & _
"((ShipToMain.Default)=Yes));"

sqlUpdate = "Update shiptomain Set shiptomain.default = no WHERE
(((ShipToMain.CoID)=" & Me.CoID & ") AND " & _
"((ShipToMain.Default)=Yes));"

If Me.Default = True And Not IsNull(sql) Then
CurrentDb.Execute sqlUpdate, dbFailOnError
End If

This line doesn't work. It doesn't do anything.
If Me.Default = False And IsNull(sql) Then
Me.Default = True
End If

Can you make a field a different value on during a BeforeUpdate event
or do I need to put this in another event before the form closes? I've
tried adding the acCmdSaveRecord, but it didn't make a difference
either. What I'm trying to say is if I checked the box and there is
already a default, make this one the default (works). If there's not a
default and I didn't check the box, then make this one the default
(doesn't work). Make sense??

Thanks for your help and leading me in the right direction!

Angi

 

[Ken Snell [MVP]]

You cannot use an SQL statement as a "value" to test for IsNull. An SQL
statement is a text string, and will never be Null.

A DCount function could be used to see if there are any records that would
need to be updated. It would look something like this:

If DCount("*", "ShipToMain", "[CoID]=" & Me.CoID & " AND [Default]=Yes") > 0
Then

The above statement will be true if any records in ShipToMain match the
criteria.

However, there is no need to do this. If you run an update query and there
are no records that match the criteria, no records get updated.

So your code might become this:


Dim sql As String
Dim sqlUpdate As String
sqlUpdate = "Update shiptomain Set shiptomain.default = no WHERE
(((ShipToMain.CoID)=" & Me.CoID & ") AND " & _
"((ShipToMain.Default)=Yes));"
If Me.Default = True Then
CurrentDb.Execute sqlUpdate, dbFailOnError
End If

 

[Angi]

Ken,
Thank you for that explanation. I always forget about that DCount and
what you said makes perfect sense. I guess I'm over-thinking this and
going code crazy! I used the DCount for the Default = False and now it
works!!

I'm eternally grateful for your help! It was driving me nuts!
Thanks so much!
Ang

 

[Ken Snell [MVP]]

You're welcome.