Re: Newbiew question...PLEASE HELP

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Fuzzy Logic (bob_at_arc.ab.caREMOVETHIS)
Date: 09/10/04 

Date: Fri, 10 Sep 2004 13:40:56 -0700

"Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in news:eFpKV02lEHA.3536
@TK2MSFTNGP12.phx.gbl:

> "Fuzzy Logic" <bob@arc.ab.caREMOVETHIS> wrote in message
> news:Xns95607B35969B0bobarcabca@207.46.248.16
>> "Dirk Goldgar" <dg@NOdataSPAMgnostics.com> wrote in
>> news:#lj#vZslEHA.2892 @tk2msftngp13.phx.gbl:
>>
>>> "Fuzzy Logic" <bob@arc.ab.caREMOVETHIS> wrote in message
>>> news:Xns955FA7E168D91bobarcabca@207.46.248.16
>>>> I have created a form that accesses a very simple table called
>>>> tabletest with two fields:
>>>>
>>>> Index Comment
>>>> 1 One
>>>> 2 Two
>>>> 3 Three
>>>> 4 Four
>>>> etc...
>>>>
>>>> I have created a form that displays one record from the form and
>>>> has a button on it. What I want to have happen when I have record 2
>>>> displayed and hit the delete button is to get the following results:
>>>>
>>>> Index Comment
>>>> 1 One
>>>> 2 Three
>>>> 3 Four
>>>> etc.
>>>>
>>>> preferably with the 'new' record 2 displayed.
>>>>
>>>> My VBA/Access skills are currently very limited. Here is the code I
>>>> have from the button>Build Event for my form:
>>>>
>>>> Private Sub Delete_Click()
>>>> Dim tempindex As Integer
>>>> tempindex = temp.index
>>>> DoCmd.RunSQL "DELETE * FROM tabletest WHERE
>>>> tabletest.index=[tempindex]" DoCmd.RunSQL "UPDATE tabletest SET
>>>> tabletest.index = tabletest.index-1 WHERE tabletest.index >
>>>> [tempindex]" Exit Sub
>>>> End Sub
>>>>
>>>> This is obviously not working. Can anyone fix the syntax of this to
>>>> make it work? Any pointers would be GREATLY appreciated as I am
>>>> getting quite frustrated with this. TIA
>>>
>>> That doesn't look far off to me. Try this:
>>>
>>> '----- start of revised (untested) code -----
>>> Private Sub Delete_Click()
>>>
>>> Dim lngIndex As Long
>>>
>>> If IsNull(Me!Index) Then
>>> MsgBox "I can't do it -- there's no index for this record!"
>>> Exit Sub
>>> End If
>>>
>>> ' Save value of current record's "Index" field.
>>> lngIndex = Me!Index
>>>
>>> ' Save current record if it has been modified.
>>> If Me.Dirty Then
>>> RunCommand acCmdSaveRecord
>>> End If
>>>
>>> With CurrentDb
>>>
>>> ' Delete this record.
>>> .Execute "DELETE * FROM tabletest WHERE [Index]=" &
>>> lngIndex, _ dbFailOnError
>>>
>>> ' Update subsequent indexes.
>>> .Execute "UPDATE tabletest SET [Index] = [Index]-1 " & _
>>> "WHERE [Index] > " & lngIndex, _
>>> dbFailOnError
>>>
>>> End With
>>>
>>> ' Requery the form to reflect changes to the table.
>>> Me.Requery
>>>
>>> ' If possible, position to the record that now has the same index
>>> ' as the one we deleted.
>>> Me.Recordset.FindFirst "[Index]=" & lngIndex
>>>
>>> End Sub
>>>
>>> '----- end of revised code -----
>>>
>>> I use the Execute method of the DAO Database object (returned by the
>>> CurrentDb function) because that way I don't get the warnings about
>>> "You are about to delete/update ...". But DoCmd.RunSQL would work
>>> fine except for that.
>>
>> Thanks for replying. I haven't tried this code yet but want to clarify
>> some things. Remember that I am new to Access/VBA. What are the Me.
>> and Me! references and what do they do?
>
> The keyword "Me" is a reference to the class object in which the code is
> running. So in code behind a form, "Me" refers to that form, whereas in
> code behind a report it refers to that report, and in a regular class
> module it refers to the current instance of that class.
>
> In referring to controls on a form, use of the "Me" keyword isn't
> strictly necessary, because it's assumed for any name that cannot
> otherwise be resolved. However, saying Me!<control name> avoids all
> doubt, and makes sure there can be no confusion if the same name could
> have multiple interpretations.
>
> Technically, "Me!SomeName" should be used to refer to a control on the
> form (the "bang" (!) says that SomeName is a member of a collection, in
> this case the form's Controls collection), while Me.SomeName should be
> used to refer to a property or method of the form. Access blurs this
> distinction, however, and you can use Me.ControlName freely in all cases
> where it couldn't be confused with a property or method of the same
> name. My habit is to use "Me." where I can, and when my interest is in
> the value of the control. However, since your field and the control
> bound to it both appear to be named "Index", which is a meaningful word
> in other contexts -- and hence a bad choice for a user-defined name -- I
> went to some lengths to ensure that Access wouldn't be confused by it.

Thanks for clarifying that. It's pretty much what I assumed and have seen
it used in other code fragments and wasn't sure. Is "Me" an acronym for
something or have any special meaning?

Quantcast