Update Form Field via VBA

[sweeneytime]

Hi guys,

On my form I have a button which I want to do the following:

Status field: Change to "Inactive"
Copy record
Paste record
Goto last record
Status field: Change to "Active"

I have the copy part done, it then moves to the last record which is the new one. But the code I have to insert value does nothing, it's what I have gotten from goggle. I have tried it as table name "dbo_Employee_Data" and "Employee_Data"

If some knows where I am going wrong it would be much appreciated!

Cheers,
Sweeneytime

Private Sub Command70_Click()

        Dim strStatusActive As String
        Dim strStatusInActive As String
        
        strStatusActive = "Active"
        strStatusInActive = "InActive"
    
        SQL = "INSERT INTO dbo_Employee_Data (Status) VALUES ('" & strStatusActive & "')"
    
        DoCmd.RunCommand acCmdSelectRecord
        DoCmd.RunCommand acCmdCopy
        DoCmd.RunCommand acCmdPasteAppend
        DoCmd.RunCommand acCmdRecordsGoToLast
        
        SQL = "INSERT INTO dbo_Employee_Data (Status) VALUES ('" & strStatusInActive & "')"
        
    
    
End Sub

 

[John Big Booty]

For starters your SQL statement is using the Insert clause which is fine your inserting a new record, but to update you need to use the Update clause, and from there you need to consider if you wish to update all the records to inactive or simply the current record.

I'll have a look at the rest of the code and post back latter.

 

[John Big Booty]

... oh and you've not actually run either of your SQL statements.

 

[John Big Booty]

Here's one way you could do this it will copy the record that currently has focus on your form and update it to Inactive;

    Dim stSQL As String
    Dim stSQL2 As String
    
    stSQL = "INSERT INTO YourTableName (  YourFirstField, YourSecondField, YourThirdField)" & _
            "SELECT YourTableName .YourFirstField, YourTableName .YourSecondField, YourTableName .YourThirdField " & _
            "FROM YourTableName  " & _
            "WHERE (((YourTableName .YOurRecordID)=[Forms]![YourFormName]![YourRecordID]));"

            
    DoCmd.RunSQL stSQL

    stSQL2 = "UPDATE TBL_Records SET TBL_Records.RecNote = " & Chr(34) & "Inactive" & Chr(34) & _
            "WHERE (((TBL_Records.RecordID)=[forms]![form1]![RecordID]));"
    
    
    DoCmd.RunSQL stSQL2

 

[John Big Booty]

...in the first SQL statement do not select the RecordID as this is probably an auto number and trying to insert it will cause your code to not work correctly

 

[sweeneytime]

Thanks for that I will try your new solution, I had worked in your other suggestions, but got a syntax error on "DoCmd.RunSQL updateSQL

Private Sub Command72_Click()

        
        Dim strStatusInActive As String
        Dim updateSQL As String
        
        strStatusInActive = "InActive"
    
        DoCmd.RunCommand acCmdSelectRecord
        updateSQL = "UPDATE Employee_Data (Status) VALUES ('" & strStatusInActive & "')"
        DoCmd.RunSQL updateSQL


End Sub

 

[John Big Booty]

Try;

Private Sub Command72_Click()

        
        Dim strStatusInActive As String
        Dim updateSQL As String
        
        strStatusInActive = "InActive"
    
        DoCmd.RunCommand acCmdSelectRecord
        updateSQL = "UPDATE Employee_Data (Status) VALUES (" & Chr(34) & strStatusInActive & Chr(34) & ")"
        DoCmd.RunSQL updateSQL


End Sub

Note: I've removed the single quotes and insert Chr(34) {Double Quotes} which is a little easier to read; I find

 

[John Big Booty]

...you do realise that the SQL you have present is simply going to update all the values in your table to InActive

And your syntax looks a little off, it should be in the format;

UPDATE TableName SET TableName.FieldName = " & Chr(34) & "Value to update to" & Chr(34) & ";"

 

[sweeneytime]

haha "You are about to update 304 records"! At least I have that part of the code working. So obviously in this case the select record doesn't take affect. Is there sql code for that?

Private Sub Command72_Click()

        
        Dim strStatusInActive As String
        Dim updateSQL As String
        
        strStatusInActive = "InActive"
    
        DoCmd.RunCommand acCmdSelectRecord
        updateSQL = "UPDATE dbo_Employee_Data SET dbo_Employee_Data.Status = " & Chr(34) & "Value to update to" & Chr(34) & ";"
        DoCmd.RunSQL updateSQL


End Sub

 

[John Big Booty]

Examine the code I presented in post number 4, you need a Where clause in your SQL.