Need Combo Box to match form Record Source results

[CSDunn]

Hello,
I have a form called 'frmRCMain' in which the Record Source is an MS SQL
Server 2000 View. The View does not use a 'WHERE' statement to narrow down
the records, but the form opens by clicking a button on another form with
the onClick event set up as follows:

Private Sub Command18_Click()
DoCmd.OpenForm "frmRCMain", , , "TeacherID=" & TeacherID
End Sub

By doing this, the records returned in 'frmRCMain' are based on the specific
'TeacherID' value.

I have a combo box on 'frmRCMain' in which its Row Source uses three fields
from the same View that the 'frmRCMain' uses. The thing I need is for the
combo box to limit its records the same kind of way that 'frmRCMain' does so
that the combo box values are in line with the 'TeacherID' value passed to
'frmRCMain'.

I suppose I could set up a Stored Procedure, pass a 'TeacherID' parameter to
it, and have the combo box show records based on the results. If I should do
it that way, how would I proceede, or is there some other way I could have
the combo box reflect the correct values?

Thanks!

CSDunn

 

[Karen Vaudreuil]

Hi,

You could use the OpenArgs property to pass the TeacherID and use it to
generate a adhoc query as rowsource for your combo box.

From the calling form:

DoCmd.OpenForm "frmRCMain", , , "TeacherID=" & TeacherID, , , TeacherID

From frmRCMain:
Private Sub Form_Open...
me.myCombo.Rowsource = "SELECT Something FROM Somewhere WHERE TeacherID
= " & me.OpenArgs

You could also use the OpenArgs to create the recordsource of your form...

Karen

 

[Vadim Rapp]

C> I have a combo box on 'frmRCMain' in which its Row
C> Source uses three fields from the same View that
C> the 'frmRCMain' uses. The thing I need is for the
C> combo box to limit its records the same kind of way
C> that 'frmRCMain' does so that the combo box values
C> are in line with the 'TeacherID' value passed to
C> 'frmRCMain'.


Besides what Karen said, there's another interesting way. For the combobox,
specify row source type=Table/View/StoredProc, but leave Row Source empty.
Then put the following code in your frmRCMain form:

Private Sub Form_Open(Cancel As Integer)
Set orderid.Recordset = Me.RecordsetClone
End Sub

Specify the columns to see in the combobox using its Column Widths property.

Vadim

 

[CSDunn]

Thanks for your help, I will try these!
CSDunn