Using VBA to define variable for query criteria

[erichusa86]

Hi

Basicaly i have a combo box with two options "ug" and "dh". this is the VBA code i have so far:

Private Sub cbosample_AfterUpdate()
 
Dim SampleType As String
 
If Me.cbosample = "dh" Then
    SampleType = "Like dh"
Else
    SampleType = "Not Like dh"
End If
 
End Sub

So if "dh" is selected in the combo box then the query criteria will be equivlaent to Like "dh" and if "ug is selected in the combo box then the query criteria will be equivalent to Not Like "dh"

Below is the code is used to reference the variable in the query. In the query criteria i put GetSampleType()

Public Sub SetSampleType(Value As String)
 
SampleType = Value
 
End Sub
 
Public Function GetSampleType()
 
GetSampleType = SampleType
 
End Function

Thanks

[Seth Schrock]

I'm not sure what your question is. Can you let us know what you need help with as well as what isn't working and the results (ie. error message along with the EXACT wording and number, or invalid values).

Also, please use the [CODE/] button when posting code.

[Rabbit]

Please use code tags when posting code.

A query does not see "Like 'dh'" as SQL. It sees it as a literal string. You will have to edit the query definition itself if you want to do it that way.

But I don't understand the need for any code at all. You can just do this directly in the query:

fieldName = Forms!formName!controlName

[erichusa86]

Ok, i am not getting any error messages, but the query criteria does not seem to be enforced as the query table does not return any values.

Let me know if this is not clear and I'll try again.

[erichusa86]

Ok, thanks.

The need for the code is because the "ug" value in the combo box has to encompass several different values which would be easiest described as all values except for "dh". ie in my table i have values "dh", "fc", "b", "st" and i want to group all values except "dh" under the "ug" combo box value.

I have been editing the query box manually for quite a while to get what i needed, but i am new to VBA and just trying to speed things up a bit using combo boxes and VBA code.

let me know if any of this doesn't make sense

Thanks.

[Rabbit]

You could use an iif or switch to change the comparison value so that each is grouped under the correct category.

[erichusa86]

Thanks Rabbit, got it sorted, just calculated a new query field

SampleType: IIf([collar]![type]="dh","dh","ug")

then used the value from the combo box as criteria.

[forms]![f_create_surpac_str]![cbosample].[value]

[Rabbit]

Glad you got it working!