When to use .Edit & .Update

  • Thread starter Thread starter MeSteve
  • Start date Start date
M

MeSteve

I am trying to update a field on a form. My old code:

Me.ProjectNameShort = Trim(Me.ProjectNameShort)

worked in ACC2003, but threw edit, update ... error when used after going to
ACC2007. So I am trying to use a .edit and .update method. This is what I
have so far, but this updates the first record, not the one that is on the
form.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_Projects")

'Add date/time stamp for DateUpdated
With rs
.Edit
!DateUpdated = Now()
.Update
End With

ShortNameEmpty:

With rs
'Force a short name if Long name is filled in
If IsNull(Me.ProjectNameLong) = False Then
.Edit
Me.ProjectNameShort = Trim(Me.ProjectNameShort)
If IsNull(Me.ProjectNameShort) Or Len(Me.ProjectNameShort) < 1 Then
'Set ShortName value using input box
ShortName = InputBox("Please enter an abbreviated name in the
Project Short Name")
'Remove leading or trailing spaces
ShortName = Trim(ShortName)
'Set ProjectNameShort to ShortName
Me.ProjectNameShort = ShortName
.Update
End If

'Prevent ShortName from being Null
.Edit
!ProjectNameShort = Trim(Me.ProjectNameShort)
.Update
If IsNull(Me.ProjectNameShort) Or Len(Me.ProjectNameShort) < 1 Then
GoTo ShortNameEmpty
End If

End If

End With

End Sub

It seems me.something = me.somethingElse no longer works?
 
What about opening the recordset with a criterium like:

Set rs = db.OpenRecordset("tbl_Projects where [idfield]=" & me.[idfield])

that way you have the id from the record you are trying to edit.
 
Set rs = db.OpenRecordset("tbl_Projects WHERE ProjectID = " & Me.ProjectID)

It returns the ProjectID, but I get an error that Access cannot find the
input table or query.

Maurice said:
What about opening the recordset with a criterium like:

Set rs = db.OpenRecordset("tbl_Projects where [idfield]=" & me.[idfield])

that way you have the id from the record you are trying to edit.
--
Maurice Ausum


MeSteve said:
I am trying to update a field on a form. My old code:

Me.ProjectNameShort = Trim(Me.ProjectNameShort)

worked in ACC2003, but threw edit, update ... error when used after going to
ACC2007. So I am trying to use a .edit and .update method. This is what I
have so far, but this updates the first record, not the one that is on the
form.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_Projects")

'Add date/time stamp for DateUpdated
With rs
.Edit
!DateUpdated = Now()
.Update
End With

ShortNameEmpty:

With rs
'Force a short name if Long name is filled in
If IsNull(Me.ProjectNameLong) = False Then
.Edit
Me.ProjectNameShort = Trim(Me.ProjectNameShort)
If IsNull(Me.ProjectNameShort) Or Len(Me.ProjectNameShort) < 1 Then
'Set ShortName value using input box
ShortName = InputBox("Please enter an abbreviated name in the
Project Short Name")
'Remove leading or trailing spaces
ShortName = Trim(ShortName)
'Set ProjectNameShort to ShortName
Me.ProjectNameShort = ShortName
.Update
End If

'Prevent ShortName from being Null
.Edit
!ProjectNameShort = Trim(Me.ProjectNameShort)
.Update
If IsNull(Me.ProjectNameShort) Or Len(Me.ProjectNameShort) < 1 Then
GoTo ShortNameEmpty
End If

End If

End With

End Sub

It seems me.something = me.somethingElse no longer works?
Click to expand...
Click to expand...
 
Hi -

Why are you using a recordset with .edit and .update when the record you want
to edit is the one on the screen? After all, changing the fields on the form
(assuming they are bound to the table fields) updates the table directly.

I don't see why Me.ProjectNameShort = Trim(Me.ProjectNameShort) would not
work - where are you using it in your code?

John

I am trying to update a field on a form. My old code:

Me.ProjectNameShort = Trim(Me.ProjectNameShort)

worked in ACC2003, but threw edit, update ... error when used after going to
ACC2007. So I am trying to use a .edit and .update method. This is what I
have so far, but this updates the first record, not the one that is on the
form.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_Projects")

'Add date/time stamp for DateUpdated
With rs
.Edit
!DateUpdated = Now()
.Update
End With

ShortNameEmpty:

With rs
'Force a short name if Long name is filled in
If IsNull(Me.ProjectNameLong) = False Then
.Edit
Me.ProjectNameShort = Trim(Me.ProjectNameShort)
If IsNull(Me.ProjectNameShort) Or Len(Me.ProjectNameShort) < 1 Then
'Set ShortName value using input box
ShortName = InputBox("Please enter an abbreviated name in the
Project Short Name")
'Remove leading or trailing spaces
ShortName = Trim(ShortName)
'Set ProjectNameShort to ShortName
Me.ProjectNameShort = ShortName
.Update
End If

'Prevent ShortName from being Null
.Edit
!ProjectNameShort = Trim(Me.ProjectNameShort)
.Update
If IsNull(Me.ProjectNameShort) Or Len(Me.ProjectNameShort) < 1 Then
GoTo ShortNameEmpty
End If

End If

End With

End Sub

It seems me.something = me.somethingElse no longer works?
Click to expand...

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
 
Here is the code that worked in ACC2003, that is broke in 2007

Private Sub Form_BeforeUpdate(Cancel As Integer)

'Add date/time stamp for DateUpdated
Me.DateUpdated = Now()

ShortNameEmpty:

'Force a short name if Long name is filled in
If IsNull(Me.ProjectNameLong) = False Then

Me.ProjectNameShort = Trim(Me.ProjectNameShort)
If IsNull(Me.ProjectNameShort) Or Len(Me.ProjectNameShort) < 1 Then
'Set ShortName value using input box
ShortName = InputBox("Please enter an abbreviated name in the
Project Short Name")
'Remove leading or trailing spaces
ShortName = Trim(ShortName)
'Set ProjectNameShort to ShortName
Me.ProjectNameShort = ShortName
End If

'Prevent ShortName from being Null
Me.ProjectNameShort = Trim(Me.ProjectNameShort)
If IsNull(Me.ProjectNameShort) Or Len(Me.ProjectNameShort) < 1 Then
GoTo ShortNameEmpty
End If

End If

End Sub
 
I don't have 2007, so take my input for what it is worth.

If your form is bound, then you should be able to do most of this in the
BeforeUpdate event by referencing bound controls and fields. You should not
even need to open a separate recordset, actually doing so will probably cause
you write conflict problems, or will cause the work you've done in the
BeforeUpdate event to be undone when the form actually writes the data to
your table.

Dale
 
So, where is this failing?

If me.ProjectNameShort or me.ProjectNameLong is NULL then TRIMMING them will
result in an error. I would do:

IF LEN(me.ProjectNameLong & "") < 1 then
'do something.

What are you doing if ProjectNameLong is null or zero length?

If LEN(me.ProjectNameShort & "") < 1 Then
ShortName = ...
ShortName = Trim(ShortName & "") 'in case ShortName was NULL
me.ProjectNameShort = ShortName
ENDIF

Given that you have a potential loop (GoTo ShortNameEmpty), you might want
to just create a loop that looks something like:

Do while LEN(TRIM(me.ProjectShortName & "")) < 1

'insert code to get and set short name

LOOP

This way, you don't need the second test for the length of ProjectNameShort.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
I am using the BeforeUpdate event. I originally was not using a separate
recordset but Me.DateUpdated = Now() and Me.ProjectNameShort =
Trim(Me.ProjectNameShort)
errors out.
 
Set rs = db.OpenRecordset("SELECT * FROM tbl_Projects WHERE ProjectID = " &
Me.ProjectID)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


MeSteve said:
Set rs = db.OpenRecordset("tbl_Projects WHERE ProjectID = " &
Me.ProjectID)

It returns the ProjectID, but I get an error that Access cannot find the
input table or query.

Maurice said:
What about opening the recordset with a criterium like:

Set rs = db.OpenRecordset("tbl_Projects where [idfield]=" & me.[idfield])

that way you have the id from the record you are trying to edit.
--
Maurice Ausum


MeSteve said:
I am trying to update a field on a form. My old code:

Me.ProjectNameShort = Trim(Me.ProjectNameShort)

worked in ACC2003, but threw edit, update ... error when used after
going to
ACC2007. So I am trying to use a .edit and .update method. This is
what I
have so far, but this updates the first record, not the one that is on
the
form.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim db As Database
Dim rs As Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_Projects")

'Add date/time stamp for DateUpdated
With rs
.Edit
!DateUpdated = Now()
.Update
End With

ShortNameEmpty:

With rs
'Force a short name if Long name is filled in
If IsNull(Me.ProjectNameLong) = False Then
.Edit
Me.ProjectNameShort = Trim(Me.ProjectNameShort)
If IsNull(Me.ProjectNameShort) Or Len(Me.ProjectNameShort) < 1
Then
'Set ShortName value using input box
ShortName = InputBox("Please enter an abbreviated name in the
Project Short Name")
'Remove leading or trailing spaces
ShortName = Trim(ShortName)
'Set ProjectNameShort to ShortName
Me.ProjectNameShort = ShortName
.Update
End If

'Prevent ShortName from being Null
.Edit
!ProjectNameShort = Trim(Me.ProjectNameShort)
.Update
If IsNull(Me.ProjectNameShort) Or Len(Me.ProjectNameShort) < 1
Then
GoTo ShortNameEmpty
End If

End If

End With

End Sub

It seems me.something = me.somethingElse no longer works?
Click to expand...
Click to expand...
Click to expand...
 
Its failing at:

Me.DateUpdated = Now()

Dale Fye said:
So, where is this failing?

If me.ProjectNameShort or me.ProjectNameLong is NULL then TRIMMING them will
result in an error. I would do:

IF LEN(me.ProjectNameLong & "") < 1 then
'do something.

What are you doing if ProjectNameLong is null or zero length?

If LEN(me.ProjectNameShort & "") < 1 Then
ShortName = ...
ShortName = Trim(ShortName & "") 'in case ShortName was NULL
me.ProjectNameShort = ShortName
ENDIF

Given that you have a potential loop (GoTo ShortNameEmpty), you might want
to just create a loop that looks something like:

Do while LEN(TRIM(me.ProjectShortName & "")) < 1

'insert code to get and set short name

LOOP

This way, you don't need the second test for the length of ProjectNameShort.

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
Click to expand...
 
OK, I tried my DB on a machine with 2003 and I am getting the same error at
the same code. 'me.DateUpdated = Now()' Any time I use Me.controlname =
something I get this error.
 
Back
Top