asked on

How to pass parameter from Access Control to SQLServer Query ?

Hi,
I am just trying to move from .mdb to .adp(SQL Server). With the help of Wizard I migrated all tables and procedures.
But in Access form controls are not working.
For example:
I have two comboboxes - combo1 and combo2
combo1 gets its data when the form opens, and according the selected value of combo1 the combo2 will be filled.
So the Query generated by Wizard is :
SELECT Combo2 FROM myData WHERE (combo1 = @Forms_myForm_combo1)

Private Sub Combo1_Change()
DoCmd.Requery "Combo2"
End Sub

But that does not work, when I select items in Combo1, Combo2 not automatically filled. It pops up a messageBox with Input.

Could anybody guide me ?

tnx

ASKER CERTIFIED SOLUTION

Vadim Rapp

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Vadim Rapp

little correction to the code above: it will work if the contents of combo1 is numeric. If it's strings, then the value will need to be enclosed in quotes:

where combo1='" & Combo1 & "'"

exceter

ASKER

Well, that seems to work.. but:
Changing Combo1 I see that values in Combo2 are changed, but the rows are empty, but there are number of rows according to selected Combo1. My query returns 2 columns, I tried to change "Bound Column" for 0,1 and 2. No effect, I see only empty rows.
I checked in SQL Server Profiler, the query works fine.

Vadim Rapp

check property "column widths".

"bound column" is unrelated, this is where the selected value is going, in the form's datasource.

exceter

ASKER

Great. it work now.
One more question - When I select in Combo1 different item, Combo2 is populated, but the selected (shown at the moment) stays unchanged unless I select different item there. That is, the old selected item stays visible.
How to clear the items in combo2 before calling combo2.RowSource ? I have not found Clear() method.

Or it is done in another way ?

Vadim Rapp

combo2.value=null

exceter

ASKER

Tnank you.
You saved me a lot of headaches and time :)

exceter

ASKER

Hmm, one more thing if possible (I was googling for several hours already without success)

I have:
form - myForm
control - myInputBox
report - myReport

the report is build according to value of myInputBox

it is called from in ButtonClick

Private Sub Button_Click()
DoCmd.OpenReport "Report", acPreview
End Sub

The RecordSource of Report is :
Select * from Table(@Forms___myForm___myInputBox)

The above does not worl of course (The result of Upsizing to SQL SERVER)

Ho to redo that ?

Vadim Rapp

> Select * from Table(@Forms___myForm___myInputBox)

does it mean that the table is different every time, and you basically pass the table as parameter?

exceter

ASKER

No, the table is the same.
I just need to get a Report, and the query for Report need a parameter.
@Forms___myForm___myInputBox - is a parameter generated by Upsizer which is not recognized by query.
I cant understand how to make it understand.

exceter

ASKER

the query I need is:

SELECT * FROM ContIN WHERE PostDATE BETWEEN datain1 and datein2

I call the Report as - DoCmd.OpenReport "myReport", acPreview, Me.date1, Me.date2

but Me.OpenArgs gets nothing and exits, that is, If IsNull(Me.OpenArgs)=true

Private Sub myReport_Open(Cancel As Integer)
Dim sSql As String

   If IsNull(Me.OpenArgs) Then
      'Openargs must specify parameter
      Beep
      Cancel = True
      Exit Sub
   End If
   
   sSql = Me.RecordSource & vbCrLf & "WHERE (DatePost BETWEEN '" & Me.OpenArgs & "' AND '" & Me.OpenArgs & "')"
   Me.RecordSource = sSql
End Sub

Open in new window

How to pass two arguments, I think it is incorrect - "WHERE (DatePost BETWEEN '" & Me.OpenArgs & "' AND '" & Me.OpenArgs & "')"

Vadim Rapp

No need in any code. Specify datasource of the report as SELECT without any WHERE, then

docmd.openreport myreport,acViewPreview,,"datepost between '" & me.date1 & "' and '" & me.date2 & "'"