Select Record from combo box 2

[Kate17]

Please help me!

I have a SQL database and I have created a DBcombo box on a form for a UserID (this is a unique value). This works fine and all IDs are displayed.

Also on the form I have other fields that relate to the UserID from the table in the database (Name, address etc...). How do I get the fields to update to display the correct information when I select an ID from the combo box? I am a newbie, so please forgive my ignorance if this is easy, but I am trying hard!

Thanks for your help! Kate

 

[perplexd]

I had exactly the same problem when starting out a few months ago!! This is what I did:
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
Dim strParameter As String

Set cmd = New Command
Set cmd.ActiveConnection = con

With cmd
.CommandText = "QueryName"
.CommandType = adCmdStoredProc
End With

'Set prm = New parameter
Set prm = cmd.CreateParameter("prmProdID", adChar, adParamInput, 15, cboBox.text)
cmd.Parameters.Append prm

'Set rs = New Recordset
Set rs = cmd.Execute

Label1.Caption = rs!Field1
Label2.Caption = rs!Field2

rs.Close
Set rs = Nothing

 

[sunaj]

As I understand it, what you want to show is simply other columns of the recordset.
You can bind the controls to the recordset by setting the .datasource to the same as the comobox and .datafield to the name of the column that you want to display.

That should do it for you. I don't know what you are doing, but in thread222-316590 a lot of people (incluing me) agrees that binding is limited and prefers to have more control writing thing out in code instead.

You can learn a lot by playing around with the data form wizard to see how it solves things by binding or in code.


Good luck Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[Kate17]

Hmm, well, as I said I am a newbie so I am a little stuck with the code perplexd put in - I am going to have a go anyway to see how I get on. The binding seems a lot easier to do, so I will try that too!

Perplexd - if you are feeling generous, perhaps you could explain a little more what to do with the code?

Thanks! Kate

 

[perplexd]

Which bit is confusing you?

Instead of doing it the way I showed, you can also do it with SQL...ie
rs.source = "SELECT * FROM tableName WHERE...."

con is the connection object you set this up similar to as follows:
Set con = New ADODB.Connection
con.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
"Data Source = H:\Misc\ACCESSDB\SeNuM.mdb"
con.Open

cmd is the command object. This is needed to access the dataase when you are wanting to used stored queries which require parameters (such as the UserID)

 

[sunaj]

Hi agian,

It is really wrong to open a new recordset every time the user clicks the combo. If you don't like the binding bit, do it manually which looks something like (I can't show accurate code because I don't know the source of your data):

