Solved

DataCombo and a TextBox

Posted on 2003-11-09
23
2,912 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
  • 10
  • 8
  • 4
  • +1
23 Comments
 

Author Comment

by:nvenho
Comment Utility
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
Comment Utility
you have to set textbox datasource and datafield.
0
 

Author Comment

by:nvenho
Comment Utility
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
 
LVL 8

Expert Comment

by:MYLim
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I don't know what that code is suposed to do and didn't help me...
0
 
LVL 4

Expert Comment

by:wulfshayde
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 4

Expert Comment

by:wulfshayde
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
       '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
Comment Utility
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 65 total points
Comment Utility
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
Comment Utility
It is still not working, The combo and textbox are still empty.
0
 

Author Comment

by:nvenho
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks for everything!!
0
 
LVL 4

Expert Comment

by:wulfshayde
Comment Utility
You're welcome. Have fun with that.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

728 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now