How to update one field on two tables immediatley AfterUpdate 1

[debq]

I have a form frm02a_Track_Log. The record source of the table is tbl01. I want to add text to a field for a specific record on the table from the form and when I move out of the text field I want tbl01 to update immediately and also update a table called tbl02_Main.

I have started a VBA function to immediatley update the source table, tbl01, but am unsure how to get the function to also update the second table with only the information for the specific record field.

Here is the code that I have so far:

Private Sub Problem_PA_Only_AfterUpdate()
    On Error GoTo ErrHandler:

   DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_ErrHandler:
    Exit Sub
ErrHandler:
    MsgBox Err.Number & Err.Description
    Resume Exit_ErrHandler
  
 End Sub

Thank you in advance for any guidance or help!!

 

[dhookom]

Can you share any field names? Does tbl02_Main only have one record or do you want to update all records in tbl02_Main?

Duane
Hook'D on Access
MS Access MVP

 

[debq]

I need to update only one text field on tbl02_Main_Table , "Problem_PA_Only".

"Problem_PA_Only" is a text field on the form that updates to tbl01_Audit Reviews. I also need to update this field concurrently on tbl02_Main_Table.

But I will only need to update the current record on the form which is driven by the unique record ID - "Audit Review Number"

I have tied to create a SQL to get the current Audit Review Number from the form and then use that Audit Review Number to update the field "Problem_PA_Only" on that record in tbl02_Main_Table.

This is the code thus far:

Private Sub Problem_PA_Only_AfterUpdate()
    On Error GoTo ErrHandler:
   
   Dim db As DAO.Database
   Dim strGetARNumSQL As String
   Dim strUpdateSQL As String
  
     Set db = CurrentDb
   
    strGetARNumSQL = "SELECT [tbl01_Audit Reviews].[Audit Review Number]FROM [tbl01_Audit Reviews]WHERE ((([tbl01_Audit Reviews].[Audit Review Number])=[Forms]![frm02a_Track_Log]![Audit Review Number]));"
        
    strUpdateSQL = "UPDATE [tbl01_Audit Reviews] INNER JOIN tbl02_Main_Table ON [tbl01_Audit Reviews].[Audit Review Number] = tbl02_Main_Table.[Audit Review Number] SET [tbl01_Audit Reviews].[Problem_PA_Only] = [tbl02_Main_Table]![Problem_PA_Only] WHERE 'Audit Review Number' = 'strGetARNumSQL';"
    DoCmd.RunSQL (strUpdateSQL)
   
   
   DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
   
Exit_ErrHandler:
    Exit Sub
    
ErrHandler:
        MsgBox Err.Number & Err.Description
        Resume Exit_ErrHandler
  
rcrdSet.close

db.close


End Sub

I am stuck getting both SQL to run. And am I on the right track to solve this puzzle??

Thanks for any help you can give...

 

[dhookom]

Why are you attempting to run a query to get the [Audit Review Number] from a table by using the [Audit Review Number]? This is like asking "Hey debq. What is your tek-tips user name?" or "What color is your red car?"

Also, isn't the value of [Problem_PA_Only] in the current form?

It also seems you were updating the wrong table.

Private Sub Problem_PA_Only_AfterUpdate()
    On Error GoTo ErrHandler:
   
    Dim db As DAO.Database
    Dim strUpdateSQL As String
  
    Set db = CurrentDb   
   
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    [green]'assuming [Audit Review Number] is text[/green]
    strUpdateSQL = "UPDATE [tbl01_Audit Reviews] INNER JOIN " & _
        "tbl02_Main_Table ON [tbl01_Audit Reviews].[Audit Review Number] = tbl02_Main_Table.[Audit Review Number] " & _
        "SET [tbl02_Main_Table].[Problem_PA_Only] = [tbl01_Audit Reviews]![Problem_PA_Only] " & _
        "WHERE [tbl01_Audit Reviews].[Audit Review Number] = '" & Me.[Audit Review Number] &  "'"
    db.Execute strUpdateSQL, dbFailOnError

Exit_ErrHandler:
    Set db = Nothing
    Exit Sub
    
ErrHandler:
        MsgBox Err.Number & Err.Description
        Resume Exit_ErrHandler
  
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[debq]

Yep... You are correct!! I have inherited this DB.... Lots of issues and problems... I was told to sync the field, "Problem_PA_Only" on both tables... (not in a position to ask why) so I am trying my best.... Yikes... and I am just learning VBA... I want to learn so as not to repeat the mistakes of those who create before me......

Thanks for your guidance and your help.

 

[debq]

Oh.... the field Audit Review Number is a number not text.......

 

[dhookom]

Try

Private Sub Problem_PA_Only_AfterUpdate()
    On Error GoTo ErrHandler:
   
    Dim db As DAO.Database
    Dim strUpdateSQL As String
  
    Set db = CurrentDb   

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    [green]'assuming [Audit Review Number] and [Proble_PQ_Only] are numeric[/green]
    strUpdateSQL = "UPDATE  tbl02_Main_Table " & _
        "SET [Problem_PA_Only] =  " & Me.[Problem_PA_Only] " " & _
        "WHERE [Audit Review Number] = " & Me.[Audit Review Number]
    debug.Print strUpdateSQL
    db.Execute strUpdateSQL, dbFailOnError

Exit_ErrHandler:
    Set db = Nothing
    Exit Sub
    
ErrHandler:
        MsgBox Err.Number & Err.Description
        Resume Exit_ErrHandler
  
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[debq]

The Audit Review Number is a Number and the field Problem_PA_Only is a text field.

Thanks for your help!!

 

[dhookom]

Try:

    strUpdateSQL = "UPDATE  tbl02_Main_Table " & _
        "SET [Problem_PA_Only] =  """ & Me.[Problem_PA_Only] & """ " & _
        "WHERE [Audit Review Number] = " & Me.[Audit Review Number]

If you get an error, please try to see if you can at least identify the cause and possibly fix it.

Duane
Hook'D on Access
MS Access MVP

 

[debq]

GREAT!!! That did it..... I knew it had something to do with single or double parenthesis... and data type number vs text.... I just could not get the syntax right... Thanks so much for your help and your patience.....