Any MS Access Experts out there? Need help!

Joebob · 10 Mar 2014 at 10:37

I'm trying to do something in Access (2010) which I would imagine shouyld be quite easy, but I cannot find a solution.

All I want is for a field, wether in a table or a form, to autopopulate according to the information entered into the field before it!

i.e. If I have a field called 'Vendor', with a dropdown list containing Adobe and Microsoft. Then I want the second field called 'Software' to have a dropdown list of either Adobe software or Microsoft software according to the selection from the first list.

Is this possible?

linktoinsanity · 10 Mar 2014 at 16:37

Can't you write a Macro for that?

Ethermaster · 11 Mar 2014 at 00:00

Look at excel? The offset function could do this

#Chri5# · 11 Mar 2014 at 08:18

On the form, what type of field is it? If both are combo boxes, you can put a procedure in AfterUpdate (on the first combo) which then filters the second one.

Example here

thenewoc · 11 Mar 2014 at 15:42

If you had a form that was bound to a table collecting Vendor and Software etc you would configure the Software dropdown to use a lookup query. The query would lookup the possible values in a separate table that contains Vendor and Software fields and the records for all the possible entries you want. You can then configure the query used for the Software dropdown to use the name of the form control for Vendor in the criteria section and that should limit the Software dropdown values as required. Just from memory but I'm pretty sure it can be done like that. Can't recall from memory whether you need a macro function to retrigger the query if the Vendor selection is changed though, don't have Access installed at the moment.

Joebob · 12 Mar 2014 at 09:29

All really useful answers, thank you. I have enough now for what I need to do - I must admit I thought it would have been automated more in newer versions of access, rather than having to input code!

dazzerd · 12 Mar 2014 at 12:26

Easiest way to do it is to bind the rowsource property of your second combo box to a select query. That way you can avoid code completely.

If this is going to be multiuser, high concurrency then avoid binding any controls in any forms else you will encounter some problems.

Joebob · 24 Mar 2014 at 14:00

dazzerd said:
Easiest way to do it is to bind the rowsource property of your second combo box to a select query. That way you can avoid code completely.

If this is going to be multiuser, high concurrency then avoid binding any controls in any forms else you will encounter some problems.

Ok, I have tried this but cannot get it working.

I made a table with both Vendors and Software. Then created a query.

I made a simple form with just vendor, using a lookup running from the lookup in the table - then I put a combo box into the form and linked it to the select query.

How do I get the select query to use the vendor data which I select?

dazzerd · 24 Mar 2014 at 19:59

DOes your select query actually work when you run it on its own?

How many fields does your select query return?

Have you put one of the criteria as the value of the source combo box in the query? What I mean here is your query will be criteria based, i.e. you want to bring back all specific products for "vendor". Therefore in the criteria bit of the query builder under the field which holds the key you will put something like = Form1!comboBox1.value. My syntax may be a bit off I don't normally do builds using bound queries and tables, but you see where I'm going with it?

If more than one column then you will have to set your combo box up to show 2 columns, you can hide one by settting its width as zero.

If you can post your example I can have a play this evening, I have some time.

wesimmo · 24 Mar 2014 at 21:23

I've just done a quick test of this.

Basically I wrote a query that creates a list of vendors from a table of Vendors and Packages.

My first ComboBox (Vendor selection) used that as it's source.

The second ComboBox (Package selection) then has an SQL statement in it's Row Source

Code:

SELECT Table1.Package FROM Table1 WHERE (((Table1.Vendor)=[Forms]![Table1]![Combo19])) GROUP BY Table1.Package;

I then added an After Update event to the first ComboBox, and used the code below.

Code:

Private Sub Combo19_AfterUpdate()

Me.Combo23.Value = Null
Me.Combo23.Requery
Me.Combo23.Value = Me.Combo23.ItemData(0)

End Sub

Apologies for the generic table / object names, but Combo19 is the Vendor selection drop down and Combo23 is the Package selection drop down.

Table 1 is the list of vendors and packages.