Posts Topics Forums Images
Search videos from message boards Videos Search messages from microblogs Microblogs Search messages from imdb.com Imdb Search messages from yuku.com Yuku Search messages from lefora.com (free forums) Lefora
My account: Login | Sign Up
Loading... 

Thread: Adding New Record To Form Using Junction Table
Started 1 month, 3 weeks ago by 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....
Site: Tek-Tips Forums  Tek-Tips Forums - site profile
Forum: Microsoft: Access Forms  Microsoft: Access Forms - forum profile
Total authors: 2 authors
Total thread posts: 9 posts
Thread activity: no new posts during last week
Domain info for: tek-tips.com

Other posts in this thread:

randy700 replied 1 month, 3 weeks ago
Have you tried an append query? CODE Dim strSQL As String strSQL = "INSERT INTO tblDocumentation(Key) VALUES(" strSQL = strSQL & Me.Key & ") DoCmd.RunSQL strSQL Randy
Size: 376 bytes
Customize:  Customize "<b>Reply 1</b>: Adding New Record To Form Using Junction Table :: Microsoft: Access Forms :: Tek-Tips Forums"

Kevsim replied 1 month, 3 weeks ago
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
Size: 437 bytes
Customize:  Customize "<b>Reply 2</b>: Adding New Record To Form Using Junction Table :: Microsoft: Access Forms :: Tek-Tips Forums"

Kevsim replied 1 month, 3 weeks ago
randy700', One other thing. I am using a combo box to add the Documentation. kevsim
Size: 156 bytes
Customize:  Customize "<b>Reply 3</b>: Adding New Record To Form Using Junction Table :: Microsoft: Access Forms :: Tek-Tips Forums"

randy700 replied 1 month, 3 weeks ago
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
Size: 448 bytes
Customize:  Customize "<b>Reply 4</b>: Adding New Record To Form Using Junction Table :: Microsoft: Access Forms :: Tek-Tips Forums"

