More Q: on combo boxes

[Ana]

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

 

[Ana]

I ment:

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

 

[Douglas J. Steele]

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

 

[Ana]

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]

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

(no e-mails, please!)

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

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

--

 

[Ana]

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

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

(no e-mails, please!)

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

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

--

 

[Douglas J. Steele]

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

(no e-mails, please!)

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

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

(no e-mails, please!)

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" <NOSPAM_djsteele@NOSPAM_canada.com> 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

 

[Ana]

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 J. Steele]

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

(no e-mails, please!)

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).

 

[Ana]

Douglas,
Is there a way around?
TIA
Ana

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

(no e-mails, please!)

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 J. Steele]

Change the value of the property!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,
Is there a way around?
TIA
Ana

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

(no e-mails, please!)

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 J. Steele]

It just occurred to me that your question may mean something other than it
appears.

Are you saying that you only want the one column to show in the combo box,
but that you want to be able to refer to the other (hidden) columns? In that
case, you set the ColumnCount property to the correct value, and you set the
ColumnWidths property appropriately. If you've got 4 columns, and you only
want the first column to be visible, you'd use something like 1";0";0";0"
(or whatever width you want for the visible column)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Change the value of the property!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Douglas,
Is there a way around?
TIA
Ana

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

(no e-mails, please!)


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).