Please help Trying to get combo box to work with Database!!! 1

[littlebyt]

I am doing a project for class and have been trying to get the combo box where you click on the name and the information pulls up on the next Tab. I get the list to work but not the pull up. Is there a code missing from my project? If any one can help or need more info please let me know. Still learning all of this Visual Basic and Access so thanks for your time.

 

[wekkew]

Hi
If you can supply a few more details I'll try to help:

re:I am doing a project for class and have been trying to get the combo box where you click on the name and the information pulls up on the next Tab


When you say next tab do you mean a tab strip control or just a text box that the user tabs into?

What do you do with the value from the combo box - do you pass it to a FIND or FILTER or paramter query/stored proc?

Has your program created a recrodset by this time? Is the record you need in that recordset?

How many record matches are you expecting for the combo box value?

Kate

 

[littlebyt]

The DataBase Combo box is for the record of last name. I am Using the Tab Dialog in the componets to try and make this work. I want it to be able to go to the next tab with all the info from the last name. I have them as array that is the database combo box. I am using access to connect with the database combo box. I get part of it to work. I get the last names but not all the info. If you need more just let me know. That is what i am trying to do. Thanks for helping.!!!!

 

[littlebyt]

Opps I am using databars to connect with the DBCombo box. i have no value for the DBCombo Box. I am still new at this.

 

[Niv3k]

What are databars??? Do you mean a data control? If so, are you using a DAO data control or an ADO data control? You will need to be a little more specific.

Kevin

 

[littlebyt]

Well it is under general and is called just Data. That is how I am linking my Database with it. I don't know the difference between the DAO or ADO I am a newby at it. Everything else works but that darn combo box. I have tried everything for it.. So thanks for the help.

 

[Niv3k]

Well, If other fields are working with the data control, (And I believe you are using the DAO data control), then add another data control (Data2) to your form and make it invisible. (Set visible=false)

Set it up to read from the "People" table of the database.

Set the Rowsource property of the dbcombo to Data2.
Set the Listfield property to the Last Name.
Set the DataSource property to Data1.
Set the DataField property to the name of the column you want the last name linked to.

If you are working with two different tables, it can get a little screwy, but you probably have to work with two different data controls.

 

[littlebyt]

Thanks for the tip!!!! Will try this and see how it works in a few. Margaret

 

[littlebyt]

well on my DAO does not have a Listfield property or DataSource property Are we using the right one. I know the combo box has those fields in it. Is there a code that I should use? Well I will keep working on it I might get it to work some time. Thanks again.

 

[littlebyt]

Well I have three data controls now how would I link them all together. The DBCombo box works but still can't pull from that box the rest of the information. Well thanks for the help!!!!

Margaret

 

[Niv3k]

Sorry, all those properties I mentioned were on the dbcombo. Here is a brief explanation of those properties on the dbCombo:

Rowsource : Which table the list in the drop-down comes from
Listfield : Which field in the above table to use
DataSource : The data control that controls the "Destination" data
DataField : The field in the destination to save the value in.

Kevin

 

[littlebyt]

Would like to be able to select from the box and the text boxes would go to the record in the database, ie such as pulling down a SSN and the Name, Address, City, State, Zip would appear below in the text boxes.

Thanks!

 

[littlebyt]

Well still trying to figure this one out Someone please help me.

 

[Niv3k]

Would you be willing to send me a .zip of the source code and database? I could take a look at it on my machine here quickly...

Kevin_G_Mossey@Fleet.com

 

[AtomicChip]

Try this:

Create a while new project. Under the "Project" menu in the VB IDE, click "References". Find the reference to "Microsoft ActiveX Data Object 2.0" and select it. Close the window, and go back to your project.

Create a module, name it whatever you want, and place the following code in it (modify the database name to what you need):

Public con as ADODB.Connection
Public rs as ADODB.Recordset

Public Sub SetCon()
With con
.ConnectionString=app.path "\databaseName.mdb;"
.Provider="Microsoft.Jet.2.0"
.Open
End With
End Sub

Public Sub SetRS(SQL)
With rs
.ActiveConnection=con
.Source = SQL
.LockType = adLockOptimistic
.CursorType = adOpenDynamic
.Open
End With
End Sub

Now, in your first form's load procedure, add the following code (assuming that the combobox mentioned in your above explanation is on that page, the table name is tblUsers, and the field that you are extracting info from is fldSSN):

Private Sub Form_Load()

SetCon 'This sets your connection object
SetRS "Select * FROM tblUsers"

'This will populate your combo box
Do While Not rs.EOF
cboSSN.addItem rs.Fields("fldSSN&quot
rs.MoveNext
Loop

End Sub

Now for the rest of the example, we'll assume that you have two text boxes on the form along with the combo box (named cboSSN). One named txtName, and the other txtAddress. We will also assume that the fields in the database have the same name, but are prefixed with "fld" instead of "txt". Add this code to your combobox's OnClick event.

Private Sub cboSSN_OnClick()

rs.MoveFirst

'This selects the requested record
SetRS "SELECT * FROM tblUsers WHERE fldSSN = '" & cboSSN.Text & "'"
'This sets the text fields
txtName.Text = rs.fields("fldName&quot
txtAddress.Text = rs.fields("fldAddress&quot

End Sub

The problem with using database connections as you described above is that, when set by static properties, the connection remains static. In other words, if you try to move the program to another machine without setting the connection properties in code, the program will not access the database unless the exact same folders are set up on that machine that were used in the creation of the program.
You can use ADO objects and place them on your form, although I personally find it kind of cluttering, and coding everything seems just a little bit cleaner (that IS just a personal preference though)

I hope this code helps you out. Good luck.
-----------------------------------------------
"The night sky over the planet Krikkit is the least interesting sight in the entire universe."
-Hitch Hiker's Guide To The Galaxy

 

[AtomicChip]

Sorry, my fault... The two following functions used above SHOULD be:

Public Sub SetCon()
Set con = New ADODB.Connection
With con
.ConnectionString=app.path "\databaseName.mdb;"
.Provider="Microsoft.Jet.2.0"
.Open
End With
End Sub

Public Sub SetRS(SQL)
Set rs = New ADODB.Recordset
With rs
.ActiveConnection=con
.Source = SQL
.LockType = adLockOptimistic
.CursorType = adOpenDynamic
.Open
End With
End Sub


-----------------------------------------------
"The night sky over the planet Krikkit is the least interesting sight in the entire universe."
-Hitch Hiker's Guide To The Galaxy

 

[littlebyt]

Thanks all for the help on this matter. I will be trying to see that all will work. I greatly appricate all of your help from everyone that has tryied to help me in this problem.

Margaret

 

[littlebyt]

well i use this statement .ConnectionString=app.path "\databaseName.mdb;" and I get a compile error saying end of statement. The rest of the code seems to work just fine. Is there something missing? Well thanks again.

 

[AtomicChip]

Sorry... Should be app.path & "\databaseName.mdb"
-----------------------------------------------
"The night sky over the planet Krikkit is the least interesting sight in the entire universe."
-Hitch Hiker's Guide To The Galaxy

 

[littlebyt]

Well hello again. Well that didn't work. If you need a demo of what I am using please let me know and I will send it to you and show you what I am trying to get out of it. I am getting close to my deadline and would like to get it done. So if any one can help please let me know. I just have one problem. Thanks again for all your time.

Margaret.