Please Help!!

[diane1969]

I have a form that has search criteria fields in the header, users can click on the various combo boxes, date range fields, etc, then click the Search button which populates the detail section of the form. I want to take those records and put them in a table. so far i've figured out to do this:

Dim SQLText As String

DoCmd.RunSQL "Delete * from tblRC"

SQLText = "INSERT INTO tblRC ( strID, strStartDate) SELECT [fldLegisID], [txtStartDate]"

DoCmd.RunSQL SQLText

HOWEVER, it is only putting the first record into the table. the criteria selected queries many records. any thoughts as to why it's doing that???

I'm more in the beginner/intermediate level with Access.

Thanks!

 

[Billy1234]

Diane - have you tried pasting the SQL statement into query builder and running it? what does the query return?

 

[diane1969]

I have a search form that users choose criteria. they can choose a date range (startdate, enddate), priority level, subject, and there is also an option where they can EITHER choose a Region OR a Country - and in the country list box they can hold down the ctrl key and choose one, or two, or however many they want, then on the Search click function it gathers all that criteria and puts it in a select statement that filters the record source. Here's the code:

Private Function BuildFilter() As Variant

Dim varWhere As Variant
Dim varCountry As Variant
Dim varItem As Variant
Dim intIndex As Integer
varWhere = Null
varCountry = Null

Select Case [optRC]
'Option Region

Case Is = 1

Me.cmbCountry.Visible = False
Me.cmbRegion.Visible = True
Me.cmbRegion.SetFocus

' Check for StartDate
If Me.txtStartDate > "" Then
varWhere = varWhere & "[fldLegisEffDate] >= " & "#" & Me.txtStartDate & "#" & " AND "
End If

' Check for EndDate
If Me.txtEndDate > "" Then
varWhere = varWhere & "[fldLegisEffDate] <= " & "#" & Me.txtEndDate & "#" & " AND "
End If

' Check for Priority
If Me.cmbPriority > 0 Then
varWhere = varWhere & "[fldPriorityID] = " & Chr(34) & Me.cmbPriority & Chr(34) & " AND "
End If

' Check for Region
If Me.cmbRegion > 0 Then
varWhere = varWhere & "[fldRegionID] = " & Chr(34) & Me.cmbRegion & Chr(34) & " AND "
End If

' Check for Subject
If Me.cmbSubject > 0 Then
varWhere = varWhere & "[fldSubjectID] = " & Chr(34) & Me.cmbSubject & Chr(34) & " AND "
End If

' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""

Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

'Option Country

Case Is = 2

Me.cmbRegion.Visible = False
Me.cmbCountry.Visible = True
Me.cmbCountry.SetFocus

' Check for StartDate
If Me.txtStartDate > "" Then
varWhere = varWhere & "[fldLegisEffDate] >= " & "#" & Me.txtStartDate & "#" & " AND "
End If

' Check for EndDate
If Me.txtEndDate > "" Then
varWhere = varWhere & "[fldLegisEffDate] <= " & "#" & Me.txtEndDate & "#" & " AND "
End If

' Check for Priority
If Me.cmbPriority > 0 Then
varWhere = varWhere & "[fldPriorityID] = " & Chr(34) & Me.cmbPriority & Chr(34) & " AND "
End If

' Check for Subject
If Me.cmbSubject > 0 Then
varWhere = varWhere & "[fldSubjectID] = " & Chr(34) & Me.cmbSubject & Chr(34) & " AND "
End If

