Add/Update/Delete both linked table and mysql database (1 Viewer)

pintu1234

New member
Local time
Today, 17:16
Joined
Apr 22, 2019
Messages
2
Need some help with linking mysql database and running a query declared in a function. Currently my access form is updating the linked table (mdb file) but need it to also update changes to mysql table. Below is the code I tried:


Code:
Dim db As DAO.Database
       Dim rstAccounts As DAO.Recordset
       Dim strSQL As String
       Set db = CurrentDb
       Set rstAccounts = db.OpenRecordset(strSQL)
       If rstAccounts.NoMatch = True Then
       Function testboxescheck()
       End If


Private Function testboxescheck()
       If FLFACheckBox = True Then
               strSQL = INSERT INTO 'accounts1' ('ShortDesc', 'Description', 'Account', 'Region', 'CostCode', 'LOB', 'State', 'StateReq', 'NAI', 'StateReq', 'Company')
                VALUES 'Me.ShortDescriptionTBox', Me.DescriptionTBox', 'Me.MajorAccountTBox', 'Me.DivRegionTBox', 'Me.CostCodeTBox', 'Me.LOBTBox', 'Me.StateTBox', 'Me.NAIC-TBox', 'Me.Text49';
                
               strSQL = INSERT INTO 'accounts1' ('ShortDesc', 'Description', 'Account', 'Region', 'CostCode', 'LOB', 'State', 'StateReq', 'NAI', 'StateReq', 'Company')
                VALUES 'Me.ShortDescriptionTBox', Me.DescriptionTBox', 'Me.MajorAccountTBox', 'Me.DivRegionTBox', 'Me.CostCodeTBox', 'Me.LOBTBox', 'Me.StateTBox', 'Me.NAIC-TBox', 'Me.Text53';
                
        ElseIf NLCheckBox = True Then
                 strSQL = INSERT INTO 'accounts1' ('ShortDesc', 'Description', 'Account', 'Region', 'CostCode', 'LOB', 'State', 'StateReq', 'NAI', 'StateReq', 'Company')
                VALUES 'Me.ShortDescriptionTBox', Me.DescriptionTBox', 'Me.MajorAccountTBox', 'Me.DivRegionTBox', 'Me.CostCodeTBox', 'Me.LOBTBox', 'Me.StateTBox', 'Me.NAIC-TBox', 'Me.Text51';
                
        ElseIf FedMutualCheckBox = True Then
                 strSQL = INSERT INTO 'accounts1' ('ShortDesc', 'Description', 'Account', 'Region', 'CostCode', 'LOB', 'State', 'StateReq', 'NAI', 'StateReq', 'Company')
                VALUES 'Me.ShortDescriptionTBox', Me.DescriptionTBox', 'Me.MajorAccountTBox', 'Me.DivRegionTBox', 'Me.CostCodeTBox', 'Me.LOBTBox', 'Me.StateTBox', 'Me.NAIC-TBox', 'Me.Text61';
                
        ElseIf AmericanaCheckBox = True Then
                 strSQL = INSERT INTO 'accounts1' ('ShortDesc', 'Description', 'Account', 'Region', 'CostCode', 'LOB', 'State', 'StateReq', 'NAI', 'StateReq', 'Company')
                VALUES 'Me.ShortDescriptionTBox', Me.DescriptionTBox', 'Me.MajorAccountTBox', 'Me.DivRegionTBox', 'Me.CostCodeTBox', 'Me.LOBTBox', 'Me.StateTBox', 'Me.NAIC-TBox', 'Me.Text55';
                
        ElseIf SACheckBox = True Then
                 strSQL = INSERT INTO 'accounts1' ('ShortDesc', 'Description', 'Account', 'Region', 'CostCode', 'LOB', 'State', 'StateReq', 'NAI', 'StateReq', 'Company')
                VALUES 'Me.ShortDescriptionTBox', Me.DescriptionTBox', 'Me.MajorAccountTBox', 'Me.DivRegionTBox', 'Me.CostCodeTBox', 'Me.LOBTBox', 'Me.StateTBox', 'Me.NAIC-TBox', 'Me.Text57';
               
    Exit Function
        End If
                
End Function
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:16
Joined
Aug 30, 2003
Messages
36,118
Presuming the MySQL table is linked, you could run similar SQL statements against it (though one would wonder why the same data was stored twice).

You realize you're never executing the SQL, right? And presumably the first bit is inside a different sub?
 

pintu1234

New member
Local time
Today, 17:16
Joined
Apr 22, 2019
Messages
2
Can you provide exactly some code example on how I would put this into a different sub? I thought to leave it in the sub for addcmdbutton_click() because I need to update the linked table (mdb file) and mysql when this event occurs (add button clicked on).



The reason for duplicate entries is that is how this database was setup before.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:16
Joined
Aug 30, 2003
Messages
36,118
Rather than try to code it, because you've got a lot wrong there, it would be simplest to create saved append queries that insert into the respective linked tables, and run them from your button with DoCmd.OpenQuery. The queries would have form references in the "Field" argument so they pulled values from the form.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 15:16
Joined
Aug 30, 2003
Messages
36,118
Since you asked for an example, here's a code method:

Code:
      strSQL = "INSERT INTO temp_ar_data_entry ( Cust_key, Reservation_num, Requestor, Description, Company_cd, " _
             & "Driver, Amount, Division, DOR_date, Tran_type, Detail_type ) " _
             & "VALUES (" & rs!account_num & ", '" & rs!confirmation_no & "', '" & rs!Requestor & "', '" _
             & rs!Desc & "', '" & Forms![frm_Company_codes display]![Company_code] & "', " & rs!Driver & ", " _
             & curFare & ", " & rs!Division & ", #" & [Forms]![frm_Trip_sheet_main]![txt_DORDate] & "#, 'I', 'R')"

      CurrentDb.Execute strSQL

I'm getting values from a recordset, you'd use your form references. A FAQ on building SQL in VBA:

http://www.baldyweb.com/BuildSQL.htm
 

Users who are viewing this thread

Top Bottom