Passing a Parameter to a Stored Procedure From a Form CBO

F

FBueller

For the life of me, I can't get this to work. I have 2 combo box
controls. I want the value selected in the first box9cboCarId) to be
passed as a parameter to the query underlying the second combo
box(cboPrevWeightEntry). Here's what I've got.

First Combo Box Properties:
Name - cboCarId
RowSourceTyoe - Table/View/StoredProc
RowSource - qry_list_active_car#_unreceived
BoundColumn - 1

Private Sub cboCarId_AfterUpdate()
Me.cboPreviousWeightEntry.Requery
Me.cboPreviousWeightEntry.Value =
Me.cboPreviousWeightEntry.ItemData(0)
Me.txt_MatType = Me.cboPreviousWeightEntry.Column(1)
Me.txt_WeightType = Me.cboPreviousWeightEntry.Column(2)
Me.txt_Scale_Weight = Me.cboPreviousWeightEntry.Column(3)
End Sub

Second Combo Box Properties:
Name - cboPreviousWeightEntry
RowSourceTyoe - Table/View/StoredProc
RowSource - qry_SearchForPreviousRecord
BoundColumn - 1

The query for the RowSource(qry_SearchForPreviousRecord) is as follows:

ALTER PROCEDURE webaccess.qry_SearchForPreviousRecord
AS SELECT dbo.RM_Notices.Car#, dbo.RM_Scale_Readings.Material,
dbo.RM_Scale_Readings.Gross_Tare_Wt, dbo.RM_Scale_Readings.Weight,
dbo.RM_Scale_Readings.Location, dbo.RM_Scale_Readings.Date_Time,
dbo.RM_Notices.[Complete?]
FROM dbo.RM_Notices INNER JOIN
dbo.RM_Scale_Readings ON dbo.RM_Notices.NoticeID =
dbo.RM_Scale_Readings.NoticeID
WHERE (dbo.RM_Notices.Car# =
N'Forms!frm_scale_house!Me.cbo_car_truck_num') AND
(dbo.RM_Notices.[Complete?] <> 1)

If I remove the parameter reference to the form cbo and make the
parameter @carID, I am prompted for the car ID and the text controls
populate as expected. But if I try to pass the form parameter to the
stored proc, I get nothing.

Can anyone help me? This is so frustrating!!
 
S

Sylvain Lafontaine

First, using any other schema than dbo can lead to trouble under ADP.

Second, the easiest way is simply to set the rowsource of the control to an
EXEC statement that call your stored procedure:

MyComboBox.Rowsource = "EXEC webaccess.qry_SearchForPreviousRecord N'" &
Forms!frm_scale_house!Me.cbo_car_truck_num & "'"

Notice the absence of parenthesis around the parameters. You can also use a
"Select * From ..." statement. There is no need to call the .Requery method
after setting the RowSource.

Another possibility would be to use the InputParameters property of the form
but this is more complicated. However, this require that all parameters used
in the InputParameters property are also used for the RecordSource of the
form (something wich is always true in my case because I always want the
Record source to have the same filtering as all the comboboxes; however,
adding some unused parameters to the SP used as the form's RecordSource
should no be a big deal).

You can also try to give the combobox the same name as the parameter
("@cboID", including the @ if I remember correctly) but I had so much
trouble with this method in the past that I don't use it any longer.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


FBueller said:
For the life of me, I can't get this to work. I have 2 combo box
controls. I want the value selected in the first box9cboCarId) to be
passed as a parameter to the query underlying the second combo
box(cboPrevWeightEntry). Here's what I've got.

First Combo Box Properties:
Name - cboCarId
RowSourceTyoe - Table/View/StoredProc
RowSource - qry_list_active_car#_unreceived
BoundColumn - 1

Private Sub cboCarId_AfterUpdate()
Me.cboPreviousWeightEntry.Requery
Me.cboPreviousWeightEntry.Value =
Me.cboPreviousWeightEntry.ItemData(0)
Me.txt_MatType = Me.cboPreviousWeightEntry.Column(1)
Me.txt_WeightType = Me.cboPreviousWeightEntry.Column(2)
Me.txt_Scale_Weight = Me.cboPreviousWeightEntry.Column(3)
End Sub

Second Combo Box Properties:
Name - cboPreviousWeightEntry
RowSourceTyoe - Table/View/StoredProc
RowSource - qry_SearchForPreviousRecord
BoundColumn - 1

The query for the RowSource(qry_SearchForPreviousRecord) is as follows:

