How do I pass parameter from from a textbox on the form to query 1

[varadha72]

Hi,

Can someone help me here, I have never used Access and I am working on my BA stuff that needs a sample mock up report for the business, where I need a form which gets data as a result of SQL query I created. But now I need to filter the data using 2 text boxes that I have on the form (Startdate and enddate). i.e. when the user enters the date in the text boxes and clicks the button next to it, I need my code to (whatever that is...I dont know) should magically get these dates into the SQL query and fetch data and populate the data in the boxes I have created in the form.

Form Name- PriceForm
Button- Cmd_Enter
TxtBoxes- txt_startdate and txt_enddate
Display text boxes- txt_1...to txt_20 (which displays the data fetched)

SQL Query- select * from FinalList where sortorder=1 and rundate between startdate and enddate

Code that I created-

Private Sub cmdEnter_Click()

Dim qdf As QueryDef
Dim sttd as date
Dim eddt as date
Dim obj As AccessObject As Object

Set qdf = CurrentDb.QueryDefs("FinalList")

Set stdt = TxtBx_EnterStartDate.Text
Set eddt = TxtBx_EnterEndDate.Text

Dim rs As Recordset

'-----PROCESSING ROW 1-------------------------------------------------------------
RunSQL = "select * from FinalList where sortorder=1 and rundate between stdt and eddt"
Set rs = qdf.OpenRecordset(RunSQL)

If Not rs.EOF Then
If rs.RecordCount = 1 Then
Me.Txtbx_1CT.Value = rs![row_count]
Me.Txtbx_1RCT.Value = rs![row_count]
Me.Txtbx_1ECT.Value = rs![except_count]
End If
rs.MoveNext

If rs.RecordCount = 2 Then
Me.TxtBx_1CT.Value = rs![row_count]
Me.TxtBx_1RCT.Value = rs![row_count]
Me.TxtBx_1ECT.Value = rs![except_count]
End If

End If
Set rs = Nothing
End Sub

Please help...................

 

[PHV]

SQL Query- select * from FinalList where sortorder=1 and rundate between Forms!PriceForm!txt_startdate and Forms!PriceForm!txt_enddate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[varadha72]

Thanks for your response PHV, but my code is still not compiling. I get error at the step Set rs=dbs.openrecord set (Runsql)

this is the scenario,
I type in startdate text box the start date and in the enddate text box the end date and when I click on the cmdEnter button I need the below to process, but I am getting compile error. Please help.

Private Sub cmdEnter_Click()
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
Set dbs = CurrentDb
Dim rs As Recordset

RunSQL = "select * from FinalList where sortorder=1 and rundate between Forms!PriceForm!txt_startdate and Forms!PriceForm!txt_enddate"
Set rs = dbs.OpenRecordset(RunSQL)

If Not rs.EOF Then
If rs.RecordCount = 1 Then
Me.Txtbx_1CT.Value = rs![row_count]
Me.Txtbx_1RCT.Value = rs![row_count]
Me.Txtbx_1ECT.Value = rs![except_count]
End If
rs.MoveNext

If rs.RecordCount = 2 Then
Me.TxtBx_1CT.Value = rs![row_count]
Me.TxtBx_1RCT.Value = rs![row_count]
Me.TxtBx_1ECT.Value = rs![except_count]

 

[PHV]

And this ?
RunSQL = "select * from FinalList where sortorder=1 and rundate between #" & Me!txt_startdate & "# and #" & Me!txt_enddate & "#"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[varadha72]

Thanks for the response, I made a few changes, but now I am getting compile error: Syntax error at RunQuery- Please help.


Private Sub ENTER_Click()
Dim start_dt As Date
Dim end_dt As Date

start_dt = txt_StartDt.Text
dtDate = Txt_EndDt.Text

RunQuery("start_dt","end_dt")

End Sub
Private Sub RunQuery(start_dt As Date, end_dt As Date)
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData

Set dbs = CurrentDb
Dim rs As Recordset

RunSQL = "select * from FinalList where sortorder=1 and rundate between #" & Me!start_dt & "# and #" & Me!end_dt & "#"
Set rs = dbs.OpenRecordset(RunSQL)

If Not rs.EOF Then
If rs.RecordCount = 1 Then
Me.Txtbx_1Rcvd_CT.Value = rs![row_count]
Me.Txtbx_1RP_CT.Value = rs![row_count]
Me.Txtbx_1ExCT.Value = rs![exception_count]
End If
rs.MoveNext

If rs.RecordCount = 2 Then
Me.TxtBx_1RvCT.Value = rs![row_count]
Me.TxtBx_1ExRP_CT.Value = rs![row_count]
Me.TxtBx_1ExExp_CT.Value = rs![exception_count]
End If

End If
Set rs = Nothing
End Sub

 

[dhookom]

You are sending text values of "Start_dt" and "end_dt" rather than the values of the memory variables. Also, you shouldn't use the .Text property in Access VBA. Use either .Value or use nothing.

Private Sub ENTER_Click()
  Dim start_dt As Date
  Dim end_dt As Date
 
  start_dt = Me.txt_StartDt
  dtDate = Me.Txt_EndDt
 
  RunQuery(start_dt,end_dt)
 
End Sub

For debugging assistance I would add a line

