Referencing many tables in one form

[mattcdse]

Hi all,

Firstly I am trying to retrieve data from three tables in a format like this

Table Data Fields Descirption

Table 1 A,B PC Details
Table 2 B,C Software/PC
Table 3 C,1,2 Software Details

Where the B's are linked as are the C's and these both form primary keys in their respective tables.

My form has on sub form in it. The main for refers to Table 1, displaying Fields A and B. My subform is linked through child/master fields by B and is used to edit which software titles are installed on each PC. However, as C, the primary key for the software, is a code, it is not obvious what it refers to exactly. Therefore I use a combo box who's row source is query containing the software title (1) and the column widths precceding the software title are set to 0 so that software title (1) is visible in the field in the subform. Hence, although you change the title name on the form, the actual change is made in Table 2 altering the Software Code (C). This is fine, except that there is more than one version (2) of some of the software titles. The versions are shown in the drop down list for the software title combo as an extra colum, so you know you are choosing the correct one. But i also want a seperate field which actually shows the version chosen. I cannot link a normal text box to the same query as it doesn't have the relevant properties, but I can do it with a combo as I am effectively doing the same as for the software title.

This is all very well, I could just lock the combo box and be done with it, but it doesn't look very professional and is a bit confusing to the user. Is there away that I can get around this problem?

Cheers,

Matt

 

[mattcdse]

Sort of tried that. So what did I do wrong?

Hi Pat,

I've kinda tried that by making a query with values from table b and c in it, but when I open the form the values in table C don't appear on the form and if you use a combo box for the table c fields, the drop down list has each value listed about 60 times instead of one each which is a bit weird. Any suggestions as why this might be happening and what I can do to fix it?

Cheers,

Matt

 

[mattcdse]

Almost There

Hi Pat,

Thanks for all your help so far. This almost works completely. My is now based on a query which contains fields B and C from Table 2 and 1 and 2 from Table 3. I can now link a text box to field 2 and display the version number. My control soure for my combo box which changes the Software code in Table 2 is field C (the software code), the row source is Table 3 which C is the primary key of and I have set the bound column to 1 as suggeted.

However, I'm still getting and odd reult. Table 3 is sorted in in ascending order by C (by default I expect) and my queries are completely unsorted, but I'm getting a strange for of sorting in the form. There are a few other fields, one of which is the department that the software belongs to and the sorting in form seems to be sorted using this field but in an odd way. The order their sorted by is IS, BS, GE, UT, which is clearly not alphabetical. Any ides y this is?

Cheers,

Matt

 

[workmad3]

Possibly the order the records were entered in? If it has no sort on it, then the DB can pretty much display it how it likes. It could be that the DB engine sorted it like that internally so that it could then do some kind of space optimisation. The only answer I could give you is that they are like that because the DB wanted to put them like that

 

[mattcdse]

Not just a pretty face!

Good skills!

It was the order I entered them in. I would never have guessed. Time to insert a sort somewhere.

Hooray....Job done!

Cheers,

Matt

 

[workmad3]

heh most of the time, an unsorted list in a db will be in the order of entry. It can do other ways though, which is why I made sure I didnt commit to that As long as when you ask for a specific sort the DB does that for you, the engine is free to do whatever the hell it likes with the order of your data.