Requery input parameters syntax?

[hinsdale1]

Upfront warning: worthless newbie



I would like ability for user to hide or skip the current record of a (query-based) form for a specified period.

I can use vba on command button "on click" update the record with a date flag to set the hiding period but once user sets the date flag, I would like the current record results to requery:

Me. Requery

Works fine except results in two questions :

1) I would like it to requery the form with the existing input parameters and not ask the user to re-input the parameters. Cant seem to find the input parameter syntax for vba?

These are my two input parameters:

Expected High Temp = [EHT]
Lowest Rating Allowed (Scale 0-99) = [LRA]

2) Also I would also like to return the user to the current record number (which will effectively be the next record once the current record is removed from the query results with the date flag)?

Any and all assistance is greatly appreciated.

[June7]

1. How are the users inputting the paramaters to begin with - into unbound combo or text boxes?

2. I return users to the same record by storing record id in an unbound textbox then code after requery:

With Me.RecordsetClone
    .FindFirst "ID=" & Me.tbxID
    Me.Bookmark = .Bookmark
End With

However, in your case you want to go to what was the next record before the requery. What you need to do is first move to the next record on the form, save the record id, requery, then move to that record.

[hinsdale1]

1. How are the users inputting the paramaters to begin with - into unbound combo or text boxes?

When opening the form the users are prompted to enter these two parameters with pop-up text boxes because these variables are undefined in the underlying query that the from is based on.

In order to preserve their answers the input variables are assigned to a field and preserved in the query as LRA and EHT.

2. I return users to the same record by storing record id in an unbound textbox then code after requery:

With Me.RecordsetClone
    .FindFirst "ID=" & Me.tbxID
    Me.Bookmark = .Bookmark
End With

However, in your case you want to go to what was the next record before the requery. What you need to do is first move to the next record on the form, save the record id, requery, then move to that record.

Must say this is confusing me a bit.. maybe will make more sense in conjuction with the code for the first part?

[June7]

I never use dynamic input parameters in queries. Except for the occassional VBA InputBox, all criteria input is done in controls on forms. All form/report filtering/searching is handled by VBA code.

Why won't the combobox work?

[hinsdale1]

Why won't the combobox work?

Well if knew why .....

But the form will not load at all when trying to assign the input variable to a combo box on the form (and causes me to have to go a backup in order to get working again). But I am fine with dynamic variable for now - it works and I am having higher priority issues then getting the combo box input to work.

[June7]

Well, I was trying to ask "What happens when using combobox - error message, wrong results, nothing?" So you did answer my implied question.

If I did use dynamic parameterized query it would with this method http://datapigtechnologies.com/flash...tomfilter.html

[Beetle]

I would like it to requery the form with the existing input parameters and not ask the user to re-input the parameters.

Why not just filter the existing record set instead of doing a requery. Let's suppose you have an unbound text box (named, say, txtDateSearch) where the users can enter a date value. In the click event of your command button just apply a filter;

Me.Filter = "[YourDateField] >=" & txtDateSearch
Me.FilterOn = True

You can then easily remove and apply the filter as needed. You would still need to code for grabbing the ID value of the next record, but this is not necessarily as simple as just grabbing the ID value of the next record before the filter is applied because the next record before the filter might not be the same as the next record after the filter. So it depends on what you mean by "next" record in this context.

[hinsdale1]

Why not just filter the existing record set instead of doing a requery. Let's suppose you have an unbound text box (named, say, txtDateSearch) where the users can enter a date value. In the click event of your command button just apply a filter;

Me.Filter = "[YourDateField] >=" & txtDateSearch
Me.FilterOn = True

You can then easily remove and apply the filter as needed. You would still need to code for grabbing the ID value of the next record, but this is not necessarily as simple as just grabbing the ID value of the next record before the filter is applied because the next record before the filter might not be the same as the next record after the filter. So it depends on what you mean by "next" record in this context.

This is just what I was wanting to accomplish.. and is functioning as hoped. Thank you for taking the time to pipe in!

[hinsdale1]

The last issue is to return to the same spot after the date flag is changed and the filter is applied.

