Crappy Combobox

Greetings, all. In trying to solve the problem I posted in the DB forum, I have encountered a better solution- but one that (sadly) I still can't quite complete. Here's the situation. I've populated a Combobox (tried normal and DBCombo) with values from a field ("entry #" = primary key) in my local (Jet for now) DB. I have put in the click event of that combobox (yes, I've read tons of posts in here) several unsuccesful hunks of code. In short, I want the user to choose a value from the list, and have the other fields associated with that PK to show up in my databound textboxes. I can already cycle through the records and have all data show up (next and previous ADO), but this "go straight to record X" bit is evading me. I tried the seek fn. I tried the find fn. I tried SQL queries. All probably work, but I'd like to hear open suggestions from you guys. Both should- when selected- make the matching PK value's record the current one...right?? I don't want to use move because records will be deleted and autonumber doesn't re-number. Here's the skeleton of my efforts:

private sub dbcombo1_click

REM I already have a connection and open recordset rs
REM I have set the dbcombo1 format to 'number'

'rs.find (rs.fields("entry #").value = dbcombo1.text)

REM Here I was just trying to force SOMETHING. Still got
REM an error. Problem must be w/ first 1/2 of fn.
REM I hate the syntax on this line, but it's a copied xample

'rs.find ("entry # = 27")

REM OK, ditch find. Let's try seek:

rs.seek "=", val(dbcombo1.text)

REM I've pulled all my hair out. Better end sub.
End Sub

Ok from what I see it's that you have combo.text as the search query. When you want to find out what text the user has selected in the text box, the proper property should be

Combo.List(Combo.ListIndex)

Also if you are searching for a number and comparing it to a string try one of the conversion methods such as

CSng (convert to single)
CLng (convert to long)
CInt (convert to integer)

And so forth.

Hope this helps

OK. Thx 4 the help cwhite40. Here's what I've got now. Run-time still tells me that arguments are wrong type, are out of range, or are in conflict. Error on find line. Do I have to put a 'movenext on error' in a loop for rs.find? Here's my current fn.:

Private Sub dbcombo1_click()
Dim Check1
Dim Check2

REM Just to be sure we're talkin apples and apples...
Check1 = CInt(Rs.Fields("entry #").Value)
Check2 = CInt(DBCombo1.List(DBCombo1.ListIndex))

REM Let ME see what you are comparing...
MsgBox ("Num: " & Check1 & " should become num: " & Check2)

REM OK, match 'em up.
Rs.Find (Check1 = Check2)

REM Doh!
End Sub

Any renovations to this?

Assuming Check1=Check2 then
rs.find (Check1=Check2)
actually looks for -1.

I don't do ADO but in DAO you have to indicate the index before you do a seek.
rs.Index = "PrimaryKey"
rs.Seek "=",cint(dbcombo1.list(dbcombo1.listindex))

Also, if the keys are integers or longs, you could store them in the itemdata property of the list so you could use more meaningful text descriptions (assuming you arn't typing anything into the combo box of course).

"I have a plan so cunning you could put a tail on it and call it a weasel!" - Edmund Blackadder

welllll.... The index and seek functions don't seem to be available to my recordset object. "Method not found" error at "index =" ...Surely I don't have to include a DAO connection to use this property?? I even included the DAO 3.6 Object Library reference, but to no avail. All I want to do is goto a specific record on a non-remote DB. That can't possibly be a difficult thing- I just cant unearth any code examples for it.

I really want to use find and/or seek, but if these aren't applicable, I'll just have to go back and implement a "position" flag that I update on move, add, and delete- though it seems ADO would support something a little more convenient.

Like I said, I don't do ADO.

In DAO you'd declare
dim rs as DAO.Recordset

but I suspect you've declared it as an ADO recordset.

My point was that there may be a similar limitation in ADO, that is, you may have to set an index of some kind before calling the Find method.


"I have a plan so cunning you could put a tail on it and call it a weasel!" - Edmund Blackadder

Try this. Make sure that the bound column is set to the proper field.
rs.find "[entry #] = " & dbcombo1.boundtext, , adSearchForward, adBookmarkFirst




d. paulson

Ok, I'll take a shot.

What I think you have to do is move the cursor (that's the pointer to the current record) to the new choice.

I think this should be easy.

Try this. In the click event, get the text from the combo box. Do a movefirst on the recordset. Step thru the
records one at a time comparing that text with it's appropriate column in the recordset. Don't forget to use
Trim() to remove extra spaces when pulling from the recordset if your working with strings. When you get a
match, all you should have to do is a .Refresh on all the data-bound controls.

If this isn't good enough, let me know and I'll dig in my archives (UGH!) because I used to do this all the time.

ADO vs DAO for this should be no different.

Hope This Helps,
->Noah

OK. I finally got an answer. I took a break from this prob and worked on other stuff. (Prob a good idea if you're stuck) I caqme back and tried a C++'ish method. It could be sped up and more complex, but for now, this works. I created an invisible textbox called txtGoto to hold the "text" from the combobox. I also restricted the combobox to what was in the list from my population :)

Private Sub goThere()
txtGoto = Infodisplay.DBCombo1.Text
If Infodisplay.text1(0) <> txtGoto Then
Do While Val(Infodisplay.text1(0)) > txtGoto Or Infodisplay.text1(0) = ""
Rs.MovePrevious
Loop
Do While Val(Infodisplay.text1(0)) < txtGoto Or Infodisplay.text1(0) = ""
Rs.MoveNext
Loop
End If
End Sub

Thanks to all for the ton of help!!! I'm gonna test the other solutions over the next few days. I've been sick lately, so sorry for the absence...

Hi,
Just been working with ADO myself and would of thought it is more efficient to use SQL than looping through all records to find a match. This depends of course on the size of your database so my advice would be to try both methods and see which is best. My method to solve a similar problem to yours was:

1 - Populate DataCombo Box from ADODC control at design time
2 - On form load event establish connection to same database = APDB
3 - Code in DataCombo Click event:

Private Sub DataCombo1_Click(Area As Integer)
'
Dim ResStr As String
Dim ResultsRS As Recordset

Me.MousePointer = 11

If DataCombo1.Text <> "" Then
ResStr = "SELECT * FROM Table WHERE Field1 Like '" & DataCombo1.Text & "'"

Set ResultsRS = New Recordset
ResultsRS.Open ResStr, APDB, adOpenStatic, adLockOptimistic

ResultsRS.MoveFirst

Text1.Text = ResultsRS!Field2
Text2.Text = ResultsRS!Field3
Text3.Text = ResultsRS!Field4
End If

Me.MousePointer = 0

End Sub

Obviously Field1, Field2 etc are your field names.

Good Luck
Phil

Heya Phil. Great solution! This will definitely be faster on a larger DB. I was messing w/ the SQL query, but couldn't get the syntax straight. Thx for the tip!
-r