Table relation problem

[brt]

Hi,

I have an access project with two tables. “Archives” contains all the folders of an enterprise with for each folder its title and the ID of the division to which it belongs. The other table is called “Divisions” and contains the division ID’s and for each ID the name of the corresponding division.

Schematically, these tables look as follows:

Archives
- Title
- Division ID

Divisions
- ID
- Name

Now I’m having a datasheet form in which I show all folders. Basically, this corresponds to the “Archives” table. Only, instead of showing the Division ID, I linked both tables so the Division name is shown instead.

The data in the form can be modified and I want that the user can modify the division of a folder by selecting the desired division name out of a listbox. I managed to add a listbox to the datasheet that shows all the division names. However, when I click another division in the listbox, instead of changing the division ID of the folder, the name of the division in the “Divisions” table is modified.

How should I modify the listbox so that it modifies the Division ID in the “Archives” table instead of the name in the “Divisions” table? Somehow I should just keep working on ID level but mask it for the user

Thanks in advance

 

[brt]

Thanks for your reply.


I added Archives.DivisionID and Divisions.ID to the RecordSource query and linked them (if I only add Archives.DivisionID, I cannot select Divisions.ID for the combobox’ RowSource). In the combobox, I put two columns, Divisions.ID and Divisions.Name, the first being hidden.


Visually, everything seems to work fine, but I’m unable to modify the value of the division combobox, while I’m able to modify the value of the folder titles. I think this is due to the fact that the combobox’ RowSource comes from a different table (Divisions) than the other form fields (Archives).


Besides that, you say that I should lock the property of the Name control. With name control, you mean the second column of the combobox? How can I lock that?