Report values from listbox

[Jerome]

Hello,

I have a form that searches a table with 8 columns. The data that meets the search criteria are shown in a list box. I would like to make a report of the list box content.

I've tried a lot of ways to bound the list box content to the report controls but still without success.

What's the easiest way to do this?

Thanks in advance and with best regards

 

[John Big Booty]

Are you using a query to populate your List Box? If so you should be able to use that same query as Record Source for your Report.

 

[Jerome]

I use this piece of code to populate my listbox:

Private Sub TextSearchString_Change()

On Error Resume Next
Dim StrSql              As String 'SQL statement for the record source
Dim sText               As String 'Contents of the criteria control
sText = Trim(Me.TextSearchString.Text)

StrSql = "SELECT * FROM TblCh "

    If Not sText = "" And IsDelOrBack = False Then
            
            StrSql = StrSql & "WHERE " & Me.ComboCategory.Column(1) & " Like '" & "*" & sText & "*" & "*' ORDER BY " & Me.ComboCategory.Column(1) & ";"
            
            Me.List.RowSource = StrSql
                
    End If

    Me.List.Requery

End Sub

I have no idea how to convert this to use to fill my report (I am pretty new into access )

 

[John Big Booty]

You should be able to use your SQL to create a query that you can then use to generate your report.

 

[Jerome]

Hi,

I've done the following:

DoCmd.OpenReport "RptTest", , , [WHERE (((TblTest.[Name]) Like "*" & li & "*")))]

but this gives error: error 2465 Can't find the reference of field | (translated)

I have created the report using the report wizard and the name is correct. What is field "|" ?

I've made the SQL query using the query wizzard. I copied the Where part into the code. The query worked ok.

What am I doing wrong?

 

[boblarson]

You don't use the word WHERE in there, just use:

DoCmd.OpenReport "RptTest", , , [Name] Like "'*" & [YourControlNameHere] & "*'"

Also, you should NOT be using NAME as a field name. NAME is an Access Reserved Word and is also one of the ones which will cause you grief even if you do put it in brackets. I would suggest RENAMING the field ASAP (unless you just used that as an example).

 

[Jerome]

Hi,

I've tried:

DoCmd.OpenReport "RptTest", , , [TestName] Like "'*" & [sText] & "*'"

But this also gives a error (2465)...... Why is is not necessary to lint to the table name? like

DoCmd.OpenReport "RptTest", , , [TblTest.TestName] Like "'*" & [sText] & "*'"

(This also does not work ?)

BTW: sText is public declared as string

Here is the database, maybe something else is wrong?

Thanks in advance

 

[Jerome]

sText = Trim(Me.TextSearchString.Text)

should be placed within :

Private Sub ButtonPrint_Click()

 

[Jerome]

I've checked the code once again to see if I can find out what's causing the error. As far as I can see nothing is wrong with the form, report and VBA code.

I am really stuck on this one... mainly because I get a strange (general) error.

I am sure It's just a small this I've overlooked, so I somebody can please be so kind to check the database to see what's wrong. That would be really great

Thanks in advance and with best regards,

Jerome.

 

[boblarson]

Change your print button code to this:

Private Sub ButtonPrint_Click()
   DoCmd.OpenReport "RptChemicals", acViewPreview, , Me.ComboCategory.Column(1) & " Like '" & "*" & sText & "*" & "*'"
End Sub

 

[Jerome]

Thanks

Why is it not neccecary to also use the table name?

 

[boblarson]

Thanks

Why is it not neccecary to also use the table name?

Because you are looking for a field on the Form or report that is opening, not the table.

 

[Jerome]

Ok, that makes sense.

Antoher thing, I use:

StrSql = StrSql & "WHERE " & Me.ComboCategory.Column(1) & " Like '" & "*" & sText & "*" & "*' ORDER BY " & Me.ComboFilteredBy.Column(1) & ";"

to populate the listbox with the search form.

The report I make is filtered the "default" way, and not according to the combobox I've made.

I've tried:

DoCmd.OpenReport "RptChemicals", acViewPreview, , Me.ComboCategory.Column(1) & " Like '" & "*" & sText & "*" & "*' ORDER BY " & Me.ComboFilteredBy.Column(1) & ";"

But this gives a error..... I think something is wrong with the syntax.

Is there some information about how SQL commands are built up? I mean what kind of statements are available (ORDER, LIKE, AND, ect...)? and how to use the "*"?
I try to understand these statements but untill now I'ts very difficult.

Thanks in advance.

 

[boblarson]

You can't use the Order by in there.

 

[Jerome]

Ok, but how can you determine the sort order for your report?

 

[boblarson]

Use this:

Private Sub ButtonPrint_Click()
   DoCmd.OpenReport "RptChemicals", acViewPreview, , Me.ComboCategory.Column(1) & " Like '" & "*" & sText & "*" & "*'"
   Reports!RptChemicals.OrderBy = Me.ComboFilteredBy.Column(1)
   Reports!RptChemicals.OrderByOn = True
End Sub