More Q: on combo boxes

Hi,

In a form, I had a combo box which selecting CLIENT_ID would trigger a
report with said client info base on the following expression:



DoCmd.OpenReport stDocName, acViewPreview, , " [CLIENT_ID] = " &
Me!Combo25.Column(0)

Me!Combo25 = ""



I now need the combo to look at two parameters: CLIENT_ID and STATUS_ID
before passing the info to the report.

I added STATUS_ID in the combo query and a second column so that CLIENT_ID
and STATUS_NAME can be displayed. (Select CLIENT_ID, STATUS_NAME,
STATUS_ID..)



I'd imagine I need to do the following?:



DoCmd.OpenReport stDocName, acViewPreview, , " [SOLICITUD_ID] AND
[dbo.TIPO_SOLICITUD.TIPO_SOLICITUD_ID] = " & Me!Combo25.Column(0) and &
Me!Combo25.Column(2)

Me!Combo25 = ""



Any help is appreciated.

TIA

Ana

I ment:

DoCmd.OpenReport stDocName, acViewPreview, , " [CLIENT_ID] AND
[STATUS_ID] = " & Me!Combo25.Column(0) and &
Me!Combo25.Column(2)
Me!Combo25 = ""

DoCmd.OpenReport stDocName, acViewPreview, , _
"[CLIENT_ID] = " & Me!Combo25.Column(0) & _
" AND [STATUS_ID] = " Me!Combo25.Column(2)

This assumes that both CLIENT_ID and STATUS_ID are numeric fields. If, for
example,STATUS_ID was a text field, you'd need to include quotes:

DoCmd.OpenReport stDocName, acViewPreview, , _
"[CLIENT_ID] = " & Me!Combo25.Column(0) & _
" AND [STATUS_ID] = '" Me!Combo25.Column(2) & "'"

Exagerated for clarity, that last line is


" AND [STATUS_ID] = ' " Me!Combo25.Column(2) & " ' "

Incidentally, if you're trying to unselect what ever was selected in the
combo box after you open the report, you might be better using

Me!Combo25 = Null

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Ana" wrote in message
...
I ment:

DoCmd.OpenReport stDocName, acViewPreview, , " [CLIENT_ID] AND [STATUS_ID]
= " & Me!Combo25.Column(0) and & Me!Combo25.Column(2)
Me!Combo25 = ""

Hi Douglas,
I made the changes you suggested but the report still isn't using status_id
as reference, it only looks at client_id (both client_ and status_id are
int). I need to force the report (or query) to use both ids as reference.
Any ideas?
Thanks much.
Ana

"Douglas J. Steele" escribió en el
mensaje de noticias ...
DoCmd.OpenReport stDocName, acViewPreview, , _
"[CLIENT_ID] = " & Me!Combo25.Column(0) & _
" AND [STATUS_ID] = " Me!Combo25.Column(2)

This assumes that both CLIENT_ID and STATUS_ID are numeric fields. If, for
example,STATUS_ID was a text field, you'd need to include quotes:

DoCmd.OpenReport stDocName, acViewPreview, , _
"[CLIENT_ID] = " & Me!Combo25.Column(0) & _
" AND [STATUS_ID] = '" Me!Combo25.Column(2) & "'"

Exagerated for clarity, that last line is


" AND [STATUS_ID] = ' " Me!Combo25.Column(2) & " ' "

Incidentally, if you're trying to unselect what ever was selected in the
combo box after you open the report, you might be better using

Me!Combo25 = Null

--

It should work.

What does your code look like now? What happens when the code runs? Do you
get an error? If so, what's the error? If you don't get an error, does the
report show all status ids, or a subset (the wrong subset) for each client?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Ana" wrote in message
...
Hi Douglas,
I made the changes you suggested but the report still isn't using
status_id
as reference, it only looks at client_id (both client_ and status_id are
int). I need to force the report (or query) to use both ids as reference.
Any ideas?
Thanks much.
Ana

