Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DataCombo and a TextBox

Posted on 2003-11-09
23
Medium Priority
?
2,925 Views
Last Modified: 2013-12-25
Hello!
I have a DataCombo and a TextBox on a form. I want the TextBox to display the data in the field next to the field of the DataCombo. When a line is picked in the DataCombo the same line in the next field should be shown in the TextBox. The field for the TextBox is named "FastenerCapacity" What should I add to this code I'm using for the DataCombo?

'---------------------------------------------------
Private Sub Form_Load()
Dim sSql As String
Dim rsInformation As New ADODB.Recordset
Dim mCnn As New ADODB.Connection

Dim strConn As String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Winsas\NewData.mdb;Persist Security Info=False"

mCnn.Open strConn

sSql = "SELECT * FROM Fasteners"
rsInformation.CursorLocation = adUseClient
rsInformation.Open sSql, mCnn, adOpenDynamic, adLockOptimistic


Set cboFastenerType.RowSource = rsInformation
cboFastenerType.ListField = "FastenerType"
Set rsInformation = Nothing
Set mCnn = Nothing
End Sub
0
Comment
Question by:nvenho
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 10
  • 8
  • 4
  • +1
23 Comments
 

Author Comment

by:nvenho
ID: 9711045
I deleted the code above and now I'm using adodc. The data in the textbox is not changing when I make a selection in the datacombo. Any hints?
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9711731
you have to set textbox datasource and datafield.
0
 

Author Comment

by:nvenho
ID: 9712510
I have set them and the textbox shows only the first row from the datafield. I need it to change when the line in datacombo changes,
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 8

Expert Comment

by:MYLim
ID: 9712562
i have understant wat u means...
u have to use adodc1.recordset.find trim(datacombo.text) becoz you just select the data inside combobox but there are no moving action to your recordset.
0
 

Author Comment

by:nvenho
ID: 9712608
I'm not sure what you mean? Can you explain or show a example if I need some code?
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9712777
you say u are using datacombo not combobox ,right ?
pls add a datagrid control and bind it to adodc1 for test !!!
1.Please select a row from datacombo.is the row in datagrid have changed?
-if not change means you haven't browse your data.

pls post the result :)
0
 

Author Comment

by:nvenho
ID: 9717621
I'm still not sure what you mean? There must be a simple way of doing this. I really don't know the differences between datacombo and combobox. I've been trying to use a datacombo and a textbox. I really don't care if it is a datacombo or combobox as long it shows the contents of a db field. When selecting one line from it the textbox should display the same line from the field the textbox is connected to.

Anyone?
0
 
LVL 8

Expert Comment

by:MYLim
ID: 9720165
ok :)
datacombo will load data into item list.
so,when you select the item inside the list,it does not means you
have moveprevious or movenext to your record.
clear ?

so,when your datacombo has change it value,you got to find the value inside recordset.

private sub datacombo1_changed()
adodc1.recordset.find "datacombo1.text"
end sub





0
 

Author Comment

by:nvenho
ID: 9722982
I don't know what that code is suposed to do and didn't help me...
0
 
LVL 4

Expert Comment

by:wulfshayde
ID: 9748205
nvenho,

I think what MYLim is trying to get at is that you have to put some code into your datacombo1_changed() routine to update the select query and refresh the textbox with the new record. Somthing along the lines of :

sSQL="<Insert SELECT Query here>"
<Execute Database with Query Command>
Textbox1.Refresh()

Best to use a dataset, and a dataAdapter and then refine your search criteria by connecting the datacombo to the dataset, and then refresh the textbox on datacombo1_changed()
Does this help? Also, Im not sure about this, but if you bind a Set cboFastenerType.RowSource = rsInformation and then two lines later say rsInformation = Nothing, wouldn't that basically reset it? Just an idea.
0
 

Author Comment

by:nvenho
ID: 9750699
Could someone please tell me in detail what controls to put on the form and then show me the code to use? I'm really not making any progress here...
0
 
LVL 4

Expert Comment

by:wulfshayde
ID: 9751587
Okay, I am ready for this task. But first tell me, I cannot find a DataCombo in VB .NET so does that mean that you are using a regular combo box bound to a database?
0
 

Expert Comment

by:ElinaL
ID: 9751653
If you want to use DataCombo Look for the Microsoft Data List Controls 6.0 (SP3) (OLEDB) which has the datacombo and datalist components.

I don't know what is the difference with ComboBox and DataCombo, and it doesn't matter which one we are going to use, I just want this thing working. I have been trying for some time now...

I have three fields in my db: "ID", "Fastener" and "Capacity". The db is named "Fasteners".

Thanks in advance.
0
 
LVL 4

Expert Comment

