Returning multiple Records

D

Dinger_80

Registered User.
Local time
Today, 15:37
Joined
Feb 28, 2013
Messages
109
I have a sub report that is based on a query. The Where clause of my SQL is giving me a bit of hang up. What I am attempting to do is return the records that are the items used to test products we test. The ID's of the records are gathered in a public function. The function is called GetCalEquipment. This returns all of the requested numbers just fine. For example ID numbers 4, 112, 124, 138, 232, 338 are returned when I call the function. Any number of records can be returned at any point. This is the unique numbers used to identify the records. I thought by having the function return the numbers the records would come up. However when I use that in my where statement, none of the records are returned. The whole SQL is as follows:

Code: 
SELECT CalibratedEquipmentListTable.ID, CalibratedEquipmentListTable.Manufacturer, CalibratedEquipmentListTable.ModelNo, CalibratedEquipmentListTable.Description, CalibratedEquipmentListTable.SerNo, CalibratedEquipmentListTable.LastCal, CalibratedEquipmentListTable.CalDue
FROM CalibratedEquipmentListTable
WHERE CalibratedEquipmentListTable.ID In (GetCalEquipment());

Any advice on how to get the query for the report to return the records desired would be appreciated.
 
Try to put in some hardcoded values in the where part of the query, then compare it from what you get returned from the "GetCalEquipment()" function.
Else post your database with some sample data + name of the report.
 
Your function will return a string, the resulting sql will look like:
WHERE CalibratedEquipmentListTable.ID In ("4, 112, 124, 138, 232, 338");
Which is obviously problematic.

To "fix" this, you need to "hand build" your sql in VBA....

Code: 
currentdb.querydefs("YourQuery").sql = " SELECT CalibratedEquipmentListTable.ID, CalibratedEquipmentListTable.Manufacturer, CalibratedEquipmentListTable.ModelNo, CalibratedEquipmentListTable.Description, CalibratedEquipmentListTable.SerNo, CalibratedEquipmentListTable.LastCal, CalibratedEquipmentListTable.CalDue " & _
                                       " FROM CalibratedEquipmentListTable
                                       " WHERE CalibratedEquipmentListTable.ID In (" & GetCalEquipment() & "); "

And then open or run your query. Or use it in recordset/source...
 
JHB,

I had previously tried doing what you suggested and put in random ID numbers. It worked just as expected. When I went back to the GetCalEquipment it stopped working again.

namliam,

Where I have used SQL before in VBA I haven't used VBA for the record source of a report. I can modify what the Function returns. I don't know if this would help any. Otherwise could you let me know how to have VBA be the record source of a report. Here is my function if it helps at all.

Code: 
Function GetCalEquipment()

Dim ListItem As Variant
Dim AllEquipment As String
Dim iCount As Integer
Dim oItem As Variant
Dim ListNum As Integer

iCount = 0
    'Added safety to ensure the list isn't empty
    If Forms.ReportForm.EquipmentUsed.ItemsSelected.Count <> 0 Then
        For Each oItem In Forms.ReportForm.EquipmentUsed.ItemsSelected
            'This gathers all of the items in the list and saves them.
            If iCount = 0 Then
                ListItem = ListItem & Forms.ReportForm.EquipmentUsed.ItemData(oItem)
                AllEquipment = DLookup("ID", "CalibratedUsedQuery", "[ID] = " & ListItem)
                iCount = iCount + 1
            Else
                ListItem = Forms.ReportForm.EquipmentUsed.ItemData(oItem)
                AllEquipment = AllEquipment & ", " & DLookup("ID", "CalibratedUsedQuery", "[ID] = " & ListItem)
                iCount = iCount + 1
            End If
        Next oItem
    End If
 
GetCalEquipment = AllEquipment

End Function
 
Well make the rowsource of your report be a query object.

Then before opening the report use the line I gave you previously to alter the SQL in the query and simply run the report of the existing query...
 
namliam,

My bad on that, clearly wasn't awake enough earlier, it is a sub report not a report. My main report returns everything I need it to. The reason I have a sub report is because of the difficulty I had with relating the multiselect field to the ID field. Since we sometimes only generate the equipment list, which is the sub report in this case, I thought it easy enough to just make a sub report. Either way, I am not sure how I would go about altering the query for the sub report. I understand about the main report, its just returning the information for the sub report that isn't working.
 
If you can't get it to work, then post your database with some sample data and name of the report in which you've the problem.
 
Report / sub-report, dont matter anything. Simply store the rowsource of the subreport as an actual query object in the database.

Then using the code I gave you alter the SQL of the query object before running the report.
 
Ok I get what you are saying now. By using a pre-built query then before the report is opened we are modifying the query to look for something different that is handled much better in VBA. That makes a lot of sense. That being said, I input what you suggested but it is still returning all of the records for the calibrated equipment list. Here is the code that I put in, perhaps I am just missing something simple. Thank you for your help on this.

Code: 
CurrentDb.QueryDefs("SubReportCalEquipmentUsedQuery").SQL = "SELECT CalibratedEquipmentListTable.ID, CalibratedEquipmentListTable.Manufacturer, " & _
                                                                "CalibratedEquipmentListTable.ModelNo, CalibratedEquipmentListTable.Description, " & _
                                                                "CalibratedEquipmentListTable.SerNo, CalibratedEquipmentListTable.LastCal, CalibratedEquipmentListTable.CalDue " & _
                                                                "FROM CalibratedEquipmentListTable " & _
                                                                "Where CalibratedEquipmentListTable.ID In (" & GetCalEquipment() & ");"
 
Best way to debug sql is to do something like:
Code: 
mySQL = "Select ..."

Debug.print mySQL

Currentdb.....sql = mySQL
Or simply fetch the sql from the query object....

What is the SQL looking like?

Also make sure your report is using your query and not your previous rowsource.
 
Sorry I haven't gotten back sooner a power outage reset some things and I had finals. To answer your question when I run my code I get what the string says only because I think I am not opening it as a record source or somethings. I can run different types of code and get the numbers of the items I have selected. I can even get the debug.print to show me the numbers like 5, 7, 9 just like that. I can even open up the form by itself and have the proper records returned. I just can't seem to be able to open up the sub form with the proper records.
 
You must log in or register to reply here.

Similar threads

D
Query to return one id for all unique combinations of four columns
Replies
4
Views
239
dobseh
D
H
Query combining records
Replies
13
Views
605
theDBguy
theDBguy
essaytee
Is it possible: Query/SQL to dynamically display a record/order/position number, without underlying field from a table
Replies
11
Views
495
essaytee
essaytee
D
Solved Clearing Recordset property value in Combobox
Replies
15
Views
446
DalynSoft
D
N
Slow downloads using ODBC driver to connect to Shopify
Replies
14
Views
389
nrgins
N

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom