Visual Basic Syntax - Combo box question

Author

Combo box question

David Clifford

2005-08-03, 4:02 am

Good morning all

I have a simple question, but I can't think of the answer...need more
coffee.

I have a small database table from which I draw a list of QA Schemes. The
list has 4 entries. Normally, I would want to populate a combobox with the
full list, however, at this time, I only want to get the first 3. Simple.
However, I have a generic function in a class that pulls all records from a
table and gives me my desired field in a combobox, so, my question is this:

I have my full list of 4 records, I only want the top 3...using the code
below, can I omit the 4th record?

cboScheme.Clear 'Add the different schemes to the
cboScheme combobox
Set rsGeneric = LabDetails.Get_Table_Recordset("SchemeType")
Do Until rsGeneric.EOF
cboScheme.AddItem rsGeneric("Scheme")
rsGeneric.MoveNext
Loop
cboScheme.ListIndex = 0 'Default Scheme

Thank you for your consideration and help

Best regards

David Clifford

Michael Cole

2005-08-03, 4:02 am

David Clifford wrote:
> Good morning all
>
> I have a simple question, but I can't think of the answer...need more
> coffee.
>
> I have a small database table from which I draw a list of QA Schemes.
> The list has 4 entries. Normally, I would want to populate a combobox
> with the full list, however, at this time, I only want to get the
> first 3. Simple. However, I have a generic function in a class that
> pulls all records from a table and gives me my desired field in a
> combobox, so, my question is this:
>
> I have my full list of 4 records, I only want the top 3...using the
> code below, can I omit the 4th record?
>

Dim i as Integer

> cboScheme.Clear 'Add the different schemes to
> the cboScheme combobox
> Set rsGeneric = LabDetails.Get_Table_Recordset("SchemeType")
> Do Until rsGeneric.EOF
> cboScheme.AddItem rsGeneric("Scheme")
> rsGeneric.MoveNext
i=i+1
If i = 3 Then Exit Do
> Loop
> cboScheme.ListIndex = 0 'Default Scheme

--
Regards,

Michael Cole

David Clifford

2005-08-03, 4:02 am

Perfect! Thank you very much for your help Michael

Best regards

David Clifford

"Michael Cole" <noone@hansen.com> wrote in message
news:%23uS4cbAmFHA.4028@TK2MSFTNGP10.phx.gbl...
> David Clifford wrote:
>
> Dim i as Integer
>
> i=i+1
> If i = 3 Then Exit Do
>
> --
> Regards,
>
> Michael Cole
>
>

Larry Serflaten

2005-08-03, 5:06 pm

"David Clifford" <sarpg939tNoSpam@SomeISP.com> wrote

> I have my full list of 4 records, I only want the top 3...using the code
> below, can I omit the 4th record?

Look ma, no new variables! ;-)

>
> cboScheme.Clear 'Add the different schemes to the
> cboScheme combobox
> Set rsGeneric = LabDetails.Get_Table_Recordset("SchemeType")
> Do Until rsGeneric.EOF
If Len(cboScheme.Tag) then cboScheme.AddItem cboScheme.Tag
cboScheme.Tag = rsGeneric("Scheme")
> rsGeneric.MoveNext
> Loop
> cboScheme.ListIndex = 0 'Default Scheme

LFS

David Clifford

2005-08-05, 5:05 pm

Humm...sorry Larry, could you explain how that works please?

Best regards

David Clifford

"Larry Serflaten" <serflaten@usinternet.com> wrote in message
news:u9mVxJCmFHA.3256@tk2msftngp13.phx.gbl...
>
> "David Clifford" <sarpg939tNoSpam@SomeISP.com> wrote
>
>
> Look ma, no new variables! ;-)
>
> If Len(cboScheme.Tag) then cboScheme.AddItem cboScheme.Tag
> cboScheme.Tag = rsGeneric("Scheme")
>
> LFS

Larry Serflaten

2005-08-05, 5:05 pm

"David Clifford" <sarpg939tNoSpam@SomeISP.com> wrote
> Humm...sorry Larry, could you explain how that works please?

It works by delaying the addition of the data by one loop.
The data is put in the Tag property in one iteration, and added
to the combobox in the next iteration.

When it gets to the last loop, the next-to-last item is added to
the combobox, and the last item is stored in the Tag property.
Because there is no loop after that, the last item never makes
it to the comboxbox, which was the desired effect.

