Change form properties on open

[mistera]

Okay, I've been spinning my wheels for a while on something that I'm sure is probably quite simple.

I have multiple tables, each of which has two fields -- a code and its description. Each table houses a different type of code. For example, tbl_State associates a state code (say, IL) to a state description (Illinois) and tbl_Country where there is a country code associated to the name of the country.

I have two forms. Let's call them frm_SelectCodeType and frm_CodeList. The first form, frm_SelectCodeType, includes an option group. Each option identifies one of the code tables. The other form, frm_CodeList, is a generic list-type form to display the records in a selected table. This form includes the code field and the description field.

When an option from frm_SelectCodeType is selected, I want to open frm_CodeList, where a label caption is changed to identify the table type (i.e. "State" vs. "Country") and the recordsource is changed to grab the data from the appropriate table.

I was trying to create code using the Select Case and interrogating the option value. However, I'm not sure where to put the VBA code -- in a button click event procedure frm_SelectCodeType to open frm_CodeList or in a form open event procedure on frm_CodeList. I also don't know how to properly code this.

PLEASE HELP!!

 

[xenou]

I'm sure there's many ways to go about this but I'd probably have a command button, hence:

1) User selects option desired in option group
2) User clicks button
3) Second form opens with desired options applied

To open the second form with the correct option applied, see if you can use the DoCmd "open" method. This will give you the option to set a "WHERE" criteria - the where criteria will come from your option group.

Very Rough Example
--based on http://msdn.microsoft.com/en-us/library/bb243764.aspx
--report example - but concept applies to forms
--not sure about how to indicate text but here I've put text in single quotes - experiment if it doesn't work (if you have numbers as filter criteria, easier!)

'//GET OPTIONGROUPVALUE
Select Case OptionGroupValue
    Case 1
        myID = "'IL'"
    Case 2
        myID = "'MI'"
    Case 3
        myID = "'Show All'"
End Select

'//OPEN REPORT
If myID = ShowAll Then
    DoCmd.OpenReport "Invoice", acViewPreview
Else
    DoCmd.OpenReport "Invoice", acViewPreview, , "[OrderID] = " & myID
End If

Hope this helps - I've probably mucked this up somewhere but maybe it will give you a hint where to go...

ξ

 

[Bill Rockenbach]

Two methds, 1 using one userform and the other using two user forms
Need more info to give you an exact solution.

One userform method:
1. Setup a two column list of code types and name it “CodeType” Colums are: Code type and Name of Code List
2. Set up two column lists of Codes and name them “State”, ”Country”, etc
3. Create a userform with two labels named lbCodeType and lbCodeList
4. Setup two combo boxes named cbCodeType and cbCodeList
3. Set the cbCodeType RowSource property to “CodeType” (without the quotes)
4. Set the lbCodeType lable to “Code Type” (without the quotes)
5. Set the lbCodeList and cbCodeList Visible properties to False
6. Set the userform Caption property to “Code Types” (without the quotes)
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Put this code in the cbCodeType_Change code module
<o></o>
When userform is opened only the Code Type controls will be visible. After a selection is made the Code Type controls are hidden and the Code List controls are visible. You can set this controls one on top of the other.
<o></o>
(You code eliminate the labels and just leave both combo boxes visible)
<o></o>
<o></o>

[FONT=Times New Roman]Option Explicit[/FONT]
[FONT=Times New Roman]<o:p></o:p>[/FONT]
[FONT=Times New Roman]Private Sub cbCodeType_Change()[/FONT]
[FONT=Times New Roman]Dim c As Range[/FONT]
[FONT=Times New Roman]On Error GoTo ErrorHandler[/FONT]
[FONT=Times New Roman]Set c = Range("CodeType").Find(cbCodeType, LookIn:=xlValues, lookat:=xlWhole)[/FONT]
[FONT=Times New Roman]cbCodeList.RowSource = c.Offset(0, 1)[/FONT]
[FONT=Times New Roman]lbCodeList = c[/FONT]
[FONT=Times New Roman]lbCodeType.Visible = False[/FONT]
[FONT=Times New Roman]cbCodeType.Visible = False[/FONT]
[FONT=Times New Roman]lbCodeList.Visible = True[/FONT]
[FONT=Times New Roman]cbCodeList.Visible = True[/FONT]
[FONT=Times New Roman]UserForm1.Caption = c.Offset(0, 1) & " List"[/FONT]
[FONT=Times New Roman]<o:p></o:p>[/FONT]
[FONT=Times New Roman]Exit Sub[/FONT]
[FONT=Times New Roman]<o:p></o:p>[/FONT]
[FONT=Times New Roman]ErrorHandler:[/FONT]
[FONT=Times New Roman]MsgBox Err.Number[/FONT]
[FONT=Times New Roman]<o:p></o:p>[/FONT]
[FONT=Times New Roman]End Sub[/FONT]

