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!!

 

[Marbles]

This might be worth a try.

If all the tables have the same field names, you could have one form based on the tables.
Then create a combo box in the header which has a value list containing the names of all your tables.

In the After Update Event of this combo box, this code seems to work in a practice I've tried:

Private Sub WhichTable_AfterUpdate()
Me.Form.RecordSource = Me.WhichTable
Me.Label1.Caption = Me.WhichTable
End Sub

WhichTable is the name of the combo box.

You can put the most commonly used table as the recordsource for when the form opens first.

 

[mistera]

Ok, I decided that it would be best to just have one table with all of the codes in it. However, now I'm having an issue with the SQL statement. Can anyone tell what's wrong with it? I simplified the number of cases below for ease of reading. By the way, how do you put code in this message the way you did?

Select Case Me!opt_Reference
Case 1
strSQL = "SELECT tbl_CodeLookup.Code, tbl_CodeLookup.CodeDesc " & _
"From tbl_CodeLookup WHERE (((tbl_CodeLookup.CodeType)='CmdtyClass'));"
strForm = "frm_CodeUpdate"
Case 2
strSQL = "SELECT tbl_CodeLookup.Code, tbl_CodeLookup.CodeDesc " & _
"From tbl_CodeLookup WHERE (((tbl_CodeLookup.CodeType)='CompCtr'));"
strForm = "frm_CodeUpdate"
Case Else
MsgBox "A reference must be selected.", , "Select Reference"
GoTo Exit_btn_Continue_Click
End Select

DoCmd.OpenForm strForm, , , , , , strSQL

 

[SydneyGeek]

It looks like you're passing the SQL string to the form using OpenArgs.
In the form you will need some code in the Open event along the lines of

If Len(Me.OpenArgs)>0 Then 
  Me.Recordsource = Me.OpenArgs
End If

To place code in your post, wrap it in CODE tags. {CODE} and {/CODE} (use square brackets instead of braces)

Denis

 

[mistera]

That worked perfectly!

I'm still a novice at this. Is there a way to pass a string to the new form that will update the caption on a label on the form? So, for example, in the Select Case statement, I could include setting a string variable to something like "Commodity Class". Then I want "Commodity Class" to show up in the label when I open the form.

 

[SydneyGeek]

You can use OpenArgs to pass more than one parameter. For example:
In the first form,

DoCmd.OpenForm strForm, , , , , , strSQL & "|" & strCaption

Where strCaption is the text that you want to use for the label

In the second form, use this for the Open event:

Dim strRS as String
Dim strLabel as String
If Len(Me.OpenArgs)>0 Then
  strRS = Left (Me.OpenArgs,Instr(1,Me.OpenArgs,"|")-1)
  strLabel = Mid(Me.OpenArgs,Instr(1,Me.OpenArgs,"|")+1)
  Me.RecordSource = strRS
  Me.SomeLabel.Caption = strLabel
End If

Denis

 

[mistera]

Awesome! Works like a charm! Thanks so much for your help.