ADO Access Problem from Form

[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

 

[Damon Heron]

Here is a way to make it simple. Add a subform to your main form, with the
recordsource the tblInsuredsBasic . forget the txtbox on mainform, just
have a command button, with this code:

Private Sub Command1_Click()
On Error GoTo Err_Command1_Click
Dim strsql As String
strsql = "Select * from tblInsuredsBasic where tblInsuredsBasic.LastName =
[?]"
Me!subfrmName.Form.RecordSource = strsql

Exit_Command1_Click:
Exit Sub

Err_Command1_Click:
MsgBox Err.Description
Resume Exit_Command1_Click

End Sub

The subform will show all the records with last name = to what the user
entered on parameter box.

Damon

 

[Damon Heron]

If you want to keep the textbox on the mainform, for whatever reason, change
the sql statement to:
strsql = "Select * from tblInsuredsBasic where tblInsuredsBasic.LastName ="
& "'" & Me.txt1 & "'"
Damon