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