Newbiew question...PLEASE HELP

  • Thread starter Thread starter Fuzzy Logic
  • Start date Start date
F

Fuzzy Logic

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
 
Fuzzy Logic said:
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
Click to expand...

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.
 
Fuzzy Logic said:
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
Click to expand...

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.
Click to expand...

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?
 
Fuzzy Logic said:
Fuzzy Logic said:
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
Click to expand...

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.
Click to expand...

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?
Click to expand...

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.
 
Fuzzy Logic said:
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.
Click to expand...

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?
Click to expand...

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.
Click to expand...

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?
 
Fuzzy Logic said:
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?
Click to expand...

Yeah ... it means "me" -- this code object here who's talking. <g>
 
Back
Top