-
Dec 29th, 1999, 11:51 PM
#1
Thread Starter
Lively Member
Hi, people!
I'm trying to select data in a datacombo and automatically updating a text box.
I don't know how to do that only using the datacombo and textbox properties, so I'm trying to create a module.
Have added the following code to the "Change" event of my datacombo:
==================
Dim MyDb As Database
Dim Myset As Recordset
Set MyDb = "<my db name>"
Set Myset = MyDb.OpenRecordset("select Description from [My table] where COD = " & [<my datacombo name])
===================
How can I connect the result of this query to my text box?
I’m starting with VB now and I don’t even know if I am in the right direction.
Thanks in advance,
Roselene
-
Dec 30th, 1999, 02:02 AM
#2
Thread Starter
Lively Member
Hi, Aaron!
Thanks for your attention and kindness!
You have used a lot of commands that I've never seen before. You module seems to be very complex and interesting.
I'm learning a lot with you SQL instruction!
Also have learnt the way of setting a form object= a field of the SQL instruction.
I'm sure you have already shown me the right direction, but, if you don't mind, I'd like to answer you one more question:
================================
Set Myset = MyDb.OpenRecordset("select Description from Equipments where MytableField = " & Mycomboname & ", dbOpenForwardOnly)")
==============================
The line above is causing a syntax error (missing operator)
Have tried a lot of changes and have been unsucessful so far.
Please, forgive my mistakes with the Language.
Thanks again and a very happy New Year!
Best Regards,
Roselene
-
Dec 30th, 1999, 02:10 AM
#3
Try:
Set Myset = MyDb.OpenRecordset("SELECT Description FROM Equipments WHERE MytableField = " & Mycomboname & ", dbOpenForwardOnly)
This tells it to retrieve a Recordset of Values where the MyTableField Value is Equal to the Value in MyComboName, if this is a String/Character Value you need to include Single Quotes, ie.
Set Myset = MyDb.OpenRecordset("SELECT Description FROM Equipments WHERE MytableField = '" & Mycomboname & "'", dbOpenForwardOnly)
------------------
Aaron Young
Analyst Programmer
aarony@redwingsoftware.com
ajyoung@pressenter.com
-
Dec 30th, 1999, 12:12 PM
#4
If you're going to be using the Data Access Objects instead of the Data Control, I'd suggest dumping the Databound Controls too, here's an example of using the DAO with a Standard Combo and Textbox..
Add a Combobox and Textbox to your Form, also makea Reference to the Data Access Objects in the Reference Section, (Or just Add then Remove the Data Control).
Code:
Private oDB As Database
Private Sub Combo1_Click()
Dim oRS As Recordset
'Display the selected Books Author in the Textbox
Set oRS = oDB.OpenRecordset("SELECT Author FROM Authors WHERE Au_ID = " & Combo1.ItemData(Combo1.ListIndex), dbOpenForwardOnly)
Text1 = oRS("Author")
oRS.Close
Set oRS = Nothing
End Sub
Private Sub Form_Load()
Dim oRS As Recordset
'Fill the Combobox with the 1st 100 Book Titles
Set oDB = Workspaces(0).OpenDatabase("Biblio.mdb", , True)
Set oRS = oDB.OpenRecordset("SELECT TOP 100 Titles.Title,[Title Author].Au_ID FROM Titles,[Title Author] WHERE Titles.ISBN = [Title Author].ISBN", dbOpenForwardOnly)
While Not oRS.EOF
Combo1.AddItem oRS("Title")
'Store the Authors ID in the ItemData
Combo1.ItemData(Combo1.NewIndex) = oRS("Au_ID")
oRS.MoveNext
Wend
If Combo1.ListCount Then Combo1.ListIndex = 0
oRS.Close
Set oRS = Nothing
End Sub
Private Sub Form_Unload(Cancel As Integer)
oDB.Close
Set oDB = Nothing
End Sub
------------------
Aaron Young
Analyst Programmer
aarony@redwingsoftware.com
ajyoung@pressenter.com
-
Jan 3rd, 2000, 07:25 PM
#5
Thread Starter
Lively Member
Aaron,
I have tried the second option you have sent me (cause my value is a string) and at first it didn't work, even tough the syntax was right. So, I find out that besides the SQL instruction, there was another problem which was causing this message:
"run time error 13 type mismatch"
So, I was given an advice to change the position of the DAO object library to a position above the ADO Library (on the "Project" Menu, option "References").
I haven't got a clue on what happened when I did so, but now, it works.
I'm starting with VB now and have lots to learn.
Thanks very much for your precious help!
Still using your module to learn!
Best regards,
Roselene
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|