When to use .Edit & .Update

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?

0
Utf
2/12/2008 2:56:01 PM
access.formscoding 7493 articles. 0 followers. Follow

11 Replies
710 Views

Similar Articles

[PageSpeed] 37

are you refreshing the form (to show the current data)?
me.requery

"MeSteve" wrote:

> 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?
> 
0
Utf
2/12/2008 3:57:02 PM
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" wrote:

> 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?
> 
0
Utf
2/12/2008 4:10:04 PM
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" wrote:

> 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" wrote:
> 
> > 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?
> > 
0
Utf
2/12/2008 5:18:01 PM
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


MeSteve wrote:
>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?

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

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200802/1

0
J_Goddard
2/12/2008 6:33:39 PM
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

"J_Goddard via AccessMonster.com" wrote:

> 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
> 
> 
> MeSteve wrote:
> >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?
> 
> -- 
> John Goddard
> Ottawa, ON Canada
> jrgoddard at cyberus dot ca
> 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200802/1
> 
> 
0
Utf
2/12/2008 6:42:03 PM
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
-- 
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"MeSteve" wrote:

> 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?
> 
0
Utf
2/12/2008 6:46:07 PM
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.



"MeSteve" wrote:

> 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
> 
> "J_Goddard via AccessMonster.com" wrote:
> 
> > 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
> > 
> > 
> > MeSteve wrote:
> > >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?
> > 
> > -- 
> > John Goddard
> > Ottawa, ON Canada
> > jrgoddard at cyberus dot ca
> > 
> > Message posted via AccessMonster.com
> > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200802/1
> > 
> > 
0
Utf
2/12/2008 7:01:02 PM
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.

"Dale Fye" wrote:

> 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
> -- 
> Don''t forget to rate the post if it was helpful!
> 
> email address is invalid
> Please reply to newsgroup only.
> 
> 
> 
> "MeSteve" wrote:
> 
> > 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?
> > 
0
Utf
2/12/2008 7:05:02 PM
Set rs = db.OpenRecordset("SELECT * FROM tbl_Projects WHERE ProjectID = " & 
Me.ProjectID)

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"MeSteve" <MeSteve@discussions.microsoft.com> wrote in message 
news:05304FA0-94BC-4496-82E0-7BE5D1BFEDC2@microsoft.com...
> 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" wrote:
>
>> 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" wrote:
>>
>> > 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?
>> > 


0
Douglas
2/12/2008 7:14:05 PM
Its failing at:

Me.DateUpdated = Now()

"Dale Fye" wrote:

> 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.
> 
> 
> 
> "MeSteve" wrote:
> 
> > 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
> > 
> > "J_Goddard via AccessMonster.com" wrote:
> > 
> > > 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
> > > 
> > > 
> > > MeSteve wrote:
> > > >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?
> > > 
> > > -- 
> > > John Goddard
> > > Ottawa, ON Canada
> > > jrgoddard at cyberus dot ca
> > > 
> > > Message posted via AccessMonster.com
> > > http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200802/1
> > > 
> > > 
0
Utf
2/12/2008 7:16:01 PM
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.

"Dale Fye" wrote:

> 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
> -- 
> Don''t forget to rate the post if it was helpful!
> 
> email address is invalid
> Please reply to newsgroup only.
> 
> 
> 
> "MeSteve" wrote:
> 
> > 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?
> > 
0
Utf
2/13/2008 5:59:04 PM
Reply:

Similar Artilces:

Use Form to prompt for report criteria
I have a form that I am using to prompt for report criteria. When I run the query outside of the form, it works fine - prompting me for both criteria. However when I run from the form, I get #Error#. Can you see what I am doing wrong? Thanks in advance. I have two combo boxes that I have put in my underlying query. In the fields of the query are: [Forms]![frmSelection Criteria Form]![OfficeNumber] [Forms]![frmSelection Criteria Form]![Manager] *** On the OnClick event is the following: Private Sub Command6_Click() On Error GoTo Err_command6_Click Dim stDocName As String st...

