Wrong records filter when i double click on an item in a listbox

[nim180]

Hi everyone,

I have a combobox on a form with a number of "Asset ID's" the user can choose from. There is also a command button when the user choose's a record from the combobox and clicks the command button it opens a report with the information regarding that record.

The query for the report has a number of fields. In the criteria of the field "Asset_ID" I have the following

[Forms]![frmAssets]![Cbo1]

I thought this would work but nothing is filtered on the report! If i use

Like "*" & [Forms]![frmAssets]![Cbo1] & "*"

The report filters but only for some records, other records stay blank. Can someone tell me where i may be going wrong.

Thanks,
Nim

 

[nim180]

Sorry mistake in title

 

[ProgramError]

Base the report on the whole table and filter with the following.

[Asset_ID] = "*'" & [Forms]![frmAssets]![Cbo1] & "'*"


Ian Mayor (UK)
Program Error
Your lack of planning is not my emergency!

 

[nim180]

Yip gave it a try but still nothing!! Any other idea's

 

[formerTexan]

Is Asset_ID the bound column in your combo box?

 

[MajP]

debug.print Forms!frmAssets!Cbo1

see if you are returning the desired value. Like formerTexan is thinking you are returning the wrong column

 

[ProgramError]

lets us know the results


Ian Mayor (UK)
Program Error
Your lack of planning is not my emergency!

 

[YoungManRiver]

nim180,

You just ran into one of the limitations of Access. You can not modify the Query once the Report open and you need to filter the output.

The way around this is:

1. Create a temp table (structure only) based on the org table,
2. Write a subroutine that uses your selection, 
   a.) Deletes all records in the temp table,
   b.) Add the current record from org table to temp table based on selection from CBox,
3. Assign the Report to the temp table,

Then when report opens it only has the one record you put into the temp table, so you are good.

You can also do this with "multi-select" from a list box but much more complex but query from org table into temp table will still be the same except for setting up a loop to handle the "Item.Selected" items from your list box.

YMR

 

[MajP]

I have no idea what YMR is saying, but I assume he misread your post. There is no reason to go to those lengths. Your methodology is fine; using form control values as criteria for a query. Your execution just needs to be fixed.

 

[TheAceMan1]

How are ya nim180 . . .

You can also use a method called [blue]Late Binding[/blue] to open the report. With [blue]Late Binding[/blue], initially there's no recordsource for the report. In the reports [blue]OnOpen[/blue] event you construct proper an SQL statement with criteria for AssetID, then set the recordsource to the SQL.

If you'd like to try this, post back the query/SQL for the report.

Also, is AssetID text/numeric?

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[nim180]

Thank you everyone for the input, aceman i always like to try new methods, the following is the queries SQL.


SELECT tblAssets.ID, tblAssets.Asset_ID, tblAssets.Tag_Number, tblAssets.Custodian_Name, tblAssets.Status, tblAssets.Description, tblAssets.Serial_ID, tblAssets.Location, tblAssets.OffSite, tblAssets.UNSW_ID, tblAssets.PO_Unit, tblAssets.PO_No, tblAssets.Dept, tblAssets.Fund, tblAssets.Project, tblAssets.Po_Line, tblAssets.Acquisition_Date, tblAssets.Purchase_Value, tblAssets.Date_Disposed, tblAssets.Replacement_Date, tblAssets.UNSW_Asset, tblAssets.Notes
FROM tblAssets INNER JOIN tblLoaned ON tblAssets.Asset_ID = tblLoaned.Asset_ID
WHERE (((tblAssets.Asset_ID)="*'" & [Forms]![frmAssets]![Cbo1] & "'*") AND ((tblAssets.Tag_Number)="*'" & [Forms]![frmAssets]![Cbo2] & "'*") AND ((tblAssets.Custodian_Name)="*'" & [Forms]![frmAssets]![Cbo3] & "'*"));

Regards,
Nim

 

[TheAceMan1]

nim180 . . .

The SQL you provided was a [blue]big insight![/blue] There are several problems in the [blue]WHERE[/blue] clause:
[ol][li]You should be using the [blue]Like[/blue] operator for what you've shown. Your using [blue]wildcards[/blue] to filter, and I'm not so sure this is what you intended.[/li]
[li]Direct errors occur below in [red]red[/red]:

[blue]Change: ((tblAssets.Asset_ID)="*'" & [Forms]![frmAssets]![Cbo1] & "'*")
To    : ((tblAssets.Asset_ID) [red][B][I]LIKE "'*[/I][/B][/red]" & [Forms]![frmAssets]![Cbo1] & "[red][B][I]*'[/I][/B][/red]")[/blue]

[/li][/ol]
To pin down the WHERE caluse please answer all the following questions:
[ol][li]Is it your intent to filter using wildcards, particularly [blue]Asset_ID[/blue]?[/li]
[li]What are the data types in the table(s) ([blue]text/numeric[/blue]) of:
[purple]Asset_ID
Tag_Number[/purple][/li]
[li]For the comboboxes, whats the [blue]column number[/blue] used?
Note: from left to right column number starts at zero.[/li][/ol]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[nim180]

Hi everyone,

Got it working with a bit of fiddling, i used the following on the onclick event of a button.

If Not IsNull(Me.Cbo1) Then
strWhere = strWhere & " (tblAssets.Asset_ID) Like '*" & Me.Cbo1 & "*'  AND"
End If

If Not IsNull(Me.Cbo2) Then
strWhere = strWhere & " (tblAssets.Tag_Number) Like '*" & Me.Cbo2 & "*'  AND"
End If

If Not IsNull(Me.Cbo3) Then
strWhere = strWhere & " (tblAssets.Custodian_Name) Like '*" & Me.Cbo3 & "*'  AND"
End If


strWhere = Mid(strWhere, 1, Len(strWhere) - 5)


    stDocName = "repAssets"
    DoCmd.OpenReport stDocName, acPreview, , strWhere

Thanks for the help,
Nim