ComboBox Recordsource Based on Variable (XP, sp-2)

[Pat97orbust]

I am trying to use a selection from a combo box to generate a report where records match selection from combo box. Variable is used in combo box recordsource (to run report on AfterUpdate event) but not working as planned.

Datasource for combo box is ...

SELECT [qry_LKUPunitClients].[RegNo], [qry_LKUPunitClients].[CliLName], [qry_LKUPunitClients].[CliFName], [qry_LKUPunitClients].[LivingArea] FROM [qry_LKUPunitClients] WHERE '["& Me!txtVar!value &"]' ORDER BY [qry_LKUPunitClients].[CliLName], [qry_LKUPunitClients].[CliFName]

... the "Me!txtVar!value " code is used because I could not determine a way to use the variable directly (is it possible to do so?). Although "Me!txtVar!value " seems to produce the desired result ([Unit]="2" , i.e., all clients from Unit 2) the combo box lists clients from ALL units, not just Unit 2.

... what am I missing, and can I just use variable in this code instead of "Me!txtVar!value " ?

[HansV]

I am confused by your description. Where does the variable come from?

If the combo box does not display the records you expect, it may have to be requeried, but you don't provide enough information to suggest where that should be done.

[Pat97orbust]

Variable is established when user first logs onto application. Excerpt from standard module:
***********
public sCritfield as String, sClientArea as string, sFilter as String

sCritField = r!criteriafield
sClientArea = r!ClientArea

Select Case sCritField
Case "unit"
sCBOrecords = "Unit=" & Chr(34) & sClientArea & Chr(34)
**********
sFilter is used when reports open to ensure that user can see only appropriate records.
Trying to use variable in combo box has not worked.

[HansV]

You treat Unit as a text field here. Is that correct?

The single quotes and square brackets before and after Me!txtVar!value will cause a problem:

Me.cboSomething.RowSource = "SELECT [qry_LKUPunitClients].[RegNo], [qry_LKUPunitClients].[CliLName], [qry_LKUPunitClients].[CliFName], [qry_LKUPunitClients].[LivingArea] FROM [qry_LKUPunitClients] WHERE '["& Me!txtVar!value &"]' ORDER BY [qry_LKUPunitClients].[CliLName], [qry_LKUPunitClients].[CliFName]"

The result will be ... WHERE '[Unit="2"]' ORDER BY ... instead of ... WHERE Unit = "2" ORDER BY ... Try this instead:

Me.cboSomething.RowSource = "SELECT [qry_LKUPunitClients].[RegNo], [qry_LKUPunitClients].[CliLName], [qry_LKUPunitClients].[CliFName], [qry_LKUPunitClients].[LivingArea] FROM [qry_LKUPunitClients] WHERE "& sCBOrecords & " ORDER BY [qry_LKUPunitClients].[CliLName], [qry_LKUPunitClients].[CliFName]"

Note that this is a VBA instruction, I don't think you can enter this row source directly in the Properties window.

[Pat97orbust]

That did the trick. Thanks!