The filter is initially applied on form load and is only reapplied after updating a single record (with a date flag update) so the ID of the next record will be the same before and after the update. ALso the current record number will also be a possible destination point because after the date flag is applied the current record will be filtered and thus the next record will be be in the place of the current record..

So if there is a way to return after the filter is applied to the current record number.?

Or if I have to retrieve the ID of the next record and then go to that ID after the filter is applied , how might one do that?

Not sure how bookmarks work either but maybe thats the easiest way?

I know june7, you provided some bookmark code above but I cant quite grasp to make it work.


Any and all suggestions from anyone would be welcomed!

[June7]

If you don't want users presented with the popups again then will have to change the parameter inputs from query popups to instead reference controls on form. The link I referenced shows that. Another approach is suggested by Beetle which does not have dynamic parameter in query. In any case, to return to the record that was originally following the removed record, will need to store an identifier for that record. This can be done by setting a VBA variable or populating an unbound textbox (can be not visible). The real trick is figuring out what event to put this code in. Can be Change event for the checkbox or a button Click. Something like:

DoCmd.GoToRecord , , acNext
intID = Me!ID
Me.Requery 'or the Filter and FilterOn method
With Me.RecordsetClone
    .FindFirst "ID=" & intID
    Me.Bookmark = .Bookmark
End With

Use whatever your field is that is the unique identifier, I use ID as an example.

[hinsdale1]

Thank you June7. Again your breadth of knowledge is only matched by your generosity of spirit.

The code you provided is working well (thanks again) but I do have a minor complaint because this is a fundamentally image based database with inefficient use of resources. . With the gotoNextrecord method you actually watch as the next record is loaded (in order to get/retain its ID number) and redrawn (with all the accompanying images), then the screen flashes and redraws again as the filter is then applied and the 1st record of the newly filtered results are presented, then flashes and redrawn again as it goes to the ID of the record that we retained in the first step. Then of course the other subs I have running require a few SQL selects to run and the resulting screen refresh.. so is a lot of flashing and redrawing all together.

I have asked and not getting any bites, but seems more intuitive (in my pea brain) that if I already know the record number of the record I want to return to (which is the current record number) wouldnt it save me a step or two to be able to just tell it to gotorecord X.

Is using the record number a possibility (if it would save some refreshes)?

And if it is, how to acquire the current record number in VBA and then how to get it to go to this record number (bookmark?) after the filter is applied?

[Beetle]

how to acquire the current record number in VBA and then how to get it to go to this record number (bookmark?) after the filter is applied?

If all you want is to return to the Current record, then use the same code June7 posted earlier but remove the DoCmd.GoToRecord , , acNext line. You indicated in previous posts that you needed the next record for some reason, so that's why example code was written as it was.

[hinsdale1]

If all you want is to return to the Current record, then use the same code June7 posted earlier but remove the DoCmd.GoToRecord , , acNext line. You indicated in previous posts that you needed the next record for some reason, so that's why example code was written as it was.

It don't think I said that I needed the next record, I think it was a method proposed by June7 - and probably a good method because the records in many circumstances may output in a different order after a requery. However this is not the case in my circumstance.. the records will always come back in the same order (minus the record that is added to the filter).

Removing the DoCmd.GoToRecord , , acNext line as you propose did not work. I would think I need to assign the current record number to a variable (somehow?) not the ID ?

[hinsdale1]

Well with trial and error I forced a gotorecord method and seems to function appropriately.. not sure that its more efficient but seems to bit quicker.

intID = [CurrentRecord] - 1
Me.Filter = "HideUntil is Null Or HideUntil <" & Date
Me.FilterOn = True
DoCmd.GoToRecord , , , intID

I am not sure why i have to subtract (1) one from the current record for it to work properly but works as expected now so got that going for me.. which is nice.

Most likely this is a poor way to do it.. and perhaps its not even more efficient than to retrieve nextRecord Id method that June7 had working so nicely for me.

[June7]

Wasn't my idea, it's what I thought you wanted as described by item 2 in first post.

Isn't the current record going to be filtered out of the recordset because the checkbox is clicked? So can't return to the current record and that's why you want to return focus to what was the following record?

Never used CurrentRecord but if it works... congratulations!