Queries that change criteria on a form...?

[gnarpeggio]

Hello folks!

I have an odd situation this morning that I'm hoping to resolve:

I have a DB that creates reports based the criteria met in a query (big surprise). This particular report is created once a week with a deposit number that changes every time a new deposit is made.

Now comes the tricky part... One of the staff that uses my DB to create these reports is not too technologically inclined so to speak, so I'm hoping to avoid entering the query design to change the criteria to the newest deposit number, which is currently the only way to do so (due to my limited Access knowledge).

I'm wondering if there's a way that I could set up a button that when clicked will open a box or window that let's users change to the new deposit number and then applies that criteria change to the query/report. The other criteria never needs to be changed in this query; only the deposit number.

Any ideas (If even possible)?

Thanks!!!

 

[boblarson]

You can have a table which has a single field and single row to store the deposit number in and you can give the staff a form to bring that up and click on a button to increment or type it in manually.

The button could just use:

Me.TextBoxNameHere = Me.TextBoxNameHere + 1

And then in your query you set the criteria to be a DLookup. So in the criteria spot you would use

DLookup("FieldNameHere", "TableNameHere")

Since there isn't more than one row you can use it without criteria on the DLookup.

 

[gnarpeggio]

Excellent suggestion Bob! I tried this method and it worked perfectly! Now I won't have to take my staff on a tutorial every time they need a report = )

Thanks again,

Matt

 

[boblarson]

Oh, I forgot to mention that you will want to set the AllowAdditions property, of the form you are using to change the deposit number, to NO so they can't accidentally add a new record but can only change the current one. You might also want to set the AllowDeletions to NO as well.

 

[gnarpeggio]

Luckily, I'm familiar with all the ways access can inadvertantly add records. I've already set the properties in this form to restrict adding anything. Users will basically modify only one record to pull up the information.

Thanks!

 

[boblarson]

Luckily, I'm familiar with all the ways access can inadvertantly add records. I've already set the properties in this form to restrict adding anything. Users will basically modify only one record to pull up the information.

Thanks!

Good - not everyone does so I just wanted to make sure that was covered. Glad you already know that potential "gotcha."