Mutlivalue Combobox Lookup field and a calculated field

[NickWren]

So I have a lookup field that takes multiple inputs if checked, however now I want to apply that field to a calculated field which uses both of the values
My calculated field:
[Location] & "-" & [ID] & "-" [TestType]

TestType is the multivalue combobox that you can choose different tests, the TestType field gives both answers but how do i combine the answers to make it one word and then put it in the calculated field

[ranman256]

you dont. Multivalue combos cant integrate into queries. Not unless you know lots of VB.

However you CAN make a point and click system WITHOUT vb, from a list box where the user picks items wanted, an append query runs and adds all picked items to a 'picked' table.
The picked table can then be joined to your data table and pull ONLY items in the picked list.

The user dbl-clicks the item in list,
append qry add it to tPicked
a delete query empties the table to start fresh
Then your main query joins on the tPicked tbl to pull results.

[June7]

Review
https://support.office.com/en-us/art...3-B6624E1E323A
http://www.techrepublic.com/blog/mic...ess-2007-2010/

[NickWren]

This is my VBA code so far, but I keep getting debug errors. Pretty much this code takes two commands buttons 1 goes to a "NETDataForm" while the other goes to "COLDataForm". However the samples dont always have both tests done, so I used a multivalue combobox to determine which tests are done, so If NETest was done the value in TestType should be "NET" while if the Colon test was done then the TestType should say "COL" and if both were done it should say "COL, NET" (alphabetically). But as of now I cant use an If than statement with the string it makes. (Debug: If CurTestType = "NET" Then)

Option Compare Database
Private Sub Command55_Click()
DoCmd.OpenForm "NETDataForm"
End Sub
Private Sub Command56_Click()
DoCmd.OpenForm "COLDataForm"
End Sub
Private Sub Form_Load()
Me.Command55.Visible = False
Me.Command56.Visible = False
End Sub
Private Sub Form_Current()
CurTestType = Me.TestType.Value
Me.Refresh
If CurTestType = "NET" Then
Me.Command55.Visible = True
ElseIf CurTestType = "COL" Then
Me.Command56.Visible = True
ElseIf CurTestType = "COL, NET" Then
Me.Command55.Visible = True
Me.Command56.Visible = True
End If
End Sub

Ik ranman said it is difficult to make a query using the multivalue combobox, but I do need to know how to do this, so how is that possible using this code for a search query?

 LIKE "*" & Forms!SearchSampleForm!TestType.Combobox.Value & "*"

[June7]

Did you review the links provided? I NEVER use multi-value fields.

If you want to pull individual values from multi-value field, need query that expands the values to individual records. I provided link to example for that.

You must understand that "COL, NET" is not what is stored in a multi-value field. "COL, NET" is a single text string. A multi-value field does not store a single text string. A multi-value field is really a 'link' to a hidden dependent table - basically what you would do if you built a normalized structure, only you would be able to see the dependent table.

Therefore a multi-select combobox does not have a single text string as a value and your If Then conditional code fails.

Suggest a normal text field and combobox with options of COL, NET, BOTH and adjust your code accordingly.

[NickWren]

O... I see, theres no way of combining the two stored values? I tried to store the value inside another hidden textbox, and it comes out saying "COL, NET" I cant use that value? because isnt this a string now from the hidden textbox?

[June7]

How do you accomplish setting value of textbox?

Code would reference the hidden textbox, not the combobox.

I really don't see need for multi-value field/combobox.

[NickWren]

The need for the combobox is so that we dont have the same sample entered twice with the only difference being the test we are running on it. it is ideal to get this multivalued combobox answer into a string to determine which form to go to next.

If anything I guess a checkbox to say yes we are doing this test or yes we are doing that test would work also. Then using an if than statement to open up the forms with the "yes".

[June7]

My suggestion of normal text field and combobox with options of COL, NET, BOTH would not require multiple sample records.

Are COL and NET the only test types involved?