QDEF issue in VBA

[edzigns]

I have this code that I modified from code provided to me... I'll explain exactly what I want it to do at the end of this post....

Dim db As Database
Dim qdfNew As QueryDef
Dim stDocName As String
Dim stSQL As String
Dim stLinkCriteria As String

stDocName = "rptGroupAssignedbyDate"
stLinkCriteria = "[GroupAssigned]=" & "'" & Me![GroupAssigned] & "'"


Set db = CurrentDb

With db
' Delete QueryDef
.QueryDefs.Delete "qselGroupAssignedbyDate"
End With

stSQL = "SELECT tblMasterTicketTracking.MasterTicketNo, tblMasterTicketTracking.MagicTicketNo, tblMasterTicketTracking.Date, tblMasterTicketTracking.ErrorCode, tblMasterTicketTracking.InternetBrowser, tblMasterTicketTracking.GroupAssigned, tblMasterTicketTracking.OS, tblMasterTicketTracking.ProblemDescription"
stSQL = stSQL & " FROM tblMasterTicketTracking "
stSQL = stSQL & " WHERE tblMasterTicketTracking.Date Between #" & CDate(Date1) & "# And #" & CDate(Date2) & "#;"
stSQL = stSQL & " WHERE tblMasterTicketTracking.GroupAssigned "


With db
' Create QueryDef.
Set qdfNew = .CreateQueryDef("qselGroupAssignedbyDate", stSQL)
' Open Recordset and print report.
DoCmd.OpenReport stDocName, acPreview
' Delete new QueryDef
.QueryDefs.Delete qdfNew.Name
End With

DoCmd.close acForm, "frmSearchGroupsbyDate"

What I want the form to do is search for MasterTicketNo, by Group Assigned (drop-down) between dates (Date1 field & Date2 field), then open report controlled by qselGroupAssignedbyDate report...

Any help with syntax or alternate coding would be appreciated!!!
Thank you in advance...

 

[charityg]

It looks like you have a confusing mess of code. Let's break down what you are attempting to accomplish into smaller pieces.

I'm going to assume a few things, please let me know if I'm right or wrong.

1. You have a report named: rptGroupAssignedbyDate

2. From your form, you want to open the above report with specific criteria.
I think your criteria is that you only want open only those records where the GroupAssigned field is equal the value selected in your GroupAssigned combobox, on your form, and only those records with TrackingDates between two date textboxes on your form (named Date1 and Date2)

3. Your report's datasource is a query named: qselGroupAssignedbyDate

If my assumptions are correct, then your challenge is actually quite easy. I think the problem you are running into with your code is that you are attempting to use two different methods for filtering your report, and neither method is complete. Either filter the report by the datasource query, or by the strLinkCriteria. I am curious why you are creating a querydef in code and then deleting it.

Please tell me if my understanding of the situation is correct, and I will try to help you work through a solution to your dilemna.

 

[edzigns]

Your assumptions seem to be right on the money... and if it is a simple solution, it wouldn't surprise me... I always have to do the difficult... I always overlook the easy and obvious (comes from being an IT administrator...), but would appreciate the help, because I have been working on and tweaking this database for over a month and I think my brain is melting....

 

[charityg]

Is there ever a time when the report will not be opened from the form with the specified criteria? If not then you would just refer to the form fields in the query parameter for the report.

If you will view ever need to view the report without the filtering parameters, the best way would be to build the strLinkCriteria parameters in the form and use it to open the report from your form.

What is your situation? Report is always opened only by the form, or the user may open the report with a different method?

 

[edzigns]

Always by the form....

 

[charityg]

Okay. Simple enough. Open your report in design view, open the properties for the report and click the ellipsis (...) beside the record source property to open the query builder for the report. I'd like to start from scratch to avoid any confusion, so remove any tables or fields appearing in the Query Design View.

Next...
Right click the table area and select Show Table and add your tblMasterTicketTracking.

Drag the fields into the grid that you would like to show on the report.

(I am assuming your form's name is frmSearchGroupsbyDate)

Then in the criteria row for the GroupAssigned column type
=forms![frmSearchGroupsbyDate]![GroupAssigned]
(this assumes that the combobox on your form is named GroupAssigned, if not, change the name in the criteria)

In the criteria row for the Date Column (word of advice, Date is a reserved function of Access and shouldn't be used as the name of a field in your table. I would DEFINITELY change the field name for this to something like TicketDate)
BETWEEN forms![frmSearchGroupsbyDate]![Date1] and forms![frmSearchGroupsbyDate]![Date2]

Save the query and the report and close it.

Now back on your form, just a couple of things. Make sure the formats for your Date1 Date2 textboxes are a date format. You may even want to use an input mask.

In the onclick event of the commandbutton you wish to open the report,

Dim stDocName As String
stDocName = "rptGroupAssignedbyDate"
DoCmd.OpenReport stDocName, acPreview
DoCmd.close acForm, "frmSearchGroupsbyDate"


That should be it. Let me know if you run into any snags, or if there is something you don't understand.

 

[edzigns]

THANK YOU

Boy, how easy was that... thank you... worked like a charm