C
Christoph
Hi,
I'm currently faced with the following problem:
I have a form "frmBasic" that has three text boxes txt1, txt2 and
txt3. They hold respectively, last name, first name and city.
I would like for the user to be able to enter just a last name in
txt1, then click a button cmdSearch that will then pull all records
out of the table that have the last name as specified in txt1. If
there are more than one record, the first record is to be displayed.
So far so good, I was able to achieve this with the following:
Private Sub cmdSearch_Click()
tbl = "tblInsuredsBasic"
Set rs = New ADODB.Recordset
strSQL = "SELECT * FROM tblInsuredsBasic " _
& "WHERE [LastName] = '" & Me.txt1 & "'"
rs.Open strSQL, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
With rs
Me.txt1 = ![LastName]
Me.txt2 = ![FirstName]
Me.txt3 = ![City]
End With
ExitHere:
Exit Sub
HandleError:
MsgBox Err.Description
Resume ExitHere
End Sub
What I can't get to work is this: I have one more button named cmdFWD.
If the query above results in more than one records, I would like to
advance to the next record by clicking the cmdFF button and then
display that next record. I have rs defined with "Dim rs As
ADODB.Recordset" outside of all subs, at the very beginning of the
form module. How can I keep my pointer/reference to the recordset
pulled with the sub above and then process it in another sub? The
ultimate goal is to develop my own record navigation buttons. However,
it seems everytime the code exits the cmdSearch_Click() procedure, it
loses all references to the data.
Thanks very much for any help.
Christoph
I'm currently faced with the following problem:
I have a form "frmBasic" that has three text boxes txt1, txt2 and
txt3. They hold respectively, last name, first name and city.
I would like for the user to be able to enter just a last name in
txt1, then click a button cmdSearch that will then pull all records
out of the table that have the last name as specified in txt1. If
there are more than one record, the first record is to be displayed.
So far so good, I was able to achieve this with the following:
Private Sub cmdSearch_Click()
tbl = "tblInsuredsBasic"
Set rs = New ADODB.Recordset
strSQL = "SELECT * FROM tblInsuredsBasic " _
& "WHERE [LastName] = '" & Me.txt1 & "'"
rs.Open strSQL, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic
With rs
Me.txt1 = ![LastName]
Me.txt2 = ![FirstName]
Me.txt3 = ![City]
End With
ExitHere:
Exit Sub
HandleError:
MsgBox Err.Description
Resume ExitHere
End Sub
What I can't get to work is this: I have one more button named cmdFWD.
If the query above results in more than one records, I would like to
advance to the next record by clicking the cmdFF button and then
display that next record. I have rs defined with "Dim rs As
ADODB.Recordset" outside of all subs, at the very beginning of the
form module. How can I keep my pointer/reference to the recordset
pulled with the sub above and then process it in another sub? The
ultimate goal is to develop my own record navigation buttons. However,
it seems everytime the code exits the cmdSearch_Click() procedure, it
loses all references to the data.
Thanks very much for any help.
Christoph