Populating a form via combo box (2000)

Topic

New Reply

Hi Guys,
I’m having this little problem with autopopulating the access form. I have two tables, one
called tblZoneList and tblServiceZone, I’m trying to have it so that when the data from the combo box is selected (eg “Australia, Sydney” or “USA, Miami” etc etc), it will
populated the State, Terminal and Zone fields.
So in a nutshell, the data would be retrieved from tblZoneList and stored to the fields with the exact name in tblServiceZone.
I’ve attached the db, its a very simple one, as you can see, I’ve attempted it but with little success. (See frmServiceZone).
If someone can help me out on this it would be greatly appreciated.

Reply | Quote

Replies

sorry, here is the attachment

Reply | Quote

This is not the way to do it. You are trying to store duplicate data. Instead, assign a unique ID (for instance an AutoNumber) to each location (record) in tblZoneList, and store this ID in tblServiceZone. You can use a query to look up the other fields.

Reply | Quote

This is not the way to do it. You are trying to store duplicate data. Instead, assign a unique ID (for instance an AutoNumber) to each location (record) in tblZoneList, and store this ID in tblServiceZone. You can use a query to look up the other fields.

Reply | Quote

Hi Hans,
I’m a little bit confused on this. Its the structure of the query that i need help on.

Reply | Quote

I am confused too. I don’t understand the purpose of your tables, nor the relation between them,

Reply | Quote

Oh ok, basically what i want to do is quite simple.
I have a table for storing transactions (tblServiceZone). A user has to select a location (eg Australia, Sydney) from a combo box, and that would automatically fill in the fields. These fields are Terminal and Zone in tblServiceZone. Such data would be retrieved from tblZoneList, but stored in the fields in tblServiceZone.
In the Access help, my problem comes under “About AutoLookup queries that enter data automatically”,,,i’ve tried using that method, but i’m having trouble in applying it.

Reply | Quote

But why do you want to store all those fields in tblServiceZone? Once you know the location, you know the rest. And where does the ‘State’ field come in?

Reply | Quote

Because tblServiceZone is where the data would be retrieved in future. I have to do an autopopulate, so the user doesnt have to type in all the details. State is an optional field, its hardly used but I have to put it there. (ie state is not different from terminal, zone and Town.

Reply | Quote

See attached modified database. I removed the Town, Terminal and Zone fields from tblServiceZone, and replaced them with an ID field that is linked to the new ID (AutoNumber) field in tblZoneList. Instead of populating these fields, they are retrieved in the query; the query acts as record source of the form. Here is the SQL for the query:

SELECT tblServiceZone.TownID, tblServiceZone.ID, tblServiceZone.State, tblZoneList.Town, tblZoneList.Terminal, tblZoneList.Zone, tblServiceZone.Status
FROM tblZoneList RIGHT JOIN tblServiceZone ON tblZoneList.ID = tblServiceZone.ID;

As you can see, ID comes from tblServiceZone, but Town, Terminal and Zone come from tblZoneList. The combo box for town now has ID as Control Source, and tblZoneList is its Row Source; the Column Count has been set to 2, and the first column (ID) is hidden by setting its width to 0.

Reply | Quote

Thankyou so much Hans, this is what i was after. Your help once again is greatly greatly appreciated.

Reply | Quote

Thankyou so much Hans, this is what i was after. Your help once again is greatly greatly appreciated.

Reply | Quote

See attached modified database. I removed the Town, Terminal and Zone fields from tblServiceZone, and replaced them with an ID field that is linked to the new ID (AutoNumber) field in tblZoneList. Instead of populating these fields, they are retrieved in the query; the query acts as record source of the form. Here is the SQL for the query:

SELECT tblServiceZone.TownID, tblServiceZone.ID, tblServiceZone.State, tblZoneList.Town, tblZoneList.Terminal, tblZoneList.Zone, tblServiceZone.Status
FROM tblZoneList RIGHT JOIN tblServiceZone ON tblZoneList.ID = tblServiceZone.ID;

As you can see, ID comes from tblServiceZone, but Town, Terminal and Zone come from tblZoneList. The combo box for town now has ID as Control Source, and tblZoneList is its Row Source; the Column Count has been set to 2, and the first column (ID) is hidden by setting its width to 0.

Reply | Quote

Because tblServiceZone is where the data would be retrieved in future. I have to do an autopopulate, so the user doesnt have to type in all the details. State is an optional field, its hardly used but I have to put it there. (ie state is not different from terminal, zone and Town.

Reply | Quote

But why do you want to store all those fields in tblServiceZone? Once you know the location, you know the rest. And where does the ‘State’ field come in?

Reply | Quote

Oh ok, basically what i want to do is quite simple.
I have a table for storing transactions (tblServiceZone). A user has to select a location (eg Australia, Sydney) from a combo box, and that would automatically fill in the fields. These fields are Terminal and Zone in tblServiceZone. Such data would be retrieved from tblZoneList, but stored in the fields in tblServiceZone.
In the Access help, my problem comes under “About AutoLookup queries that enter data automatically”,,,i’ve tried using that method, but i’m having trouble in applying it.

Reply | Quote

I am confused too. I don’t understand the purpose of your tables, nor the relation between them,

Reply | Quote

Hi Hans,
I’m a little bit confused on this. Its the structure of the query that i need help on.

Reply | Quote