Adding New Record To Form Using Junction Table

[Kevsim]

I have a table called Fix and another called Documentation.
These 2 tables are connected to each other through a junction table which has the keys FixID and DocumentationID.

There is a form called Fix, the form works well with all the Fix controls, add new record, edit record etc.
I have added a combo box called Documentation (to update the Documentation table) along with all the Fix controls.

When adding a totally new record on the Fix form all works OK including using the Documentation control, all three tables update, Fix, Junction and Documentation.

Editing an existing record on the Fix form where Documentation has an existing record everything can be changed without a problem and all tables update as required.

If I go to an existing record on the Fix form where Documentation does not have an existing record, I can not add a Documentation record.
Is there any VB code that will allow this to happen.

I hope you can understand this explanation, if not please request further detail.

I would appreciate your advise on how to add a Documentation record to an existing record on the Fix form.
kevsim

 

[randy700]

Have you tried an append query?

Dim strSQL As String
strSQL = "INSERT INTO tblDocumentation(Key) VALUES("
strSQL = strSQL & Me.Key & ")
DoCmd.RunSQL strSQL

Randy

 

[Kevsim]

randy700'
Thanks for the info.
Where the code states "Key", what values do I put in there?

Also to explain a little further,
Table T_Fix has FixID and some other foreign keys, Table T_Link has FixID and DocumentationID only, Table T_Documentation has DocumentationID only.
I would appreciate some more information.
kevsim

 

[Kevsim]

randy700',
One other thing.
I am using a combo box to add the Documentation.
kevsim

 

[randy700]

Replace "Key" in my code with the name of the key field in your table. Me.Key signifies the control on the form
that is bound to the key field in your query/table.

You can continue to add more fields as necessary, making sure you use the proper delimitors.

Randy

 

[Kevsim]

randy700,
Thanks for the info provided.
I have changed the code a little as I want to put a key value in each key field in the Junction table.
When I run the code I receive an error "Run Time Error 3450, Syntax error in query, incomplete query clause"
My code is as follows-

strSQL = "INSERT INTO tblT_Junction.(FixID), tblT_Junction.(DocsID)VALUES("
strSQL = strSQL & Me.[Text188] & ")" & Me.[Combo350] & ");"
DoCmd.RunSQL strSQL

I can manually update the key fields OK, so must be missing something in SQL.

I would appreciate a little more help.
kevsim

 

[randy700]

Add a comma...

strSQL = "INSERT INTO tblT_Junction.(FixID), tblT_Junction.(DocsID)VALUES("
strSQL = strSQL & Me.[Text188] & ")[b][COLOR=red], [/color][/b]" & Me.[Combo350] & ")"

This assumes both fields are numeric. For text, you need to add single quotes...

strSQL = "INSERT INTO tblT_Junction.(FixID), tblT_Junction.(DocsID)VALUES([b][COLOR=red]'[/color][/b]"
strSQL = strSQL & Me.[Text188] & "[b][COLOR=red]'[/color][/b]), [b][COLOR=red]'[/color][/b]" & Me.[Combo350] & "[b][COLOR=red]'[/color][/b])"

Randy

 

[Kevsim]

randy700,
Once more thank you for the help.
I added the comma to the code but still receiving the same error. When holding the cursor over strSQL the second value was separated by a comma but did not have a leading bracket. I added a bracket after the comma and now both values are enclosed, I thought this may have been the problem, but still receiving the error "Run Time Error 3450, Syntax error in query, incomplete query clause"
This is the revised SQL still receiving the same error.
strSQL = "INSERT INTO T_Junction.(FixID),T_Junction.(DocsID)VALUES("
strSQL = strSQL & Me.[Text188] & "),( " & Me.[Combo350] & " );"
kevsim

 

[Kevsim]

randy700,
Thank you for all your help, success at last.
I have corrected the SQL statement, I had the table mentioned twice in INSERT INTO and I had to remove the middle brackets between text and combo boxes.
Find revised code -
strSQL = "INSERT INTO T_Junction(FixID,DocsID)VALUES ("
strSQL = strSQL & Me.[Text188] & "," & Me.[Combo350] & " );"
DoCmd.RunSQL strSQL
All working OK.
kevsim