Paramaterized recordset for form

[gem1204]

I have a form in my database that I use as a search form. In the original query I used for the form, I used the values the user entered into the text values as the query parameters as in Forms![frmSearch]![BookingID]. After I had the form completed it was decided that a navigation form would be used for the database so now my query wouldn’t work. Rather than modifying the query so that the parameters now referenced my form which is now a sub form, I decided to set up my parameters so I could use the form or the query anywhere and not be restricted to just using my query or form in one place. I decided to just define parameters for my query and then use vba to define the parameters for the query definition and then use the query definition open record set method to define the record set for the form.


Here is my code:

Private Sub cmdSearch_Click()
    Set MyDB = CurrentDb
    Set MyQdf = CurrentDb.QueryDefs("qrySearchMain")
    MaxLong = 2000000000
    Me.Mindate = #1/1/1900#
    Me.MaxDate = #1/1/2090#
    
    With MyQdf
    '//////////Set booking paramater
        If IsNumeric(Me.txtLkBookingID) Then
           ' MsgBox "IsNumerid"
            .Parameters("BookID_Low").Value = Me.txtLkBookingID
            .Parameters("BookID_Hi").Value = Me.txtLkBookingID
            Else
           ' MsgBox "NotNumerid"
             .Parameters("BookID_Low").Value = 0
            .Parameters("BookID_Hi").Value = Me.MaxLong
        End If
    '//////////Set event paramater
          
      If IsNumeric(Me.txtLkEventIDNew) Then
        .Parameters("EventID_low").Value = Me.txtLkEventIDNew
        .Parameters("EventID_Hi").Value = Me.txtLkEventIDNew
        Else
        .Parameters("EventID_low").Value = 0
        .Parameters("EventID_Hi").Value = Me.MaxLong
     End If
     
    '////////Set booking start date
    If Not IsDate(Me.txtLkBookingFrom) Then
        .Parameters("BkStartDate").Value = Me.Mindate
        Else
        .Parameters("BkStartDate").Value = Me.txtLkBookingFrom
    End If
    
    '///////Set booking end date paramater
        If IsNull(Me.txtLkBookingTo) Then
            .Parameters("BkEndDate").Value = Me.MaxDate
            Else
            .Parameters("BkEndDate").Value = Me.txtLkBookingTo
        End If
        
     '//////Set event data start paramater
        If IsNull(Me.txtLkEventDateFrom) Then
            .Parameters("EventStDate").Value = Me.Mindate
            Else
            .Parameters("EventStDate").Value = Me.txtLkEventDateFrom
        End If
        
        '////////Set event end date paramater
        If IsNull(Me.txtLkEventDateTo) Then
            .Parameters("EventEndDate").Value = Me.MaxDate
            Else
            .Parameters("EventEndDate").Value = Me.txtLkEventDateTo
            
        End If
        
        .Parameters("EventTypeLk").Value = Me.cmboLkEvenrType
        .Parameters("Company").Value = Me.cmboLkCompanyName
        .Parameters("EventPlaceLK").Value = Me.cmboLkEvenrPlace
        .Parameters("CityLK").Value = Me.cmboLkCityOfEvent
       
            
        
     
    End With 'Myqdf
    Set Me.Recordset = MyQdf.OpenRecordset
End Sub

I got all of that to work. On the form that shows the results of the search, the user also needs to be able to sort the records. There could be as many as a couple of hundred records in the record set and depending on their purpose the may want to sort the records in a variety of was. Since I’m using a record set, when the user tries to sort all of the parameters pop up again.

My question is, Does anyone know how to get around this problem? How can I set the recordset to a query that requires parameters and still allow sorting?


Thanks

[ranman256]

you can assign any query to a report. But if you save the report with the query (no params) then you can create the param as you open the report.
docmd.openreport "rMyReport",,"[clientID]=" & txtBoxID

youd want a form with the param boxes. The report reads the params off the form.

[orange]

Can you show readers the SQL for your final query?

Have you seen this article by Allen Browne re search?

It seems you are asking your user to enter numeric identifiers, is that true? If so, wouldn't users be more familiar with Names rather than IDs?

You know your situation better than any readers, so my comments may be irrelevant to the actual situation.

[gem1204]

Thanks. I know you can assign any query or table to a report but I don't think you can assign a recordset to a report the same as you can in a form........

My main question was about the form. I don't want my query to reference text boxes on a form. Just like the problem in my original post. First it was a stand alone form now its a sub form so the form fields that are referenced in the query are no longer valid. I may have to end up using the form as a stand alone form or a subform as part of a navigation form.

The only real question is How do I allow sorting on a form when the recordset of the form is based on a parameterized query with out the user being prompted for the paramaters?

[orange]

You seem more concerned with using a parameterized query than looking at effective solutions/options for running a report that accepts various parameters for completing the reports Record source.

There may be options for achieving what you are trying to do.

[gem1204]

Thanks orange. I'm sorry for being so confusing. I have the parameterized query question solved. My query may not be the best in the world but it works. I could care less about reports right now... that was just an add on question – something I was just wondering about

I guess I provided to much information and confused every one.

1) The search fields on my search form are in the header section of the form and the form is set up to continuous forms.
2) originally the query referenced the fields in the header section of the form.
3) form was changed from a stand-alone form and put on a navigation form - now query doesn't work because it is not referencing as a sub form
4) Instead of using a record source for the form, I decided to use a record set for the form based on opening the query definition as a record set
5)Search form now works great
6) Users also need to be able to sort by various fields (this is basically an adhoc form) – This is where I’m running into problems – When the user tries to sort the form he is prompted to re-enter the parameters.

My main question is

How can I fix this? How can I allow the user to sort the records when the form is based on a record set that is derived from a parameterized query instead of a record source?

The project I’m working on is still in development and at this point my search form could either be used as a stand-alone form and/or as a sub form or on a navigation form so I need to be flexible. Other than the sorting problem, I can now use my form either way.

How can I sort the records on my form using a record set instead of a record source?

[orange]

The project I’m working on is still in development and at this point my search form could either be used as a stand-alone form and/or as a sub form or on a navigation form so I need to be flexible. Other than the sorting problem, I can now use my form either way.

Can you post a copy of the database --remove anything confidential first ?

Do a compact and repair, then zip and attach to a post.

You may get some ideas from this free video by Steve Bishop. It isn't a parameterized query, but concept is to add info to your basic recordsource. May be a little off topic, but may be worth a few minutes viewing.

[gem1204]

Thanks Orange, but there is too much personal information in the database. I looked at Allen Browne’s sample and it basically the old Add-To-Where of the old Northwind database which creates a filter which is fine but I always was told that a query was better than a filter. I may have to use his technique if I can solve the sorting problem but its gonna be a slow query. My query gets data from 4 tables with two of the tables containing more than 100,000 rows. I would much rather use a query than a filter. I’m trying to get them to upsize their data to sql server.

This is what I was taught:

Definition of a filter: Fetch me all the rows. Now that you fetched all the rows only show me rows where Country = USA
Definition of a query: Fetch only the rows where Country = USA.

I learned that when you open a form with a filter, the form initially opens and access all the records defined by the sql in the record source. Once it has retrieved all the records then it displays only the records defined by the filter. I don’t know if this is correct but I always try to use queries whenever I can.

[orange]

I wasn't trying to suggest filter necessarily. It was more of an attempt to say there may be more ways than a parameterized query to do what you are trying to accomplish. There is no doubt that you want to limit the amount of unnecessary traffic between front end and back end.

You could certainly build WHERE clause criteria and requery.
In fact you could start with a recordsource along the lines of
SELECT the fields you need from Table/TablesInvolved
Where 1 =2.

This would not return any records. Then select some values from textboxes, or combos, or listboxes and
use vba to build your ultimate WHERE clause. Then requery.

I use this approach much more than filter -- just habit.

As for the dtabase, if you can make up some test data --Porky Pig, Hesa Payne etc and include your query and report code that may suffice. We don't care about the details of 100,000 records etc. We're just looking to help, and hands on the code is usually most productive.

Good luck.