LFS

David Clifford

2005-08-06, 4:03 am

Thank you Larry, that sounds really good! I will give it a try in a moment.
Thank you for your time.

Best regards
David Clifford

"Larry Serflaten" <serflaten@usinternet.com> wrote in message
news:%235L8YXgmFHA.572@TK2MSFTNGP15.phx.gbl...
>
> "David Clifford" <sarpg939tNoSpam@SomeISP.com> wrote
>
> It works by delaying the addition of the data by one loop.
> The data is put in the Tag property in one iteration, and added
> to the combobox in the next iteration.
>
> When it gets to the last loop, the next-to-last item is added to
> the combobox, and the last item is stored in the Tag property.
> Because there is no loop after that, the last item never makes
> it to the comboxbox, which was the desired effect.
>
> LFS
>

Russ Holsclaw

2005-08-10, 5:08 pm

"David Clifford" <sarpg939tNoSpam@SomeISP.com> wrote in message
news:%23GuCqxkmFHA.2152@TK2MSFTNGP14.phx.gbl...
> Thank you Larry, that sounds really good! I will give it a try in a
> moment.
> Thank you for your time.
>
> Best regards
> David Clifford

This whole exchange

dens me... especially since you seemed to consider
both approaches to the problem to be valid.

Questions should have been asked:

