[RESOLVED] ComboBox or DataCombo?

[osemollie]

I have read in many forums about the disadvantages of using bound controls in a VB project. However, I was wondering what to use in my case:-

I have a users table and a category table.
Users table has (userId, FirstName, LastName, catID).
Category table has:- (catId, catDescription)

The catId field in the users table , is a number that corresponds to a value in the catId field of the Category table. I need to use either of the controls to act as a lookup table. It displays the description of the categories but passes the catId to the users table. In so doing, the users must NOT know the catId inorder to choose category id's for the users since they will read the descriptions and choose from the drop down. I need to INSERT new users and UPDATE. I also want to enable a user to be able to add categories that might be missing from the already created categories.

Now how can I implement this? Can the same be implemented using code instead of bound controls?

[Hack]

Anything you can do with bound controls you can do better with code.

For creating new records or updating existing records I would use textboxes.

For a list of categories, I would use a standard combo box.

What kind of database are you using and how are you connecting to it now?

[osemollie]

Hi Hack,

I am using MSAccess 2003 as my database and I am connecting to the database by :

cnNewUser.Provider = "Microsoft.Jet.OLEDB.4.0"
cnNewUser.Open App.Path & "./databases/Visualtek.mdb"

I just don't know how I can use the standard combobox as a lookup table. I am really looking forward to your help.

Thanks.

[si_the_geek]

See the Database FAQ's link below - it includes code to fill a [normal] combo via ADO (which is what you appear to be using).

It not only shows the text, but also sets a hidden (numeric) value for each item in the list, which you can read using the ItemData property.

[osemollie]

Hi,
I have gone through the tutorial. I have been able to populate the combobox. But when I try to use the INSERT statement, i get a compile error "Arguement not optional" where am I going wrong?

The code I have created is:-

sSQL = "INSERT INTO users (UserId, FirstName, LastName, catID,Date) VALUES ("
sSQL = sSQL & Val(txtIDcard.Text) & ", "
sSQL = sSQL & "'" & txtFirstName.Text & "', "
sSQL = sSQL & "'" & txtLastName.Text & "', "
sSQL = sSQL & "'" & cboCategory.ItemData & "', "
sSQL = sSQL & "'" & dtpLoanDate.Value & "') " ' This is a Date Picker control

cnAddMovie.Execute sSQL

_____________________

Thanks

[Hack]

Well, everything matches on boths sides of the VALUES clause, so it may be that you are having a problem because you are using a reserved word for a field name.

Date is a reserved word in just about every language I've ever heard of, so either (and this is preferable) change the field name, or, if you insist on sticking with using Date, you will need to put it in brackets.

sSQL = "INSERT INTO users (UserId, FirstName, LastName, catID,[Date]) VALUES ("

[osemollie]

I have sorted it out, it was something to do with the .ItemData(index)

Thanks very much

[si_the_geek]

In addition to Hacks' correction, there is a minor flaw with your use of ItemData - you need to specify which rows ItemData you want (even if it is selected). You can do it like this:

sSQL = sSQL & "'" & cboCategory.ItemData[u](cboCategory.ListIndex)[/u] & "', "

edit: so close, but you beat me to it!