Insert from form into two tables

[hollyneal]

I've got two databases. The main one has a field, QuoteNum. The child one has a field, QuoteNum and a field, Notes. There can be more than one Notes for each QuoteNum which is why I split it into another table.

I have a form from which the user enters the data. Is there a way to get the form to insert the QuoteNum into both tables and the Notes field into the child table?

 

[jzwp22]

What you describe is a one-to-many relationship. To represent that in a form, you would have a main form based on the main table (the one with QuoteNum) with a subform based on the second table (the one with the notes)

 

[hollyneal]

Ok I can do that. But is there a way to insert QuoteNum into both databases without having the user type it twice?

 

[jzwp22]

I assume you mean both tables not databases.

If your tables are properly structured and you have set up the relationship between the two table and then created the form/subform, Access will take care of the linking for you.

Can you provide your current table structure or zip and post a copy of your database (with any sensitive data removed, of course)?

 

[hollyneal]

Ok I zipped it and I hope I did it right. Let me know if I didn't...
View attachment Level2.zip

 

[jzwp22]

I was able to get your database, and I see several issues that I am working on correcting. I did have a question though, you have a notes field in the tracking table and then you have notes in the quotenotes table. Shouldn't the notes in the tracking table be in the quotenotes table since a quote can have many notes? I was just curious as to why you had notes in both tables.

 

[hollyneal]

The notes were designed as part of the Tracking table at first but then I realized it would be good to split it off into two tables. I created the second table but didn't delete the field off the first one...just in case. The Notes field on the Tracking table is irrelevant.

 

[jzwp22]

The notes were designed as part of the Tracking table at first but then I realized it would be good to split it off into two tables. I created the second table but didn't delete the field off the first one...just in case. The Notes field on the Tracking table is irrelevant.

I noticed that after I posted.

I have reworked the database for the following reasons. Each tracking item has several people/organizations involved: a UW(underwriter I assume), an agent and an insured. That describes a one-to-many relationship. So to make things easier, I created 1 table to hold all people/organizations. Each person/organization is one of 3 types as previously mentioned, so I captured those types in a table and related the type back to the people/organization table. Since each tracking item has multiple people, we needed a table to link those two entities (tblTrackingPeopleOrganization).

It is best to link the key field of one table to its corresponding key field in the related table. You were linking the text field rather than the numerical field. I have corrected that aspect such that all joins between tables are done with numeric fields. Relational databases are more efficient with number fields compared to text fields.

I created a form (frmQuoteTracking) that opens when you open the database. It has two subforms, one for notes and the other for people related to the quote tracking item.

The revised DB is attached.

 

[hollyneal]

Wow. Thank you so much!

 

[hollyneal]

I'm having trouble understanding one thing. On the People tab on the form there is a drop-down for People/Organization. The choices on the menu are all the name records in the database so far. There is no way to enter a new People/Organization when I'm logging a new call. Does that make sense?

 

[jzwp22]

There is no way to enter a new People/Organization when I'm logging a new call. Does that make sense?

There are a couple of ways to handle this. If you know ahead of time that the person is new, you can go to a form to enter the person's info first and then open the tracking form.

Or you can use the combo box's On Not in List event to automatically open a form to enter a new person at the time you are entering a new person to the subform in the tracking form. This would take some code. Based I what you described, it is probably your preferred method. I did not set this up since there are probably other fields you would want in the various tables and you would have to revise the forms after making those field additions anyway.

This site has an example database on how to use the On Not in List event. If you need more help with it, just post back.

 

[hollyneal]

I couldn't find any code there, but I found this someplace else and was hoping I could adapt it:

[LIST=1]<LI class=codeLI>Option Compare Database <LI class=codeLI>  <LI class=codeLI>Private Sub cboChargeCode_NotInList(NewData As String, Response As Integer) <LI class=codeLI>Dim strSQL As String <LI class=codeLI>Dim i As Integer <LI class=codeLI>Dim MSg As String <LI class=codeLI>  <LI class=codeLI>'Exit this sub if the combo box is cleared <LI class=codeLI>If NewData = "" Then Exit Sub <LI class=codeLI>  <LI class=codeLI>MSg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr <LI class=codeLI>MSg = MSg & "Do you want to add it?" <LI class=codeLI>  <LI class=codeLI>i = MsgBox(MSg, vbQuestion + vbYesNo, "Unknown Charge Code...") <LI class=codeLI>If i = vbYes Then <LI class=codeLI>    strSQL = "Insert into tblChargeCode ([ChargeCode])" & " VALUES ('" & NewData & "')" <LI class=codeLI>    CurrentDb.Execute strSQL, dbFail <LI class=codeLI>    Response = acDataErrAdded <LI class=codeLI>Else <LI class=codeLI>    Response = acDataErrContinue <LI class=codeLI>End If 
[*]End Sub
[/LIST]

I think this will insert NewData into the table where it needs to go, but I'm not sure how to capture the other stuff like the type of Person/Organization it is.

You're very helpful!

 

[jzwp22]

I think this will insert NewData into the table where it needs to go, but I'm not sure how to capture the other stuff like the type of Person/Organization it is.

The code you showed would work fine if you were only entering data into 1 field for the new record. What you need is to open a form, input values in all of the controls, save the record and then move back to the combo box.

I have added the necessary code in the attached database. There is code in the On Not in List event of the combo box in the subform. The code basically opens the unbound form called frmAddNewPersonOrg.

If you do not see a particular name in the combo box, you would type in the primary/last name in the combo box and when you hit tab, the code will execute.

There is also code in the On Load event of frmAddNewPersonOrg that copies what was typed into the combo box into the primary name control on the form. You will fill in the other controls and hit the save button. There is code behind the save button that first checks to make sure the primary name and person/org type are specified and then saves the record to the table. The form will then be hidden and will take you back to the subform.

I also put in code to