1. Do you want to list the "first 3", or "all but the last"?
2. Why are you so sure that the entry you don't want to list will always be
the last one?
3. What happens if the table is updated? After all, someone might delete
that last row (which is apparently defective, or you'd be listing it)?
4. What happens if someone adds a new row, will you want to exclude *it*?
or will you still want to exclude the fourth one?
5. If a new row is inserted, do you know for certain what position it will
occupy in the list, relative to the one you want to exclude?
6. If the list is fixed, why isn't it hard-coded into the program, instead
of being read from an updatable database?

The biggest irony here, for me, is that this application you're working on
apparently has something to do with Quality Assurance (QA).

Well, here's a "Quality Assurance scheme": know what you're doing. Is that
one on your list?

David Clifford

2005-08-15, 5:05 pm

As you know nothing about the project that I am working on, I don't think
that any of your comments are valid.

1. Both solutions are in fact valid. I want to exclude the last scheme on
the list because it is not yet on line. It will be very soon, and so the
"problems" that you highlight will not occur.
2. There will be no other schemes added to this scheme set. The last entry
is the last entry, there will be no others added to the list.
3. Someone may also delete the entire database. The table that I am
referring to is a look-up table and is not normally accessible to the user.
Therefore, nobody can delete the last or any other entry, unless they are
being malicious.
4. See 2.
5. See 2.
6. The table is not updatable via the user interface, it is a look-up table
that I have made.

You know nothing about my work, nor my programme, and I find your last two
sentences to be both ignorant and offensive.

"Russ Holsclaw" <russ@holsclaw.nyet> wrote in message
news:uXPsp2dnFHA.3900@TK2MSFTNGP09.phx.gbl...
> "David Clifford" <sarpg939tNoSpam@SomeISP.com> wrote in message
> news:%23GuCqxkmFHA.2152@TK2MSFTNGP14.phx.gbl...
>
> This whole exchange

dens me... especially since you seemed to consider
> both approaches to the problem to be valid.
>
> Questions should have been asked:
>
> 1. Do you want to list the "first 3", or "all but the last"?
> 2. Why are you so sure that the entry you don't want to list will always
be
> the last one?
> 3. What happens if the table is updated? After all, someone might delete
> that last row (which is apparently defective, or you'd be listing it)?
> 4. What happens if someone adds a new row, will you want to exclude *it*?
> or will you still want to exclude the fourth one?
> 5. If a new row is inserted, do you know for certain what position it will
> occupy in the list, relative to the one you want to exclude?
> 6. If the list is fixed, why isn't it hard-coded into the program, instead
> of being read from an updatable database?
>
> The biggest irony here, for me, is that this application you're working on
> apparently has something to do with Quality Assurance (QA).
>
> Well, here's a "Quality Assurance scheme": know what you're doing. Is that
> one on your list?
>

Russ Holsclaw

2005-08-15, 5:05 pm

I apologize for my lack of tact.

But, if the list is *forever* unchangable, why would it be kept in a
database?

If the list is truly inchangable, it would make more sense to simply
hard-code it into the program, rather than go through the formality of
reading the entries from a database table.

In 38 years as a programmer, I've learned that nothing is ever unchangable,
especially things as ephemeral as "QA Schemes". :-)

Assuming that it could change (however unlikely you may think that is), it
would make sense for the program to continue to be valid after those
changes are made, so as to minimize the impact of change.

By accepting the assumptions on which both of the "solutions" are
predicated, you have condemned yourself to having to change both the
database, and, almost certainly, the program, to make any change.

I was only trying to caution you about the implications of these two
methods, based on the fact that you are reading data from a database, which
is normally quite mutable, by definition.

In particular, a table in a relational database is normally supposed to be
regarded as an unordered set, unless you are looking at a recordset that
was explicitly ordered via an "ORDER BY" clause in SQL. Therefore, any
table with the same set of rows is considered to be equivalent, regardless
of which is "first", "last", "next" or "previous".

It makes more sense, therefore, to exclude the one member of the list that
happens to be last (in the current table) based on criteria other than its
position in the table.

What would make the most sense, in the context of a database, would be to
make this exclusion as the result of a "Where" clause in your database
query, establishing whatever criterion it is that makes you want to exclude
it. Since you didn't say what that was, I can't be any more specific than
that, except to say that if the "excludability" of it cannot be determined
by the data in the row, then it would make sense to add a field ... perhaps
a simple Boolean ... that conveys the semantic that corresponds to the
reason you're excluding it.

David Clifford

2005-08-16, 4:01 am

Thank you for your reply.
To re-iterate. I wanted to exclude the last entry in the table purely on the
basis that that scheme was not yet in use, therefore I did not want the name
of the scheme to appear in any combo boxes that lists that particular set of
schemes. Yes, I am very well aware of other methods of excluding entries in
a database table, however, as a fast method, I just wanted to exclude this
last entry in an unordered recordset, whilst at the same time not having to
re-write my generic function to exclude the entry...lazy yes..permanent no.
That's why I asked my original question. The recordset does not needed to be
ordered.
I wrote the small look-up table because this list of schemes crops up over
quite a few other forms. I see no sense in hardcoding this list innumberable
times when I can get the same information from a simple table, and an
equally simple function in a class module. Your solution of hardcoding the
list of schemes, to me, would be infinately more complicated to change if a
new scheme were indeed added. Yes, nothing is permanent, and a new scheme
may be added at some time in the future. To track down each form that
references this list of schemes, and then amend the hardcoded list, would
take ages. My method simply entails me adding a new scheme to the bottom of
my small look-up table. Maybe I'm wrong, but the table seems more logical to
me.
The excluded scheme is now in operation, and my code al la Michael Cole's
reply has been amended to include the full list now. Simple. I just wanted
to exclude the last entry. My programme, in numerous places, does include
the various exclusion methods that you highlight for other database tables,
and numerous tables include a boolean value to specically exclude
"redundant" methods/reagents/participants etc. After nearly 20 years of
database programming, I did manage to learn a few little tid bits.
"Russ Holsclaw" <russ@holsclaw.nyet> wrote in message
news:ul91ICcoFHA.3304@tk2msftngp13.phx.gbl...
> I apologize for my lack of tact.
>
> But, if the list is *forever* unchangable, why would it be kept in a
> database?
>
> If the list is truly inchangable, it would make more sense to simply
> hard-code it into the program, rather than go through the formality of
> reading the entries from a database table.
>
> In 38 years as a programmer, I've learned that nothing is ever
unchangable,
> especially things as ephemeral as "QA Schemes". :-)
>
> Assuming that it could change (however unlikely you may think that is), it
> would make sense for the program to continue to be valid after those
> changes are made, so as to minimize the impact of change.
>
> By accepting the assumptions on which both of the "solutions" are
> predicated, you have condemned yourself to having to change both the
> database, and, almost certainly, the program, to make any change.
>
> I was only trying to caution you about the implications of these two
> methods, based on the fact that you are reading data from a database,
which
> is normally quite mutable, by definition.
>
> In particular, a table in a relational database is normally supposed to be
> regarded as an unordered set, unless you are looking at a recordset that
> was explicitly ordered via an "ORDER BY" clause in SQL. Therefore, any
> table with the same set of rows is considered to be equivalent, regardless
> of which is "first", "last", "next" or "previous".
>
> It makes more sense, therefore, to exclude the one member of the list that
> happens to be last (in the current table) based on criteria other than its
> position in the table.
>
> What would make the most sense, in the context of a database, would be to
> make this exclusion as the result of a "Where" clause in your database
> query, establishing whatever criterion it is that makes you want to
exclude
> it. Since you didn't say what that was, I can't be any more specific than
> that, except to say that if the "excludability" of it cannot be determined
> by the data in the row, then it would make sense to add a field ...
perhaps
> a simple Boolean ... that conveys the semantic that corresponds to the
> reason you're excluding it.
>

