One form multiple source

[CEH]

Working on a small DB, one form has 4 combo boxes to pick information. This will be choices, in each combo, that the users adds from another form. So what I am trying to do is use the same form for each combo but changing the source.. Something like this... Should do that...
Public Sub sFormData(intOption As Integer)

Dim strRS As String 'strRS can be a Query or Table

DoCmd.OpenForm "frmTest" 'Change to your form

Select Case intOption
Case 1
strRS = "qry1"
Case 2
strRS = "qry2"
Case 3
strRS = "qry3"
End Select

Forms!frmTest.Form.RecordSource = strRS 'Change Form Name

End Sub

But... The problem is this doesn't change the recordsource of the 2 textboxes on the form. One the ID, one the description.
So when you open the form with say "option2" The texboxes still have the same source as they did in "Option1"
I know I can't be the first to use this...but can't locate any examples.

 

[pbaldy]

If you're saying the fields would change, you'd have to change the control sources of the textboxes. I've done that in reports, where the sorting/grouping fields change based on user selections.

 

[Galaxiom]

Are you quite sure the first record in both Record Source options don't have the same ID and Description so it just looks like nothing changes?

 

[CEH]

Paul, I think that is correct. So I would need to add that change to each "Case" statement? eg.....
Case 1
strRS = "qry1"
Textbox1ID.controlsource = WhateverfieldID
Textbox2.controlsource = Whateverfield2

Using whatever field is neede from "qry1" ??

 

[pbaldy]

Yes, I've done it in the open event of the report:

Private Sub Report_Open(Cancel As Integer)
  Select Case Forms!frmReports.fraGrouping
    Case 1
      Me.GroupLevel(0).ControlSource = "CarType"
      Me.txtGroup.ControlSource = "CarDesc"
    Case 2
      Me.GroupLevel(0).ControlSource = "Company"
      Me.txtGroup.ControlSource = "Company"
    Case 3
      Me.GroupLevel(0).ControlSource = "DispDateTime"
      Me.txtGroup.ControlSource = "DispDateTime"
      Me.GroupHeader0.Visible = False
  End Select
End Sub

 

[CEH]

Thanks Paul,
I'll give it a shot.