<o></o>
--------------------------------------------------------------
<o></o>
Two Userforms method
1. Setup a two column list of code types and name it “CodeType”
2. Set up two column lists of Codes and name them “State”, ”Country”, etc
<o></o>
Userform1
1. Create a userform with 1 label named lbCodeType and I combo box named cbCodeType
2. Set the cbCodeTpye RowSource property to “CodeType” (without the quotes)
3. Set the lbCodeType lable to “Code Type” (without the quotes)
<o></o>
Userform 2
1. Setup 1 label named lbCodeList and 1 combo boxe named cbCodeList

<o></o>
Place this code in the cbCodeType_Change code module of Userform1

[FONT=Times New Roman]Option Explicit[/FONT]
[FONT=Times New Roman]<o:p></o:p>[/FONT]
[FONT=Times New Roman]Private Sub cbCodeType_Change()[/FONT]
[FONT=Times New Roman]Dim c As Range[/FONT]
[FONT=Times New Roman]On Error GoTo ErrorHandler[/FONT]
[FONT=Times New Roman]UserForm1.Hide[/FONT]
[FONT=Times New Roman]UserForm2.Show[/FONT]
[FONT=Times New Roman]<o:p></o:p>[/FONT]
[FONT=Times New Roman]Exit Sub[/FONT]
[FONT=Times New Roman]<o:p></o:p>[/FONT]
[FONT=Times New Roman]ErrorHandler:[/FONT]
[FONT=Times New Roman]MsgBox Err.Number[/FONT]
[FONT=Times New Roman]<o:p></o:p>[/FONT]
[FONT=Times New Roman]End Sub[/FONT]

Place this code in the Private Sub UserForm_Initialize() code module of userform 2<o></o>

[FONT=Times New Roman]Option Explicit [/FONT]
[FONT=Times New Roman]Private Sub UserForm_Initialize()[/FONT]
[FONT=Times New Roman]Dim c As Range[/FONT]
[FONT=Times New Roman]On Error GoTo ErrorHandler[/FONT]
[FONT=Times New Roman]Set c = Range("CodeType").Find(UserForm1.cbCodeType, LookIn:=xlValues, lookat:=xlWhole)[/FONT]
[FONT=Times New Roman]cbCodeList.RowSource = c.Offset(0, 1)[/FONT]
[FONT=Times New Roman]lbCodeList = c[/FONT]
[FONT=Times New Roman]UserForm2.Caption = c.Offset(0, 1) & " List"[/FONT]
[FONT=Times New Roman]<o:p></o:p>[/FONT]
[FONT=Times New Roman]Exit Sub[/FONT]
[FONT=Times New Roman]<o:p></o:p>[/FONT]
[FONT=Times New Roman]ErrorHandler:[/FONT]
[FONT=Times New Roman]MsgBox Err.Number[/FONT]
[FONT=Times New Roman]<o:p></o:p>[/FONT]
[FONT=Times New Roman]End Sub[/FONT]

 

[mistera]

Please note that my issue is with Access, not Excel.

In response to xenou:
I'm not looking to filter anything. I want the second form to open with all records from a given table depending on the option selected. I just want to refer to a different recordsource on the form.

In response to Bill:
I think your code is in relation to lists in Excel. I don't think the solutions will work right for Access.

Any other ideas??

 

[Norie]

This is probably a crazy idea, but why not actually use a filter when you are opening the form.

 

[xenou]

[deleted]

Okay, sorry - I guess you want to open different tables? How many tables do you have? If its only a few, you could have a form for each "view", and then open the right form depending on the option chosen (i.e, option 1 - open form 1, option 2 - open form 2, etc.).

 

[xenou]

BTW, what are you doing here? You want to open up all your state or country codes in a table/form? Purpose is ...

 

[mistera]

xenou,

Yep, you're right. I actually started making a form for each "view" and found that they were all exactly the same except for the caption on a label and the recordsource. I have 15 different tables that are all basically the same. I considered having one table that included an extra field to identify which type of code the record was describing, but I already had the tables built. If I have to do it that way, I could and then I would just have the one table for the recordsource and then I would have to perform a filter depending on which option is selected. I was just hoping not to have to do that.

 

[xenou]

Hmmm ...
Seems you could pass a query to the form as an openargs:

something like:

    Dim strSQL As String
    Select Case myVal
        Case 1
            strSQL = "SELECT Field1 As Field1 FROM Table1 Order By Field1"
        Case 2
            strSQL = "SELECT Field1 As Field1 FROM Table2 Order By Field1"
        Case 3
            strSQL = "SELECT Field1 As Field1 FROM Table3 Order By Field1"
   End Select
   
   DoCmd.OpenForm "form1", acNormal, , , , , strSQL

Then in the query's open event code, use the openargs to set the recordsource.

All very untested ... if this works I'd be amazed (something like this should be possible - only thing is that your controls will need to have the same names for the fields so maybe the field aliases will get it through). I have by the way started putting all my lookups in a single table too ... just saves having lookup tables scattered about.

 

[xenou]

Maybe another idea would be to create one big union query from your 15 tables - then use a filter on the results of that union query!