Kevsim replied 1 month, 2 weeks ago
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.[...
Size: 706 bytes
Customize:  Customize "<b>Reply 5</b>: Adding New Record To Form Using Junction Table :: Microsoft: Access Forms :: Tek-Tips Forums"

randy700 replied 1 month, 2 weeks ago
Add a comma... CODE strSQL = "INSERT INTO tblT_Junction.(FixID), tblT_Junction.(DocsID)VALUES(" strSQL = strSQL & Me.[Text188] & ") , " & Me.[Combo350] & ")" This assumes both fields are numeric. For text, you need to add single quotes... CODE strSQL = "INSERT INTO tblT_Junction.(FixID), tblT_Junction.(DocsID)VALUES( ' " strSQL = strSQL & Me.[Text188] & " ' ), ' " & ...
Size: 1,081 bytes
Customize:  Customize "<b>Reply 6</b>: Adding New Record To Form Using Junction Table :: Microsoft: Access Forms :: Tek-Tips Forums"

Kevsim replied 1 month, 2 weeks ago
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 ...
Size: 750 bytes
Customize:  Customize "<b>Reply 7</b>: Adding New Record To Form Using Junction Table :: Microsoft: Access Forms :: Tek-Tips Forums"

Kevsim replied 1 month, 2 weeks ago
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
Size: 538 bytes
Customize:  Customize "<b>Reply 8</b>: Adding New Record To Form Using Junction Table :: Microsoft: Access Forms :: Tek-Tips Forums"

 

Top contributing authors

Name
Posts
Kevsim
6
user's latest post:
Adding New Record To Form Using...
Published (2009-11-09 23:55:00)
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&nbsp;&nbsp;- strSQL = "INSERT INTO T_Junction(FixID,DocsID)VALUES (" strSQL = strSQL &amp; Me.[Text188] &amp; "," &amp; Me.[Combo350] &amp; " );" DoCmd.RunSQL strSQL...
randy700
3
user's latest post:
Adding New Record To Form Using...
Published (2009-11-09 12:15:00)
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;Add a comma... CODE strSQL = "INSERT INTO tblT_Junction.(FixID), tblT_Junction.(DocsID)VALUES(" strSQL = strSQL &amp; Me.[Text188] &amp; ") , " &amp; Me.[Combo350] &amp; ")" &nbsp;&nbsp;&nbsp;This assumes both fields are numeric. For text, you need to add single quotes... CODE strSQL = "INSERT INTO tblT_Junction.(FixID),...

Related threads on "Tek-Tips Forums":

Form overwriting record in table instead of adding record  Tek-Tips Forums - site profile Microsoft: Access Forms - forum profile Form overwriting record in table instead of adding record
Form overwriting record in table instead of adding record
Problem adding record to empty table  Tek-Tips Forums - site profile Microsoft: Access Modules (VBA Coding) - forum profile Problem adding record to empty table
Problem adding record to empty table
Linking a table with a lookup field to a junction table  Tek-Tips Forums - site profile Microsoft: Access Tables and Relationships - forum profile Linking a table with a lookup field to a junction table
Linking a table with a lookup field to a junction table
Tricky query problem updating junction table  Tek-Tips Forums - site profile Microsoft: Access Queries and JET SQL - forum profile Tricky query problem updating junction table
Tricky query problem updating junction table
Create a junction table  Tek-Tips Forums - site profile Microsoft: Access Tables and Relationships - forum profile Create a junction table
Create a junction table
Adding a webquery table  Tek-Tips Forums - site profile VBA Visual Basic for Applications (Microsoft) - forum profile Adding a webquery table
Adding a webquery table
Query very slow when adding one particular table to a join  Tek-Tips Forums - site profile Microsoft SQL Server: Programming - forum profile Query very slow when adding one particular table to a join
Query very slow when adding one particular table to a join
Adding data to table with code  Tek-Tips Forums - site profile Microsoft: Access Modules (VBA Coding) - forum profile Adding data to table with code
Adding data to table with code
Adding a new table with in a Data Model  Tek-Tips Forums - site profile PLATINUM technology solutions - forum profile Adding a new table with in a Data Model
Adding a new table with in a Data Model
Adding to a table, replace value  Tek-Tips Forums - site profile Microsoft SQL Server: Programming - forum profile Adding to a table, replace value
Adding to a table, replace value

Related threads on other sites:

Adding record in table Problem  Lockergnome Forums - site profile General Discussions - forum profile Adding record in table Problem
Adding record in table Problem
[RESOLVED] [2005] Adding Record to Table  VBForums - Visual Basic and VB .NET Discussions and More! - site profile Visual Basic .NET - forum profile [RESOLVED] [2005] Adding Record to Table
[RESOLVED] [2005] Adding Record to Table
Adding record in table not correctly opened  Access World Forums - site profile Forms - forum profile Adding record in table not correctly opened
Adding record in table not correctly opened
Junction table record from a form in...  Discussions in microsoft.public.access.forms - site profile microsoft.public.access.forms - forum profile Junction table record from a form in microsoft.public.access.forms
Junction table record from a form in microsoft.public.access.forms
Problem adding new record to table via form - error 2105...  Discussions in microsoft.public.access.forms - site profile microsoft.public.access.forms - forum profile Problem adding new record to table via form - error 2105 "You can't go to the specified record" in microsoft.public.access.forms
Problem adding new record to table via form - error 2105 "You can't go to the specified record" in microsoft.public.access.forms
Problem adding new record to table via form - error 2105...  Microsoft Office (Excel, Word) forum - OfficeFrustration - site profile Using Forms - forum profile Problem adding new record to table via form - error 2105 "You can'tgo to the specified record"
Problem adding new record to table via form - error 2105 "You can'tgo to the specified record"
Junction table record additions  Access World Forums - site profile Tables - forum profile Junction table record additions
Junction table record additions
Error while adding record to phpbb_users table  http://www.phpbbhacks.com - site profile Other phpBB 2 Questions and Discussion - forum profile Error while adding record to phpbb_users table
Error while adding record to phpbb_users table
Adding a Record to table in Access Db  www.daniweb.com - IT Tech Talk - site profile MS Access and FileMaker Pro - forum profile Adding a Record to table in Access Db
Adding a Record to table in Access Db
Table validation on junction table or form-level? in...  Discussions in microsoft.public.access - site profile microsoft.public.access - forum profile Table validation on junction table or form-level? in microsoft.public.access
Table validation on junction table or form-level? in microsoft.public.access
Thread profile page for "Adding New Record To Form Using Junction Table" on http://www.tek-tips.com. This report page is a snippet summary view from a single thread "Adding New Record To Form Using Junction Table", located on the Message Board at http://www.tek-tips.com. This thread profile page shows the thread statistics for: Total Authors, Total Thread Posts, and Thread Activity