Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Posts
    7

    Unanswered: Vb variable inside a comboboxs' rowsource sql

    Hi all.

    I am very new to access and this is my first bit of vb so please bear with me

    I have two tables set up.

    tblMaterial has the fields MaterialID (Primary key), Material, Spec and grouping. grouping is set up as a lookup (column count 2, column width 0cm) of tblgrouping which has the fields groupingID (Primary key) and grouping.

    I am trying to create two forms. The first form (frmGroupings) has a combobox with groupings on and a button. I want the user to select one of the groupings, say this is called ComboValueGrouping from the combobox and press the button. I want this to open a second form (frmChoose) again with a combobox but with this second comboboxes' rowsource equal to the following sql:

    SELECT tblMaterial.MaterialID, tblMaterial.Material, tblMaterial.Spec, tblMaterial.Grouping FROM tblMaterial WHERE (((tblMaterial.Grouping)=ComboValueGrouping)) ORDER BY tblMaterial.Spec;"

    From my understanding, if a create a variable to pull the id number from the grouping that has been seleced in the first form's combobox (called cboGrouping), open the second form up and then update the comboboxes' (called cboMaterial) rowsource to this sql, it should work. Therefore I attached the following code to the button on The first form (frmGroupings)

    Dim FormMaterial As String
    FormMaterial = "frmChoose"
    Dim FormGroupings As String
    FormGroupings = "frmGroupings"
    Dim ComboValueGrouping As String
    ComboValueGrouping = Forms(FormGroupings)![cboGrouping].Column(0)
    DoCmd.OpenForm FormMaterial
    Forms(FormMaterial)![cboMaterial].RowSource = "SELECT tblMaterial.MaterialID, tblMaterial.Material, tblMaterial.Spec, tblMaterial.Grouping FROM tblMaterial WHERE (((tblMaterial.Grouping)=" & "ComboValueGrouping" & ")) ORDER BY tblMaterial.Spec;"

    So i want this to look at the first combobox and get the id number from the grouping and put this number in the correct position in the rowsource of the second combobox, but it doesn't work. Access asks the user to enter a parameter value (this puzzles me as I thought the code above would tell access to use the value pulled from the first combobox as the parameter value). Typing in a number does work as expected but I wanted it to do it automatically. I would greatly appreciate any help you could give, I think I am very close, just need the last step(s). If I have explained this badly please tell me.

    Thanks in advance.

    Peter.

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    No need to put everything in variables

    ComboValueGrouping = Forms!MyFormName!MyComboOrListBoxName.column(1)

    Once you get the value, it's probably easiest to just set the rowsource of the 2nd box by setting it to the same query name used in the first combobox (or save a separate one (ie. query) which shows the value(s) you need. Then you just set the sourceobject value in code to: Forms!MyFormName!MyCombobox.rowsource ="NewQueryName"

    You can also set criteria in the 2nd combobox rowsource to be dependant upon a value being a certain field that matches the values in the first combobox. That way, when the 1sr combobox is selected, the 2nd combobox will requery to show the records that match the criteria from the 1st combobox. To do this, in the rowsource of the 2nd combobox, for the field which contains the same values as the first column combobox put in criteria : =Forms!MyFormName!MyFirstCombobox.
    Last edited by pkstormy; 06-26-09 at 00:57.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jun 2009
    Posts
    7
    Thanks for your help but I dont really understand - do you mean create a different query for each grouping? - I have attached a rough idea of what i am trying to do.

    if you open up frmGroupings and click on any of the values in the list and then press select I want it to get the id number from column zero and put this in the part of the record source automatically, but for some reason it doesn't get the value.

    For example choosing seven should give 'never' and 'rec' because the seven in the grouping table is assigned to 'never' and 'rec in the material table.

    This does work but it needs manual input. I want it to do this

    1) open up frmGroupings
    2) click seven on the list and click select..
    3) ... which opens up the form frm choose with the list containing only 'never' and 'rec' (see tables)

    At the moment this happens

    1) open up frmGroupings
    2) click seven on the list and click select..
    3) ... which opens up an 'enter parameter value' box with combovaluegrouping on it.
    4) typing 1 for this value and pressing ok...
    4) ... opens up the form frm choose with the list containing only 'never' and 'rec' (see tables)

    Thanks
    Attached Files Attached Files

  4. #4
    Join Date
    Jun 2009
    Posts
    7
    anyone any other thougths?

  5. #5
    Join Date
    Sep 2006
    Posts
    265
    This what I with Lookups as a Function
    Code:
    Function LookupMaterial()
    
        With Screen.ActiveControl
            .RowSource = "SELECT tblMaterial.MaterialID, tblMaterial.Material, tblMaterial.Spec, tblMaterial.Grouping FROM tblMaterial WHERE tblMaterial.Grouping= '" & .[ComboValueGrouping] & "' ORDER BY tblMaterial.Spec;"
        End With
    
    End Function
    After selecting ComboValueGrouping Requery [cboMaterial] to reflect the ComboValueGrouping

    Simon

  6. #6
    Join Date
    Jun 2009
    Posts
    7
    Hi all,

    Thanks for your help but I have now sorted it. I did two stupid things -

    1) in the rowsource of the cboMaterial box, I put [ComboValueGrouping] in the criterior when i was working out how to do what I wanted.

    SELECT tblMaterial.MaterialID, tblMaterial.Material, tblMaterial.Spec, tblMaterial.Grouping FROM tblMaterial WHERE (((tblMaterial.Grouping)=[ComboValueGroupings])) ORDER BY tblMaterial.Spec;

    The problem I was getting was cause by me not changing the rowsource back to blank it after I had written the vb code.

    2) I also put quotation marks around ComboValueGroupings by mistake - see part of coding:

    Forms(FormMaterial)![cboMaterial].RowSource = "SELECT tblMaterial.MaterialID, tblMaterial.Material, tblMaterial.Spec, tblMaterial.Grouping FROM tblMaterial WHERE (((tblMaterial.Grouping)=" & "ComboValueGrouping" & ")) ORDER BY tblMaterial.Spec;"

Posting Permissions

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