How to get values from a field shown in a single cell on a report?

[kdee]

Hi to everyone!

I'm actually a beginner in access/vba etc.

Like many of you, I also have a problem with something. That »something« is called »How to get values from a field in a table or a query shown in a single cell on a report? (They should be separated with commas and limited to a cell in a report)”.

If somebody knows the answer to this or knows the article about it, please , be so kind to reply an answer or direct me to some tutorial or etc.


Many thanks in advance.

[PEB]

You want to et a value from a table/ query or from a field in a report?

[MMcCarthy]

A report is not an interactive interface. It doesn't respond to clicking on it. You need to use a form for this.

Hi to everyone!

I'm actually a beginner in access/vba etc.

Like many of you, I also have a problem with something. That »something« is called »How to get values from a field in a table or a query shown in a single cell on a report? (They should be separated with commas and limited to a cell in a report)”.

If somebody knows the answer to this or knows the article about it, please , be so kind to reply an answer or direct me to some tutorial or etc.


Many thanks in advance.

[Killer42]

A report is not an interactive interface. It doesn't respond to clicking on it. You need to use a form for this.

Perhaps the desired end could be achieved by using a query which mushes the multiple fields together into a single output field in the required format, then using this in the report.
Otherwise, as mmccarthy says, you probably need to use a form.

[kdee]

For now I (and people who helped me ) achieved to put up something like this.


Private Sub Report_Open(Can cel As Integer)
'------------------------------------------------------------
' Dodaj_stevilke_ faktur_na_repor t
'
'------------------------------------------------------------
On Error GoTo Dodaj_stevilke_ faktur_na_repor t_Err

Dim rs As Recordset
Dim MyStr As String
Dim FirstRec As Boolean
Dim Responce
Dim okno As TextBox
set okno = [Report_Faktura-lot-osnovna].CMR_ji
Set rs = CurrentDb.OpenR ecordset("SELEC T [CMR-ji za fakturo].[CMR] FROM [CMR-ji za fakturo];")
If rs.RecordCount > 0 Then
MyStr = ""
FirstRec = True
While Not rs.EOF
If FirstRec Then
MyStr = rs![CMR]
Else
MyStr = MyStr & ", " & rs![CMR]
End If
rs.MoveNext
Wend
End If
rs.Close
Set rs = Nothing
Responce = MsgBox("My String = " & MyStr, vbOKOnly, " Example")

okno.value = MyStr

Dodaj_stevilke_ faktur_na_repor t_Exit:
Exit Sub

Dodaj_stevilke_ faktur_na_repor t_Err:
MsgBox Error$
Resume Dodaj_stevilke_ faktur_na_repor t_Exit

End Sub

There is a problem though. It shows an error "Too few parameters. Expected 1"

Any suggestions?

Thanks in advance

[MMcCarthy]

try

okno.text = MyStr

For now I (and people who helped me ) achieved to put up something like this.


Private Sub Report_Open(Can cel As Integer)
'------------------------------------------------------------
' Dodaj_stevilke_ faktur_na_repor t
'
'------------------------------------------------------------
On Error GoTo Dodaj_stevilke_ faktur_na_repor t_Err

Dim rs As Recordset
Dim MyStr As String
Dim FirstRec As Boolean
Dim Responce
Dim okno As TextBox
set okno = [Report_Faktura-lot-osnovna].CMR_ji
Set rs = CurrentDb.OpenR ecordset("SELEC T [CMR-ji za fakturo].[CMR] FROM [CMR-ji za fakturo];")
If rs.RecordCount > 0 Then
MyStr = ""
FirstRec = True
While Not rs.EOF
If FirstRec Then
MyStr = rs![CMR]
Else
MyStr = MyStr & ", " & rs![CMR]
End If
rs.MoveNext
Wend
End If
rs.Close
Set rs = Nothing
Responce = MsgBox("My String = " & MyStr, vbOKOnly, " Example")

okno.value = MyStr

Dodaj_stevilke_ faktur_na_repor t_Exit:
Exit Sub

Dodaj_stevilke_ faktur_na_repor t_Err:
MsgBox Error$
Resume Dodaj_stevilke_ faktur_na_repor t_Exit

End Sub

There is a problem though. It shows an error "Too few parameters. Expected 1"

Any suggestions?

Thanks in advance

[kdee]

rs (recordset) has a value (rs = nothing) when i run throuhg line by line.



I don't think VBA recognizes the filed [CMR-ji za fakturo].[CMR] or even the query [CMR-ji za fakturo] which are members of a database.

It recognizes docmd.openquery "CMR-ji za fakturo" (it opens query)

but not like this docmd.openquery [cmr-ji za fakturo]



could it be the same with sql's select from? How could I then refer to a field (column) inside the "CMR-ji za fakturo"?



SELECT "cmr-ji za fakturo".cmr from "cmr-ji za fakturo" doesn't work.

Thanks in advance

[MMcCarthy]

I don't think VBA recognizes the filed [CMR-ji za fakturo].[CMR] or even the query [CMR-ji za fakturo] which are members of a database.

Have you tried to run the query in the query window as follows:

SELECT CMR FROM [CMR-ji za fakturo];

What result do you get?

[kdee]

There is no problem in querying inside access mdb file.

SELECT CMR FROM [CMR-JI ZA FAKTURO]

works just fine in standard access query. It displays a column with all the data in it.

But I noticed that when I activate the vba procedure with the same select statement the query becomes empty. And it's probably the same reason why rs=nothing

Is there some need maybe to connect the querry with vba differently?

Thanks

[PEB]

Hey, are you macedonian or serbian???

;)

[kdee]

Slovene, thanks for asking though.

[NeoPa]

My Sister-in-Law is from Slovenija. The breadbasket of the old Yugoslavia. I'm glad you found your answer :)

[PEB]

A very pretty country!

I've been for a while!

;)

Slovene, thanks for asking though.

[MMcCarthy]

There is no problem in querying inside access mdb file.

SELECT CMR FROM [CMR-JI ZA FAKTURO]

works just fine in standard access query. It displays a column with all the data in it.

But I noticed that when I activate the vba procedure with the same select statement the query becomes empty. And it's probably the same reason why rs=nothing

Is there some need maybe to connect the querry with vba differently?

Have you resolved all your issues kdee?