converting tabular structures in a Word document into an actual table or reading data from the tabular structures using VBA code
I have a macro which can read the last cell/column of all tables in a Word 2003/2007 document and store the data in an MS-Access table. But, some Word documents have the data in structures like a table format but are not actually tables. The structure looks like a table, but the table borders are actually line connectors. These documents were created by a software(VeryPDF PDF to Word converter) which converted the PDF documents(the original format these documents were) into Word documents. 1. Is there a way I can convert/replace the tabular structures with actual tables in Word so t...

Trying to create an Update query based on HR data to find upline V
Hi All, looking for some advice. I have an HR table that contains employee information but does not contain management chain info. Basically i am trying to determine who the employees upline VP is. The fields i have to work with are [Employee Name], [Manager Name] and [Job Title]. I figure the logic would be to check the employees' manager and if the manager is a VP (based on job title), return the manager's name to a field called [VP]. If the manager is not a VP then check that manager's manager, so on and so forth until a VP is found. Any ideas would be much appr...

Insert,Update Data in sage (MS Access Linked tables) using Vb.net form
Hi folks, I am developing application using vb.net which requires integration with SAGE LINE 50 (Accounting software ) V11... The data which SAGE is using is MC ACCESS 2003 database... with linked tables in it... Now I Have developed the Sage connection using ODBC which works fine when reading the record but cannot Add or Update record into the Linked tables.... When i debug the program the error is at the line where it has... <br> MyodbcCommand.ExecutenonQuery() <br> Can anybody Help ????? -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/acce...

loan amortisatio chart not updating
Hi, I use MS Monet 2007 premium. I have created a loan amortisation account which breaks up my monthly instslment into pricipal and interest. The loan commencd from 7 October 2005 and is for a period of 5 years. The problem is that the loan account does ot show any loan instalments beyond 7 October 2006 (exactly 1 year after the commencement). Why is this happening. Why is the account not updating with instalments which have been debited to my account after 7 October 2007. Please help. Were you depending on downloaded transaction data for this account? Did the download link br...

Using atl based win dll with CString functions from the mfc projec #3
I have atl based general windows dll with class which contains functions which uses CString as parameters or return values. This dll might be used from the atl or mfc project. Dll can be used from ATL project without problems but whenever I try to use this class from the MFC project I get the following linker errors: error LNK2019: unresolved external symbol "__declspec(dllimport) public: int __thiscall MyClass::AddMenu(long,class ATL::CStringT<wchar_t,class StrTraitMFC_DLL<wchar_t,class ATL::ChTraitsCRT<wchar_t> > > const &,long)" ... If I replace CStri...

Time update as a limited user not working
I added time update permisssion to my limited user acct. but it does not work. When I try, the time synchonization is greyed out. How can I get it to work. Thanks. On Apr 4, 12:33=A0pm, Mint <chocolatemint77...@yahoo.com> wrote: > I added time update permisssion to my limited user acct. but it does > not work. > When I try, the time synchonization is greyed out. > > How can I get it to work. > > Thanks. Is this Windows MCE SP2? What method did you use to add time update permission to your limited user account? Does your unlimited user accou...

Not using zeros in graphing.
I have a running workbook that has tons of information. I have added a sum page in order to have all the data summed up in one simple place. I have formulas that read back into the workbook to link to a cell. Depending on what moth it is, that cell could be empty as it is a yearly wookbook. For example, if this is August, then there is information in the workbook up to August, but none after. With that said, the sum page has the #DIV/0! in the cell which essentially equals zero. I also have graphs that I have linked to this sum page. My problem is in order to keep the graphs up to...

