Retain value from unbound comboboxes

[whirlygig]

I have four levels of cascading combo boxes (level 1 filters level 2, which filters level 3, etc). On the form they are unbound controls, but I need to collect and store the data in the table behind the form. How do I do that? I've been searching the internet and cannot find anything so far. I will also need to be able to pull those fields for reports, etc., later.

Thanks in advance

 

[gemma-the-husky]

if they are unbound, where are you trying to store them

presumably the settings relate to an indivdiual record, (ie they will be different for each record) in which case they ought to be bound controls

you may have an unbound form, but these are generally unbound for specific reasons, and need fairly advanced usage techniques - you wouldnt use an unbound form generally ,,,, so what exactly are you trying to do with this form

 

[whirlygig]

The requirement I was given is to force the choices. The option lists are stored in separate tables with the foreign keys to relate them to each other. I need to store the selected options from the boxes in a separate service call form that the repair person will complete, using the combo boxes to force the options they chose, so that we will be able to pull reports and track what the calls are on.

 

[Mike_Burns67]

This may not be ideal, but a solution i have used in the past is to create a temp table containing 4 fields; box1, box2 etc, then do a sql insert command;

docmd.runsql "insert into temp ([box1], [box2], [box3], [box4])" & _
"values ([forms]![myform].txtbox1, [forms]![myform].txtbox2, [forms]![myform].txtbox3, [forms]![myform].txtbox4);"

Then you can reference them in queries, reports etc nice and easy.

Mmike.

 

[whirlygig]

Update: Retain values of comboboxes

I did bind the controls to the table they should save to, and when I look at the table they seem to have saved. However, when I open the form I get an error "Enter Parameter Value: Val-A" (for cboBox1). The answer put there does not seem to affect the data that shows on the form, and cboBox 2 has no data in it. cboBox 3 seems to retain the information with no problems. I don't understand what it's doing; for this (showing record 1 of X) it should show the entered information, correct? If so why do I get the error?


Thank you

 

[gemma-the-husky]

if you have cascaded combo boxes so that

box4 depends on box3, depends on box2, depends on box1, then you only need to store the value of box4.

if all the boxes are independent, then you need to store each of them

its not really clear to me exactly how you are trying to use these combo boxes, or how they relate to your data

--------
if what you are trying to do is select various criteria in the combo on which to base a the rows that a query or form returns, then your query needs to pick up the values of these (the appropriate) combo boxes

 

[whirlygig]

if you have cascaded combo boxes so that

box4 depends on box3, depends on box2, depends on box1, then you only need to store the value of box4.

Yes, they depend on each other like that. However I need to be able to run filters on the value of each combo box later, for a report.

its not really clear to me exactly how you are trying to use these combo boxes, or how they relate to your data

We test the product before it leaves, and run the same set of tests if it is returned. Rather than having maintenance write a freehand description of the problem, we want them to chose from a standardized list for ease of tracking & quality control. If there is a problem at level A, was the problem A1, A2, or A3? Ok, A2, so was that caused by A2.1 or A2.2? I need the values selected for A, A2, and A2.2 to all be stored in the table behind the form. When I have that working I will have a report that will show, say, how often A is a problem, and how much of that is caused by A2.2.

--------

if what you are trying to do is select various criteria in the combo on which to base a the rows that a query or form returns, then your query needs to pick up the values of these (the appropriate) combo boxes

I think I just answered that but if I didn't, let me know & I'll try again.

Thanks

 

[whirlygig]

Re: Update: Retain values of comboboxes

I did bind the controls to the table they should save to, and when I look at the table they seem to have saved. However, when I open the form I get an error "Enter Parameter Value: Val-A" (for cboBox1). The answer put there does not seem to affect the data that shows on the form, and cboBox 2 has no data in it. cboBox 3 seems to retain the information with no problems. I don't understand what it's doing; for this (showing record 1 of X) it should show the entered information, correct? If so why do I get the error?

I found this error came from the RowSource in Properites - the source was a query and it said:
"SELECT [.tblMode].[.lngModeID], [.tblMode].[.lngTypeID], [.tblMode].[.strFailMode] FROM tblMode WHERE [.lngTypeID]=1;"

I removed the WHERE clause to get rid of the error.

I did that for each cboBox and it seems to be storing the needed information and not kicking back any other errors.

However if you chose one of the combo boxes when editing information (so it starts with stored values) it shows unfiltered results, since they didn't trigger the AfterUpdate event on the previous cboBox. I'm not sure what to do about that one, either.