Cascading comboboxes in datasheet, is it possible?

[Guest]

I have 3 tables:
1) Table Projects with autonumber primary key ProjectID, ProjectName (text)
2) Table Activities with autonumber primary key ActivityID,
ActivityName(text), ProjectID (foreign key)
3) Table HoursWorked with autonumber ID field, ProjectID, ActivityID and
Hours (Number)

Tables Activities and Projects are linked. One project can have many
activities. In table HoursWorked users insert ProjectID, ActivityID and
Hours. This third table is linked to a datasheet form.

What I would like to do is to select projects and corresponding activities
by using cascading combo boxes in the datasheet, I wonder if this is possible
at all?

I insert a combobox (cboProject) as the first column of the datasheet bound
to projectID with TableProjects as rowsource.I insert a second combobox
(cboActivity) bound to ActivityID and displaying the ActivityName. This
second combobox must have its rowsource set from the value of the first
combobox: Select ActivityID, Activityname from TableActivities where
ProjectID = Me.cboProject. I set this rowsource on cboproject AfterUpdate
event.

This does not work in my datasheet because all activities in all records are
updated from this event. If I am working on record 5 of the datasheet, the
Activitynames on row 1 to 4 of the datasheet are also updated, although the
data in table HoursWorked are not changed. How can I fix this?

Regards

Tore

 

[Guest]

That was a good idea, but not exactly what I wanted. Thank you for a good tip
anyway. It seems like cascading comboboxes in a datasheet is not straight
forward.

Another idea I have is to trigger a popup form when the user clicks in a
datasheet cell, and then use comboboxes in the popup form. But I still would
prefer cascarding comboboxes in the datasheet.

Regards

Tore

 

[Jeff Boyce]

Instead of using a datasheet view, use a single form view. You can create a
single form that looks very much like a row of data. Then you can make the
window in which that single form appears much taller, leading to more "rows"
being displayed. It looks like a datasheet, but each individual form acts
independently, which is, I believe, what you are trying to do.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Ron2006]

That was a good idea, but not exactly what I wanted. Thank you for a good tip
anyway. It seems like cascading comboboxes in a datasheet is not straight
forward.

Another idea I have is to trigger a popup form when the user clicks in a
datasheet cell, and then use comboboxes in the popup form. But I still would
prefer cascarding comboboxes in the datasheet.

Regards

Tore

if you have the link field be the displayed project number insteat of
the project id (and the other boxes the same thing) it will work as
you probably are expecting.

ron

 

[Charles Wang[MSFT]]

Hi Tore,
I have one question about your requirement:
Since you had used a datasheet view, why did you use combo-box in multiple
rows?

Per my understanding, if you are in a datasheet view, there should be only
one row which contains the project combo-box and the activities combo-box.
You can select any project from the combo-box and then the related
activities are listed in the activities combo-box. This can be easily
implemented in the combo-box's AfterUpdate event.

Please feel free to let me know if you have any questions or concerns.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[Guest]

I want the comboboxes in different columns of a datasheet.
In the first column the user selects a project from a combobox
In the next (second) column I would like the user to select the project
activities from an activities combobox, i.e the activities that belongs to
the project in the first column. In the third column the user shall input
hours worked.

In the datasheet i would like to see many records where project as well as
acitivity are different from one record to the next. I try to use the after
update event of the projects combobox (first column) to set the rowsource
for the activities combobox (second column). The rowsource seems to be set
for all instances of the activities combobox within the datasheet, causing a
wrong or blank activity to be displayed in some rows.
This happends for datasheet activity comboboxes even though the data in the
connected table remain constant.

It seems that I cannot set rowsource for one instance only (one datasheet
row) of the activities combobox.
If this can be solved I will be happy. If not i will make a popup menu or
something for this.

Regards

Tore

 

[Charles Wang[MSFT]]

Hi Tore,
Thanks for your response.

I performed a simple test and it worked fine. My code was as following:
Private Sub cboCategories_AfterUpdate()
Me.cboProducts.RowSource = "SELECT ID,ProductName From tblProducts
WHERE Category=" & _
Me.cboCategories.Value & _
" ORDER BY ProductName"
Me.cboProducts = Me.cboProducts.ItemData(0)
End Sub

I had two combo-boxes in the form, one was for Category and the other was
for Product. When I selected a record from the category combo-box, the
related products should be displayed in the product list. For the empty
list in your activities combo-box, you may check if you did not select a
Value column for the combo-box. In my test, if I did not specify [ID]
column in the SQL statement, the product list was also empty.

If it does not help, could you please mail me
(changliw_at_microsoft_dot_com) your sample Access file so that I can
reproduce your issue at my side and find a resolution at last? It is my
pleasure to work with you for further assistance.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[Guest]

This solved my problem, thank you. I am now able to build the cascading
comboboxes as i wanted.

Regards

Tore

 

[Charles Wang[MSFT]]

Hi,

Appreciate your update and response. I am glad to hear that the problem has
been fixed. If you have any other questions or concerns, please do not
hesitate to contact us. It is always our pleasure to be of assistance.

Have a nice day!

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[Guest]

I ran into some more trouble with cascading comboboxes in datasheet.
Combobox 1 has the projects table as its constant input, it works fine.
Rowsource for Combobox 2 depends on contents of comobox 1.
I use the after update event of the combobox 1 (project) to set the
rowsource for combobox 2 (Activities in project). This works fine.
When I go from one record to the next in the datasheet (clicking with the
mouse) I use the on current event to set set the rowsource for combobox 2
(activities). This also works fine. The dropdown list shows the right
contents.

The problem with many visible records in a datasheet is that the on current
event seem to blank out contents of combobox 2 for some of the rows that are
not clicked. How can I prevent combobox 2 to be blanked out?

Regards

Tore

 

[Charles Wang[MSFT]]

Hi Tore,
I consulted the product team and just got their response. They pointed me
that this was a common-seen issue. Currently you need to consider other
ways to work around it. You may refer to this article:
Data in a combo box control on a continuous form/datasheet disappears
http://www.fmsinc.com/free/newtips/access/accesstip16.asp

If you have any other questions or concerns, please let me know. It is my
pleasure to be of assistance.

Best regards,
Charles Wang
Microsoft Online Community Support
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
======================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
======================================================

 

[Guest]

Thanks. I guess I have to accept that this cannot be done the way I wanted. I
will find some other method.

Regards

Tore

 

[Douglas J. Steele]

Before you give up, see whether what I had in my February, 2006 "Access
Answers" column in Pinnacle Publication's "Smart Access" is what you're
looking for. You can download the column (and sample database) for free from
http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[Guest]

Thank you. Your answer helped me a lot, I will use your solution. You
describe a solution for continous forms, but it works just as well for
datasheet. I just narrow down the colomn with the combox so that only the
down-arrow is visible. The next text column is updated according to your
advice and stays stable. Thank you.

Regards

Tore