C
Christoph
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
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