Trying to do a 'pick' from seperate table

[GarryBrogden]

Access 2003.

Apologies for the somewhat nebulous title of the question, but I'm a COBOL programmer trying to get to grips with Access & VB.

I have two tables: one is an application table, the other is a list of companies. The two are linked via a 'company code' (AppCoCode) so:

1234 = ABC Company, Some Street, Some Town
1235 = XYZ Company, Another Street, Another town...

I have created a form based on both tables to add applications onto which the user must input an AppCoCode, and then when they tab away from that box, the company details will be displayed (in unmodifiable text) from the Company table so they are sure they have the correct code.

However there are occasions when the user will not know what the AppCoCode is, and will have to search on the Company table by name (CoName). I have created an unbound combo box linked to the company name field on the company table to do this on the form.

What I therefore now need to do is 'reverse populate' the remaining fields: i.e. they pick the company name, and that autofills the AppCoCode field, and the remaining address fields. I've tried various beforeinsert and afterinsert combinations, but the CoName combobox remains 'detached' from the rest of the data: i.e input AppCoCode, the combobox remains blank; input data into the combobox, and the remaining boxes are blank, and can have any old rubbish put into them.

I don't particularly need the code, but rather a general outline of the process, if that makes sense.

In COBOL this has to be done by a query and pick, but it seems like I could cut that huge corner in Access.

Hope that makes sense. Thanks.

 

[DCrake]

In your combo box make sure you have at least 2 columns, 1 the company id (hidden) and the company name. Make the company id column the bound column. Then after the user selects a company from the combo box use the after update event and set a filter on the froms underlying data source, such as:

Me.Filter = "[CompanyID]=" & me.combobox
Me.FilterOn = True


David

 

[shez]

well a general process using VBA would be to:

1. use the onChange even on the companyName combo
2. Create vba code
3. do a select query and use the value in the companyName combo so something like:
select * from table where name = companyName.value
4. loop through the result & manually set the other fields like so:
companyId.value = rs.recordset(id)
companyName.value = rs.recordset(name) and so on..

Notice that when i say rs.recordset(id) thats not the "right code" you use to get the info from result of a query... the right code is similar but I have just started learning VBA.