Form/Subform picking records

[MarcusAntonius]

I have two tables: licence and machine.

I've created a form/subform: licence form, machine subform.

When I look at a licence the subform will contain a list of the machines with that licence. The machine table is already populated with 30k records and the the licence table currently holds one record.

I've added a combo box to the machine subform but when I open the form/subform and select the combo box I do not get a list of records. Any ideas?

 

[Ukraine82]

Are you saying that your combo box is blank(nothing in a drop down box) or your subform is blank (no record showing at all since you added a combo box)?

Michael

 

[MarcusAntonius]

To provide a little more info: The main form is from table 'Licence' which has no records (despite what I said previously). The subform is from table 'Machine' which contains 30k records.

This is what I actually want to do: When I open the form I see the licence and then in the subform below I can add PCs. However, I don't actually want to add PCs since they are already in the table 'Machine' I want to somehow be able to 'connect' them to the licence.

 

[Ukraine82]

Try using the relationship with same primary key.

This will help you link fields together.

Michael

 

[MarcusAntonius]

They are linked with a relationship.

(Sorry, this is a little difficult to explain... You see I don't want to add records to the Machine table since they are being imported from a csv file.)

Let's try this, let's assume that both tables(Machine and Licence) are empty.

You open the form and add a new licence and in the subform you add a new machine. When you add another licence after that, and it's for the same machine, you don't want to type in the details for that machine all over again. What is the easiest way to do this? Basically I want to be able to select the machine from a list to enter it on the subform.

I hope I've made it a little clearer...

 

[Ukraine82]

I would make a combo box wizard from the table that has all your machine list.

Anytime you would type in the license number afterward use the combo box from your drop down list.

If I'm wrong than I'm sorry.

If I'm right and you're not sure what I'm talking about attach a sample of your database and I will help you out with it.


Michael

 

[MarcusAntonius]

Michael,

I found out how to do it. The two tables have a many to many relationship so there is a table called 'link' to break this up. (I foolishly forgot to mention this)

Therefore, the Licence table remains as the basis for the main form, but the subform should not be based on the Machine table but on the Link table.

If you do it on the Machine table it will add records to the Machine table when you select the record from the combo box - this is quite obvious since I am selecting a field and then trying to get Access to fill in the other fields for me (I can't believe I didn't see this!)

Anyway, sorry if I was wasting your time for such an elementary mistake.