Two dropdowns in subform in form - need help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an Access 2002 database that has a form which contains a subform. The
subform has two dropdown lists. The second dropdown list's values will
depend on the selection in the first dropdown list (list one wil lhave a
choice of 'Service' or 'Product'; list two will contain the list of services
or products). There are seperate Service and Product tables.

I have implemented relationships in the database. None of the tables'
fields use lookups of any kind. What I need to know is how do I populate the
second dropdown list (the names of the products or services) based on the
value chosen from the first dropdown list bearing in mind that both of these
controls appear in the subform. (If they were in a regular form, I would
just set the RecordSource property of the dropdown, but I don't know what to
do in a subform.)

TIA,
 
Joe said:
I have an Access 2002 database that has a form which contains a subform. The
subform has two dropdown lists. The second dropdown list's values will
depend on the selection in the first dropdown list (list one wil lhave a
choice of 'Service' or 'Product'; list two will contain the list of services
or products). There are seperate Service and Product tables.

I have implemented relationships in the database. None of the tables'
fields use lookups of any kind. What I need to know is how do I populate the
second dropdown list (the names of the products or services) based on the
value chosen from the first dropdown list bearing in mind that both of these
controls appear in the subform. (If they were in a regular form, I would
just set the RecordSource property of the dropdown, but I don't know what to
do in a subform.)
Click to expand...


Here's an article that should help you get going;

http://www.mvps.org/access/forms/frm0028.htm
 
Marsh,

Thanks. This is what I have coded behind my subform (VisitTypeID is the
first dropdown list; TypeID is the second dropdown list):

Option Compare Database
Option Explicit

Private Enum VisitTypeValues
Service = 1
Product
End Enum

Private Sub VisitTypeID_Change()
Select Case VisitTypeID.Value
Case VisitTypeValues.Product
TypeID.RowSourceType = "Table/Query"
TypeID.RowSource = "SELECT ID, Name FROM Product"
TypeID.Enabled = True
Case VisitTypeValues.Service
TypeID.RowSourceType = "Table/Query"
TypeID.RowSource = "SELECT ID, Name FROM Service"
TypeID.Enabled = True
End Select
End Sub

When I select either Service or Product fro the first dropdown, I get this
error: "The expression On Change that you entered as the event property
setting produced the following error: Object or class does not support the
set of events." Would you please help me to translate and resolve this?

TIA,
 
Joe said:
Thanks. This is what I have coded behind my subform (VisitTypeID is the
first dropdown list; TypeID is the second dropdown list):

Option Compare Database
Option Explicit

Private Enum VisitTypeValues
Service = 1
Product
End Enum

Private Sub VisitTypeID_Change()
Select Case VisitTypeID.Value
Case VisitTypeValues.Product
TypeID.RowSourceType = "Table/Query"
TypeID.RowSource = "SELECT ID, Name FROM Product"
TypeID.Enabled = True
Case VisitTypeValues.Service
TypeID.RowSourceType = "Table/Query"
TypeID.RowSource = "SELECT ID, Name FROM Service"
TypeID.Enabled = True
End Select
End Sub

When I select either Service or Product fro the first dropdown, I get this
error: "The expression On Change that you entered as the event property
setting produced the following error: Object or class does not support the
set of events." Would you please help me to translate and resolve this?
Click to expand...


You need to use the AfterUpdate event, not the Change event.

The Case statements don't make sense to me. What is
VisitTypeValues.Product supposed to be???
 
Marsh,

See the enum above. The VisitTypeID stores the ID and Name of the Visit
Type (Service, Product). VisitTypeValues.Product = 2 = product service type
ID in the ServiceType table (Service ID = 1).

Why use AfterUpdate here? Is there a place where I can learn more about the
events and which one I shoudl choose to implement code in?

Thanks very much,
 
Joe said:
See the enum above. The VisitTypeID stores the ID and Name of the Visit
Type (Service, Product). VisitTypeValues.Product = 2 = product service type
ID in the ServiceType table (Service ID = 1).

Why use AfterUpdate here? Is there a place where I can learn more about the
events and which one I shoudl choose to implement code in?
Click to expand...


OK, I missed the Enum before. Is the Product item supposed
to be missing a value? It seems wrong (kind of
un-normalized?) to me to use an Enum for this because you
have two different data structures (a table and the Enum)
defining the same data. but I guess that's neither here nor
there wrt to your problem. Beside, I don't see a way to
avoid the issue :-(

AfterUpdate fires when the Value of the control has been
updated. The Change event fires with every keystroke, but
the Value of the text box will not have been updated, so you
are checking the value it had before starting the
seleection.

I expect there are some books out there that discuss which
event are good for what, but I haven't used them. My source
is the Help files (best is A97). Try using Help's Answer
Wizard on "Order of events for database objects" and see if
that leads anywhere.
 
Joe said:
I get the same error with AfterUpdate.
Click to expand...


Sorry, but I don't have any good ideas.

I'm guessing here, but it sounds like you're using an object
inappropriately. Check the names of everything and make
sure they don't conflict with some other object.

Maybe if you compile the module, the compiler might give you
a more definitive message.

As a last thought, try changing/recoding the procedure one
aspect at a time to see if you can find the line that's
causing the problem.
 
You probably solved your problem since this thread is from June, but since I
recently accomplished this task dependent combo boxes (with the help of
Office Online), I thought I'd share it with you...it worked for me.

Look up in the Microsoft Office Assistance "Basing one combo box on
another"-you need to put code in the AfterUpdate event and the Load event.

Good luck, if you haven't already figured it out.
 
Back
Top