opening already filtered datasheet on button click !!! 1

[prasadmokashi]

Hi,

I have created one form with two combo boxes and button.When I select options in two combo boxes and click that button I would like one form to open in "datasheet" type view with the records filtered on the conditions selected in combod boxes.

Could anybody please help ?

Thanks,
Prasad

 

[cmmrfrds]

All you need to do is open the continuous Form with the filter criteria passed in. Here is an example from one of my apps.

Dim DocName As String
Dim LinkCriteria As String
'-- set up the filter.
LinkCriteria = "kintReportRequestPrelimId = " & Me.kintReportRequestPrelimId
DocName = "frmRequestWebTable"
DoCmd.OpenForm DocName, , , LinkCriteria

 

[prasadmokashi]

Thanks cmmrfrds,

As suggested, I am passing link criteria like "ProductName = " & sProductName and at run time sProductName contains the text in combo box...e.g. "T.V.", But after clicking on the button it gives me inputbox to enter "T.V".

link = " ProductName = " & sProductName
DoCmd.OpenForm "frmShowProductData", acFormDS, ,link


I think I am doing something wrong. Any suggestion ?

Regards,
Prasad

 

[cmmrfrds]

I assume sProductName is a String/Text field. It must be surrounded by quotes. Access uses double quotes for a string literal whereas sql server uses single quotes.

chr(34) = double quote.
chr(39) = single quote.

link = " ProductName = " & chr(34) & sProductName & chr(34)

 

[Zion7]

Yes, for starters, as cmmrfrds said, If sProductName is a string, then you need double quotes. As a safeguard, I usually use 2 double quotes,
link = " ProductName = """ & sProductName & """"
in case there are apostrophes, or quotes in your criteria itself, this will not confuse the syntax of the expression.
..but, you said you have 2 cboboxes, is that for 2 different criterion, or is one the form/field & the other the criterion.
It appears, 2 criterion. in that case, you need to modify you Where clause, to accomodate the two.
is one cboBox optional? If not...


link = " ProductName = """ & sProductName & """ AND OtherField = """ & sOthercboBox & """"


If so...

If Not IsNull(cboBox1) AND IsNull(cboBox2) Then
link = "ProductName = """ & sProductName & """"
ElseIf IsNull(cboBox1) AND Not IsNull(cboBox2) Then
link = "OtherField = """ & sOthercboBox & """"
Else
link = " ProductName = """ & sProductName & """ AND OtherField = """ & sOthercboBox & """"
End If

..Just a thought, good Luck!

PS, remove all quotes surrounding variable, if it holds an integer.
link = "OtherField = " & sOthercboBox

 

[prasadmokashi]

Hi cmmrfrds & dboulos !

It is working now. I tried both, putting chr(34) and double quotes. Both work great.

Thank you very much for all your help.

Cheers,
Prasad

 

[prasadmokashi]

Hi,

Extending the same problem.

Currently my datasheet form is sourced from a simple query like "Select * from product". So I can understand the link criteria.
But suppose it is sourced from a complex UNION query how do we pass the link criteria for each select in union ?

Thanks & Regards,
Prasad

 

[cmmrfrds]

What I have done in that case is return the paramter from a function and code it directly in the query.

'-- in standard module.
Public pubStartDate As Date
Public pubEndDate As Date

Function ReturnStartDate() As Date
ReturnStartDate = pubStartDate
End Function

Function ReturnEndDate() As Date
ReturnEndDate = pubEndDate
End Function

'------ the query called qryABC
SELECT qry1Rpt.[Staff Assigned], qry1Rpt.Category, Sum(qry1Rpt.Hours) AS SumOfHours1
FROM qry1Rpt, FiscalYear
where qry1Rpt.Date Between ReturnStartDate() And ReturnEndDate()
Group By qry1Rpt.[Staff Assigned], qry1Rpt.Category

UNION
SELECT qry2Rpt.[Staff Name], qry2Rpt.Category, Sum(qry2Rpt.Hours) AS SumOfHours1
FROM qry2Rpt,FiscalYear
where qry2Rpt.Date Between ReturnStartDate() And ReturnEndDate()
Group By qry2Rpt.[Staff Name], qry2Rpt.Category

UNION SELECT qry3Rpt.[Staff Assigned], qry3Rpt.Category, Sum(qry3Rpt.SumOfHours) AS SumOfHours1
FROM qry3Rpt,FiscalYear
where qry3Rpt.Date Between ReturnStartDate() And ReturnEndDate()
Group By qry3Rpt.[Staff Assigned], qry3Rpt.Category;

'------ record source on report.
qryABC

'----- Form to enter the parameters.
'---- on some event.
pubStartDate = Me.Sometextbox
pubEndDate = Me.Someothertextbox

Run the report.

 

[Zion7]

Without being too familiar with Union Queries, I know in "InnerJoin" queries, just a full reference to the field s required, only if 2 fields have the saME NAME.

"txtName =""" & strName & """ AND txtCountry = """ & strCountry & """"
becomes
"tblTable1.txtName =""" & strName & """ AND tblTable2.txtCountry = """ & strCountry & """"

again. this may not apply to Union queries, but, on the other hand, it may in general, whenever more than one table is involved.

just a stab, Good Luck!

 

[prasadmokashi]

Hi cmmrfrds,

Fantastic solution. Worked great!
Thank you very much for your help.

dboulos,thank you for your tip on more than one table case.

Cheers,
Prasad

 

[prasadmokashi]

cmmrfrds,

Now I have created one pass-through query in which I am connecting to Sybase database and trying to run one stored procedure. It runs with no problem.

The only thing is stored procedure accepts some parameters.
e.g myStoredProc param1, param2

I have currently hard-coded param1 & param2.
Now I want to pass my form field as param1. How would I able to do it ? I tried similar approach of creating module function and calling it(which works in case of Union query ). But it doesn't work in case of pass-through query.

I would be very happy if anybody has solution for this too.

Thanks & Regards,
Prasad

 

[cmmrfrds]

Create a pass-thru query. Example.
Name is: passThruCustOrders

The code in the pass-thru will look something like.
exec dbo.sp_customerOrders @id='alfki', @begdate='1/1/1992', @enddate='1/1/2002'

Create a Function to dynamically modify the pass-thru to add the current parameters.

Example of changing code in the function.

Public Function CheckQrydef()
Dim qdef As DAO.QueryDef
Dim strSQL As String
Dim strParam As String

Set qdef = CurrentDb().QueryDefs("passThruCustOrders")
Debug.Print qdef.sql
qdef.sql = "exec dbo.sp_customerOrders @id='alfki', @begdate='1/1/1992', @enddate='1/1/2002'"
qdef.CreateProperty.Properties.Refresh
Debug.Print qdef.sql

End Function

Run the function to modify the code.
Then run the pass_thru query with the modified code (this is how to dynamically build the parameters in the pass-thru).

 

[prasadmokashi]

YES !!! It worked very well.

Thank you very much cmmrfrds.

Cheers,
Prasad