How to have a combo box set the function of a command

[lccrews]

I have a command that runs a query when items are entered into a designated field. I'd like to adjust my command function to open a specific "set" of queries if the combo value says "Skid 5" and another set of queries if the combo box says "Skid 6", and another set if the value is "Skid 7". Is this possible? I know how to have a report/query opened based on cbo but I'm not sure how to do it with customized information (i.e. "skid 6" opens qry... and qry...).

[June7]

Runs what kind of query? I presume you mean an action SQL (INSERT, UPDATE, DELETE)?

I seldom build and run action query objects. I use VBA to construct and run action SQL, like:

CurrentDb.Execute "DELETE FROM tablename"

CurrentDb.Execute "INSERT INTO tablename(fieldname) VALUES(" & Me.combobox & ")"

If what you really want is to open a SELECT query, yes, VBA can open particular query object based on conditional code.

But why open SELECT query object?

[lccrews]

Hey June,
Let me explain what is going on in my situation. I have 3 fields: Raw Material, Skid, and Flavor. There is a table that I want to add ingredients to using a command button. The user types in the ingredient they want, select the appropriate Skid, and the appropriate flavor. The command button runs two separate action queries that have two different quantity calculations. My issue is that Skid 6 and Skid 7 have different calculations than Skid 5.

So when the user selects Skid 6, I want them to still be able to press the same command button.

The only other solution I can think of (keeping in mind I'm not a VBA guru) is to have three separate command buttons.

[June7]

Why saving calculated data instead of calculating when needed? There may be justification for saving calculated data but there must be a clear need as the saved calculation can become 'out of sync' with raw data if not managed properly.

Certainly code can use different calculations based on conditions.

But if you want to avoid VBA, then maybe an expression in textbox will accomplish. All depends on how complex the calcs and conditions are. Until you provide the rules and formula, hard to say.

[lccrews]

The reason for saving calculated data is because the user is building recipe instructions to print out and deliver to the operations floor. The calculations are relatively simple, it's the selection of appropriate ingredients that difficult. I have 9 queries to get 2 answers.
query 1 - Totals ingredients in Skid
2 - Identifies the ingredient for calculation
3 - Runs calculation on that ingredient
4 - Runs calculation on the answer from the previous query
5 - Appends a record using final answer from previous calculations
6 - Runs calculation to determine the number of rows (the command button appends the ingredient with calculation #2 as many times as this says)
7 - Runs simple calculation on total of the ingredient (total / # of rows)
8 - Runs calculation on the ingredient using previous query
9 - Appends a record using final answer from previous calculations

I am using queries to run the calculations so that someone without experience can make adjustments to it without getting confused.

Should I give you an example from start to finish? I'm basically looking to have my command button run the two action queries if Skid 5 is selected and run 2 different queries should Skid 6 or 7 be selected.

[ssanfu]

What code do you have for the button now?

If the determining control is the "Skid" combo box, you could use a "Select Case...End Select" construct.

I have 3 fields: Raw Material, Skid, and Flavor. There is a table that I want to add ingredients to using a command button. The user types in the ingredient they want, select the appropriate Skid, and the appropriate flavor. The command button runs two separate action queries that have two different quantity calculations. My issue is that Skid 6 and Skid 7 have different calculations than Skid 5.

'Calculations based on Skid selected in "cboSkid" combo box control
Select Case Me.cboSkid
    Case "Skid 2"
        'Do These calculations for Skid 2
    Case "Skid 5"
        'Do other calculations for Skid 5
    Case "Skid 6", "Skid 7"
        'Do Different calculations if Skid 6 or Skid 7 selected
End Select

[Micron]

Agree that calculations are often best not stored. Mainly, if a value used in the calculation is likely to change, the calculation or perhaps associated values, becomes "wrong". Not sure, but in your case, that may not be happening. You be the judge.

Basically, you have 2 options. A series of If statements or a Select Case block. For the first, you'd use

If condition 1 Then
 do A
ElseIf condition 2 Then
 do B
ElseIf condtion 3 Then
 do C
...
Else
Do whatever's left to be done
End If

While that is often sufficient, sometimes a Select Case is better or at least more concise

Select Case Something
 Case 1
  do A
 Case 2
  do B
 Case 3,4,7
  do C
 Case Else
  do whatever's left
End Select

The case block handles multiple choice neatly (as in 3,4,7) as well as comparison operators (>,= etc) although the syntax is slightly different. Not sure if there's a compelling reason for either approach.

[ssanfu]

@Micron

Great minds!!!!


I tend to use Select Case because it is easier (for me) to read and modify.
(and I should have added the "Case Else".. Doh!!

[Micron]

Curses! 2nd place again.

[lccrews]

Thank you for the help everyone, the Select Case function is exactly what I needed and it works great!