Can a report and form be bound to same query and view different results?

[donkey9972]

Okay, I know my question is probably a bit vague and weird. So please allow me to elaborate on it...

So what I am trying to find out is this. I have a form, I want it to be able to scroll through all the records to the query it is bound to. I also have a report bound to the same query, but I only want it to show the current data, not anything from the past. I know I can go into the SQL on the query and Select Top 1, but that would limit the form to. Is this type of result possible or do I need to make a 2nd query for that particular data filter?

 

[Minty]

You can open the report with it's own filter/where clause:

DoCmd.OpenReport method (Access)

Office VBA reference topic

learn.microsoft.com

 

[June7]

I have never used FilterName argument. I use WhereCondition argument. Something like:
DoCmd.OpenReport "Sales Report", acViewPreview, , "ID=" & Me.tbxID

Or use report Filter and FilterOnLoad properties in design.

 

[donkey9972]

I am trying to filter the records based on the last date entered. So for instance if the last record to be entered was 12/03/2024 then I want the data for that date on the report to appear. But let me give this a shot. I am still trying.

 

[donkey9972]

Still no luck. I am trying the following combination to try to filter this, but I think I am putting something wrong.

DoCmd.OpenReport "rpt_DailyCoversheet", acViewPreview, , "txtDate=" & Me.D_Date

Ok, so the txtDate is the name of the field on the report. The Me.D_Date is the control source for the field on the report. What am I doing wrong?

 

[June7]

If focus is on the new record, it must first be committed to table so data is available.

Reference field in table and control on form.

DoCmd.OpenReport "your report name", acViewPreview, , "date field name=#" & Me.controlname & "#"

 

[donkey9972]

Ah ok let me adjust and try again. Thank you for your patience. All I ever did in the past was make a new query with an SQL statement limiting the data.

 

[June7]

One way to commit record before calling report:

    If Me.Dirty Then Me.Dirty = False

 

[donkey9972]

Well the report is opened using a button from a form. So I am using the onclick part. So I am putting this specific code in:

DoCmd.OpenReport "rpt_DailyCoversheet", acViewPreview, , "txtDate=#" & Me.D_Date & "#"

But I am getting the following error:

Run-time error '3075':

Syntax error in date in query expression '(txtDate=##'.

 

[June7]

Is the field really named txtDate? Is this a date/time type field?

Looks like Me.D_Date is Null.

Did you see my previous post?

 

[donkey9972]

I did see the one about Me.Dirty, this is a date field.

 

[June7]

If you want to provide db for analysis, follow instructions at bottom of my post.

 

[donkey9972]

Ok. Please don't judge.

 

[June7]

Okay, the field is named D_Date and the textbox is named txtDate. Which means your code is backwards.

Review post 6.

 

[donkey9972]

Ok, I see what is happening. I made the changes you recommended and it still gave me the same error as before but for the D_Date part now. But what happens is if I go ahead and put a date in the date field, it will open the report and show that record. I need it to show the last record of the date entered even if there is nothing entered in the date on the form when it opens.

 

[June7]

You need to navigate to that record first. Otherwise, code will get more complicated.

 

[donkey9972]

I see. That is not always going to be an option as I am not the only one using this. So maybe an extra query might be the way I have to go.

 

[June7]

If there are multiple users and they work simultaneously, this db should definitely be split and each user run their own copy of frontend.

Could there be multiple records for each date?

And why could you not navigate to desired date, regardless of how many users there are?

 

[donkey9972]

I could navigate to the specific date but the other people might not. This DB is run only on one computer, so only 1 person at a time can be using it. There is code to prevent multiple days, I just have not put it in there yet. So multiple records for each day is not possible.

 

[June7]

And now I remember you - "only 1 user at a time".

As I said, then code needs to be more complex. But just do the manual navigation for now to test the report output. Time for me to hit the hay. Will look at again next day or so.