by:wulfshayde
ID: 9751865
Okay, I will give you an example using a combobox. If you are going to use data from a MDB, I found it easiest to load the data using the Dataenvironment. The dataenvironment is an add-in, so you may have to select it and turn it on to use it. Tools > Add-In manager. Ensure that it is selected to run, and start on startup. If you right click on your project, and click add you should have the option for a DataEnvironment. If you load this then you can load Commands (tables and queries) into it and manipulate it from there, its the easiest way I've found to do this. To add a connection in the dataenvironment is as easy as clicking properties on the existing one, and following the steps choosing Microsoft Jet 4.0 database name and location.

Once you have added it and made your connection do the following. Add a Command to the connection, call it "Fasteners" and select the "Fasteners" table from the DataEnvironment1 Connection. Ensure you have a ComboBox, and a TextBox on your form, then add the following code to your load script.

--------------------------------------------------------

Private Sub Form_Load()

'Use the DataEnvironment Recordset (Command) named Fasteners
With DataEnvironment1.rsFasteners
    'If the Recordset is Closed, Open it
    If .State = adStateClosed Then .Open
    'Set it to Filter no records, so all records are showing
    .Filter = ""
   
        'Clear the ComboBox in case of any previous data
        Me.ComboBox1.Clear
        'While Recordset doesn't return End Of File, do this loop
        While Not .EOF
        'Add "Fastener" field to the ComboBox List
        Me.ComboBox1.AddItem .Fields.Item("Fastener").Value
        'Give the new field a value of the corresponding ID in the database for reference.
        Me.ComboBox1.ItemData(Me.ComboBox1.NewIndex) = .Fields.Item("ID").Value
        'Move to the next record in the table
        .MoveNext
        'Wend to Loop with While
        Wend
        'We are done, so close the Recordset
        .Close
'End With Recordset    
End With

'If There are any records in the Combobox, automatically select the first one.
If Me.ComboBox.ListCount > 0 Then Me.ComboBox.ListIndex = 0

End Sub

-----------------------------------------------------------

Now you will have to put a routine in the ComboBox1_Click() routine to catch the click and load the info. It's like this

--------------------------------------------------------------

Private Sub ComboBox1_Click()

'Use the DataEnvironment Recordset (Command) named Fasteners
With DataEnvironment1.rsFasteners
'If the Recordset is Closed, Open it
If .State = adStateClosed Then .Open
'This time you want to Filter the records and select only the one that matches the ID of the selected Item in the ComboBox
.Filter = "ID=" & ComboBox1.ItemData(ComboBox1.ListIndex)
'If a record is found
If Not .EOF Then
    'Make the TextBox1.Text match the "Capacity" field.
    me.TextBox1.Text=.Fields.Item("Capacity").Value & ""
Else
    'Or else Tell the user that record was not found
    me.TextBox1.Text="No Record Found!"
End If
'Close Recordset
.Close
'End Recordset With
End With

End Sub

-----------------------------------------------------

I hope this helps to steer you in the right direction. I know I had a lot of problems when first starting with VB, It took me ages to get this sorted out. Good Luck!
0
 

Author Comment

by:nvenho
ID: 9752088
Thanks again!
Here is my code. The ComboBox and TextBox are still empty. The connection is working when I test it. What properties do I have to set for the ComboBox and TextBox in the properties window? Is there something wrong with the code?


'---------------------------------------------------------------------------------
Private Sub frmFasteners_Load()

'Use the DataEnvironment Recordset (Command) named Fasteners
With DataEnvironment1.rsFasteners
    'If the Recordset is Closed, Open it
    If .State = adStateClosed Then .Open
    'Set it to Filter no records, so all records are showing
    .Filter = ""
   
        'Clear the ComboBox in case of any previous data
        Me.cboFastener.Clear
        'While Recordset doesn't return End Of File, do this loop
        While Not .EOF
        'Add "Fastener" field to the ComboBox List
        Me.cboFastener.AddItem .Fields.Item("Fasteners").Value
        'Give the new field a value of the corresponding ID in the database for reference.
        Me.cboFastener.ItemData(Me.cboFastener.NewIndex) = .Fields.Item("ID").Value
        'Move to the next record in the table
        .MoveNext
        'Wend to Loop with While
        Wend
        'We are done, so close the Recordset
        .Close
'End With Recordset
End With

'If There are any records in the Combobox, automatically select the first one.
If Me.cboFastener.ListCount > 0 Then Me.cboFastener.ListIndex = 0

End Sub

'----------------------------------------------------------------------------------------
Private Sub cboFastener_Click()

