Cascading combo boxes

[mike7]

I followed the example about Cascading combo boxes as much as I can and the debugger still gives me an error msg. Here's the code I wrote:

Private Sub cboOrg_AfterUpdate()
On Error Resume Next
cboClient.RowSource = "Select Clients.nomclient " & _
"FROM Clients " & _
"WHERE Clients.nomorg = '" & cboOrg.Value & "' " & _
"ORDER BY Clients.nomclient;"
End Sub

Private Sub Form_Current()
On Error Resume Next
' Synchronise org combo with existing clients
cboOrg = DLookup("[nomorg]", "Clients",
"[nomclient]='" & cboClient.Value & "'")
' Synchronise client combo with existing client
cboClient.RowSource = "Select Clients.nom_client " & _
"FROM Clients " & _
"WHERE Clients.nomorg = '" & cboOrg.Value & "' " & _
"ORDER BY Clients.nomclient;"
End Sub

My error msg comes up at this stage:

' Synchronise org combo with existing clients

I have:

tbl Clients
field1 nomclient
field2 nomorg

Just don't have a clue where my mistake is...

Thanks for your help.

 

[mike7]

Am I stupid enough....

Am I stupid enough ? I'm just posting my solution to help some other newbies like me.

The following instruction just had to be written on one single line, not on two like above.

' Synchronise org combo with existing clients
cboOrg = DLookup("[nomorg]", "Clients","[nomclient]='" & cboClient.Value & "'")

Thanks for all the people who read my posting.

 

[adam_fleck]

Suggestion

A possible cause of the error you’re getting is that your Select statements don’t seem to refer to your table “[tbl Clients]”.

Also, I’m not sure that Form_Current is the best place to put the code you need. I’m guessing that your form is unbound (no underlying table) and that you want to be able to use cboOrg to pick an organisation, at which point the list attached to cboClient will be updated so as to show only clients for that org.

On an unbound form, with combo boxes cboOrg and cboClient, try the following code:

Private Sub cboOrg_AfterUpdate()
On Error Resume Next

    'Change the cboClient list so that it will show only clients who belong
    'to the current org
    cboClient.RowSource = "Select [tbl Clients].nomclient " & _
                          "FROM [tbl Clients] " & _
                          "WHERE [tbl Clients].nomorg = '" & cboOrg.Value & "' " & _
                          "ORDER BY [tbl Clients].nomclient;"
    
    'And set the value of cboClient to the first client in this list
    cboClient = DLookup("[nomclient]", "[tbl Clients]", "[nomorg]='" & cboOrg.Value & "'")
                          
End Sub

Private Sub Form_Load()
On Error Resume Next

    'Get the list of orgs
    cboOrg.RowSource = "Select Distinct [tbl Clients].nomorg " & _
                       "FROM [tbl Clients] " & _
                       "ORDER BY [tbl Clients].nomorg;"

    'Get the first org in the list
    cboOrg = DLookup("[nomorg]", "[tbl Clients]")

    'And call cboOrg_AfterUpdate to update cboClient
    cboOrg_AfterUpdate

End Sub

Hope that helps.

 

[Rich]

Why are you using DLookUp, and why not just base your combos on sql statements?

 

[mike7]

some answers...

To answer Rich, I picked up that code in http://www.fontstuff.com/access/acctut10.htm, on the third example.

But, to make a comment on Adam Fleck's reply, I have to say that my stuff in not running perfect at this time.

My combo boxes are running fine. My table is updated with new records. But when I opened the form, it starts at record #1 and I can't see what I've already entered in my table although it contains 4 or 5 records.

Do you have a clue ?

 

[Rich]

We've had issues with this guys design tips before, his example isn't correct, you do NOT need separate tables for each country, here's a simple example

 

[adam_fleck]

Yep, your example is much better

We've had issues with this guys design tips before, his example isn't correct, you do NOT need separate tables for each country, here's a simple example

Martin Green's suggestion on http://www.fontstuff.com/access/acctut10.htm is daft. All those different tables. What a nuisance. It's much easier to keep lookup values in one table, then have some fields to identify the lists a value can show up in.

My combo boxes are running fine. My table is updated with new records. But when I opened the form, it starts at record #1 and I can't see what I've already entered in my table although it contains 4 or 5 records.

Do you have a clue ?

If you could attach a zipped version of the form you're working on, we could have a look at it and maybe make some suggestions.

Cheers

Adam

 

[mike7]

a little comment....

In response to Rich:

If you're looking carefully at http://www.fontstuff.com/access/acctut10.htm in examples 2 and 3, Martin is grouping everything in the same table.

I've tested his method and it's working fine now after I redid everything from scratch.

I've also had a look at Rich's database and seems very attractive. From my poor experience, it's very hard for me to decide which avenue is the best as long as it works !

From what I can see on this forum and the number of postings concerning combo boxes, nobody have already submited a step-by-step and easy method (for newbies) to implement these cascading combo.
Imagine if someone would ask for 3 cascading combo boxes....

I just discovered this forum a week ago or so and it's incredible the amount of information we can find. There are a lot of pro's here but it's not always easy for someone like me who would like to explore Access more and discover it's enormous potentiel. I start to believe that it's often a question of trial and error when you're lacking experience.

 

[rkana]

I think that may be what i am looking to do, so let me know if i am right
i have a form where a user enters data about themselves then another form that the date goes to but i have to select which user i want so what i am trying to figure out is can i make a combo box for me to chose the user and the have the next 3 boxes or labels or something that contain other information about the user that they entered on the first form automaticallly fill in so i can see this information so i can transfer it to the one i need it on. so i am trying this cascading and seeing how it works.

 

[Mile-O]

From what I can see on this forum and the number of postings concerning combo boxes, nobody have already submited a step-by-step and easy method (for newbies) to implement these cascading combo.

I wrote a cascading combobox FAQ and placed it in the FAQ Forum a few months ago.

Imagine if someone would ask for 3 cascading combo boxes.

If you can do two (and it really is a process that takes a minute or two once you know what you're doin) then three is just a logical extension. As far as I see it if you use one line of code for two (which is all that's necessary) then you only need two lines of code for three combos.


By the way, I've not looked at Rich's example but I'd suggest that's the way to go. That guy's [Martin Green] website is full of crap - there are so many bad habits to be formed if you hang around there. So many of his "solutions" - such as the multiple tables for cascading combox - require maintenance if you add a new option in - create new table, query, and update the Select Case statement. Not the way to go.

 

[rkana]

I think that may be what i am looking to do, so let me know if i am right
i have a form where a user enters data about themselves then another form that the date goes to but i have to select which user i want so what i am trying to figure out is can i make a combo box for me to chose the user and the have the next 3 boxes or labels or something that contain other information about the user that they entered on the first form automaticallly fill in so i can see this information so i can transfer it to the one i need it on. so i am trying this cascading and seeing how it works.

ok i found it thanks Milo and for anyone else heres the post i found

Make a query that selects the fields you want from the table. Set this query as the combobox's RowSource. Set the ColumnWidths to 0 except the field with the person's name (I like to concatenate the person's name in the query so that my combo only shows one column). Set the ColumnCount to reflect the number of fields in the query.

In the textboxes that you want to display the other data, set their ControlSource to be, for example: =[MyCombo].[Column](1)

I hope it was okay to do that