Ahoy Database Wizards!

quad_red

Hey guys,
I've been thrown in at the deep end here and I'd like some advice.

I've got set up a (pretty) extensive contact database. I know how to set up tables, enter data etc.

But I want to do something specific but I don't know if Access can do it or how to do it.

Basically, I want the front form of the database to contact title (select from a drop down list), name, surname etc. All fine.

But for 'sector' I want to set up a drop down list which then set's up/populates the following fields.

So, for example, I select 'gov dept' from sector. I want the next field (institution/organisation) to populate a list taken from a table I've composed with gov depts. When the person selects a department, i want the next field (address 1) to populate with the list of offices for that dept (contained in another table).

Is that possible? I've tried MS owns office site but it isn't clear.

All help most appreciated.
Cheers guys.

Ginger

Just use the OnChange option of the Select box to run another query that populates the second select box passing in the value that has been selected in the 1st select box

EL_Loco

Not too sure about the interface in access, but I assume like most MS applications you can use VB in them.

The code below is from a front end that "talks" to a MS database, hopefully it'll be useful. Basically what you need to do is update the combo box 2 depending on what you choose in combo box 1.

maybe use the "lostfocus" method so when you move off the first combo, it triggers the code to fill the 2nd. They might be a cleaner way to start the code running though. EDIT: actually onChange sounds good as suggested above.

Dim sTemp As String
Dim rs01 as Recordset
Dim db01 as Database
Dim strSql01 as String

Set db01 = OpenDatabase("C:\mydatabase.mdb")

sTemp = Combo1.Text

'construct string for query
strSql01 = "SELECT * FROM Adresses_Table WHERE Dept_field = '" & stemp & "'"

' get Record set from database db01 using the query you've constructed
Set rs01 = db01.OpenRecordset(strSql01)

'fill combo 2 with the results of the query
With rs01
    Do While Not .EOF
         combo2.AddItem rs01!field_name_in_datebase
        .MoveNext
    Loop
    .Close
End With

hope that helps.

Ginger

Access has forms and allows form driven design as well as coding. For this its case of using the forms .

Coding it will take a bit longer for a standard piece of functionality.

Also if you use the OnChange event this will fire when the value in the dropdown changes. Using the lostfocus means that once you tab out it will attempt to load the new values in dropdown 2 regardless if the value in dropdown 1 has changed

quad_red

First of all - thanks a million for taking the time to reply. I appreciate it.

Unfortunately, I know no visual basic so I'm sunk if I need that kind of knowledge.

Ginger wrote:

Just use the OnChange option of the Select box to run another query that populates the second select box passing in the value that has been selected in the 1st select box

Ginger, I'm sorry dude, but I'm gonna have to ask you to explain that more.

name / sector / institution

I've got my field in the form eg. sector. I can set a combo box to look up the various sectors from a table called 'sector' and get the selection to populate the box once clicked.

To get a specific option from that list, eg. 3rd level, to populate the next field 'institution' with a list from a table called 'third level' is what I need to figure out how to do. So if I select 3rd level it'll look up the 3rd level table. If I select 'gov' it'll look up the government table.

I'm going to do a bloody advanced Access course. But that isn't going to help me here.

Thanks guys!

Ginger

This should explain it all

Worked example

http://www.blueclaw-db.com/download/dependent_combo_box.htm