2 Combo boxes for criterias 1

[DajTwo]

Hi all

In a form, i need to store the inputs of 2 combo box to be critarias in a query.

From what I know, I will have to Dim 2 variables (one for each combo), then refer to these variables in the query.

Private Sub frm_rpt_onClick ()

On Error goto ErrorHandler

Dim cmbone as String
Dim cmbtwo as String

cmbone = me.comboone.Value
cmbtwo = me.combotwo.Value


Can someone help me finish this pathetic attempt?

Thanks

If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[TheAceMan1]

How are ya DajTwo . . .

Your [blue]primary concern[/blue] is wether or not the [blue]Bound Column[/blue] contains the data you wish to return.
[ol][li]If true, then you simply return the data in the query thru a form reference:

[blue][purple][b]Combobox on Form:[/b][/purple]
   WHERE ([[i]FieldName[/i]] = [Forms]![[i]FormName[/i]]![[i]ComboboxName[/i]])

[purple][b]Combobox on subForm:[/b][/purple]
   WHERE ([[i]FieldName[/i]] = [Forms]![[i]FormName[/i]]![[i]subFormName[/i]].Form![[i]ComboboxName[/i]])[/blue]

[/li]
[li]If no, then you have to use the [blue]Column[/blue] property to return the proper data. However ... [red]you can't use the Column property in a query![/red]. The only way out is returning the data thru a function:

[blue]Public Function CbxDat()
   CbxDat= Me!ComboboxName.Column(?)
End Function 

[purple][b]Where clause in query would look like:[/b][/purple]
   WHERE ([[i]FieldName[/i]] = CBxDat())[/blue]

[/li]
[li]Of course 1 & 2 above are contingent on the form being open! If the form needs to be closed before you call the query, then the data needs to be [blue]stored in a variable[/blue], in a module in the modules window (the function is moved to the module as well) and returned thru the function method:

[blue]Option Compare Database
Option Explicit

Public ComboDat


Public Function CbxDat()
   CbxDat = ComboDat
End Function[/blue]

[/li]
[li][blue]Special Note:[/blue] the above is strictly for combobox use in [blue]queries[/blue], for SQL in VBA the data can be directly concatenated ...[/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DajTwo]

Hi Aceman.

As you see, I am still having major issues with combo boxes and this is not the same problem we worked earlier in the month where I required that the 1st combo act as a filter for the 2nd combo.

Unfortulately, I do not fully understand your reply.

You refer to a subform, cant I put both combo on the main form, which is unbound?

Both combos will return the value I require in column 0 and will be closed thus variables are required.

The user would select the data in combo 1 and 2, press a command button then

Private Sub frm_rpt_onClick ()

On Error goto ErrorHandler

Dim cbxdatone as String
Dim cbxdattwo as String

cbxdatone = me!comboone.Column(0)
cbxdattwo = me!combotwo.Column(0)

End Sub

The query could be

WHERE ([FieldName] = cbxdatone() OR cbxdattwo () )

Do I get the right idea on this?


If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[PHV]

Have a look at the 4rth argument of the DoCmd.OpenReport method.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

[blue]Unfortulately, I do not fully understand your reply.

[purple]You refer to a subform, ...[/purple][/blue]

I was just showing the proper syntax for referencing a combobox on a form or subform.

You need to be more specific about the following caveat ([purple]in purple[/purple]):

[blue]Both combos will return the value I require in column 0 [purple]and will be closed[/purple] thus variables are required.[/blue]

Are you saying your closing the form before you run the query? Need an answer here.

Now ... you need to understand the starting indices of [blue]Column Index[/blue] and the [blue]Bound Column[/blue] property.
[ol][li][blue]Column Index[/blue] is zero based and starts at zero. Its also independent of any column width set to zero in the [blue]Column Widths[/blue] property. That is to say: hidden columns are still counted.[/li]
[li]The [blue]Bound Column[/blue] property is 1 based and starts at 1.[/li]
[li]So if we have say five fields in the combo, the indices look like the following:

[tt] qName qCity State Zip Phone
----- ----- ----- --- -----
Column Index 0 1 2 3 4
Bound Column 1 2 3 4 5[/tt]

I hope this is clear.[/li][/ol]
Now since your using column(0) you can go direct without using the column property! Just make sure the [blue]Bound Column[/blue] property of both comboboxes are set to 1.

Using the direct method, proper syntax would be:

[blue]   
   WHERE (([FieldName] = [Forms]![FormName]![ComboboxName1]) OR ([FieldName] = [Forms]![FormName]![ComboboxName2]))[/blue]

As for the function method, you'll need two variables and two respective functions. Note: [red]variables, functions and routines can not have the same name![/red]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DajTwo]

Thanks aceman,

That is again what I needed, abetter understanding on how things work.. I can close the form after the reprot is issued so all is well

Great Thanks!!!

If I did not say it before, I really appreciate the time and expertise of the users on this forum.

 

[TheAceMan1]

DajTwo . . .

Could you post your solution so others can bebefit?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[DajTwo]

Hi Aceman,

I opted for your solution to refer in the query:

WHERE (([FieldName] = [Forms]![FormName]![ComboboxName1]) OR ([FieldName] = [Forms]![FormName]![ComboboxName2]))

However the users were not satisfied with the process as erroneous inputs could be selected and the reports would be useless.

Thus I am stuck with another instance where the best solution is interactive combo boxes, which despite your hardest attempts, I cannot comprehend.

I will yet post another time on the commbo box issues very soon.

Thanks Aceman.

If I did not say it before, I really appreciate the time and expertise of the users on this forum.