ADO Access 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

 

[Tom van Stiphout]

On Fri, 23 May 2008 14:05:31 -0700 (PDT), Christoph

That seems such a clunky user interface. Why not use a subform to
display the data from that table, filtered by the lastname (or other
fields) entered? Concatenate the where clause, then set the Filter
property for the subform (me.mysubformcontrol.form.filter) and set the
FilterOn property to True.

-Tom.

 

[Albert D. Kallal]

Why even bother using ADO here?

Why not just shove the sql directly into the form's reocrdset, and save all
the time coding your doing.

furthermore, if you'll owe the data into a record set, then how do you plan
to develop and write the code for navigation on the form to move among the
records?


just go:
dim strSQL as string
strSQL = "SELECT * FROM tblInsuredsBasic " _
& "WHERE [LastName] = '" & Me.txt1 & "'"
me.reocrdSource = strSql

I count about 3 lines of code here. Furthermore this design works if you add
more controls are more fields to the screen, it just makes no sense to a B.
as in record sets here when you got forms that can do all the dirty work for
you.

, txt2 and

txt3. They hold respectively, last name, first name and city.

ok, then go:

dim strSQL as string

if isnull(me.txt1) = false then
strSQL = "LastName = '" & me.txt1 & "'"
end if

if isnull(me.txt2) = false then
if strSQL <> "" then strSQL = strSQL & " and "
strSQL = strSQL & "Firstname = '" & me.txt2 & "'"
end if

if isnull(me.txt3) = false then
if strSQL <> "" then strSQL = strSQL & " and "
strSQL = strSQL & "City = '" & me.txt3 & "'"
end if

me.RecrdSource = "select * from tblInsuredsBasic where " & strSsql

The above will work if you enter any combination of the three text boxes.

So, just take the resulting sql, and shove it in the forms reocrdSource.
Using the forms record source will also allow you to navigate if there is
more then one record match. Furthermore, it will allow you to edit and
update the data.

If you use a reordset and a ADO setup, then that's way too much work, and
you'll not have much flexibility in the end results either.

 

[Christoph]

Why even bother using ADO here?

Why not just shove the sql directly into the form's reocrdset, and save all
the time coding your doing.

furthermore, if you'll owe the data into a record set, then how do you plan
to develop and write the code for navigation on the form to move among the
records?

just go:
    dim strSQL     as string
    strSQL = "SELECT * FROM tblInsuredsBasic " _
        & "WHERE [LastName] = '" & Me.txt1 & "'"
    me.reocrdSource = strSql

I count about 3 lines of code here. Furthermore this design works if you add
more controls are more fields to the screen, it just makes no sense to a B..
as in record sets here when you got forms that can do all the dirty work for
you.

, txt2 and

txt3. They hold respectively, last name, first name and city.

ok, then go:

dim strSQL       as string

if isnull(me.txt1) = false then
   strSQL = "LastName = '" & me.txt1 & "'"
end if

if isnull(me.txt2) = false then
   if strSQL <> "" then strSQL = strSQL & " and "
   strSQL = strSQL & "Firstname = '" & me.txt2 & "'"
end if

if isnull(me.txt3) = false then
   if strSQL <> "" then strSQL = strSQL & " and "
   strSQL = strSQL & "City = '" & me.txt3 & "'"
end if

me.RecrdSource = "select * from tblInsuredsBasic where " & strSsql

The above will work if you enter any combination of the three text boxes.

So, just take the resulting sql, and shove it in the forms reocrdSource.
Using the forms record source will also allow you to navigate if there is
more then one record match. Furthermore, it will allow you to edit and
update the data.

If you use a reordset and a ADO setup, then that's way too much work, and
you'll not have much flexibility in the end results either.

Hey Albert,

first of all thank you very much for you help. I followed your
suggestion above and it works great.

I have two problems left now:

1. I also used ADO to save a recordset. Obviously, I now figure that
saving a record must be possible without any ADO code as well. What do
you suggest here? Somehow, the

DoCmd GotoRecord ,, acNewRec

doesn't seem very elegant programming style.

2. Using the suggestion you gave me above, I then used the following
command to create my own "forward to next record" button with the
following underlying code:

DoCmd.GoToRecord acDataForm, "frmInsuredBasic", acNext

