VBA Userform for datarange and checkbox for selection

[RvdH]

Hi there,

I've created a database with Access2019. In this database I want to create a report which consist of the data
between two chosen dates (textboxes) 'or no specific daterange if not filled in' and location(s) (checkboxes).
I've tried different methods including Queries and Forms but nothing seems to do exactly what I want or
is just not user friendly.

Is what I want even possible?

In the table dates have to be picked using a calendar and locations using a listbox.

Hopefully there's an answer. Grtz!

 

[theDBguy]

Hi. Welcome to AWF!

What you want is definitely possible. We probably just need more info about your setup to give you an appropriate solution.

 

[RvdH]

That was awefully quick Thnx. What info do you need?

 

[theDBguy]

That was awefully quick Thnx. What info do you need?

Well, the best way is if you could post a sample copy of your db showing the problem. Otherwise, we'll need some ideas about your setup. For example, names of your objects, copy if code you're using, the SQL statement of your query, etc.

 

[RvdH]

Here is a copy of the db. Its nothing fancy. Just a table, form and report (and the same with a Query). At this moment the Query
is selecting the within a time domain. I can add a question which location but the is not suitable for several locations in one
answer (for example: Curacao and Bonaire, within the time domain 01-04-2020/01-05-2020). Thats why I was thinking of using
a VBA userform instead. If possible I would like to run the VBA Userform with the commandbutton 'Rapport Openen'. When the
VBA userform is filled in with the time domain and the wanted locations (checkboxes) then use the commandbutton 'Naar rapport'
to show the selected time domain and locations. Default checkboxes is 'Alle locaties' and default time domain is (when not filled in)
all data.

Out of frustration I have deleted earlier attempts/scripts. Alle the Youtube videos were without result

The module is the VBA userform.

 

[MajP]

I do not think there is any reason to use a UserForm, you can make that exact same thing in native access. There is nothing special about that form as far as I can tell. Why do you feel you need a UserForm?

 

[MajP]

I do not understand the rest of your requirements, but just use an Access form. I have the default set to no selections, but if you want all just change the on load to true instead of false.

 

[RvdH]

Please be patient with me But in the case of an Acces form I keep getting all the data. The date range does and/or checkboxes
do not select or filters the data for the report. In this case...there's one record with Curacao (15 Apr) and one with Bonaire (19 Apr).
If the date range is 10 Apr. untill 20 Apr. and the checkbox Curacao is the only one that is active I want only Curacao to turn up the report.
If the date range is 10 Apr. untill 20 Apr. and the checkbox 'Alle locaties' is active I want (in this case) both to turn up the report.
If all records our outside the date range no records should turn up in the report.
If no date range is entered there is no date limit. The only filter/selection is in that case made by the checkboxes.

 

[RvdH]

Oké. Now im really freakn lost. Im very sorry. How do I 'build a filter'? I build one in a query I dont have the feeling were talking about the same thing. I have the feeling I keep going in circles. Just a brain fart.....(and once again excuse me for asking so many questions on something that I think should be simple (but im just not getting it).....isnt it possible, to refer a filter (for example in the properties menu of the report, to the txtOut txtbox in the subform? And if I use this in a docmd.openreport (I added the command to the cmdbutton code) it keeps forwarding me to print the report instead of viewing it. Although I typed acviewsomething.

 

[MajP]

In this database I want to create a report which consist of the data
between two chosen dates (textboxes) 'or no specific daterange if not filled in' and location(s) (checkboxes).
I've tried different methods including Queries and Forms but nothing seems to do exactly what I want or
is just not user friendly.

How do I 'build a filter'? I build one in a query I dont have the feeling were talking about the same thing. I have the feeling I keep going in circles. Just a brain fart.....(and once again excuse me for asking so many questions on something that I think should be simple (but im just not getting it).....isnt it possible, to refer a filter (for example in the properties menu of the report, to the txtOut txtbox in the subform? And if I use this in a docmd.openreport (I added the command to the cmdbutton code) it keeps forwarding me to print the report instead of viewing it. Although I typed acviewsomething.

I do not know what to say. Hopefully someone else can help because I feel I did exactly what is described above. You need to do a better job of explaining. Do not say what you cannot do, explain in clear words what you want. Do not explain the things you tried.

I built an ACCESS form where you can provide a date range and or selections or just selections. It clearly builds the correct filter to pass to a form or report or build a query from it.

Case 1. Return all within range

Case 2. Return specific selections in range

Case 3. Return Specific selections no range

 

[RvdH]

Im sorry. Its just frustrating (completely my own incompetence). What I want: a cmd button on the MajP_Form1 that opens report Tabel2
and report Tabel2 only shows the selected data (date range and or selections or just selections).

 

[MajP]

It clearly builds the correct filter to pass to a form or report or build a query from it.

So pass it to the report

DoCmd.OpenReport "Tabel2", acViewPreview, , strOut

 

[RvdH]

Great stuff! Thnx for all your patience and perseverance

 

[MajP]

FYI. The form with checkboxes is not that flexible. If you want to add more destinations, you will have to rebuild the form. A more flexible solution would be a multiselect listbox. No new code or form design would be required.