Add data to one field, it then updates two tables - Possible?

[timnicholls]

I have a bound field on a form, it holds the names of an individual involved in a workplace incident.

Now I wish to have those names added to another table called 'OtherNames'. That is, when you add the name to the incident form, the original incident table updates, because the name control is bound to the field on the incident table, also the name appears in the second table.

So one name added, it appears in two tables. Possible?

I think any SQL (if any is needed) goes in the after update of the name field.

Thanks

 

[scriverb]

This can be done with some VBA code in your forms. Either in the AfterUpdate event procedure or when the record is saved. But, my question has to do with whether these two tables are linked in anyway. Normalization would have it that you want to put the name only in one table and have other tables linked to it to display the name. Would have to know your table structure to be able to advise in that manner. So, at time I can give you code to update the OtherNames table if you can just give me the field names of the other table. Not just the one to be updated but are there other fields that need updated.

Please post back with a little more info here.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[timnicholls]

Yeah I know what you mean normalisation wise!
This is a real work around.

At the moment I have the incident form, on it is three option choices, staff, clients and others. When you choose one of the three, a combobox is populated with the appropriate names, so then you can choose an appropriate name. So you choose staff from the option, you get staff names in the combo, you choose a staff name which goes in the bound 'Name' field on the form. Clear as mud

But wait! What about adding names not in the combobox (ie) Others. More importantly I want to have those 'added' names appear as those you can choose from in the combobox

So I want to add the name in the bound 'Name' field and have it appear in the incident details table which is default, then have them in the Others table so they will appear in the combobox.

Given all that

The Others table only holds one field 'OtherName', the table name is 'Incident_Other'

The table which holds the field the name is coming from is called '[Incident Details]' and the name field is called 'IncidentInvolved1'.

So I need to get from [Incident Details].IncidentInvolved1 to Incident_Other.OtherName.

Thanks

 

[scriverb]

This can be done by setting the Limit To List property of the combobox to Yes. Then put the following code in your NotInList event procedure of the combobox:

Private Sub Others_Combo_NotInList(NewData As String, Response As Integer)
    ' Prompt user to verify they wish to add new value.
    If MsgBox("Add this Name to the table Incident_Others?", vbOKCancel) = vbOK Then
       Dim db as DAO.Database
       Dim rs as DAO.Recordset
       Set db = CurrentDB
       Set rs = db.OpenRecordset("Incident_Other", dbOpenDynaset)
       rs.AddNew
       rs("OtherName") = NewData
       rs.Update
       rs.close
       db.close
       ' Set Response argument to indicate that data is being added.
       Response = acDataErrAdded
    Else
        ' If user chooses Cancel, suppress error message and undo changes.
        Me![Others_Combo] = Null
        Response = acDataErrContinue
    End If
End Sub

Post back with any questions.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[timnicholls]

Bob, I have added the code.
It's a neat way of doing it !

Thank You

 

[scriverb]

Glad to be able to help you here. Have a nice day.



[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]