Hi,

Following advice gleamed from the minds of the experts on this site who've so far been a great help, I've changed the structure of my db to make it more straightforward. However, something that I thought should work isn't, and I was wondering if you could help put me right.

Part of my db has 2 tables, linked by a junction table:

TblItems
Item ID (PK)
ItemNumber
PartNumber

TblAssemblies
AssemblyID
AssemblyNumber
AssemblyDescription

jnxAssemblyItem
AssemblyItemID (PK) (which is to be used later in the db)
AssemblyID (FK)
ItemID (FK)

In order to assign an item to an assembly, I've made a form that specifies the Assembly Number in question. Beneath this I've got a subform that runs from 'qryjnxAssemblyItem'; and is a 'continuous forms' that allows many items to be assigned to that assembly.
Following Crystal's advice in another post, I'd tried to use the ID fields as the references, making it much simpler. So in the query, I have the

AssemblyID ------> qryjnxAssemblyItem (and specified to link to that ID shown in the main form)
ItemID ---------> qryjnxAssemblyItem
ItemNumber ----------> tblItems
PartNumber ------> tblItems

With my (somewhat limited knowledge), I'd thought that since the ID fields were linked via the junction table, the ItemNumber would automatically be picked up, and so when I specify this to be displayed as a combo-box, it should show the item numbers required. However, it doesn't seem to work, and the combo-box displays blank. Am I missing something, and/or is my understanding a bit off?

Finally, Crystal posted this as a response on another post, but I was a bit confused about the RowSource and how this shows the names from another table whilst storing only the ID's. Apologies for the apparrent stupidity if it comes across this way!!

Name --> EmployeeID
ControlSource --> EmployeeID
RowSource --> SELECT EmployeeID, LastName & " " & Firstname, HireDate AS EmpName FROM Employees ORDER BY LastName, Firstname
BoundColumn --> 1
ColumnCount --> 3
columnWidths --> 0;2;1 (etc for however many columns you have -- the ID column will be hidden)
ListWidth --> 3 (should add up to the sum of the column widths)

EmployeeID will be stored in the form RecordSource while showing you names from another table... a MUCH better and more reliable method.


Thanks very much in anticipation,

Tim

The subform should be bound to the table jnxAssemblyItem. You do not need to add the item table to the subform's recordsource. Make sure the subform's Master/Child fields are set to the correct PK/FK of the mainform and subform respectively. In the subform, use a combobox for the Item selections. This combobox would be where you would get the item description information to be displayed when an Item is added to the assembly part list.

The Rowsource property, along with the bound column and column width properties determines what gets stored to the bound field and what gets displayed on the form. The rowsource can be one of three things, a field list from a table, a "hard coded" list of values, or records from a table. Usually, the latter is what is used.

Hi Jerry,

Thanks for getting back to me.
I've had a look in a couple of books, etc. about setting the master and child fields, but with not much luck - are they known as something else, or would you be able to offer some assistance in how I assign these? Sorry for appearing stupid on this!
With the combo-box, I guess that you mean displaying both the item number and part description in one drop-down, which would make sense?

Thnaks again,

Tim

Hi,

I'm still not sure about the master and child fields, but think I may have got something together. The Combo-box drops down with a list of the Item Numbers (as it should), but when I try to select one of them, it won't let me, stating that
"Control can't be edited - it's bound to Autonumber field 'AssemblyID'"

The same thing happens when I try to select a different Assembly from the main form.

Any suggestions as to where I may have gone wrong?

As always, your help is very much appreciated,

Tim

Edited by: TimJ on Thu Feb 9 9:46:13 EST 2006.

They are called Link Master Field and Link Child Field properties. You find them by selecting the subform CONTROL (don't open the subform in design view or select the subform, just select the subform control) and opening its property sheet. You'll know you have the subform control selected if it has the sizing control boxes on the object frame.

The combobox must be bound to the FK (foreign key) field in the junction table that corresponds to the PK of the rowsource.

For example, you need to make sure the subform's recordsource is set to the junction table. Then bind the item combobox to the ItemID field of the junction table.

Thanks Jerry,
That does make sense. However, when the user now wants to select an assembly (or Item, the same holds), if I'm right they can only see the IDs, since the actual Item Number, (or Assembly Numbers) aren't available in the junction table. Is there a relatively straightforward way of getting the user to see the actual numbers (not the IDs) to allow them to make the correct choices? This would need to reference back to the 'raw' tables, but with only having the record source available in the properties (which is already taken) to make the reference, I'm not sure how you can do this.

Sorry to keep asking for help, but it's a crucial bit of my db and I feel as though I'm going nowhere fast!

Thanks in advance,

Tim

No, you are not right. They see whatever you display (via the column width property) in the combobox. Just because the cbo is bound to the ID, does not mean it is what you must display. Take a look at any subforms that are based on junction tables in the NorthWind example db.

Cheers Jerry - the advice was much appreciated.
I've had a look at the sample database, and have seen a bit more where to go with it.
I guess I should have looked there first...

All the best,

Tim

No problem! You're welcome.

Glad you're getting it worked out. Please don't hesitate to ask.