ALTER PROCEDURE webaccess.qry_SearchForPreviousRecord
AS SELECT dbo.RM_Notices.Car#, dbo.RM_Scale_Readings.Material,
dbo.RM_Scale_Readings.Gross_Tare_Wt, dbo.RM_Scale_Readings.Weight,
dbo.RM_Scale_Readings.Location, dbo.RM_Scale_Readings.Date_Time,
dbo.RM_Notices.[Complete?]
FROM dbo.RM_Notices INNER JOIN
dbo.RM_Scale_Readings ON dbo.RM_Notices.NoticeID =
dbo.RM_Scale_Readings.NoticeID
WHERE (dbo.RM_Notices.Car# =
N'Forms!frm_scale_house!Me.cbo_car_truck_num') AND
(dbo.RM_Notices.[Complete?] <> 1)

If I remove the parameter reference to the form cbo and make the
parameter @carID, I am prompted for the car ID and the text controls
populate as expected. But if I try to pass the form parameter to the
stored proc, I get nothing.

Can anyone help me? This is so frustrating!!
Click to expand...
 
A

aaron.kempf

you would just need to call the textbox as carID not @carID.. right?

I frequently use a form/subform in this situation where I use
LinkMasterFields and LinkChildFields

I think that is a pretty elegant solution

-Aaron

First, using any other schema than dbo can lead to trouble under ADP.

Second, the easiest way is simply to set the rowsource of the control to an
EXEC statement that call your stored procedure:

MyComboBox.Rowsource = "EXEC webaccess.qry_SearchForPreviousRecord N'" &
Forms!frm_scale_house!Me.cbo_car_truck_num & "'"

Notice the absence of parenthesis around the parameters. You can also use a
"Select * From ..." statement. There is no need to call the .Requery method
after setting the RowSource.

Another possibility would be to use the InputParameters property of the form
but this is more complicated. However, this require that all parameters used
in the InputParameters property are also used for the RecordSource of the
form (something wich is always true in my case because I always want the
Record source to have the same filtering as all the comboboxes; however,
adding some unused parameters to the SP used as the form's RecordSource
should no be a big deal).

You can also try to give the combobox the same name as the parameter
("@cboID", including the @ if I remember correctly) but I had so much
trouble with this method in the past that I don't use it any longer.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


FBueller said:
For the life of me, I can't get this to work. I have 2 combo box
controls. I want the value selected in the first box9cboCarId) to be
passed as a parameter to the query underlying the second combo
box(cboPrevWeightEntry). Here's what I've got.

First Combo Box Properties:
Name - cboCarId
RowSourceTyoe - Table/View/StoredProc
RowSource - qry_list_active_car#_unreceived
BoundColumn - 1

Private Sub cboCarId_AfterUpdate()
Me.cboPreviousWeightEntry.Requery
Me.cboPreviousWeightEntry.Value =
Me.cboPreviousWeightEntry.ItemData(0)
Me.txt_MatType = Me.cboPreviousWeightEntry.Column(1)
Me.txt_WeightType = Me.cboPreviousWeightEntry.Column(2)
Me.txt_Scale_Weight = Me.cboPreviousWeightEntry.Column(3)
End Sub

Second Combo Box Properties:
Name - cboPreviousWeightEntry
RowSourceTyoe - Table/View/StoredProc
RowSource - qry_SearchForPreviousRecord
BoundColumn - 1

The query for the RowSource(qry_SearchForPreviousRecord) is as follows:

ALTER PROCEDURE webaccess.qry_SearchForPreviousRecord
AS SELECT dbo.RM_Notices.Car#, dbo.RM_Scale_Readings.Material,
dbo.RM_Scale_Readings.Gross_Tare_Wt, dbo.RM_Scale_Readings.Weight,
dbo.RM_Scale_Readings.Location, dbo.RM_Scale_Readings.Date_Time,
dbo.RM_Notices.[Complete?]
FROM dbo.RM_Notices INNER JOIN
dbo.RM_Scale_Readings ON dbo.RM_Notices.NoticeID =
dbo.RM_Scale_Readings.NoticeID
WHERE (dbo.RM_Notices.Car# =
N'Forms!frm_scale_house!Me.cbo_car_truck_num') AND
(dbo.RM_Notices.[Complete?] <> 1)

If I remove the parameter reference to the form cbo and make the
parameter @carID, I am prompted for the car ID and the text controls
populate as expected. But if I try to pass the form parameter to the
stored proc, I get nothing.

Can anyone help me? This is so frustrating!!
Click to expand...
Click to expand...
 
Top