Forum Discussion

Mark1932's avatar
Mark1932
Copper Contributor
Oct 15, 2021

TROUBLE UPDATING MULTIPLE TABLES WITH

Hello.  My first time on the forum.  Hopefully someone with more skill than I can help  me.  MY GOAL:  Update "multiple* tables with an unbound form.   I thought that the following code would work, but at a dead end.

 

CurrentDb.Execute ("UPDATE Participant Registration List (Academy Cohort Number, #Cohort Start Date#, Agency Names, Seats Allocated, Employee ID Number, Employee Last Name, " & _
"Employee HR title, Email Address, Phone Number, LOS City, Time In HR, Group)

VALUES ('" & Me.ACADEMY_COHORT_NUMBER - 1 & "', '#" & Me.COHORT_START_DATE - 2 & "#', " &_


"'" & Me.AGENCY_NAMES - 3 & "', '" & Me.SEATS_ALLOCATED - 4 & "', '" & Me.[EMPLOYEE ID NUMBER-5] & "', '" & Me.EMPLOYEE_LAST_NAME - 6 & "', '" & Me.EMPLOYEE_FIRST_NAME - 7 & "', '" & Me.EMPLOYEE_HR_TITLE - 8 & "', " & _


"'" & Me.EMAIL_ADDRESS - 9 & "', '" & Me.PHONE_NUMBER - 10 & "', '" & Me.LOS_CITY - 11 & "', '" & Me.TIME_IN_HR - 12 & "', '" & Me.GROUP - 13 & "'")

 

MsgBox "Updated Successfully..."

 

Can someone please tell me where I'm going wrong?

2010
2016
access
  • George_Hepworth's avatar
    George_Hepworth
    Silver Contributor
    Oct 15, 2021

    Mark1932 So many things to address.

     

    For starters why do you want to use an unbound form in the first place? Access is unique among development tools in its ability to use bound forms. Why not take advantage of that power? I'm not saying there are no situations where unbound forms are reasonable, only that they are rare.

    We try, as professional developers, to follow good design principles. Another one is that we do NOT try to update data in multiple tables simultaneously in a single form. If you have tables in a one-to-many relationship and want to add or update data in both the parent (one-side table) and the child (many-side table), the preferred design is a main form/sub form. The one-side table is bound to the main form and the many-side table is bound to the subform. Data must be added first to the table bound to the main form, then it can be added to the table bound to the subform.

     

    It's possible to deviate from that standard design, but there ought to be a powerful reason to justify it.

     

    Finally, your posted SQL looks like it has table and field names with spaces in them, e.g. Participant Registration List or Employee ID Number.


    If that is accurate, you must delimit all of those names with square brackets, e.g. [Participant Registration List] and [Employee ID Number].

     

    However, a much better approach is not to use spaces or other special characters in table and field names at all to head off this complication, e.g. ParticipantRegistrationList, or EmployeeIDNumber.

Share