ACCESS FEED: RE: [MS_AccessPros] Re: SET statement

Minggu, 28 April 2013

RE: [MS_AccessPros] Re: SET statement

Phucon-

Short answer is no. The rs variable will have whatever it was last set to.
If FORM2 opens after FORM1, and both set the rs variable, then rs will have
the recordset opened by FORM2.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of saigonf7q5
Sent: Sunday, April 28, 2013 8:47 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: SET statement

Since the Set statement is used in more than 1 form and procedure, I
declared the variables in a standard module:

Public db As DAO.Database
Public rs As DAO.Recordset

If there's more than 1 recordset opened, for example I open a recordset in
FORM1:

Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

then open another in FORM2

Set db = CurrentDb
Set rs = db.OpenRecordset(strTableName, dbOpenDynaset)

If the recordset in FORM1 is close:

ExitProcedure:
rs.Close
Exit Sub

Will that trigger the opened recordset in FORM2 to close?

Phucon

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...>
wrote:
>
> Phucon-
>
>
>
> If you use the SAME form and recordset in all the procedures, then you
> can declare the form and recordset variable at the module level - just
> below Option Compare Database. Set them both in the form Load event
> and close / clear them in the form Close or Unload event.
>
>
>
> John Viescas, Author
>
> Microsoft Access 2010 Inside Out
>
> Microsoft Access 2007 Inside Out
>
> Microsoft Access 2003 Inside Out
>
> Building Microsoft Access Applications
>
> SQL Queries for Mere Mortals
>
> http://www.viescas.com/
>
> (Paris, France)
>
>
>
>
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Clive
> Sent: Monday, April 15, 2013 12:13 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: SET statement
>
>
>
>
>
> Hi Phucon,
>
> Are you wanting to cycle through all of the items in the ListBox for
> the same recordset?
>
> If so, then you can have a loop in your routine to cycle through all
> of the Listbox entries using just one Set statment in each routine.
>
> If that is not what you want then you need to give more information
> about what the task consits of.
>
> Regards, Clive.
>
> --- In MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> , "saigonf7q5"
> <saigonf7q5@> wrote:
> >
> >
> > Hello
> >
> >
> > My form module hasseveral procedures. The procedues all have the
> > same "SET" statements,like the set frm and set rs that's shown in
> > the cmdSave and cmdDelete below:
> >
> > Is it possible to recreatesomething like a variable that the all
> > procedures can refer to it, so the SET Statement does not need to be
> > repeated in each sub?
> >
> > I have tried to place them out side the procedure, but ended up with
> > error.
> >
> >
> > Phucon
> >
> >Private SubcmdSave_Click()
> > On Error GoToErrorHandler
> > Set frm = Form_frmAllStaff
> > Set rs = CurrentDb.OpenRecordset("tblAllStaff",dbOpenDynaset)
> > With rs
> > .FindFirst "[Sid] = " &frm!lstBox.Column(0) If Not rs.NoMatch Then
> >.Edit !An = Me.txtDAn !Re = Me.txtDRe !Ex = Me.txtDEx !Rn =
> >Me.txtDRn .Update .Requery End If End With
> >ExitProcedure:
> > rs.Close
> >Exit Sub
> >ErrorHandler:
> > MsgBox "Err = " & Err &vbCrLf & Err.Description Resume
> >ExitProcedure Resume End Sub
> >
> >Private SubcmdDelelte_Click()
> >On Error GoToErrorHandler
> > Set frm = Form_frmAllStaff
> > Set rs = CurrentDb.OpenRecordset("tblAllStaff",dbOpenDynaset)
> > With rs
> > .FindFirst "[Sid] = " &frm!lstBox.Column(0) If Not rs.NoMatch Then
> >.Delete .
> > .
> > .
> > End If
> > End With
> >ExitProcedure:
> > rs.Close
> > Exit Sub
> >ErrorHandler:
> > MsgBox "Err = " & Err &vbCrLf & Err.Description Resume
> >ExitProcedure Resume End Sub
>
>
>
>
>
> [Non-text portions of this message have been removed]
>

------------------------------------

Yahoo! Groups Links