Combo box to contain list of values from another table ms access

[mond007]

Hi (sorry for a basic question - newbie)

I have an MS Access database with a Form (Starter Leaver). The underlying table has a dozen fields of which the first is the PT-Dept.
The User must fill in PT-Dept that the Starter or Leaver will be joining/Leaving.

So I have two Tables Tbl_Starter_Leaver and Tbl_PT_Dept.

I need the form field PT-Dept to be a combo box which contains the list of Depts from the Tbl_Pt_Dept (1st column/field).

I can create a combobox in general and set it to be a list of values from the PT-Dept table but this just is a field on its own not the one on the Tbl_Starter_Leaver
I need to know how to set the list of values to be the field that is based on the Tbl_Start_Leaver.PT-Dept ?.

Thanks in advance

[pbaldy]

Well, normally I'd expect the combo's row source to be either Tbl_Pt_Dept or SQL that selected certain fields from Tbl_Pt_Dept. I'd expect the combo's control source to be a field in Tbl_Starter_Leaver. If you use the combo wizard, and the form is bound to Tbl_Starter_Leaver, it should walk you through it.

[mond007]

I think I see...

Since, then I have created a new test DB with just Tbl_Pt_Dept and Tbl_Starter_Leaver and created a relationship first between Tbl_Starter_Leaver.Id --> Tbl_PtDept.PtDept

If I create the FORM (Frm_Starter_Leaver) after this table relationship is created and is already in place then the FORM designs great.

I can not however on an existing DB Design change the datatype of Tbl_Start_Leaver.PtDept without deleting relationships, start all over again! which is a lot of work and involves starting all over again.

Thanks again.

[pbaldy]

I would expect the relationship to be on the PT-Dept field in Tbl_Starter_Leaver. Can you attach the db here, or the relationship diagram?

[mond007]

Indeed. I have managed to do this ok now.

Basically, I had to :

a) Delete the field from the Form Frm_Starter_Leaver
b) Break the link between the two tables
c) Make the Tbl_Start_Leaver.PtDept field "lookup" values from the Tbl_Pt_Dept table.
d) In design view add the field from the "Add Existing Field" on the ribbon.
The drop down work great.

Thank you very much for the pointer.

[pbaldy]

I'm glad you got it working. Personally, I would not have used the lookup field:

http://access.mvps.org/access/lookupfields.htm

[mond007]

Understood. Point 4 worries me the most but I will have a go at creating a report/queries to see if it messes up a lot of things.

The problem is I was relieved to get the thing working and now feel I have to go back to the drawing board.

Please could you help with a work-around. The only way I thought of was to create a combo-box and somehow set the Tbl_Starter_Leaver.PT-Dept to be the value from the dummy combo-box ???

Thanks in advance.

[pbaldy]

If it's working I'd probably leave it alone. The normal setup would be like I described in post 2.

[mond007]

HI

Ok, since I already had this working the incorrect way i.e. Tbl_Start_Leaver.PtDept as a type "Number" using the "Lookup" in table design view, I had another crack at trying to get this working the correct way using post #2.

(comment : As it happens, in the DB relationship, I removed the join between Tbl_Start_Leaver.PtDept and Tbl_PtDept.PtDept but it seems to have retained the Row source SQL. i.e. SELECT [Tbl_Pt_Dept].[ID], [Tbl_Pt_Dept].[PtSubDept], [Tbl_Pt_Dept].[PtDept], [Tbl_Pt_Dept].[SeniorMgrDept] FROM Tbl_Pt_Dept ORDER BY [PtSubDept];

I then changed the data type of the Tbl_Start_Leaver.PtDept to "TEXT" and now this seems to be working fine.

Basically, you suggest the following :
>combo's row source to be either Tbl_Pt_Dept or SQL that selected certain fields from Tbl_Pt_Dept.
I think I addressed this in the above comment : consequently looking at the Row Source is where the SQL/query lies (in the Query Builder).

>I'd expect the combo's control source to be a field in Tbl_Starter_Leaver.
As one would expect.

>If you use the combo wizard, and the form is bound to Tbl_Starter_Leaver, it should walk you through it.

So this is the part I couldn't get to ?
Please could you explain where to invoke the "Combo Wizard" would this be from the Query Builder as mentioned above or from the top menu strip as I cold not work this bit out.

Or is it the case that I create a Combobox and set the Control Source to PTSubDept and Row source to be a query using query builder ?.

Thanks in advance

[pbaldy]

If I click on the combo box icon in the Controls area of the ribbon, then click on the form where I want to put it, I get a wizard. Getting the wizard would depend on the "Use Control Wizards" button on the ribbon being clicked (it should stay selected, and I think is on by default). It can be done manually, but the wizard will walk you through it step-by-step.

[mond007]

Thank you.

I did trying setting this up manually using all the different properties and managed to get it working but you are correct its hard work and getting it right is fidgety indeed.
(good for the knowledge though

I should be able to get this up and running now.

Regards

[pbaldy]

No problem. Once you've done it a couple of times it's pretty easy, but the wizard is definitely easier and a good way to see what the resulting settings look like.