By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,490 Members | 1,489 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,490 IT Pros & Developers. It's quick & easy.

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

P: 9
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.
Oct 17 '06 #1
Share this Question
Share on Google+
13 Replies


PEB
Expert 100+
P: 1,418
PEB
You want to et a value from a table/ query or from a field in a report?
Oct 21 '06 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Oct 23 '06 #3

Expert 5K+
P: 8,434
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.
Oct 23 '06 #4

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


Private Sub Report_Open(Cancel As Integer)
'------------------------------------------------------------
' Dodaj_stevilke_faktur_na_report
'
'------------------------------------------------------------
On Error GoTo Dodaj_stevilke_faktur_na_report_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.OpenRecordset("SELECT [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_report_Exit:
Exit Sub

Dodaj_stevilke_faktur_na_report_Err:
MsgBox Error$
Resume Dodaj_stevilke_faktur_na_report_Exit

End Sub

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

Any suggestions?

Thanks in advance
Oct 23 '06 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
try

okno.text = MyStr


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


Private Sub Report_Open(Cancel As Integer)
'------------------------------------------------------------
' Dodaj_stevilke_faktur_na_report
'
'------------------------------------------------------------
On Error GoTo Dodaj_stevilke_faktur_na_report_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.OpenRecordset("SELECT [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_report_Exit:
Exit Sub

Dodaj_stevilke_faktur_na_report_Err:
MsgBox Error$
Resume Dodaj_stevilke_faktur_na_report_Exit

End Sub

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

Any suggestions?

Thanks in advance
Oct 23 '06 #6

P: 9
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
Oct 30 '06 #7

MMcCarthy
Expert Mod 10K+
P: 14,534

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?
Oct 31 '06 #8

P: 9
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
Nov 2 '06 #9

PEB
Expert 100+
P: 1,418
PEB
Hey, are you macedonian or serbian???

;)
Nov 4 '06 #10

P: 9
Slovene, thanks for asking though.
Nov 6 '06 #11

NeoPa
Expert Mod 15k+
P: 31,476
My Sister-in-Law is from Slovenija. The breadbasket of the old Yugoslavia. I'm glad you found your answer :)
Nov 6 '06 #12

PEB
Expert 100+
P: 1,418
PEB
A very pretty country!

I've been for a while!

;)

Slovene, thanks for asking though.
Nov 6 '06 #13

MMcCarthy
Expert Mod 10K+
P: 14,534
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?
Nov 7 '06 #14

Post your reply

Sign in to post your reply or Sign up for a free account.