Problem with filtering records and also with report output

[Meaghen]

I've got 2 problems. The first is getting my records filtered for my report properly. I have a number of client records, and want to filter them by the field 'lastname' in order to group them together and produce a report for the accounts of whole families, 1 family at a time.

I have a form which has a command button on it that will bring up a second form which displays 1 of the records (accounts)in the families' group of records. (If I hit the remove filter button, hit <ctrl-F> and do a find on the last name field it'll come up with the family name I've searched for.) There is a command button on this second form to go to the next record allowing me to scroll through and view all the records for that specific family.

I have been trying to get my report to filter in the same way, but I don't know how to set the filter up so that it will grab just one family's records as opposed many families. I have so far, set up a command button on the initial form to open up the report, but it still shows every family in the whole database!

The second problem, seems realted to the first. When I open up the report it displays multiples of each individual record, when I only want it to display each record once. How can I get it to stop doing this? I've checked my queries to see if they are causing the problem, and they don't seem to be. I need these records shown only once or my calcuated field totals are way off.

It also doesn't help that someone else built this database with a rather high GIGO factor - things aren't very consistent throughought the database, which I'm trying to fix.

Meaghen Mac Laren

 

[anonymousme]

Hey there:

I ran into the same sort of problem this week -- what I did to fix it is pretty primitive, but it works. I'm assuming that each family has a unique key in your table... you'll need this to just grab one family instead of all of them. Basically, what you need to do is narrow down the WhereCondition parameter when you open up your report from the form. I'm guessing the filter you're using looks something like this:

&quot;LastName = '&quot; & <lastname> & &quot;'&quot;

where <lastname> is grabbed from a form control (textbox or whatever).

To narrow down the report results, you'll need to change it to:

&quot;LastName = '&quot; & <lastname> & &quot;' AND [<InsertYourTableNameHere>].ID = &quot; & <unique_id>

What I did was to open up a connection to the database in the VB for the command button click, grab the first record that matched the original filter (in your case, LastName = blah), and then grab the unique ID for that record. Then, use that unique ID as part of your WhereCondition for the report... make sense? That way, your report will only use one record as its RecordSource.

The code that you add to your form VB might look like this:

Private Sub previewReport_Click()
   ' Event handler for previewing a report.
   Dim strWhereCondition as String
   Dim strSql as String
   Dim uniqueID as Long
   Dim rst as ADODB.Recordset

   Set rst = New ADODB.Recordset

   ' Note -- strLastName must be initialized somehow before this - it should be an input from your first form, i.e., your original filter.

   strWhereCondition = "LastName = '" & strLastName & "'"   

   strSql = &quot;SELECT DISTINCT * FROM <insertYourTableNameHere> WHERE &quot; & strWhereCondition
   
   rst.Open Source:=strSql, _
            ActiveConnection:=CurrentProject.Connection, _
            CursorType:=adOpenStatic, _
            options:=adCmdText
   If Not rst.EOF Then
      uniqueID = rst.Fields(&quot;ID&quot;)
      strWhereCondition = strWhereCondition & &quot; AND [<TableName>].ID = &quot; & uniqueID
   End If
   rst.Close
   Set rst = Nothing
   ' Finally, open up the report with strWhereCondition as your filter.
   DoCmd.OpenReport <YourReportName>, acViewPreview, , strWhereCondition

<end sub>

Hope that helped... like I said, it's not exactly an elegant solution, but it does what it needs to.

To fix the multiple records, you might look into the Hide Duplicates property, or this may have fixed it also.

cheers,
allen