VBA code does not update table from form data

[ldeant]

I am trying to build a form to update fields in a table based on customer input in a form. The form has a criteria field, which is used to search for matching entries then an update field to update another Table field. My problem is that I cannot get the update to work when I reference the fields from the form. Here is the code:

    Dim db As Database
    Dim strSQL As String
    Set db = CurrentDb
    Set dbs = CurrentDb
    Dim uiTAGNAME As String
    Dim uoX_NAM As String
    
 
    
    Forms![Prefill Xmtr Data]!TxtCPointTagname.SetFocus
    uiTAGNAME = Forms![Prefill Xmtr Data]!TxtCPointTagname.Text
    
    Forms![Prefill Xmtr Data]!TxtQTransmitter.SetFocus
    uoX_NAM = Forms![Prefill Xmtr Data]!TxtQTransmitter.Text
    
    Debug.Print uiTAGNAME
    Debug.Print uoX_NAM
    
    '   [" + table2 + "]
    ' suggestion from stackoverflow with format used in udatedata
    strSQL = "UPDATE [IO Data] SET [io data].x_nam = '" & uoX_NAM & "' " & _
    "WHERE [IO Data].tagname Like 'bkr7*';"
    Debug.Print strSQL
    db.Execute strSQL

When I set a break, I can view the value of uoX_NAM, and it is updated from the form correctly.

I'm pretty sure I have the form field referenced incorrectly, but I have tried various other methods, none of which update the table.


The output of the Debug.Print strSQL is (aaa is the text I input into the form field TxtQTransmitter):


UPDATE [IO Data] SET [io data].x_nam = 'aaa' WHERE [IO Data].tagname Like 'bkr7*';


If I try the following code, the correct fields update, but it is not the entry from the form:

'this updates the correct fields
db.Execute "UPDATE [IO Data] SET [io data].x_nam = 'BRKRCMD'" & _
"WHERE [IO Data].tagname Like 'bkr7*';"

So my problem appears to be in how I reference the uoX_NAM variable.


Or possibly in how I am updating my table [IO Data]? But the table updates fine if I am not taking the value from the form.

[rpeare]

You are hardcoding the LIKE part of your statement as it relates to the TAGNAME field of your table. If you run this aren't you just updating the same data over and over again?

if you are trying to substitute the tag text from your main form you would have to use something more like:

    strSQL = "UPDATE [IO Data] SET [io data].x_nam = '" & uoX_NAM & "' WHERE [IO Data].tagname Like '" & uitagname & "*';"

[ldeant]

That is correct, that is where I am heading, but since the table is not getting updated now, I decided to reduce the number of variables I am working with.

[orange]

Just a comment --when you post code especially a sub or function or event code, please post the whole thing.

As for UPDATE queries, they can be quite unforgiving. A suggestion is to do a SELECT query first to absolutely be sure you are selecting the right records to be updated. Then, run the UPDATE knowing what will be affected.
The other option is to make a backup of the table involved, just in case things don't work as you expected-- you can always restore the table as it was.

Good luck.

[rpeare]

I agree with orange's statements, I just assumed it was a new field that you wanted to populate and didn't consider it might have data in it already.

Below is the code I used and it updated the table correctly.

Dim db As Database
Dim strSQL As String
Set db = CurrentDb
Dim uiTAGNAME As String
Dim uoX_NAM As String


uiTAGNAME = "bkr7"
uoX_NAM = "TEST THIS"


Debug.Print uiTAGNAME
Debug.Print uoX_NAM


strSQL = "UPDATE [IO Data] SET [io data].x_nam = '" & uoX_NAM & "' " & _
"WHERE [IO Data].tagname Like '" & uiTAGNAME & "*';"


Debug.Print strSQL
db.Execute strSQL

[ldeant]

rpeare, I commented out all of my code and pasted yours in, but my table is still not updated

Private Sub BtnQApplyUpd_Click()

    Dim db As Database
    Dim strSQL As String
    Set db = CurrentDb
    Dim uiTAGNAME As String
    Dim uoX_NAM As String


    uiTAGNAME = "bkr7"
    uoX_NAM = "TEST THIS"


    Debug.Print uiTAGNAME
    Debug.Print uoX_NAM


    strSQL = "UPDATE [IO Data] SET [io data].x_nam = '" & uoX_NAM & "' " & _
    "WHERE [IO Data].tagname Like '" & uiTAGNAME & "*';"


    Debug.Print strSQL
    db.Execute strSQL
End Sub

The immediate window shows this:
bkr7
TEST THIS
UPDATE [IO Data] SET [io data].x_nam = 'TEST THIS' WHERE [IO Data].tagname Like 'bkr7*';

Which looks an awful lot like the hard-coded example that does update the table.

I really do appreciate your help, I've been beating on this one for a while now.

[rpeare]

provide a sample database if you would because I used your code and it worked just fine, I just hard coded the uitagname and uox_nam variables.