Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Updating Junction table

Posted on 2002-04-15
7
Medium Priority
?
500 Views
Last Modified: 2008-02-07
I have table A and table B with a many-to-many relationship.  I have table C as a junction table.

I have a form that browses through table A with a subform that shows a summary of the related items from table B.  If I double click any of the items in the summary subform, I get another form that allows me to edit all the items in table B.  The relationships and the junction table are working fine.

My problem is this:  I have to initially create the items for table B by filling in a new summary line on the subform.  Apparently Access is updating the junction table for me.  That's nice.

But what I really want is to have a button labeled "add" that will cut out the middle man and just pop up a blank form for creating a table B row.  I can do that of course, and the row is created in table B, BUT THE JUNCTION TABLE IS NOT UPDATED.  

How do I update the junction table in this scenario??

(sorry for the long description :-)

Thanks for your help!
0
Comment
Question by:Dennis_Todd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
7 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 6943247
Just create a form with the DataEntry option set to Yes for the form that's defined on table B. (Just select properties right after opening the form in design mode and go to the data tab)

Then place a button and select the form open for this created form.

Clear ?

Nic;o)
0
 

Author Comment

by:Dennis_Todd
ID: 6943271
This is exactly what I've done (although it makes no difference what I set the DataEntry value to).  I prefer to have DataEntry turned off (so it's clear that you HAVE to add data by clicking the button rather than filling in a blank summary line on the summary form).  I tried it with DataEntry turned on as you suggest, however, but I still have the same problem.  The junction table does NOT get updated when I enter the data.

Let me clarify a little more:

I have 1 form (bound to table A) which contains 1 subform (bound to table B).  I want to click a button to invoke a second form (third, if you count the subform) where I enter the full data for a new row in table B.  After I close the third form, I programatically do a requery which should make the new table B data appear in the subform.
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 400 total points
ID: 6943338
It will be updated when you leave the form and activate it again...

Access sometimes is rather "stubborn". The me.refresh doesn't always prooves to be sufficient.
I switch in these cases to the "odd" statement:

me.rowsource = me.rowsource

or the equivalent for a subform:

me.sfrmSubform.form.rowsource = me.sfrmSubform.form.rowsource

Looks silly but works for 100% ;-)

Nic;o)
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:Dennis_Todd
ID: 6943342
I must be doing something wrong, because I clicked my add button, filled out the form, closed it and went so far as to close down Access and come back.  The row of new data gets inserted into table B ok, but the junction table does NOT get a corresponding row added to it.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 6943409
Then I would suspect there's a filter (criteria) on a field blocking the newly created row, or your looking/using the wrong row to select from (happened to me too). Are you sure the B key is originated from the B-table and not from the DISTINCT C-tableentries...

Nic;o)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7103452
for Dennis_Todd

No comment has been added for the last two months.
So it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
 - Answered by: nico5038  
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Expert Comment

by:Netminder
ID: 7123765
Per recommendation, force-accepted.

Netminder
CS Moderator
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question