using vba so search multiple Sheets
i'm trying to search across multiple sheets based on data submitted via an input box. So essentially, you click the button, a box appears, you type what your looking for and if it finds it, it'll select it otherwise a error message appeats. i found the below code, but it only works if the cell with the value in it is active (i.e. i've clicked on it). Code: Dim datatoFind Dim sheetCount As Integer Dim counter As Integer Dim currentSheet As Integer On Error Resume Next currentSheet = ActiveSheet.Index datatoFind = InputBox("Please enter the value to search...

free ActiveX Control for MathML editing
I'd like to suggest free ActiveX Control for MathML creating/editing/rendering: http://www.hermitech.ic.zt.ua/hdata/fmlaxxp_home.php This ActiveX Control is a part of Formulator MathML Suite by Hermitech Laboratory: http://www.hermitech.ic.zt.ua. It makes it simple to develop new software products, keenly aware of the mathematical typesetting and MathML presentation/semantics rules. Vyacheslav Levitsky ...

Excel Running Wild After Update
I ran the Office update via Auto Update. Word and Powerpoint seem fine but Excel attemtps to open every compatible file (and a few that aren't) when I start it up. After it overdoses on files it fails and closes. Any ideas on what to do other than reverting to an older version, pre-update? Wild guess: Look in Applications:Microsoft Office 2004:Office:Startup:Excel: . Are there a bunch of random files in there? I think Excel would try to open anything in there on launch. On 3/23/06 9:04 AM, "Jeff Fread" wrote: > I ran the Office update via Auto Update. Word and Powe...

DPM and VMM on same server using side-by-side SQL installation
Windows 2008 Std R2 Server, 64bit VMM 2007 is currently installed (with the built-in default database) SQL 2005 Express Edition, SP3, 32bit. I was advised by Microsoft that both VMM and DPM could be installed on the same server (small DR environment) The VMs are hosted on a Windows 2008 Enterprise Server w/ VS2005R2 - because the hardware didn't support HyperV. VMM works great to manage the Windows 2008 VS2005R2 Host running Win2K3 server instances ... after lots of trial and error. Now, I need to install DPM 2007 onto the same server... DPM wants to install the 64bit vers...

Use a VBA Macro inside an Excel Cell
This is a multi-part message in MIME format. ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/plain; charset="windows-1255" Content-Transfer-Encoding: quoted-printable its been helpful to me so maybe it will do good for you too: how to create a simple macro within Microsoft Excel, and then how to use = that macro to calculate a single cell value. http://sysudi.blogspot.com/2009/06/use-vba-macro-inside-excel-cell.html ------=_NextPart_000_02B7_01C9F6B9.C9F418D0 Content-Type: text/html; charset="windows-1255" Content-Transfer-Encoding: quoted-printable &l...

Using "fixed" decimal place function data for charts
Is there a way to graph data which utilizes the "fixed" function for decimal places? When I try this it will not graph, as the data has become text or something - even though I've selected number in the cell formats. If you've fixed the number of digits using only number formats, the values remain numeric, and they should chart just fine. This means selecting the range, going to the Formatting menu and selecting Cells, and on the Number tab, selecting Number in the category list, and setting a number of digits. I'm not familiar with a "fixed function". I...

Lotus123 Millennium Edition 9 to Excel 2003
I have several files in Lotus 123 Mellennium ed 9. Is there a way to convert files in Excel 2000/2003 Thanks Excel does not have a converter for .123 format files so you would have to either convert such files to Excel format or .WK4 format from 1-2-3 or use a conversion program like Conversions Plus (www.datawiz.com). -- Jim Rech Excel MVP "NR" <anonymous@discussions.microsoft.com> wrote in message news:354b01c4aa1b$58c65b90$a401280a@phx.gbl... |I have several files in Lotus 123 Mellennium ed 9. | Is there a way to convert files in Excel 2000/2003 | Thanks ...

slow sync with Calendar using Kyocera 7135 via Outlook 2003
I'm stuck and need help. I have a Two Day Old Brand New Dell Dimension 4600. Does anyone out there know what to do when Outlook 2003 is giving a Log report that reads that Outlook Calendar - Slow Sync synchronization failed? This is a first time occurence for me. Outlook also reported that a device is trying to get access to my E- Mail addresses which may indicate a Virus may be present. I've run my McAfee Virus Protection and it didn't detrect anything. I'm at my wits end and don't know what else to do. Can anybody help? ...

Howto save an edited xml doc w/MSXML
The following script modifies the content of my xml file: --------------------------------------------------------------------------------------------------------------------------------------- Dim nodeSel, nodeVal, index Dim name : name = "//ROOT/first_level/second_level/third_level" Dim xmlfile : xmlfile = "temp.xml" Dim value : value = "false" Dim xDoc Set xDoc = CreateObject( "MSXML.DOMDocument" ) index = 0 If xDoc.Load( xmlfile ) Then Set nodeSel = xDoc.selectSingleNode( name ) 'WScript.Echo "Found node", nodeS...

insert text to different pages and update
Is there a way to insert a text cell from one page put into another page and when the first page cell is updated the second page cell will also be updated. Using the = sign just displays the formula, not the text. :confused: Thanks Frank -- fwburkey ------------------------------------------------------------------------ fwburkey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=30314 View this thread: http://www.excelforum.com/showthread.php?threadid=505331 Not clear what you are doing wrong. the formula should look lik =Sheet1!A1 Make sure there is noth...

February updates on EPM (WSS+PServer+MSProject)
Hi, February updates Links are available on http://epm2007.wordpress.com You can comment and poll to give feed back on post installation. Regards, Xavier HOVASSE www.ixila.com Hi Sam, If you have sufficient permissions: In ProjProf: File -> Open ... -> Retrieve the list of all projects ... Right click on project name and select Rename. In PWA: Project Center -> mark project in any column except Idicator and Project Name. Click on Edit Project Properties. Change name and click on Save and Publish. Regards Barbara Am 01.03.2010 08:58, schrieb Xavier HOVASSE...

invalid use of property message
I am trying to cre4ate a macro that checks the value of a number of sheets to determine if they are already visible and if so it will not close them - With Sheets("Pay Inflation - Biometrics") Sheets ("Statistics") Sheets ("Direct Cost Savings Breakdown") Sheets ("OT Reduction") Sheets ("Nurse OT Reduction") Sheets ("Premium Labor Utilization") Sheets ("Pay inflation - Timestamp") Sheets ("Calculation Error") Sheets ("Le...

can not Send/receive using Outlook 2003
Just bouhght new computer and am loading all of my software that worked onmy old one. When I try to send and recieve I get the message object not found. I have used test and this work ok. I have also reloaded all of my broadband and office software numerous times to no prevail. Software Windows xp Office 2003 ADSlL broadband with bigpond Gp Check to make sure the accounts are setup correctly if they are then run Detect and Repair, still an issue then create a new profile. May also want to check your anti-virus software program setting for outgoing mail and disable that option. Rega...

using rule creates multiple entries. How can I stop this?
I'm going to give you the details of this so maybe someone can just tell me how to correct or whether it can be corrected. I've tried creating different rules, rules with exceptions, multiple rules with multiple exceptions, all I can think. Here is the scenario. Although I'm using Lisa as an example, this problem applies to every staffer in my 9 person office. Lisa is my secretary. She sends me internal email, staff questions and stuff which goes to a a "Staff" subfolder of the Inbox. I have a rule for this. She also sends me mail she has scanned. I ha...

Add/Editing a shared workbook hyperlink
I have a shared Excel work book containing many hyperlinks. The users add or edit the hyperlinks every time during the day. The problem is that in order to add or edit a hyperlink one has to take off the shared workbook. Can any one help me out here I want to share the file and add and edit the hyperlinks without kining out other users. Thanks sana ...

updates 05-29-10
I keep getting configuering updates when I turn on anff my computer.Also cant search for updates and there is no record of any updates being downloaded gerry wrote: > I keep getting configuering updates when I turn on anff my > computer.Also cant search for updates and there is no record of any > updates being downloaded When was the last time you freshly installed Windows ME? -- Shenan Stanley MS-MVP -- How To Ask Questions The Smart Way http://www.catb.org/~esr/faqs/smart-questions.html ...

Excel chart linked in Word gets formatted when updateing.
Hi, I have a bit of an annoying issue with excel and word 2007, I have a Chart in excel, and the text on the horizontal axis is rotated 270 degrees,This Chart is linked in a Word document and when i do an update of the linked chart the text gets all messed up. Both of the files are saved in 97-2003 format. ...