retrieving the column value in a multicolumns combo

[rocco]

Hello,
this is driving me crazy...
I have a combox which lists all the visit patients have done during a week.
Each patient can come twice but mostly they come once.
Each patient has an ID which is a number and two letters.
A combobox will allow me to retrieve info for the visits patients have done.
The combobox has two column: the first one list the patient ID and it is the
bound one; the second list the data of the visit.
Trouble comes when a patient comes twice. let's think to patient coded :1TO
who had two visits first on 7/29/2006 and second on 7/30/2006. My combo will
list someting like:
1TO | 7/29/06
1TO | 7/30/06
I would like to retrive the info for each visit using the value for the
second column to build a filter but...
If i try to select 1TO | 7/30/06 in the combo, it comes up with the data
for the 7/29/06 visit!!!
It seems like I'm not selecting the second row, but always the first
one!This is the portion of code I'm using to retrieve the info and
re-populate the unbound form:
rst.Open "SELECT * FROM " & TABL.Name & " WHERE ID='" &
Forms(ff.Name).Controls("FINDVISIT").Value & "' AND VISITDATE=#" &
Forms(ff.Name).Controls("FINDVISIT").Column(1) & "#",
CurrentProject.Connection, adOpenStatic, adLockReadOnly
For i = 0 To rst.Fields.Count - 4

Forms(ff.Name).Section(acDetail).Controls(rst.Fields(i).Name).Value =
rst.Fields(i)
Next i
rst.Close


thanks folks,
Rocco

 

[Marshall Barton]

I have a combox which lists all the visit patients have done during a week.
Each patient can come twice but mostly they come once.
Each patient has an ID which is a number and two letters.
A combobox will allow me to retrieve info for the visits patients have done.
The combobox has two column: the first one list the patient ID and it is the
bound one; the second list the data of the visit.
Trouble comes when a patient comes twice. let's think to patient coded :1TO
who had two visits first on 7/29/2006 and second on 7/30/2006. My combo will
list someting like:
1TO | 7/29/06
1TO | 7/30/06
I would like to retrive the info for each visit using the value for the
second column to build a filter but...
If i try to select 1TO | 7/30/06 in the combo, it comes up with the data
for the 7/29/06 visit!!!
It seems like I'm not selecting the second row, but always the first
one!This is the portion of code I'm using to retrieve the info and
re-populate the unbound form:
rst.Open "SELECT * FROM " & TABL.Name & " WHERE ID='" &
Forms(ff.Name).Controls("FINDVISIT").Value & "' AND VISITDATE=#" &
Forms(ff.Name).Controls("FINDVISIT").Column(1) & "#",
CurrentProject.Connection, adOpenStatic, adLockReadOnly
For i = 0 To rst.Fields.Count - 4

Forms(ff.Name).Section(acDetail).Controls(rst.Fields(i).Name).Value =
rst.Fields(i)
Next i
rst.Close

The Column property has an optional second argument for you
to indicate the row you want to retrive the value.

If the combo box's data set is sorted ascending by the date,
then you can get the latest date from the last entry:

...).Column(1, ...).ListCount-1)

 

[rocco]

thank you..but...what I need is to retrieve the value of the second column
for the combination ID and DATA that I choose from the combo.
I mean... looking at my example, If I choose the second row (and i mean I
select and click it), I will expect that the FINDVISIT control will show 1TO
and its .column(1) will be 7/30/06, even if I cannot see it.
Then I can use this value in the code.
But this is really not going to happen!
It turns out to be always and always 7/29/06.

 

[rocco]

Maybe I wasn't able to point out this clearly... what I mean is:
what code will you write to get the values, let say..., for the second
column of a multicolumn combobox AFTER you have choosen an entry in the combo
itself?
If your combo has only 2 columns and two entries:
1TO | 7/29/06
1TO | 7/30/06
Would you expect AFTER having clicked the second enter in the list that if
you question for .column(1) you will get 7/30/06?
Me yes!..but I always get 7/29/06....

 

[rocco]

i found it... I have better to play with the click event instead of the
change event.
But to me it sounds still odd that it doesn't give the *expected* value
after the change action...at least it changes because I choose something (and
thus I click..).
Thanks anyway.

 

[smk23]

rocco,
Something that is nifty that you might try is pulling the needed data in the
combobox itself. I use a query that collects whatever data I want to put in
my unbound controls in the rowsource of the combo. Then I use

unboundControl1= combo.Column(2)

or whatever column you need. In "columns width" property I just set all the
columns that I don't wish to view to zero.

 

[Marshall Barton]

Your code is correct for doing that:
...).Column(1)

But the Change event is definitely the wrong place. It
should be in the AfterUpdate event procedure, because that's
the event that signals a new value for the control (not
quite the same as the Click event).