ACCESS FEED: [MS_AccessPros] Re: SET statement

Minggu, 28 April 2013

[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]
>