The problem I run into here is that I get an error when the command
above goes beyond the dimension of the record. In other words, if the
last record is displayed on the form and I then click to go to the
next one using the code above, I get an error message. Do you have a
suggestion how to avoid the DoCmd (or if you tell me it's decent
programming style, so be it) and how I can check for the boundries of
the recordset so that I don't get an error message when I go past the
last record.

Thank you so much,
Christoph

 

[Albert D. Kallal]

1. I also used ADO to save a recordset. Obviously, I now figure that

saving a record must be possible without any ADO code as well. What do
you suggest here? Somehow, the

Assuming a bound form, nothing need be done to save the data, it is
automatic.

if you for some reason you need to force the data in the form to be written
to the table them simply go:

if me.dirty = true then
me.dirty = false
end

2. Using the suggestion you gave me above, I then used the following
command to create my own "forward to next record" button with the
following underlying code:

DoCmd.GoToRecord acDataForm, "frmInsuredBasic", acNext

I just use:

On Error Resume Next
DoCmd.GoToRecord acActiveDataObject, , acNext

The reason for the above syntax is we don't want to hard code the form name
into our code, so the code can be cut/pasted, or even put into a code module
that we use for all forms.

And, if you turn on the forms navigation buttons, then you really don't need
to put your own buttons on the form to navigate. I would try to resist
placing your own navigation buttons on the form, since then if you do this
for one form, then you have to do this for all forms (your users will expect
this). And, with your own buttons, then you have to make EXTRA efforts to be
more consistent, and ensure that you always place them in the same location.
If you use the built in ones, and then you get this feature on every form
for free, and it more consistent in look and feel for your application.

As mentioned an act of the user navigating to a new record will cause the
current record to be saved. (why would a user be trying to move to the next
record if they don't want the current one saved? So, not much reason to
force the user to death with a save button when that's what they want
anyway).

 

[Christoph]

saving a record must be possible without any ADO code as well. What do
you suggest here? Somehow, the

Assuming a bound form, nothing need be done to save the data, it is
automatic.

if you for some reason you need to force the data in the form to be written
to the table them simply go:

   if me.dirty = true then
      me.dirty = false
   end

2. Using the suggestion you gave me above, I then used the following
command to create my own "forward to next record" button with the
following underlying code:

   DoCmd.GoToRecord acDataForm, "frmInsuredBasic", acNext

I just use:

     On Error Resume Next
     DoCmd.GoToRecord acActiveDataObject, , acNext

The reason for the above syntax is we don't want to hard code the form name
into our code, so the code can be cut/pasted, or even put into a code module
that we use for all forms.

And, if you turn on the forms navigation buttons, then you really don't need
to put your own buttons on the form to navigate. I would try to resist
placing your own navigation buttons on the form, since then if you do this
for one form, then you have to do this for all forms (your users will expect
this). And, with your own buttons, then you have to make EXTRA efforts to be
more consistent, and ensure that you always place them in the same location.
If you use the built in ones, and then you get this feature on every form
for free, and it more consistent in look and feel for your application.

As mentioned an act of the user navigating to a new record will cause the
current record to be saved. (why would a user be trying to move to the next
record if they don't want the current one saved? So, not much reason to
force the user to death with a save button when that's what they want
anyway).

Wow, thank you so much. Unbelievable, this whole complex project I had
is now a piece of cake with your help. Avoiding ADO definitely reduces
the amount of coding.

I have to ask though - when would it make sense to use ADO? I bought a
book called "Access 2007 Bible" and in the programming section it
mentions that you need to use ADO to connect to your data. Why would
one do this if you can bind the form to a recordsource? Is this
strictly for accessing external databases?

Thanks again,
Christoph

 

[Albert D. Kallal]

- when would it make sense to use ADO? I bought a

book called "Access 2007 Bible" and in the programming section it
mentions that you need to use ADO to connect to your data. Why would
one do this if you can bind the form to a recordsource? Is this
strictly for accessing external databases?

Thanks again,
Christoph

=========

A good question!

Your spider sense about wanting to access external data bases is a good
answer to when to use ADO.

There are some instances and situations in which you might want to use some
ADO programming. A really good example would be to execute a stored
procedure that is on SQL server. For the most part we don't need ADO when
just working with a form and some data. Another scenario in which will use
an ADO records is if you need to pull some data from a table, loop for each
record, and do some processing with that data (but you have no need to
display that data in a form for example). Even in this type of scenario you
can simplye exeucte SQL on the table, and again you don't need ado. However,
in some cases you can't get SQL to do what you want, so you load up a
recordset (via ADO) and then process each record one by one...