'Use the DataEnvironment Recordset (Command) named Fasteners
With DataEnvironment1.rsFasteners
'If the Recordset is Closed, Open it
If .State = adStateClosed Then .Open
'This time you want to Filter the records and select only the one that matches the ID of the selected Item in the ComboBox
.Filter = "ID=" & cboFastener.ItemData(cboFastener.ListIndex)
'If a record is found
If Not .EOF Then
    'Make the TextBox1.Text match the "Capacity" field.
    Me.txtCapacity.Text = .Fields.Item("Capacity").Value & ""
Else
    'Or else Tell the user that record was not found
    Me.txtCapacity.Text = "No Record Found!"
End If
'Close Recordset
.Close
'End Recordset With
End With

End Sub
0
 
LVL 4

Expert Comment

by:wulfshayde
ID: 9752113
       'Add "Fastener" field to the ComboBox List
        Me.cboFastener.AddItem .Fields.Item("Fasteners").Value
                                           ^
I put an extra space right there, ooops.
If that doesn't fix it, Can you send me your code and DB via Email so I can check them?
razorfang@netspace.net.au
I will see if I can come up with a conclusion.

-wulfshayde
0
 
LVL 4

Expert Comment

by:wulfshayde
ID: 9752449
nvenho,

That account doesn't appear to be working, so if you could just send the database zipped to wulfshayde@hotmail.com I can send you back the results. and post them here, for future reference.

-wulfshayde
0
 
LVL 4

Accepted Solution

by:
wulfshayde earned 260 total points
ID: 9753100
Okay, make a form with a ComboBox and a Textbox on it. Don't edit any values on it and copy this code to the editor form.

------------------------------------------------------

'Public Declarations to simplify Code
Public strConn As String
Public sSql As String
Public rsInformation As New ADODB.Recordset
Public mCnn As New ADODB.Connection

Private Sub Combo1_Click()

'Open Connection
mCnn.Open strConn

'SQL SELECT Statement
sSql = "SELECT * FROM Fasteners WHERE ID=" & Me.Combo1.ItemData(Me.Combo1.ListIndex)
'Set Cursor Location
rsInformation.CursorLocation = adUseClient
'Open Recordset
rsInformation.Open sSql, mCnn, adOpenDynamic, adLockOptimistic

'If a record is found
If Not rsInformation.EOF Then
    'Make the TextBox1.Text match the "Capacity" field.
    Me.Text1.Text = rsInformation.Fields.Item("Capacity").Value
Else
    'Or else Tell the user that record was not found
    Me.Text1.Text = "No Record Found!"
End If

'Close Recordset
rsInformation.Close
'Empty rsInformation
Set rsInformation = Nothing
'Close Connection
mCnn.Close

End Sub

Private Sub Form_Load()

'Set Connection String
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Winsas\NewData.mdb;Persist Security Info=False"

'Open Connection
mCnn.Open strConn

'SQL SELECT Statement
sSql = "SELECT * FROM Fasteners"
'Set Cursor Location
rsInformation.CursorLocation = adUseClient
'Open Recordset
rsInformation.Open sSql, mCnn, adOpenDynamic, adLockOptimistic

'Use With rsInformation for simplicity and loop through records to populate combobox
With rsInformation
'While not End of File
While Not .EOF
    'Add "Fastener" Item to Combobox
    Me.Combo1.AddItem (.Fields.Item("Fastener").Value)
    'Add ID to Fastener Item
    Me.Combo1.ItemData(Me.Combo1.NewIndex) = .Fields.Item("ID").Value
    'Move to next record
    .MoveNext
Wend
End With

'Close Recordset
rsInformation.Close
'Empty rsInformation
Set rsInformation = Nothing
'Close Connection
mCnn.Close

'If the list has any records, select first one.
If Me.Combo1.ListCount > 0 Then Me.Combo1.ListIndex = 0
End Sub

-------------------------------------------------------------------------

That should do it, works on mine.

-wulfshayde
0
 

Author Comment

by:nvenho
ID: 9753196
It is still not working, The combo and textbox are still empty.
0
 

Author Comment

by:nvenho
ID: 9753209
Oops, it was just me who had messt up. Now It's working fine! Thanks a lot!

What if  I want to have one more textbox added and it should work like Text1?
0
 
LVL 4

Expert Comment

by:wulfshayde
ID: 9753764
All you would have to do is the following for each new textbox under the Sub Combo1_Click()

----------------------------------------------------

Me.Text2.Text = rsInformation.Fields.Item("<Fieldname>").Value
Me.Text3.Text = rsInformation.Fields.Item("<Fieldname>").Value
Me.Text4.Text = rsInformation.Fields.Item("<Fieldname>").Value

---------------

etc...

hope this helps.

-wulfshayde
0
 

Author Comment

by:nvenho
ID: 9754832
Thanks for everything!!
0
 
LVL 4

Expert Comment

by:wulfshayde
ID: 9756295
You're welcome. Have fun with that.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question