How can I run an update of a table from a command button

[rew2009]

How can I run an update of a table from a command button. I know I can run a query but I want to pass parameters to the query. how can I put the query code into the command button? Like:

I have a table named "Clients" with a field named 'field11' and I want to put the contents of a the text box 'text100' into all records with an ID larger than 1651.

I tried the following in a command button but got an error.

UPDATE Clients SET field11 = me.text100.value
WHERE [ID]>1651;

What is the correct code?

Thanks

 

[dhookom]

Assuming field11 is numeric, try:

Dim strSQL as String
strSQL = "UPDATE Clients SET field11 = " & _
    & Me.text100 & " WHERE [ID]>1651"
DoCmd.RunSQL strSQL

If field11 (it really isn't named field11 is it) is text, try:

Dim strSQL as String
strSQL = "UPDATE Clients SET field11 = """ & _
    & Me.text100 & """ WHERE [ID]>1651"
DoCmd.RunSQL strSQL

Duane
Hook'D on Access
MS Access MVP

 

[rew2009]

Thanks dhookom, That was the type of code that I was looking for. I did get a syntax error having to do with the '"'s but I simplified it to one row like: 'strSQL = "UPDATE Clients SET field11 = text100 WHERE [ID]>1651"
and had to take the "Me" out. I don't know why it didn't like "Me.", but it got me on the right track. Also I do have a field named 'field11' and it is only used for testing code in my database. I don't want to screw up a working field when I am testing code. Anyway, Thanks

 

[dhookom]

I find it hard to believe this would work:

'strSQL = "UPDATE Clients SET field11 = text100  WHERE [ID]>1651"

There is no way the query would know the value of text100 unless there was a field named text100 in the Clients table.

Duane
Hook'D on Access
MS Access MVP

 

[rew2009]

This is the actual code that I applied that concept to. I am using it to xfer a name in the text box "text21" on the form to a field "Manager_owner" in the table, and xfers a numb in text box "text26" to a field "manager_num" in the same table all for records where a check box bound to FLAG1 (which is a field) is checked "TRUE". Then I reset all FLAG1 fields to "FALSE".

This is the code:
________________________

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim strSql As String

strSql = "UPDATE Apts SET manager_owner = text21,manager_num=text26 " & _
"WHERE [FLAG1]=TRUE" ' This inserts the Propery Management name into 'manager_owner' for Flagged Records

DoCmd.RunSQL strSql


strSql = "UPDATE Apts SET FLAG1 = FALSE " & _
"WHERE [ID]>0" ' This resets all the flag boxes to FALSE

DoCmd.RunSQL strSql

Exit_Command0_Click:
Exit Sub

Err_Command0_Click:
MsgBox Err.Description
Resume Exit_Command0_Click

End Sub
________________________

And the code works! - I have been using it for the last few days in the actual access program. I just tried again to put in "me.text21" to see the error I was talking about and I got a small dialogue box which popped up which said: "Enter Parameter Value - me.text21" with an input box for a parameter value. But it works with no "me." in there; i.e., no dialogue box. Again, I am confused as to why it works with no "me." but it does! If there was some way I could send you a screen shot I would be happy to. I am still at the stage where a lot of this stuff is black magic to me, but I am still trying to understand as least the code that I am using.

By the way, this all occurs on a popup form but that should not make any difference as far as I know. The only other thing I can tell you is that the popup form is unbound but it has embedded a subform which is bound to "APTS" table - that is where I flag the records that I want changed and change them with the code above from the command button in the main popup form.


Your thoughts?

 

[dhookom]

I would expect code like this:

Private Sub Command0_Click()
On Error GoTo Err_Command0_Click

Dim strSql As String
' assuming Manager_Owner and Manager_Num are both text
strSql = "UPDATE Apts SET manager_owner = """ & _
   Me.text21 & """,manager_num=""" & _
   Me.text26 & """ " & _
   "WHERE [FLAG1]=TRUE"
' This inserts the Propery Management name into 'manager_owner' for Flagged Records

DoCmd.RunSQL strSql

strSql = "UPDATE Apts SET FLAG1 = FALSE " & _
  "WHERE [ID]>0"
' This resets all the flag boxes to FALSE

DoCmd.RunSQL strSql

Exit_Command0_Click:
    Exit Sub

Err_Command0_Click:
    MsgBox Err.Description
    Resume Exit_Command0_Click
    
End Sub

BTW: find and use a naming convention. "text21" and "text26"?

Duane
Hook'D on Access
MS Access MVP

 

[rew2009]

Duane, I substituted your code instead of the original code above and your code does work. The only other difference other than the missing “me.” was that I didn't have all of the """ in my original code but both codes work! You might want to try my version on a sample database of yours. Again I am baffled why mine works because I agree it shouldn't. And by the way the text boxes text21 and text26 are not fields in the table but only text boxes in the form.

I do have another question. In my (or your) code, I am setting all the field "flag1" back to "FALSE" after the xfer operation. But the way I am doing it is setting all the 12,000 records "flag1" to FALSE. However, when I do this xfer operation I normally only flag between one and maybe 50 records. Is there a more efficient way of doing it where it is only operating on the 50 or so records (without writing too much more code).

Thanks

 

[dhookom]

You should change the names of your text boxes from the default textN to a name that makes sense. It takes 5 seconds and kicks your programming up a notch.

Your last SQL only updates the required records, not all 12,000. As many as 11,999 might be ignored by the SQL.

Duane
Hook'D on Access
MS Access MVP