"Douglas J. Steele" escribió en el
mensaje de noticias ...
DoCmd.OpenReport stDocName, acViewPreview, , _
"[CLIENT_ID] = " & Me!Combo25.Column(0) & _
" AND [STATUS_ID] = " Me!Combo25.Column(2)

This assumes that both CLIENT_ID and STATUS_ID are numeric fields. If,
for
example,STATUS_ID was a text field, you'd need to include quotes:

DoCmd.OpenReport stDocName, acViewPreview, , _
"[CLIENT_ID] = " & Me!Combo25.Column(0) & _
" AND [STATUS_ID] = '" Me!Combo25.Column(2) & "'"

Exagerated for clarity, that last line is


" AND [STATUS_ID] = ' " Me!Combo25.Column(2) & " ' "

Incidentally, if you're trying to unselect what ever was selected in the
combo box after you open the report, you might be better using

Me!Combo25 = Null

--

The form works so I don't get any errors.
However, the report still bases on client_id. In my case, I have 2 client_id
with the same number. To differenciate them, status_id is used. That's why
it's important that both ids are trasfered to the report.
The report is linked to a query which has both ids selected.
I think the problem is that on the form I have to select the main column as
reference so by default, column(1) is selected (client_id). If I can select
2 columns may solve my problem but it appears that Access doesn't allow it.
Rgs,
Ana

"Douglas J. Steele" escribió en el
mensaje de noticias ...
It should work.

What does your code look like now? What happens when the code runs? Do you
get an error? If so, what's the error? If you don't get an error, does the
report show all status ids, or a subset (the wrong subset) for each
client?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Ana" wrote in message
...
Hi Douglas,
I made the changes you suggested but the report still isn't using
status_id
as reference, it only looks at client_id (both client_ and status_id are
int). I need to force the report (or query) to use both ids as reference.
Any ideas?
Thanks much.
Ana

"Douglas J. Steele" escribió en el
mensaje de noticias ...
DoCmd.OpenReport stDocName, acViewPreview, , _
"[CLIENT_ID] = " & Me!Combo25.Column(0) & _
" AND [STATUS_ID] = " Me!Combo25.Column(2)

This assumes that both CLIENT_ID and STATUS_ID are numeric fields. If,
for
example,STATUS_ID was a text field, you'd need to include quotes:

DoCmd.OpenReport stDocName, acViewPreview, , _
"[CLIENT_ID] = " & Me!Combo25.Column(0) & _
" AND [STATUS_ID] = '" Me!Combo25.Column(2) & "'"

Exagerated for clarity, that last line is


" AND [STATUS_ID] = ' " Me!Combo25.Column(2) & " ' "

Incidentally, if you're trying to unselect what ever was selected in the
combo box after you open the report, you might be better using

Me!Combo25 = Null

--

Is the Status_ID in the 3rd column of the combo box? That's what your code
assumes.

DoCmd.OpenReport stDocName, acViewPreview, , _
"[CLIENT_ID] = " & Me!Combo25.Column(0) & _
" AND [STATUS_ID] = " Me!Combo25.Column(2)

Try this.

Dim strCriteria As String

strCriteria = "[CLIENT_ID] = " & Me!Combo25.Column(0) & _
" AND [STATUS_ID] = " Me!Combo25.Column(2)
MsgBox strCriteria
DoCmd.OpenReport stDocName, acViewPreview, , strCriteria

Does the message box contain what you expect?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Ana" wrote in message
news
The form works so I don't get any errors.
However, the report still bases on client_id. In my case, I have 2
client_id with the same number. To differenciate them, status_id is used.
That's why it's important that both ids are trasfered to the report.
The report is linked to a query which has both ids selected.
I think the problem is that on the form I have to select the main column
as reference so by default, column(1) is selected (client_id). If I can
select 2 columns may solve my problem but it appears that Access doesn't
allow it.
Rgs,
Ana

