help converting DAO to ADO

[karatelung]

The double-click part of this is written in DAO. I need to covert it to ADO to work with our SQL Server 200 backend. Can anyone help?

It's a lookup form. When an item in the list is double-clicked, a form is opened to the specific record.

Private Sub Form_Load()
With Me.List0
.RowSource = "SELECT identification,lastname + ', ' + Coalesce(firstname, ' ') FROM [foster parents] " & _
"WHERE identification IS NOT NULL AND active = 1 Order By lastname, firstname"
.ColumnCount = 2
.ColumnWidths = "0;.75 in;"

End With
End Sub



Private Sub List0_DblClick(Cancel As Integer)
Dim CustForm As Form
Dim Cust As DAO.Recordset
DoCmd.OpenForm FormName:="frmARV"
Set CustForm = Forms("frmARV")
Set Cust = CustForm.RecordsetClone
Cust.FindFirst "identification = " & Me.List0
CustForm.Bookmark = CustForm.RecordsetClone.Bookmark


End Sub

Thank you. Hopefully with this knowlege, I'll be able to rework most of the others myself.

 

[pbaldy]

For starters, you don't need to convert to ADO; DAO will work fine (for an mdb at least). Secondly, wouldn't this be a lot simpler:

DoCmd.OpenForm "frmARV", , , "identification = " & Me.List0

 

[karatelung]

I forgot to mention that I'm working with an Access project (.adp) frontend.

That IS a lot simpler, and it works.

Thank you.


Edit:

The only problem I have with that is that it filters the record. if i leave the form open and double-click on another name, nothing comes up. Maybe a find technique rather than a filter would work better for me.

Any suggestions?

 

[pbaldy]

I just played with an ADP and this worked. I assume you can adapt it back to your needs:

Dim CustForm As Form
Dim Cust As Object

DoCmd.OpenForm FormName:="tblReservations"

Set CustForm = Forms("tblReservations")
Set Cust = CustForm.Recordset.Clone

Cust.Find "ResNum = 2"
CustForm.Bookmark = Cust.Bookmark

 

[karatelung]

Thank you. It now works exactly as I want it to.