Populating a combo box with (x) number of fields from a database

scojones

I have a database with 3 Tables. Users, Loans, and Books. I'm trying to implement a search for these tables. On the main form the person running the app puts his/her mouse over "Users" or "Books" or "Loans" and a menu appears with the options "Edit", "Search", "Print". When they click search frmSearch is loaded. It can tell if you want to search for a user/book/loan via a global variable (don't want to get into that). So.. When frmSearch loads it has two combo boxes "cboType" and cboAttributes. cboType is "Users", "Books", "Loans". Now here's what i want to do (about time eh =P). When you select Books from cboType i want cboAttributes to load the fields from the Books table i.e "Title, Author" ect, and when you select Users from cboType i want cboAttributes to load the fields from the User table "forename, surname, address" ect, so as i can do search for stuff like all the steven king books in the db, or all members (users) with a certain surname.
Here's the snipet of code i'm stuck on:

<code>
Set rstTemp = X.OpenRecordset("User")
Do Until rstTemp.EOF
For i = 1 To rstTemp.Fields.Count
cboAttributes.AddItem rstTemp.Name, i
Next i
Loop
</code>

I know the value in rstTemp.Name will be "User". Am i going about this the wrong way. Your input would be much appreciated. Cheers

scojones

basically what i am asking is do i have to hardcode the field names or is there a way of loading them from the access database. Just the field names, not their values.

Talliesin

You are cycling through each field, but adding the name of the recordset to the combo.

Rather you want something more like:

Dim rstTemp as Recordset
Dim fiField as Field
Set rstTemp = X.OpenRecordset("User")
For Each fiField in rstTemp.Fields
	cboAttributes.AddItem fiField.Name
Next

silverside

ahh VB database programming...
my sympathies go out to you in writing that stuff, I've got stuck with maintaining an access/VB/ado database for internal use (not surprising as I wrote it originally) and the language is such a mess and badly documented : Oh if i could just take the time to write a proper ODBC wrapper around it it would be so much easier...

Anyway... sorry to digress I just wanted to get that off my chest :-)

scojones

Thanks Talliesin, it works like a charm

bonkey

Originally posted by sjones

<code>
Set rstTemp = X.OpenRecordset("User")
Do Until rstTemp.EOF
For i = 1 To rstTemp.Fields.Count
cboAttributes.AddItem rstTemp.Name, i
Next i
Loop
</code>

I know that this code of yours didnt work, but just a few quick comments on it (and Taliesin's code) which may be of use to you in future.

1) You have a loop running to EOF, but never do an rs.MoveNext. so this would end up in an infinite loop from what I can see.

2) In general, I wouldnt bother specifying the second parameter in combo.AddItem. This is the index position to add at. Omitting this will autmatically add the value to the end of the list anyway...as Taliesin's code snippet demonstrates. This is probably at odds with my usual "clarity is better" stance, but hey - I never said I was consistent

3) I would add a cboResults.Clear (or whatever it is) immediately before you start adding the records. This way, you can be 100% sure you never add more than one set of values to the combo through some unexpected set of events firing or something.

4) I would also add some extra code to check to see what was loaded into the combo, and what I needed. In otherwords, if an event tells me I need to load the combo with values from Table X, the first thing I would do is check that the combo wasnt already loaded with values from Table X

jc

Talliesin

Originally posted by bonkey
2) In general, I wouldnt bother specifying the second parameter in combo.AddItem. This is the index position to add at. Omitting this will autmatically add the value to the end of the list anyway...as Taliesin's code snippet demonstrates. This is probably at odds with my usual "clarity is better" stance, but hey - I never said I was consistent

Add a comment stating that you are allowing it to be added automatically to the end and you have the advantage of both.

On a similar note while

Next i

is more readable than

Next

especially with nested loops, it is less efficient (there's no good reason why it should be less efficient, it's something that should be optimised away, but it isn't).

Next 'i

has the advantages to a human reader of the first, but is the same as the more efficient second example to the compiler.

As for what silverside said about VB database coding. For something as simple as this VB is a perfectly good solution. I hate a lot of things about VB, but it has its place.

bonkey

Originally posted by silverside
my sympathies go out to you in writing that stuff, I've got stuck with maintaining an access/VB/ado database for internal use (not surprising as I wrote it originally) and the language is such a mess and badly documented : )

Ahh...dont blame the language for your code

(Note to humourously impaired, I am taking the p1ss here).

VB is a bit clunky at times, but its DB Access is, in general, excellent (IMHO).

Of course, having said that, I should qualify to say that certain approaches are excellent - typically the ones where you do everything through code and dont borhter using dynamic resultsets or anything for Inserts/Updates/Deletes.

And documentation....I'd agree for vb6 - the docco was terrible. I always thought VB5 was one of the best-documented development languiages I've ever worked with, though.

jc

The Corinthian

Originally posted by bonkey
And documentation....I'd agree for vb6 - the docco was terrible. I always thought VB5 was one of the best-documented development languiages I've ever worked with, though.

I agree. VB5 was a long time coming to market and I’ve always considered VB6 to have be (coming, as it did, less than a year after VB5) more of a VB5.1 than a new version in itself (look at the difference between VB3 and VB4 or, in particular, VB4 and VB5 as a comparison).

So we got a few extra OCX’s and DLL’s, and slightly better support for Web related development, while in recompense all the help files were then bundled away onto separate disks that would grab an unfeasible amount of space (at the time devoting 2Gb to any one application was asking a lot) on your hard disk, and if you didn’t you invariably lost the CD’s

silverside

actually you are probaly right about VB being ideal for this kind of work, I haven't used VB 6 but it is supposed to be a big improvement.

What I was working on is access VBA: its fine for very simple stuff but for anything where you have to do complex validations or automatic updates, it just gets messy, and it's hard to determine what's really happening in the background. I don't know if VB has got better over the years in that regard - I suppose coming from a VC++ background the different approach just takes a bit of getting used to.

scojones

Thanks alot for the input people. I agree with the comments about vb + database programing.