"Douglas J. Steele" escribió en el
mensaje de noticias ...
It should work.

What does your code look like now? What happens when the code runs? Do
you get an error? If so, what's the error? If you don't get an error,
does the report show all status ids, or a subset (the wrong subset) for
each client?

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Ana" wrote in message
...
Hi Douglas,
I made the changes you suggested but the report still isn't using
status_id
as reference, it only looks at client_id (both client_ and status_id are
int). I need to force the report (or query) to use both ids as
reference.
Any ideas?
Thanks much.
Ana

"Douglas J. Steele" escribió en el
mensaje de noticias ...
DoCmd.OpenReport stDocName, acViewPreview, , _
"[CLIENT_ID] = " & Me!Combo25.Column(0) & _
" AND [STATUS_ID] = " Me!Combo25.Column(2)

This assumes that both CLIENT_ID and STATUS_ID are numeric fields. If,
for
example,STATUS_ID was a text field, you'd need to include quotes:

DoCmd.OpenReport stDocName, acViewPreview, , _
"[CLIENT_ID] = " & Me!Combo25.Column(0) & _
" AND [STATUS_ID] = '" Me!Combo25.Column(2) & "'"

Exagerated for clarity, that last line is


" AND [STATUS_ID] = ' " Me!Combo25.Column(2) & " ' "

Incidentally, if you're trying to unselect what ever was selected in
the
combo box after you open the report, you might be better using

Me!Combo25 = Null

--

Is the Status_ID in the 3rd column of the combo box? That's what your code
assumes.

Yes. I've Status_Name as a second column (1)


Dim strCriteria As String

strCriteria = "[CLIENT_ID] = " & Me!Combo25.Column(0) & _
" AND [STATUS_ID] = " Me!Combo25.Column(2)
MsgBox strCriteria
DoCmd.OpenReport stDocName, acViewPreview, , strCriteria

Does the message box contain what you expect?

Yes. It displays: [CLIENT_ID] = AND [STATUS_ID] =
However, if I change ...Column(2) to ....Column(1), the box displays the
same msg as above.
In few words, the combo box doesn't see (or pass) anything but column(0).

I'm guessing that the ColumnCount property of the combo box doesn't reflect
the true count.

The Column collection only goes as high as the ColumnCount property says to.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Ana" wrote in message
...
Is the Status_ID in the 3rd column of the combo box? That's what your code
assumes.

Yes. I've Status_Name as a second column (1)


Dim strCriteria As String

strCriteria = "[CLIENT_ID] = " & Me!Combo25.Column(0) & _
" AND [STATUS_ID] = " Me!Combo25.Column(2)
MsgBox strCriteria
DoCmd.OpenReport stDocName, acViewPreview, , strCriteria

Does the message box contain what you expect?

Yes. It displays: [CLIENT_ID] = AND [STATUS_ID] =
However, if I change ...Column(2) to ....Column(1), the box displays the
same msg as above.
In few words, the combo box doesn't see (or pass) anything but column(0).

Douglas,
Is there a way around?
TIA
Ana

"Douglas J. Steele" escribió en el
mensaje de noticias ...
I'm guessing that the ColumnCount property of the combo box doesn't
reflect the true count.

The Column collection only goes as high as the ColumnCount property says
to.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"Ana" wrote in message
...
Is the Status_ID in the 3rd column of the combo box? That's what your
code
assumes.

Yes. I've Status_Name as a second column (1)


Dim strCriteria As String

strCriteria = "[CLIENT_ID] = " & Me!Combo25.Column(0) & _
" AND [STATUS_ID] = " Me!Combo25.Column(2)
MsgBox strCriteria
DoCmd.OpenReport stDocName, acViewPreview, , strCriteria

Does the message box contain what you expect?

Yes. It displays: [CLIENT_ID] = AND [STATUS_ID] =
However, if I change ...Column(2) to ....Column(1), the box displays the
same msg as above.
In few words, the combo box doesn't see (or pass) anything but column(0).