Linking forms

[Alisdair]

Populating Fields (Please Please help)

Hi,

I have been trying this for a few days and I need to get it done by 3pm today.

It is simple I know, but it aint working for me.

Here goes (please help)

I have 2 table in a DB

1 table - several fields including Provider Name and Provider Code No information in table yet)
2 table - Just Provider Name and Provider Codes (information already their)

I want to show a form using table 1, the user starts to enter the name of a Provider or using a drop down box in the provider name field (this will come from table 2) once he has done that and tabs, the provider code box will populate automatically with the correct code from table 2. He will then continue to fill all other fields and then move on, saving the details entered.

I have used the Primary Key in table 2 as the Provider Code and also the same for table 1.

I dont know coding etc, but I have tried and this is driving me nuts.

All help will be appreciated.

Thanks

 

[neileg]

You don't need two tables since table 2 is just a partial duplicate of table 1. Populate table 1 with the data you already have, and ditch 2.

 

[Alisdair]

Hey Neileg, you must be joking right!!!

I am going to try this and if it works, you can name your tipple. Of course you will have to fly out to Dubai to have it, but I will certainly buy you a drink.

Thanks

 

[ColinEssex]

You could select the provider from a ComboBox and having created the Combo using the wizard, select option 3 where is says something like "Find a record on my form based on the value I select"

The form must be bound to a query for this option to show. Incidentally, all forms should be bound to queries - never the table, a query is much more flexible.

Col

 

[ansentry]

Provider Name and Provider Code

These should not appear in both tables.

Have a look at the sample I have posted, is this what you want to do?

 

[Alisdair]

Neileg

Ok buddy, the one table approach did not work. This is because the Provider name / provider code can be used many times by the user, therefore duplications are made.

I just dont think I explained it properly as Col had implied before. I shall keep on tyring. My deadline has passed and I took it on the chin. I will just plod on till it works.

 

[ColinEssex]

so you need to apply the "groupBy" option in the query behind the ComboBox - then you won't get the duplicates, you'll just get one example of each

Col

 

[neileg]

Rather than a Group By I would use a Select Distict query.