add records to both sides of MtoM (A2K)

Topic

New Reply

I need to create a form combination that will allow me to add a record to each side of a many to many relationship. I need to be able to add a new consumer and a new organization and have the two linked via a join table that contains lngConsumerID and lngOrgID. Does anyone have a sample I can look at or tell me a good strategy?

The organizations attached to the consumer also need to be readily visible to the user.

E

Reply | Quote

Replies

You can search for many-to-many in this forum. For example, post 364203 has a simple sample database attached with two forms that display a many-to-many relationship from both sides.

Reply | Quote

You can search for many-to-many in this forum. For example, post 364203 has a simple sample database attached with two forms that display a many-to-many relationship from both sides.

Reply | Quote

Suggest you think about using an unbound data entry form. You can then add information about both the new consumer and organisation into variables on the unbound form and put some code under an Add button to save the 2 records and the linking record using a transaction to ensure that all the saves take place.

Reply | Quote

Can you tell me where I can learn more about using unbound data entry forms along with the code required. I think this would help me with my room data sheet problems.

Reply | Quote

I hope your problem can be solved by using subforms instead of linked forms.

If you want to create an unbound data entry form, I would do it as follows:
– Start by creating a bound form, the way you usually do. You can use a wizard, or create it manually, or a combination of both.
– When it looks OK, clear the Control Source of all controls bound to a field, and clear the Record Source property of the form.
– Put two command buttons on the form: cmdOK, with caption OK (or Save), and cmdCancel with caption Cancel (or Close).
– In the On Click event procedure for cmdOK, open a recordset and create a new record; this can be done in DAO or ADO. If you want to add records to several tables, handle them separately, making sure to create the necessary primary keys first.

For example using DAO (you need a reference to the Microsoft DAO 3.6 Object Library for this):

Private Sub cmdOK_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo ErrHandler

‘ Perform a check
If IsNull(Me.txtLastName) Then
MsgBox “Please enter a last name.”, vbExclamation
Me.txtLastName.SetFocus
Exit Sub
End If

‘ Open recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(“tblSomething”, dbOpenDynaset)
‘ Add a new record
With rst
.AddNew
!LastName = Me.txtLastName
!FirstName = Me.txtFirstName
!BirthDate = Me.txtBirthDate
.Update
End With

ExitHandler:
‘ Clean up
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
DoCmd.Close
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

– In the On Click event procedure for cmdCancel, just close the form:

Private Sub cmdCancel_Click()
DoCmd.Close
End Sub

Reply | Quote

I hope your problem can be solved by using subforms instead of linked forms.

If you want to create an unbound data entry form, I would do it as follows:
– Start by creating a bound form, the way you usually do. You can use a wizard, or create it manually, or a combination of both.
– When it looks OK, clear the Control Source of all controls bound to a field, and clear the Record Source property of the form.
– Put two command buttons on the form: cmdOK, with caption OK (or Save), and cmdCancel with caption Cancel (or Close).
– In the On Click event procedure for cmdOK, open a recordset and create a new record; this can be done in DAO or ADO. If you want to add records to several tables, handle them separately, making sure to create the necessary primary keys first.

For example using DAO (you need a reference to the Microsoft DAO 3.6 Object Library for this):

Private Sub cmdOK_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset

On Error GoTo ErrHandler

‘ Perform a check
If IsNull(Me.txtLastName) Then
MsgBox “Please enter a last name.”, vbExclamation
Me.txtLastName.SetFocus
Exit Sub
End If

‘ Open recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(“tblSomething”, dbOpenDynaset)
‘ Add a new record
With rst
.AddNew
!LastName = Me.txtLastName
!FirstName = Me.txtFirstName
!BirthDate = Me.txtBirthDate
.Update
End With

ExitHandler:
‘ Clean up
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
DoCmd.Close
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub

– In the On Click event procedure for cmdCancel, just close the form:

Private Sub cmdCancel_Click()
DoCmd.Close
End Sub

Reply | Quote

Sorry for delay in responding. Been off the system due to upgrade faults. Hans has shown how to create an unbound form.

Looking back at your original query you want to add a new consumer and organisation at the same time. You can certainly do this in code, but I now wonder what you achieve by it. You have a table tblConsumers and a table tblOrganisations (or whatever their names are). By definition a many-to-many join allows you join one consumer to many organisations AND one organisation to many consumers. Is this a good description of how your organisation works?

Whilst you may want to add a new consumer and a new organisation at the same time there must be many more occasions where you want to add a new consumer to an existing organisation, and vice-versa. I think you would be better to add records for the two tables separately with two different add forms. Ask yourself the question do all consumers have to be linked to at least one organisation? or the other way round? That will determine which bit of info needs to be added first.

I have a scenario where I record details of applicants for our properties. Although this is many-to-many, a property need not have any applicants but all applicants must be linked to at least one property. Properties are therefore added without any linking as there is no need (indeed we are unlikely to have applicants at the point we take the property on to our books). The applicant add form, however, collects data about a new applicant and has a list box of all current properties. This list box is multi select. On saving the code checks basic data and that the user has selected at least one property to link to the applicant. That’s the beauty of unbound entry forms – you can error check to you’re hearts content. The save routine then saves the new applicant details and reads the list box adding a record in the link table for each selected. The whole saving is done within a transaction to ensure that an applicant cannot be saved unless all the link entries are also successful.

If the scenario I describe is what you are looking for, I’ll post a stripped down form and code but it will not be until 8th July as I am out of the office.

Reply | Quote

Sorry for delay in responding. Been off the system due to upgrade faults. Hans has shown how to create an unbound form.

Looking back at your original query you want to add a new consumer and organisation at the same time. You can certainly do this in code, but I now wonder what you achieve by it. You have a table tblConsumers and a table tblOrganisations (or whatever their names are). By definition a many-to-many join allows you join one consumer to many organisations AND one organisation to many consumers. Is this a good description of how your organisation works?

Whilst you may want to add a new consumer and a new organisation at the same time there must be many more occasions where you want to add a new consumer to an existing organisation, and vice-versa. I think you would be better to add records for the two tables separately with two different add forms. Ask yourself the question do all consumers have to be linked to at least one organisation? or the other way round? That will determine which bit of info needs to be added first.

I have a scenario where I record details of applicants for our properties. Although this is many-to-many, a property need not have any applicants but all applicants must be linked to at least one property. Properties are therefore added without any linking as there is no need (indeed we are unlikely to have applicants at the point we take the property on to our books). The applicant add form, however, collects data about a new applicant and has a list box of all current properties. This list box is multi select. On saving the code checks basic data and that the user has selected at least one property to link to the applicant. That’s the beauty of unbound entry forms – you can error check to you’re hearts content. The save routine then saves the new applicant details and reads the list box adding a record in the link table for each selected. The whole saving is done within a transaction to ensure that an applicant cannot be saved unless all the link entries are also successful.

If the scenario I describe is what you are looking for, I’ll post a stripped down form and code but it will not be until 8th July as I am out of the office.

Reply | Quote

Can you tell me where I can learn more about using unbound data entry forms along with the code required. I think this would help me with my room data sheet problems.

Reply | Quote

Suggest you think about using an unbound data entry form. You can then add information about both the new consumer and organisation into variables on the unbound form and put some code under an Add button to save the 2 records and the linking record using a transaction to ensure that all the saves take place.

Reply | Quote