Ken Halter

2005-08-16, 5:09 pm

"David Clifford" <rpg939t@ntlworld.com> wrote in message
news:%23XuORljoFHA.2484@TK2MSFTNGP15.phx.gbl...
> Thank you for your reply.
> To re-iterate. I wanted to exclude the last entry in the table purely on
> the

I believe Russ was letting you know that "the last entry" is not guaranteed
to be the last record in a recordset. There's no guarantee that records are
stored in any order. That means you have to have a sort column. I know this
by experience. We use DBs here for INI files (more or less). These records
need to be in a specific order. Problem is, the developer that designed this
mess never added a sort column. That meant that any time someone compacted
or repaired the database, the records were completely jumbled up (thanks,
who ever you are/were) and required that we manually build new tables (what
a pita). Thankfully, that problem's gone for good... but what a nightmare it
was.

--
Ken Halter - MS-MVP-VB - http://www.vbsight.com
DLL Hell problems? Try ComGuard - http://www.vbsight.com/ComGuard.htm
Please keep all discussions in the groups..

Russ Holsclaw

2005-08-16, 5:09 pm

David Said.
| > Thank you for your reply.
| > To re-iterate. I wanted to exclude the last entry in the table =
purely on=20
| > the
Ken said:
| I believe Russ was letting you know that "the last entry" is not =
guaranteed=20
| to be the last record in a recordset. There's no guarantee that =
records are=20
| stored in any order. That means you have to have a sort column. I know =
this=20
| by experience. We use DBs here for INI files (more or less). These =
records=20
| need to be in a specific order. Problem is, the developer that =
designed this=20
| mess never added a sort column. That meant that any time someone =
compacted=20
| or repaired the database, the records were completely jumbled up =
(thanks,=20
| who ever you are/were) and required that we manually build new tables =
(what=20
| a pita). Thankfully, that problem's gone for good... but what a =
nightmare it=20
| was.

Well put. That is exactly the point I was trying to make, although I was =
approaching it more from a theoretical viewpoint rather than a practical =
one. I had not actually experienced the fact that a compact/repair =
operation might change the order of an otherwise unordered table, but =
the fact that it can happen only underscores the point. Is that Jet, I =
presume? It never happened to me, but then I always have =
primary/clustering keys in my tables.

The fact is, though, that in the formal "Relational Model" according to =
Ted Codd and Chris Date, a "table" (what they called a "relation") is =
defined as a "set of tuples" (or "rows", if you prefer). The =
mathematical concept of a "Set" is not the same as that of a "List", in =
that there is no concept of order in a Set.

This point was brought home to me a few years ago, when one of my wife's =
friends, a genealogist, was working on making an index of some =
government records for which she had microfilmed copies. She was using =
a relational system under MSDOS (I think it was dBase or some such). =
While proofreading her compilation of data, she discovered that =
sometimes she had accidently skipped over an item on the microfilm. She =
wanted to correct the error by inserting a new row into the table at the =
position corresponding to its position in the original printed records, =
but found that the database permitted no such operation. She wondered =
if I could "fix" that. I could think of no way of doing it other than to =
add a column with some sort of "sequence number" that left a gap between =
adjacent rows, such that a new row could be inserted with a sequence =
number in between those of the "previous" row and the "next" row. This =
solution was flawed, however, because it could only handle so many =
insertions in one place before the entire table had to be re-numbered. =
This could happen if she had accidently skipped an entire page of =
material, or worse, a whole reel of microfilm. (It reminded me of the =
early days of Basic programming on PC's and other microcomputers, when =
inserting new lines of code into sequence-numbered source, and of =
similar chores when working on sequence-numbered code in Mainframe =
software systems).

It certainly proved to me that there are some jobs ill-suited to a =
relational database, even if the source data seems to be =
nicely-structured in tabular form.

Sponsored Links