find all values in a query

[boboivan]

Hi guys!

I've created a query (TestQuery) in order to see all IDlead in my "TableTest", related to each ID in "PaperOut" table.



When I open the report for the "IDpaper" record 3 (from the form "PaperOut"), I need to see all the names of IDlead (1,2,5,6).
For this, I've tried the following code, but it returns only the name of the first ID (1):

Private Sub Report_Load()
If DLookup("IDlead", "TestQuery", "[IDpaper] =" & Forms("PaperOut").IDpaper) = 1 Then
Me.Text17 = "the name of the leader 1"
ElseIf DLookup("IDlead", "TestQuery", "[IDpaper] =" & Forms("PaperOut").IDpaper) = 2 Then
Me.Text19 = "the name of the leader 2"
ElseIf DLookup("IDlead", "TestQuery", "[IDpaper] =" & Forms("PaperOut").IDpaper) = 3 Then
Me.Text20 = "the name of the leader 3"
ElseIf DLookup("IDlead", "TestQuery", "[IDpaper] =" & Forms("PaperOut").IDpaper) = 4 Then
Me.Text21 = "the name of the leader 4"
ElseIf DLookup("IDlead", "TestQuery", "[IDpaper] =" & Forms("PaperOut").IDpaper) = 5 Then
Me.Text22 = "the name of the leader 5"
ElseIf DLookup("IDlead", "TestQuery", "[IDpaper] =" & Forms("PaperOut").IDpaper) = 6 Then
Me.Text23 = "the name of the leader 6"
End If
End Sub

Any help, please?

[ranman256]

You don't use IIF statements.
make a conversion table,this has the values and text to join to the other table.
1, leader1
2,leader2
etc.

Join the 2 tables in a query to get the results.
no need for vb code.

[boboivan]

Thx Ranman!

Maybe I didn't make myself very clear.
The point is that in my table I have a field with multiple values (ex: John, Beth, Ruth).
When I open the report for the an specific record of my table (where I have these 3 options selected), I need to see all the choices, each in separated textboxes (in textbox19 to see John, in textbox20 Beth, in textbox 21 Ruth)
How can I do that?

Thx again!

[ranman256]

The 'text boxes' would be records. John is 1 record, Mary is another record,etc.
these would show as a data sheet in a subForm.
This is how multi value data is shown.

[boboivan]

I know that, but it doesn't help too much.
I need each textbox to have additional info and different formats. I've set the textboxes as rich boxes in order to do that as I need.

[Bulzie]

Can you use the OnFormat event in the detail section to do what you need. Make sure IDLead field is in the detail section. Maybe something like:

If Me.IDlead = 1 Then
Me.Text17 = "the name of the leader 1"
ElseIf Me.IDlead = 2 Then
Me.Text19 = "the name of the leader 2"
ElseIf Me.IDlead = 3 Then
Me.Text20 = "the name of the leader 3"
ElseIf Me.IDlead = 4 Then
Me.Text21 = "the name of the leader 4"
ElseIf Me.IDlead = 5 Then
Me.Text22 = "the name of the leader 5"
ElseIf Me.IDlead = 6 Then
Me.Text23 = "the name of the leader 6"
End If

[boboivan]

Thx Bulzie, but it doesn't work either.

[aytee111]

Can you post your db?

[boboivan]

I've tried, but the size limit of the attached files on this site is max 500kB. My db is over this limit.

[andy49]

How about creating a record set to run through. Something like...

Option Compare Database

Private Sub filltxts()

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("TestQuery")
Do Until EOF
Select Case rs!IDPaper
Case Is = 1
Me.Text17 = "the name of the leader 1"
Case Is = 2
Me.Text19 = "the name of the leader 2"
Case Is = 3
Me.Text20 = "the name of the leader 3"
Case Is = 4
Me.Text21 = "the name of the leader 4"
Case Is = 5
Me.Text22 = "the name of the leader 5"
Case Is = 6
Me.Text23 = "the name of the leader 6"
End Select
rs.MoveNext
Loop
End If
End Sub

[ssanfu]

I've tried, but the size limit of the attached files on this site is max 500kB. My db is over this limit.

Do a "Compact & Repair", then Zip it. You should then be able to attach it to a post.

The point is that in my table I have a field with multiple values (ex: John, Beth, Ruth).

It sounds like you have used a multi-value field in a table. These type of fields are a real PITA (IMO) to use......

You might see
https://support.office.com/en-us/art...C-6DE9BEBBEC31
and
https://support.office.com/en-us/art...3-B6624E1E323A

[boboivan]

Thx andy49!
You got the point of my issue.
Your code returns though a Compile error: Argument not optional, line: Do Until EOF
Maybe it's because I didn't used the code in the right place.
I took ssanfu advice and I've made a smaller size DB, in order to post it here. Check the attachment!

Thx again!
Happy new year!

[andy49]

Aha. Should be do until rs.eof

Silly error!!

I'll look on my laptop in the new year


Sent from my iPhone using Tapatalk

[andy49]

The alternative (as you'll end up with empty text boxes) could be to have 6 text boxes text1, text2 etc.

find how many records the query has (four for 1,4,5,6 as an example)



Then set the value of the first four text boxes with me.txt1 = rs!idpaper

And make the remaining (2) text boxes invisible

Might look "neater" but not sure if that's what you're after


Sent from my iPhone using Tapatalk

[boboivan]

Now I get the Compile error: End if without block if: line End if
I've took out the line and I get the Run-time error '3061': Too few parameters. Expected 1: line Set rs = db.OpenRecordset("TestQuery")