Help With Cascading Dropdown/Combo Box Code

[dgunkel]

Hey Everyone,



I'm working on creating a form for work. It's a really simple database that is being used to show a "dashboard view" of some client revenue information. I'm having trouble programming the "after update" event for my drop down list. I want to be able to select a client name from the drop down box and then have the relevant revenue information show up in the combo box below.

I'm not great in Access, help would be appreciated.

Thanks

ITPA.zip

[rpeare]

Can't read access 2007+ if you can convert it to 2003 I'll take a look.

[June7]

Concatenate variables into sql string. Reference to Me!Company is a variable.

.RowSource = "SELECT Revenue_Amount " & _
"FROM Client_Revenue " & _
"WHERE Client_Name = '" & Me!Company & "'"

Company is not included in the form's RecordSource. There is no field or control by that name, perhaps should be Client_List?

The control named cbo_Revenue is a listbox, not combobox, better name would be lst_Revenue. You have the listbox set for 5 columns, with the first 0 width but the code retrieves only 1 field in the listbox RowSource.

Try these edits:

Set Client_List combobox BoundColumn to 1.

Set cbo_Revenue RowSource to:
SELECT * FROM Client_Revenue WHERE Client_Name=[Client_List];

Change code to:
Option Compare Database
Option Explicit
Private Sub Client_List_AfterUpdate()
Me.cbo_Revenue.Requery
End Sub

[dgunkel]

Hi,

I updated my code to concatenate the variables in sql string. Not something I'm familiar with clearly.

Also you are correct about Company that was a typo on my part. It was indeed suppost to be Client_List. I also updated the listbox name to more accurately reflect the type of control. Initially I was planning on pullin in all 5 columns in the Revenue table for the selected client, but as I started to have issues I was just trying to get one column to populate. Could you help me to populate all 5? Any chance you could upload a working version of the database?

Here's the updated version:

ITPA Updated.zip

[dgunkel]

ITPA 2003.mdb

I converted it to Access 2003 if you want to have a look:

[June7]

Perhaps I was editing my post as you created yours. Review again. Did you try the edits I suggested?

[dgunkel]

Yeah sorry I updated the attachedment above a few times in the last few minutes. I did incorporate the edits you suggested, however it's still not working. Here is the latest version:

Just saw your post up there, forums aren't so good for real time stuff. Let me try those edits.

REALLY appreciate your help, I'm a bit in over my head.

ITPA Latest.zip

[dgunkel]

I updated the Database with your edited suggestions, and it's working! Thanks a bunch. What exactly do these lines of code do?

Option Compare Database
Option Explicit

[June7]

Not sure about the first. Just know all my code modules are generated with it. http://support.microsoft.com/kb/98227

The second aids in debugging errors. http://www.fmsinc.com/free/NewTips/V...ion/index.html