Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jul 2002
    Location
    Petersburg, Virginia
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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 " ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  3. #3
    Star Lounger
    Join Date
    Jul 2002
    Location
    Petersburg, Virginia
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    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.

  5. #5
    Star Lounger
    Join Date
    Jul 2002
    Location
    Petersburg, Virginia
    Posts
    54
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    That did the trick. Thanks!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •