Limit field values with a combobox on a subform

[svdoerga]

Hello!

I have a question I've been working on for a few days now and I can't find a solution. I hope there is someone that has the answer :)

I'm using Access 2003 in win XP. I have a main form called "MulitLineView", that has a subform called "subLinelistView". In the main form's "Form_Open" event I use a query to open the contents of a table for editing in the subform in datasheet mode like this:

Set qdf = db.CreateQueryDef(strQueryName, strQuery)
subLinelistView.SourceObject = "Query." & strQueryName

This all works fine, the subform shows the proper fields. I'm also applying locks to fields (using a combobox which I hide) with:

Forms("MultiLineView").Controls("subLinelistView").Form.Controls(cmbMayNotEdit.Column(0, intCounter)).Properties("Locked") = True

This works fine too.

My question is: How can I make it so that for some columns, the values which can be entered in the field are limited by a combobox?
The rowsources for the comboboxes should be queries which I want to provide in the code for each column. I've been trying to find the right control property, I tried things like:

strQuery = "SELECT plant_abbr FROM Plants"
Forms("MultiLineView").Controls("subLinelistView").Form.Controls("Plant_abbr").RowSource = strQuery

and

Forms("MultiLineView").Controls("subLinelistView").Form.Controls("Plant_abbr").Properties("RowSource") = strQuery

but in runtime I always get an error msg saying the object doesn't have that property.

Can anyone help me pull this off? Any help is greatly appreciated! :)

[puppydogbuddy]

My question is: How can I make it so that for some columns, the values which can be entered in the field are limited by a combobox?[/u]
The rowsources for the comboboxes should be queries which I want to provide in the code for each column. I've been trying to find the right control property, I tried things like:

strQuery = "SELECT plant_abbr FROM Plants"
Forms("MultiLineView").Controls("subLinelistView").Form.Controls("Plant_abbr").RowSource = strQuery

and

Forms("MultiLineView").Controls("subLinelistView").Form.Controls("Plant_abbr").Properties("RowSource") = strQuery

but in runtime I always get an error msg saying the object doesn't have that property.

Can anyone help me pull this off? Any help is greatly appreciated! :)

try this syntax, assuming plan_abbr is your combo box:
see this link:
http://www.mvps.org/access/forms/frm0031.htm

Forms!MultiLineView!subLinelistView.Form![Plant_abbr].RowSource = strQuery

Or better:

Me!subLinelistView.Form![Plant_abbr].RowSource = strQuery

[svdoerga]

try this syntax, assuming plan_abbr is your combo box:
see this link:
http://www.mvps.org/access/forms/frm0031.htm

Forms!MultiLineView!subLinelistView.Form![Plant_abbr].RowSource = strQuery

Or better:

Me!subLinelistView.Form![Plant_abbr].RowSource = strQuery

Thanks for your reply!
I tried the syntax you provided, but I get the same message. My Access is in Dutch but the message should translate to: "This property or method is not supported by this object".

Maybe the problem lies here: I'm opening the query in the subform that is blank. I haven't put any controls in the subform, because I'm using a query as sourceobject. This query is generated is run-time and can be different every time, since I've made it so that the user is able to select which columns to view.
The query I use is:

strQuery = "SELECT " & strColumns & " FROM " & CurLinelistTN & IIf(Forms!MainMenuNew.cmbSelectPlant.Value = "All", "", " WHERE [Plant_abbr] = '" & Forms!MainMenuNew.cmbSelectPlant.Value & "' ") & IIf(strSortBy = "", "", " ORDER BY " & strSortBy & "") & ";"

This query works fine though, it shows what it's supposed to every time.

But I don't know what kind of controls are created for a query in datasheet view. Is there a way to control this? I've tried to get the controltype with:

strControlType = Me!subLinelistView.Form![Plant_abbr].ControlType

But that just returns a "116" every time with any column I put in in the place of "Plant_abbr". I don't know what that '116' means. As far as I have been able to find out, the "ControlType" property should return something like "acTextBox" or "acComboBox".

Is there any way that I can choose what type of controls are created to show the fields when I open the query? So that I can have comboboxes for certain fields, where I can set the rowsource?

[puppydogbuddy]

What is Plant_abbr.....a textbox? and is it on the main form or the subform?

If you are executing your code from the subform:
Change this:
strControlType = Me!subLinelistView.Form![Plant_abbr].ControlType

To this:
strControlType = Me![Plant_abbr].ControlType


And re this:

strQuery = "SELECT " & strColumns & " FROM " & CurLinelistTN & IIf(Forms!MainMenuNew.cmbSelectPlant.Value = "All", "", " WHERE [Plant_abbr] = '" & Forms!MainMenuNew.cmbSelectPlant.Value & "' ") & IIf(strSortBy = "", "", " ORDER BY " & strSortBy & "") & ";"

You can refer to columns this way ( I used column 0 for example) :

strQuery = "SELECT " & strColumns & " FROM " & CurLinelistTN & IIf(Forms!MainMenuNew.cmbSelectPlant.Value = "All", "", " WHERE [Plant_abbr] = '" & Forms!MainMenuNew.cmbSelectPlant.Column(0) & "' ") & IIf(strSortBy = "", "", " ORDER BY " & strSortBy & "") & ";"

[svdoerga]

Plant_abbr is a column name. It's one of the columns that can be selected in the query that is displayed in the subform. I'm running all code from the main form. The subform is completely blank, it doesn't have anything on it. I just use it to open the query on. But we can forget about the big query, I just showed it to explain that the query I display in the subform has a lot of variables, but that's all working fine. Maybe I elaborated too much by showing that.

I think the syntax is OK, it's just that I dont' know which properties to use. I know that I'm getting to the controls of the query that's opened in the subform, since I can lock columns.

Maybe I can explain it better with a simplified example. Let's forget all the above and go with this: I have a table called "Linelist_current" which has 10 columns of data. I have a main form "MultiLineView", which has a subform "subLinelistView". The subform is completely blank, nothing on it. All code is run from "MultiLineView". Let's say I do the following:

Dim db as DAO.Database
Dim qdf as DAO.QueryDef
Dim strQueryName, strQuery as String
Set db = currentDb
strQueryName = "MyLinelistView"
strQuery = "SELECT IDLine, Plant_abbr, LineSize FROM Linelist_current WHERE Plant_abbr = 'POLY';"
Set qdf = db.CreateQueryDef(strQueryName, strQuery)
subLinelistView.SourceObject = "Query." & strQueryName
Me!subLinelistView.Form.Controls("IDLine").Locked = True

Now I have opened the query on the subform in datasheet view. I see 3 columns with data: IDline, Plant_abbr, and LineSize. I can edit everything except for the IDLine column, since I locked that one. Now I want to make sure that the values that can be entered in the "LineSize" column are limited by a combobox. The source of this combobox has to be a table called "Size", that has just 1 column with numbers. I would want to do something like:

Me!subLinelistView.Form.Controls("LineSize").RowSourceType = "Table/Query"
Me!subLinelistView.Form.Controls("LineSize").RowSource = "SELECT * FROM Size"

But this doesnt work, I get the error message saying that the property or method is not supported by the object.

How do I go about making a combobox for the "LineSize" column?

[puppydogbuddy]

Here is a basic example of the way I set up my comboboxes in code:

Dim cboType As ComboBox
 
Set cboType = Me!cboLineSize
        With cboType
            .RowSourceType = "Table/Query"
            .RowSource = "SELECT *  From Size"
        End With
 
Me!cboLineSize.Locked = True
 
Set cboType = Nothing

[svdoerga]

First of all, thank you for your replies I appreciate your effort to help me!
Maybe I haven't been clear on this. This form is part of an access application that has around 30 users, editing a table that has around 64 columns (no..it can not be normalized :p). Each user is able to customize a "view" of the table, specifying which columns to view (anywhere from 1 to 64), in which order, and by which columns to sort by. I use these views, which are stored in a table, to create the queries that get data from the table, at run-time, and I delete the query when the form closes. So I'm dealing with the results of a temporary query. Because the query is different every time, I don't use a form with a set query as recordsource, but I assign the results to a blank subform. So I'd like to know how I can change the properties of this query result, to set a rowsource for certain columns, so that they become comboboxes.
Is there some way to link a combobox created in code to the query results?

[puppydogbuddy]

You have complete flexibility to set the combobox properties in the example I gave you above. Here is a more detailed example excerpted from one of my applications. In the example below, a specific limited group of accounts is displayed for selection in a subform combobox depending on the the report and account that the user selected from 2 comboboxes on the main form. The code below is used in conjunction with a button click event that makes the combobox visible. There are many ways to do it ....you have complete control over which properties you want to set for the combobox. this is the way I chose to do it for my application.

Private Sub Form_Open(Cancel As Integer)    ' subform open event
On Error GoTo Error_Routine
 
        'Hide the reclass combo box until reclass button clicked (button click event)
        Me!cboAcctClassX.Visible = False
 
        'deselect any checkboxes that are ticked (initialize all checkboxes as unchecked)
        btnDeselect_Click
 
        Set cboType = Me!cboAcctClass
        With cboType
            .RowSourceType = "Table/Query"
            If Parent![cboReport].Column(2) = "XXXReportXXXX" Then
                Select Case Parent!cboReport.Column(1)
                    Case 20, 22            'balance sheet reports                               
                        .RowSource = "SELECT * From OneofMyTables"  
Case Else                                  'revenue & expense reports
                        .RowSource = "SELECT * From AnotherOneOfMyTables" 
                End Select
            End If
        End With
 
Set cboType = Nothing
 
Me!cboAcctClass.Locked = True
 
Exit_Continue:
        Exit Sub
Error_Routine:
        MsgBox "Error# " & Err.Number & " " & Err.Description
        Resume Exit_Continue
 
End Sub

[svdoerga]

I didn't your previous post well enough the last time, my apologies! That's because I'm so stressed out and tired from trying so many things haha. So I tried your example. After opening the query I create a combobox, asssign a column to it, then set rowsourcetype and rowsource ("Plant_abbr" is the name of a column) :

Dim cmbTest As ComboBox
Set cmbTest = Me!subLinelistView.Form![Plant_abbr]
With cmbTest
    .RowSourceType = "Table/Query"
    .RowSource = "SELECT Plant_abbr FROM Plants"
End With

But at this line: .RowSourceType = "Table/Query" I get an error message saying something like "the expression contains an invalid referral to the RowSourceType property". I'm running this code form "MultiLineView" (the parentform of "subLinelistView")

I wonder what kind of controls are created when the query opens in the subform. Because to me it sounds like it's saying that the control for the "Plant_abbr" column is not a combobox. Is there any way to control that? Or maybe I have the syntax wrong?

[puppydogbuddy]

I didn't your previous post well enough the last time, my apologies! That's because I'm so stressed out and tired from trying so many things haha. So I tried your example. After opening the query I create a combobox, asssign a column to it, then set rowsourcetype and rowsource ("Plant_abbr" is the name of a column) :

Dim cmbTest As ComboBox
Set cmbTest = Me!subLinelistView.Form![Plant_abbr]
With cmbTest
    .RowSourceType = "Table/Query"
    .RowSource = "SELECT Plant_abbr FROM Plants"
End With

But at this line: .RowSourceType = "Table/Query" I get an error message saying something like "the expression contains an invalid referral to the RowSourceType property". I'm running this code form "MultiLineView" (the parentform of "subLinelistView")

I wonder what kind of controls are created when the query opens in the subform. Because to me it sounds like it's saying that the control for the "Plant_abbr" column is not a combobox. Is there any way to control that? Or maybe I have the syntax wrong?

Your syntax is wrong. Based on the above info and your prior postings your combobox is named cmbSelectPlant and is on the subform, thus your code should be launched from the <<subform>> and should look something like this:

Dim cmbTest As ComboBox
Set cmbTest = Me!cboSelectPlant
With cmbTest
    .RowSourceType = "Table/Query"
    .RowSource = "SELECT Plant_abbr FROM Plants"
End With

Now the above query just displays the Plant_abbr column. If you wanted to be able to switch the row source based on some identifier in the main form, you can do that using an if else endif construct (see below) or a select case construct as I showed you in my previous post.

Dim cmbTest As ComboBox
Set cmbTest = Me!cboSelectPlant
With cmbTest
    .RowSourceType = "Table/Query"
     If Parent.[txtbox1].Value =  xxx then
                .RowSource = "SELECT Plant_abbr FROM Plants"
     Elseif Parent.[txtbox1].Value =  xxx then
                .RowSource =  "Select XXXXXXXXXXXXXXXX "
     Else
                .RowSource =  "Select XXXXXXXXXXXXXXXX "
     End If
End With

.

[svdoerga]

I have found out from some other people that what I'm trying to do is impossible, because I'm trying to set the properties of a query result in datasheet view. There are only a few things that can be changed I'm told. Can't add comboboxes and change backcolors with it.
So I have started again but this time I have made a form with a query that opens all the columns of the table as rowsource. Then I hide the columns I do not wish to view. Locking works, changing backcolor works, but now I only need to find out how to set column order and how to order by certain columns. I'm currently wrestling with the "ColumnOrder" property to get the columns in the proper order. I still have some things I need to try, if I get stuck somewhere you might see another post from me hehe

Thanks alot for all your help so far, I appreciate it.

[puppydogbuddy]

I have found out from some other people that what I'm trying to do is impossible, because I'm trying to set the properties of a query result in datasheet view. There are only a few things that can be changed I'm told. Can't add comboboxes and change backcolors with it.
So I have started again but this time I have made a form with a query that opens all the columns of the table as rowsource. Then I hide the columns I do not wish to view. Locking works, changing backcolor works, but now I only need to find out how to set column order and how to order by certain columns. I'm currently wrestling with the "ColumnOrder" property to get the columns in the proper order. I still have some things I need to try, if I get stuck somewhere you might see another post from me hehe

Thanks alot for all your help so far, I appreciate it.

You are welcome. I can't confirm about dataSheet view, but the code I gave you works in form view because I use it all the time. As far as ordering the columns in your query, you can use an "order by" clause in sql view or go to the query grid and place the columns in the order you want them and then select the type of sort from the sort row.