recordset query to multiple records

I have a table in access that consists of three columns.
column headings: Heading,Key,SupplierKey

I open a recordset from my form to select * from table WHERE SupplierKey = "something"
This recordset fills up a combobox

Combobox_click event
When I select an item in the combobox, I want the recordset to move to a record in the recordset where the combobox.text returns the value of recordset!Key (this will be the value for text1.text)

How do I do this. Please help!
I thought about INNER JOIN, but I don't want to join two tables.
The 'Heading' column can have the same value e.g Keyboards, but the 'Key' column identifies which record I want.
The reason I am selecting records from the 'SupplierKey' column to open the recordset, is because this is being selected from a different combobox.  

Thanks for the answer!!!
andre_vilAsked:
Who is Participating?
 
sonereConnect With a Mentor Commented:
You use a text box depends on adodc1 (datasource) and datafield key

when the recordset order and combobox is the same and after combo1 click


Adodc1.Recordset.AbsolutePosition = Combo1.ListIndex + 1

write this statement
You can see the key in the text box

best wishes.

Sonere
0
 
MarineCommented:
Ok let me understand this right. The comboBox gets filled correct ? Then when selecting an item from it you want to show a value for selected item? is this right ?
0
 
esolveCommented:
The combobox gets filled correct!
When I select an item it displays the record's 'heading'. With this displayed, I want to fill up a textbox with the record's correspondent 'key'.

Since the recordset is open I can show any value for recordset!Key with the following code:

rs.movefirst
text1.text = rs!key

this returns the first value for 'key' but not the record which is selected in the combobox!

help!
 
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
MarineCommented:
Ok try this so in your listbox click event place this
and read it form a recordset on every click on the item
select Key from table WHERE SupplierKey =" & list1.listindex
and just assign that value to your textbox.
0
 
esolveCommented:
When I do this I have to open a new recordset.
I want to do something like this:
SELECT key FROM previousRecordset WHERE SupplierKey = combobox.text
I know this wouldn't work but since I have selected items from the table, I have to query these items again to get the records I want.

Hope you understand what I want to do!
Thanks
0
 
andre_vilAuthor Commented:
Adjusted points to 50
0
 
MarineCommented:
You will still have to query to get the selected record from to combo. Thats why i suggestd to open a new recordset.
SELECT key FROM previousRecordset WHERE SupplierKey =" & combobox.ListIndex
0
 
andre_vilAuthor Commented:
Can you make a query to two items from a record in a table.
i.e SELECT key, supplierkey FROM table WHERE key = "" and supplierkey = "" 
0
 
MarineCommented:
yes you can .
I don't know if your fields are numeric type or string so this one is for numeric
rs = "SELECT From table where key=" & combo1 & "AND" & setkeys = " & combo1"

and this one for string
rs2 = "SELECT * FROM TABLE where table1='" & text1.Text & "' & " And " & setkeyset='" & text2.Text & "'"
0
 
TTCommented:
U can try this,
  rs.FindFirst "SupplierKey = '" & _
      Combo1.Text & "'"    
  If Not rs.NoMatch() then
      Text1.text = rs.Key
  EndIf
0
 
MarineCommented:
He needs to serach by 2 feilds . If he is using ADO theer is no fiddifsrt also find can search one creteria . Must use Filter 2 search more
0
 
andre_vilAuthor Commented:
Thanks a lot Sonere!
This is what I want. i am not using a ADOData control, but it applies to my project as well.

Regards
André
0
 
sonereCommented:
Dear andre_vil,

Nice to see your comment like this. Good programs in VB



0
All Courses

From novice to tech pro — start learning today.