' Check for Country in multiselect list
For Each varItem In Me.cmbCountry.ItemsSelected
varCountry = varCountry & "[fldLegisCountryCode] = " & Chr(34) & Me.cmbCountry.ItemData(varItem) & """ OR "

Next

' Test to see if we have subfilter for Country..
If IsNull(varCountry) Then
' do nothing
Else
' strip off last "OR" in the filter
If Right(varCountry, 4) = " OR " Then
varCountry = Left(varCountry, Len(varCountry) - 4)
End If

' Add some parentheses around the subfilter
varWhere = varWhere & "( " & varCountry & " )"
End If

' Check if there is a filter to return...
If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere

Case Else

End Select

End Function

I then filter the forms recordset with the function...

Me.Form.RecordSource = "SELECT * FROM qryLegislation " & BuildFilter

The ultimate goal is to take the result of the criteria, which in the detail datasheet lists the records matching the selection criteria, and make a report out of it. Now I'm way out of practice doing this stuff (taken 7 years off to raise my kids), but I can't figure out how to get the records into a report. My thought was to export the records into a table, then run the query off the result, then clear the table after the report was run, ready for the form to Search again.

I'm really pulling my hair out on this. Am I just forgetting the easy way to do this???

THANKS!!

 

[Joe4]

I guess I am a little confused as to why you think you need to write the results back to a table. You can build the SQL of the query using your VBA script. Then, just base your report on this query (queries can be control sources of reports just the same as tables).

Here is a "template" of some basic code to do something like that:

'   Build SQL for "on-the-fly" selection query (based on user form criteria entered)
    mySQL = ...

'   Assign SQL code built above to the query (which is the source for the report)
    CurrentDb.QueryDefs("qry-40").SQL = mySQL
'   Open Correct Report in Print Preview
    DoCmd.OpenReport "rpt-40", acPreview, "", ""

 

[diane1969]

I've tried messing around with what you've suggested, but I'm very out of practice and beginner level and just have no idea now of what you're talking about. I don't know how to use a vb statement that is in a form in query design. Sorry... I feel like an idiot!

 

[Joe4]

You are a lot closer than you think. You already have done most of the hard work.

The first step I mentioned is to build the SQL code in VBA. You already did that part with this here:

Me.Form.RecordSource = "SELECT * FROM qryLegislation " & BuildFilter

The other key is that you have a Report, let's call it "MyReport". It Control Source (what it is based on) will be some query, let's call it "MyQuery". This Control Source will never change, what will change is the query itself (which we are re-building everytime "on-the-fly" with your Criteria User Form). So we are just re-writing "MyQuery" every time based on the user's selections.

So, the code would look something like this:

'   All the code you have in your post that builds your "BuildFilter"
...

'   Build SQL for "on-the-fly" selection query (based on user form criteria entered)
    mySQL = "SELECT * FROM qryLegislation " & BuildFilter

'   Assign SQL code built above to the query (which is the source for the report)
    CurrentDb.QueryDefs("MyQuery").SQL = mySQL
'   Open Correct Report in Print Preview
    DoCmd.OpenReport "MyReport", acPreview, "", ""

 

[diane1969]

Thank you, using the BuildFilter in the reports open where slot worked... yes, that was too easy! You have no idea how much time I wasted on that!<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Now... with the above info I can have users run the reports from the main search screen (let's say frmMain) just fine -- and if I can't figure this out that's just the way it's gonna have to be!! What I'd like to do is have another form (let's say frmReports) with check boxes listing all the available reports they could run, and have them print the reports from there. I can open frmReports with the BuildFilter criteria, but once I'm there I can't seem to then use the BuildFilter function anymore. Is that how they work... you can only "call" them from the form they were built in? I tried to make it a module (which I've never used/done before), but that doesn't work. I'm tempted to go back to my original thought process and put my criteria in a table and run the reports from that, but I know that's probably not the most efficient way.<o></o>
<o></o>
I have tried to do what you said (and then searched the internet high and low looking for all kinds of examples) for the currentdb.querydefs etc, and I just can't seem to get it to work. <o></o>
<o></o>
I'm sorry. I'm still a stay at home mom and haven't done this in years, but I'm just doing this one project for a friend.<o></o>

 

[Joe4]

What I'd like to do is have another form (let's say frmReports) with check boxes listing all the available reports they could run, and have them print the reports from there. I can open frmReports with the BuildFilter criteria, but once I'm there I can't seem to then use the BuildFilter function anymore. Is that how they work... you can only "call" them from the form they were built in? I tried to make it a module (which I've never used/done before), but that doesn't work.

I am a little confused.
Are all the reports using the same criteria (BuildFilter)?
Are they all using the same query or data source?

If you would like to use the string you calculate for BuildFilter in other Modules, then you can declare it as a Global Variable.

 

[diane1969]

You are the MAN, Joe! I feel like an idiot because looking back because this was fairly easy. I really do need an Access vba lesson. I mostly just plow through things trial and error til it works, and/or copying code from sample db etc. I rarely ever step back and *think* about what I'm doing.

Thank you, thank you, thank you!