------------------------------------------------------
Private Sub Combo1_Click()
Text1.Text = YourRecordset.Fields("MyTextField&quot
Text2.Text = CStr(YourRecordset.Fields("MyNumericField&quot)
Label1.Caption = YourRecordset.Fields("MyOtherTextField&quot
End Sub
------------------------------------------------------
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[lovell811]

I agree with sunaj there is no reason to open up the recordset everytime that will just slow things up. Also, I wouldn't bind just because I really don't care for it, so all you need to do is alter your code to look like the above code that sunaj put. ----------------
Joe

 

[Kate17]

Thanks for all your messages - I am so sorry I am getting confused - I will explain myself a little better.

I have 10 records and 3 fields (there's a lot more, but doing it for this will get me well on the way!!) - UserID is different for each, and they each have Surname and Forename. On my form I have set the UserID field to be a DBCombo box, when I change this to a different one than is displayed, I want the Surname and Forename field to display the correct information. I sort of understand the code that Sunaj has written (thank you!) but what do I specify for YourRecordset (I am a little unsure as to what needs to be altered).

Thank you all for your help - I have many stars to dish out! Kate

 

[lovell811]

try:

Private Sub cmb1_Click()

rs.source = "SELECT * FROM tableName WHERE UserID = '"& cmb1.text &"'"
rs.open
Label1.Caption = rs!Field1
Label2.Caption = rs!Field2
rs.close ----------------
Joe

 

[sunaj]

No!
That would open a new recordset - no need to do that. All the information we need has already been retrieved from teh database in a recordset.

Kate:
You must have some kind of data control. You are using an old standard (dbcombo, the new is called datacombo). if you have a datacontrol, it stores your recordset:

Private Sub Combo1_Click()
Text1.Text = Data1.recordset.Fields("MyTextField&quot
Text2.Text = CStr(Data1.recordset..Fields("MyNumericField&quot)
Label1.Caption = Data1.recordset..Fields("MyOtherTextField&quot
End Sub

If this dosen't do it you'll have to give some more information about how you retrive the data.

Alternatively: use the data form wizard, it will create extacty what you want automatically! menu: add-ins -> add in manager and set data form wizard to 'loaded'. click ok and there will be a menu item to show teh data form wizard in the add-ins menu. Try to make a data form with ADO control and another with ADO code. The code will show you want actually happens.

Good luck!

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[Kate17]

Thanks Joe, tried that - but it comes up with error message "Object required", then when I debug, it shows the line rs.source = "Select * etc.... - any ideas? Kate

 

[lovell811]

can you post some code so we can see what you have that will help determine what you need, because like sunaj said you may not need to open it and I would prefer not to everytime you select a UserId, but I wasn't sure how you were doing it so could you let us know what you have?? ----------------
Joe

 

[Kate17]

Hi Sunaj,

I used the data form wizard but the 2 main problems with that are - I can't use a view from sql, only tables, and there doesn't seem to be an option where I can choose the UserID and view the other details?

This seems to be alot more complicated than I thought!!! Kate

 

[lovell811]

My opinion on that is you should forget the wizard they're good for examples but not creating an actual program, but that is just what I think. ----------------
Joe

 

[Kate17]

I have deleted my projects so far - I try something new each time, so I am starting from scratch.

I will explain why I am doing this and you may be able to suggest a much better way to do it...

I am really writing a small add-on - the SQL database was designed for a helpdesk and has a web front end through which calls are added. This app is purely to display information about the caller, their company, their SLA etc, but separate from the web front end as this would be far too complicated to incorporate. I also need to practise my VB by doing this as we will need to do alot more VB work in the future.

So, starting from scratch - i have a designed a view (containing 4 linked tables) in SQL from which i want to basically display information depending on which UserID is selected (information which has been collated in the view).

I hope that explains a bit more what I am trying to achieve,

Thanks Kate

 

[Schroeder]

To use the cmb1_Click event the way lovell811 suggested, you'd have to have rs set up as a recordset object in advance. It would either need form level scope, i.e. declared in the form's general declarations section, or you'd need to declare and instantiate it every time the cmb1_Click event is fired. The latter would be very time consuming.
Personally, I'd have the complete recordset on hand for the entire time the form is open and search through it in the cmb1_Click event. Since you've already got a bound control though (the DBCombo), I'd do what sunaj suggested in his last post.
Not being that familiar with bound controls, I wonder: will Kate not need to check the source of her DBCombo to be sure that the additional fields are available to her? Assuming she hadn't planned to use it to supply this additional info when she first set it up, wouldn't it probably just have the one field ready to populate the DBCombo?

 

[sunaj]

lovell811 - I agree about the wizard.

Kate: I'm sorry if we have confused you.

Which database are you using? The wizard can show all tables from any database, but some database requires that you make a system DSN before you can use it. E.g. SQL server. Which database are you using?

Below I've posted a complete example that does what (I think) you want form an SQL server database.

---------------------------------------------------------------
'Place a combobox and a textbox on a form.
'Set a reference to Microsoft ActiveX Data Objects 2.* and paste the following into the code section of the form
Option Explicit
Dim conn As Connection
Dim Rst As Recordset

Private Sub Combo1_Click()
Rst.AbsolutePosition = Combo1.ListIndex
Text1.Text = Rst.Fields("firstname&quot
End Sub

Private Sub Form_Load()

Set conn = New Connection
conn.Open "Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;User ID=dbo;Initial Catalog=YourDataBase;Data Source=YourSQLServer"


Set Rst = New Recordset
Rst.CursorLocation = adUseClient
Rst.Open "SELECT ID, FirstName FROM tblPerson", conn, adOpenStatic, adLockOptimistic

Combo1.Clear
If Not Rst.EOF And Not Rst.BOF Then
Rst.MoveFirst
While Not Rst.EOF
Combo1.AddItem Rst.Fields("ID&quot
Rst.MoveNext
Wend
Else
'no records to show
End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
Rst.Close
Set Rst = Nothing
conn.Close
Set conn = Nothing
End Sub
----------------------------------------------------------------

Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[lovell811]

If you can get your code to look similar to sunja's above then we can go from there, because the code I mentioned depends upon how you have the coneection to the table set up so if we all need to get on the same page here like sunja said sorry for confusing you, it just that we all need to know what we're dealing with here. ----------------
Joe

 

[Kate17]

Thanks for all your help - I think I will spend some time reading the books I have as I am sure what I am trying to do is really unecessary and there will be a really simple way to do it!

I will go and have a lie down and start again tomorrow!

Thanks again for all your help and the time you took to answer my questions (I may be confused, but you have helped me understand some things!).

Kate