combo box to find records on unbound form ADO

[tman72]

I have an unbound form that displays records from an ADO recordset. Is it possible to have a combo box on the form to go to a record selected in the combo box? If so, how? I know how to do it with a bound form and have done it that way many times, but this is new ground for me and I'm lost. I'm using access 2002(XP). Any help is greatly appreciated.

 

[PaulF]

Why are you using an Unbound form to display the result from a Recordset? Does the RecordSet change and the fields change?... And what do you use to populate the ComboBox?
Perhaps a little more info might allow us to help you.

PaulF

 

[tman72]

The reason is that I am looking at records from a dbase database located on a different server. The fields do not change but the records are updated several times per week. I had been downloading and importing the table to access, but if I forget to download and import it daily then the data becomes outdated, so I was trying to use a recordset instead as a way to view and search the data. My thinking right now is to use a search form to find the record, then based on that search criteria populate the form from the recordset. Typically I'd use a bound form with a combo box as a search tool and update the form based on the selection in the combo box.

Right now I load the recordset into the form on the form's OnLoad event and populate the text boxes with the data. Currently I have custom navigation buttons to move through the records. I was hoping to build a combo box that would move through the recordset quicker than moving one at a time with the naviagation buttons I currently am using.

 

[KenReay]

Hi

Not sure if this is the best way to do it, if the data is on a remote (SQL?) Server, then perhaps a pass thru query executing a stored procedure would be better

but..

Dim Rs As Recordset
Set Rs = New Recordset
With Rs
.ActiveConnection = CurrentProject.Connection
.Source = "SELECT * FROM tblYourTableName " & _ "WHERE key = " & cboValue & " ;"
.CursorType = adOpenStatic
.Open
If .RecordCount > 0 Then
' in here populate the form
end If
end With
...etc

Depending on the type of DB from which you are getting the data yo may need to change the line refreing to the connection and make an appropriate connect string

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[tman72]

Ken,

With some changes to fit my my situation, I got it to work the way I was hoping. Thanks for pointing me in the right direction.

Thanks,
T

 

[pokeymonsc]

Ken:

I was searching for an answer to a problem and saw this post and wondered if I could butt in to ask a related question?

I have a data entry form and am selecting a manager's title in a combo box. The source for the combo box is a table in a different sql server database. I can open a cnn to that database open a recordset and import the data and display it (debug.print). What I don't know yet is how to use the data as a rowsource for the combo box (I'm at the 'in here populate the form - in your above example). What I thought about doing was load a temp table and use that as a source for a rowsource query, but I don't know how to get the data out of the recordset into a local table --- or maybe to the rowsource property directly
Me.cbxautofctitle.RowSource = ??????
Any suggestions on this?

 

[KenReay]

Maybe I have completely miss-understood your question, but the rowsource is in the bound table (or query) so just handle it in the same way as all of the others, it is the look up list which is in the remote table

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[pokeymonsc]

I've tried using this code
me.cbxautofctitlel.rowsource = rstEmpNames
but I get a run error saying that Jet can't find a table or query with which to populate the combobox. With ADO I have to open a connection to a remote table and then use a recordset to import the data.
What I'm doing now is using the following code to get the data out of the recordset and populate a temporary table and base my rowsource on this table. This way works but is slow and it just seems to me that there must be a way to use the rowsource and recordset directly instead of going to new york from la via china.

With rstEmp
If Not .EOF Then
strBuf = rstEmp(0).name
End If

Do While Not .EOF

If Not rstEmp(0) = "" Then
strEmpID = rstEmp(0)
Else
strEmpID = ""
End If

If Not rstEmp(1) = "" Then
strEmpLName = rstEmp(1)
strEmpLName = Replace(strEmpLName, "'", "")
'Debug.Print strEmpLName
Else
strEmpLName = ""
End If

If Not rstEmp(2) = "" Then
strEmpFName = rstEmp(2)
Else
strEmpFName = ""
End If

If Not rstEmp(3) = "" Then
strEmpTitle = rstEmp(3)
Else
strEmpTitle = ""
End If

If Not rstEmp(4) = "" Then
strEmpPh = rstEmp(4)
Else
strEmpPh = ""
End If

strInsert = "insert into tblTmpStorage values ('" & strEmpID & "','" & strEmpLName & "','" & strEmpFName & "','" & strEmpTitle & "','" & strEmpPh & "')"
DoCmd.RunSQL strInsert