RunSQL = "select * from FinalList where sortorder=1 and rundate between #" & Me!start_dt & "# and #" & Me!end_dt & "#"
Debug.Print RunSQL   [COLOR=#4E9A06]'open the debug window (press Ctrl+G) to view[/color]

Duane
Hook'D on Access
MS Access MVP

 

[varadha72]

Thanks for the response, but I am still getting compile error. Please help.

Private Sub ENTER_Click()
Dim start_dt As Date
Dim end_dt As Date

start_dt = Me.txt_StartDt
dtDate = Me.Txt_EndDt

RunQuery(start_dt,end_dt) ------> Getting compile error here.

 

[Andrzejek]

It is really hard to help you since you type your code, instead of copy/paste:

Dim start_dt As Date
Dim end_dt As Date

start_dt = Me.txt_StartDt[red]
dtDate[/red] = Me.Txt_EndDt

RunQuery(start_dt,end_dt)

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[varadha72]

Sorry, I copied the exact code without any change, please take a look and let me know. I am still getting the compile error.

Private Sub ENTER_Click()

Dim start_dt As Date
Dim end_dt As Date

start_dt = Me.txt_StartDt
end_dt = Me.Txt_EndDt

RunQuery(start_dt,end_dt)

End Sub
Private Sub RunQuery(start_dt As Date, end_dt As Date)
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData

Set dbs = CurrentDb
Dim rs As Recordset

RunSQL = "select * from FinalDistinctWithLike where sortorder=1 and rundate between #" & Me!start_dt & "# and #" & Me!end_dt & "#"

Set rs = dbs.OpenRecordset(RunSQL)

If Not rs.EOF Then
If rs.RecordCount = 1 Then
Me.Txtbx_1Rcvd_CT.Value = rs![row_count]
Me.Txtbx_1RP_CT.Value = rs![row_count]
Me.Txtbx_1ExCT.Value = rs![exception_count]
End If
rs.MoveNext

If rs.RecordCount = 2 Then
Me.TxtBx_1RvCT.Value = rs![row_count]
Me.TxtBx_1ExRP_CT.Value = rs![row_count]
Me.TxtBx_1ExExp_CT.Value = rs![exception_count]
End If

End If
Set rs = Nothing
End Sub

 

[dhookom]

Try change this:

RunQuery(start_dt,end_dt)

to this

RunQuery start_dt, end_dt

I would also modify the beginning of your sub to:

Private Sub RunQuery(start_dt As Date, end_dt As Date)
 Dim dbs As DAO.Dabase
 Set dbs = Currentdb
 Dim rs As DAO.Recordset

Do you compile the code after all changes? Do you know how to use Debug.Print and breakpoints to troubleshoot your code?

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

It is a personal preference, but I like to 'call' my Subs:

[blue]Call[/blue] RunQuery(start_dt, end_dt)

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[varadha72]

hi Dhookom and Andy,

Thanks for your responses, I tried both your techniques, but each time, I get this error
"Microsoft Access cant find the field 'start_dt' referenced to in your expression."
and the debug pointer points to below step;

RunSQL = "select * from FinalDistinctWithLike where sortorder=1 and rundate between #" & Me!start_dt & "# and #" & Me!end_dt & "#"

Please help.

 

[dhookom]

There shouldn't be a Me!

RunSQL = "select * from FinalDistinctWithLike where sortorder=1 and rundate between #" & start_dt & "# and #" & end_dt & "#"

Duane
Hook'D on Access
MS Access MVP

 

[varadha72]

Hi,

I think this worked since I am not getting any compile error. But I have this basic question with the foundation of the Query and VB Code.

So this is how I created my Access Query- I linked by SQL Server, accessed the Table and created a SQL Query in the Access application which is as below-

SELECT DISTINCT (T.File_name) AS filename, T.row_count, T.exception_count, Min(T.run_Date) AS rundate, M.sortorder
FROM dbo_SOURCE_RUN_DATA AS T, Master AS M, dbo_SOURCE_RUN_DATA AS A
WHERE (((T.file_name) Like "*" & M.filename & "*")
And ((A.FILE_NAME)=T.FILE_NAME) And ((A.ROW_COUNT)=T.ROW_COUNT)
And ((A.EXCEPTION_COUNT)=T.EXCEPTION_COUNT)
And ((T.Run_Date) Between Date()-1 And Date())
And ((T.result)=0))
GROUP BY T.row_count, T.exception_count, M.sortorder, T.File_name
ORDER BY M.sortorder, T.File_name;

VB Code- Then in the VB code I used this sql query in the click event of the button

RunSQL = "select * from FinalDistinctWithLike where sortorder=1 and rundate between #" & start_dt & "# and #" & end_dt & "#"

So my requirement is:
The user must be able to run the form for a selectable date range, and displayed data wrt to the sortorder, hence kindly clarify the below questions-
1. Do I have to keep the Access Query separate and use the same query in the VB form and pass the parameter for the date in the form OR
2. Can I use the parameters for the date in the Access query and run the VB code off the result of the access filtered data for the selected date range.

 

[dhookom]

Since the tables seem to all be SQL Server, I typically would use a saved pass-through query to pull the records. Create and save any query to the SQL database and then use a little DAO code to update the SQL property of the P-T.



Duane
Hook'D on Access
MS Access MVP