?
Solved

Access 2007 - Issue with a combo box with dynamic row source

Posted on 2011-02-21
5
Medium Priority
?
411 Views
Last Modified: 2012-05-11
I've got a form that has a combo box where the user can select a project and a sub form (continuous form) showing expenditure data for the project selected in the combo box.

On sub form is tied to the parent form on ProjectID and in the 'afterUpdate' event of the project combo box, I set the selected project of the parent form to the ProjectID in the combo box and then I requery the sub form.  So the result is that every time you change the combo box it changes the the expenditures shown in the sub form.

This works perfectly...

Now, in the sub form for each record I have 3 combo boxes and they are such that the first combo box's options are dependent upon the project selected, the second combo box's options are dependent upon the first combo box's selection, and the third combo box's options depend on the second combo box's selection.

So far I'm still working on getting the first combo box to populate correctly based on the change in project combo box in the parent form.  I've got the row source of the first combo box set to this...

SELECT tbContractProject.ContractProjectID, tbContract.Name FROM tbContractProject INNER JOIN tbContract ON tbContractProject.ContractID=tbContract.ContractID WHERE (((tbContractProject.ProjectID)=Forms!frmExpenditure!ProjectID));

And every time the user changes the value in the project combo box in the parent form, I run some vba that does a requery on the first combo box which does effectively change the options in the first combo box.  However when I then go to change one of the values in the first combo box I'm getting this message:

"The current field must match the join key '?' in the table that serves as the 'one' side of one-to-many relationship.  Enter a record in the 'one' side table with the desired key value, and then make the entry with the desired join key in the 'many-only' table."

I don't understand what this means.

Any help trying to figure out this issue would be greatly appreciated.  Thanks.
0
Comment
Question by:JosephEricDavis
[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
  • 2
  • 2
5 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 1000 total points
ID: 34944805
Do you have parent and child link fields set for your subform as well?  Is the selection from the subform combo somehow in conflict with the parent child links?

 
0
 
LVL 7

Author Comment

by:JosephEricDavis
ID: 34945116
The parent form is linked to the child form on ProjectID

The combo box is bound to a field in the child form's record source called ContractProjectID.

How could this be a conflict?
0
 
LVL 3

Expert Comment

by:DockieBoy
ID: 34949288
Are you able to upload the db
0
 
LVL 3

Assisted Solution

by:DockieBoy
DockieBoy earned 1000 total points
ID: 34949313
Wild guess... sounds like a referential integrity problem.

table a requires a valid record in/from table b
0
 
LVL 7

Author Comment

by:JosephEricDavis
ID: 34951642
Doesn't seem like that is possible.  The only values I supply as options to fill are those that have values in the table that demands the referential integrity.

But no matter at this point.  I'm taking the project in a different direction to avoid this issue.

Thanks for all the help though.  I'm going to go ahead and close the question and split points between participants.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

765 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