Hi everybody,

I hope nobody is going to laugh about this post. I am in the very
early steps of learning VBA for Access 2007. I have some basic
programming skills in VB, so that helps a bit.

I have a very basic form with two text fields, "Text1" and "Text7" and
a button "cmdTestButton". The underlying table for this form is a
table "tblBasic" that has three fields [ID], [LastName] and
[FirstName].

I can enter a first name and a last name in the two text fields and
then click the button. The code that is correctly executed to add an
entry to my table looks like this:

---

Private Sub cmdTestButton_Click()

   Dim rs As ADODB.Recordset

On Error GoTo HandleError

   Set rs = New ADODB.Recordset
   rs.Open "tblBasic", CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

   With rs

   .AddNew

      ![LastName] = Me.Text1.Value
      ![FirstName] = Me.Text7.Value
      .Update

   End With

   rs.Close
   Set rs = Nothing

ExitHere:
   Exit Sub

HandleError:
   MsgBox Err.Description
   Resume ExitHere

End Sub

---

What I am completely puzzled about is how I can now run a query in
ADO. Assume I put a last name into the Text1 textbox. Then I have a
second button "cmdSecondButton". If I push this button then I'm trying
to execute code that will find all records in my table that have the
[LastName] field equal to what last name I entered in Text1. I would
like for the first recordset to be displayed in the two text boxes.
However, I'm getting "No value given for one or more parameters." when
executing this code:

---

Private Sub cmdSecondButton_Click()

   Dim rs As ADODB.Recordset
   Dim strSQL As String

On Error GoTo HandleError

   Set rs = New ADODB.Recordset

   strSQL = "SELECT tblBasic.ID, tblBasic.LastName,
tblBasic.FirstName FROM tblBasic " _
     & "WHERE [LastName] = " _
     & Me.Text1

   rs.Open strSQL, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

   With rs

       Me.Text1.Value = ![LastName]
       Me.Text7.Value = ![FirstName]

   End With

   rs.Close
   Set rs = Nothing

ExitHere:
   Exit Sub

HandleError:
   MsgBox Err.Description
   Resume ExitHere

End Sub

---

What am I doing wrong? And second, if there are more than one
recordsets that the query returns, how can I "move" to the next one
and display that one, etc. (in other words, creating my own "navigate
forward" button). In my References, I have in addition to the first
three items "Microsoft ActiveX Data Objects 2.8 Library" selected. I
tried the 6.0 but I had problems running the code. Is 2.8 the right
selection? Do I need to select anything else?

I know it's a lot but maybe somebody has the motivation to help me
out. It is greatly appreciated.

Best regards,
Christoph