Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old January 28th, 2007, 08:56 AM
Friend of Wrox
 
Join Date: Apr 2006
Location: Ternat, , Belgium.
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default cascading list problem

Hi,

I have a problem when trying to use cascading list on a form.

I have a first combo box named "Hoofdgroep" wich takes his list directly from a table "tblHoofdgroep". The "tblHoofdgroep" is joined with a table "tblSubEnHoofdgroepBA06" where I have 2 fields "Hoofdgroep" (that comes from the "tblHoofdgroep" table) and "Subgroep". Both are text type fields.

What I would like on the form is that when in the first combo box "Hoofdgroep" I select one of the values, the combo box "Subgroep" on the form only shows the values for wich it finds values in the "tblSubEnHoofdgroepBA06" table.

I'm using the following code for this:

private sub Hoofdgroep_afterupdate()

on error resume next
Subgroep.rowsource = "select tblSubEnHoofdgroepBA06 " & _
"from tblSubEnHoofdgroepBA06 " & _
"where tbl.SubEnHoofdgroepBA06.Hoofdgroep = '" & Hoofdgroep.value & "' " & _
"order by tblSubEnHoofdgroep.Subgroep;"

end sub

This code however does not work... Is there something wrong with it or am I just forgetting something?

 
Old January 29th, 2007, 10:00 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Here is an easy way to do this:

Use the combo box wizard to select the field Hoofdgroep from tblHoofdgroep. This combo box should also select the PK for the records, but hide that and use it as the bund column, unless Hoofdgroep IS the PK for this table. Let's call this cboHoofdgroep.

Create a second combo box for Subgroep looking up in the tblSubEnHoofdgroepBA06 table, and include the Hoofdgroep FK field in the fields in the list (the fields should be the OPK field for tblSubEnHoofdgroepBA06, Subgroep, and Hoofdgroep FK). Let's call this cboSubGroep.

Then open the properties dialog box for cboSubGroep, and selec the data tab. Click on the Row Source, and open the query designer. In the column for the Hoofdgroep FK field, add this line in the Criteria:

[Forms]![frmYourFormName].[cboHoofdGroep]

Then Uncheck the Show check box. Don't click the save button, just close the query designer, and check yes to save the changes.

Go to the Format tab, and remove the last ";1" from the Column Widths list.

Then go to the After Update event of the cboHoofdGroep, and add this code:

Me.cboSubGroep.Requery

Did that work for you?



mmcdonal
 
Old January 29th, 2007, 10:02 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

"bund" = "bound"
"OPK" = "PK"
"selec" = "select"
Fat fingering everything this morning.

mmcdonal
 
Old January 29th, 2007, 01:06 PM
Friend of Wrox
 
Join Date: Apr 2006
Location: Ternat, , Belgium.
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

It seems to work fine, but I still have a little question...

When I insert a record and move to a next record to insert, than the "Subgroep" combo box on the form for the new record still shows the "Subgroep" data of the previous inputted record (if I do not select a "Hoofdgroep" first). Is it possible to insert a code somewhere that when I move to a new record, that the "Subgroep" combo box should be emptied again?

 
Old January 29th, 2007, 01:40 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You might put a requery event for both combo boxes on the On Current event of the form. Requery the first one, then the second. If that doesn't work I will work on a solution.

mmcdonal
 
Old January 30th, 2007, 11:41 AM
Friend of Wrox
 
Join Date: Apr 2006
Location: Ternat, , Belgium.
Posts: 159
Thanks: 0
Thanked 0 Times in 0 Posts
Default

This works fine, thanks very much





Similar Threads
Thread Thread Starter Forum Replies Last Post
Cascading menu u49698 Pro Visual Basic 2005 1 February 15th, 2007 08:28 AM
Cascading Lists Problem Brendan Bartley Access 5 October 4th, 2006 12:28 PM
cascading style sheet problem nikotromus ASP.NET 2.0 Professional 0 March 24th, 2006 07:49 PM
got problem with cascading iframes nerssi CSS Cascading Style Sheets 1 June 17th, 2005 11:11 AM
Cascading iframe problem nerssi HTML Code Clinic 0 June 13th, 2005 04:36 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.