By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,998 Members | 2,837 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,998 IT Pros & Developers. It's quick & easy.

Update of Field after Change

P: n/a
Hello All,

I have a table for my employee data. I have a field that generates
date before update of latest changes made. I also have two fields
within called CreatedBy and UpdatedBy. I would like this autogenerated
but not sure how to make this happen. Since this over a network, the
system knows who is creating and working within a database, I'm at a
loss as to how I point to this to update the field.

Any suggestions? I'm rather new at this!

Thanks!

Aug 7 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
Ask the system how it would like the information, since it already
knows who is in the database. I hope you speak Systemese though,
they've got awful accents which are very hard to understand.

That aside, I'd probably stick a few hidden text boxes on your form and
have them populated with the users name and todays date. Link those
text boxes back to the table the data resides in.
Colleen wrote:
Hello All,

I have a table for my employee data. I have a field that generates
date before update of latest changes made. I also have two fields
within called CreatedBy and UpdatedBy. I would like this autogenerated
but not sure how to make this happen. Since this over a network, the
system knows who is creating and working within a database, I'm at a
loss as to how I point to this to update the field.

Any suggestions? I'm rather new at this!

Thanks!
Aug 7 '06 #2

P: n/a
Use the BeforeUpdate event procedure of the *form* to write the value to the
fields.

You can get the network user name from this link:
http://www.mvps.org/access/api/api0008.htm

Then in the BeforeUpdate event procedure of the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me.CreatedBy = fOSUserName()
Else
Me.UpdatedBy = fOSUserName()
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Colleen" <te*************@frontiernet.netwrote in message
news:11*********************@m73g2000cwd.googlegro ups.com...
Hello All,

I have a table for my employee data. I have a field that generates
date before update of latest changes made. I also have two fields
within called CreatedBy and UpdatedBy. I would like this autogenerated
but not sure how to make this happen. Since this over a network, the
system knows who is creating and working within a database, I'm at a
loss as to how I point to this to update the field.

Any suggestions? I'm rather new at this!

Thanks!

Aug 8 '06 #3

P: n/a
Thanks!

I followed the procedure and when I attempted to use it I get the
following:

Compile Error: expected variable or procedure, not module.

When I click ok, fOSUsername on created by is highlighted.

I've never worked with these before, any suggestions?
Allen Browne wrote:
Use the BeforeUpdate event procedure of the *form* to write the value to the
fields.

You can get the network user name from this link:
http://www.mvps.org/access/api/api0008.htm

Then in the BeforeUpdate event procedure of the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me.CreatedBy = fOSUserName()
Else
Me.UpdatedBy = fOSUserName()
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Colleen" <te*************@frontiernet.netwrote in message
news:11*********************@m73g2000cwd.googlegro ups.com...
Hello All,

I have a table for my employee data. I have a field that generates
date before update of latest changes made. I also have two fields
within called CreatedBy and UpdatedBy. I would like this autogenerated
but not sure how to make this happen. Since this over a network, the
system knows who is creating and working within a database, I'm at a
loss as to how I point to this to update the field.

Any suggestions? I'm rather new at this!

Thanks!
Aug 8 '06 #4

P: n/a
Sounds like you named the module with the same name as the procedure.

If so, rename to module to something else, such as Module1.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Colleen" <te*************@frontiernet.netwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...
Thanks!

I followed the procedure and when I attempted to use it I get the
following:

Compile Error: expected variable or procedure, not module.

When I click ok, fOSUsername on created by is highlighted.

I've never worked with these before, any suggestions?
Allen Browne wrote:
>Use the BeforeUpdate event procedure of the *form* to write the value to
the
fields.

You can get the network user name from this link:
http://www.mvps.org/access/api/api0008.htm

Then in the BeforeUpdate event procedure of the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me.CreatedBy = fOSUserName()
Else
Me.UpdatedBy = fOSUserName()
End If
End Sub

"Colleen" <te*************@frontiernet.netwrote in message
news:11*********************@m73g2000cwd.googlegr oups.com...
Hello All,

I have a table for my employee data. I have a field that generates
date before update of latest changes made. I also have two fields
within called CreatedBy and UpdatedBy. I would like this autogenerated
but not sure how to make this happen. Since this over a network, the
system knows who is creating and working within a database, I'm at a
loss as to how I point to this to update the field.

Aug 8 '06 #5

P: n/a
Boy Doggies I sure am learning things today! I really appreciate your
help!

I renamed the module, I had named it fOSUserName (duh!). Anyway, I now
get run-time error 2465-Microsoft Access can't find the field
'NewRecord' referred to in your expression. This is the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me!NewRecord Then
Me!CreatedBy = fOSUserName()
Else
Me!UpdatedBy = fOSUserName()
Me!DateLastAccessed = Now()
End If
End Sub

Suggestions?
Allen Browne wrote:
Sounds like you named the module with the same name as the procedure.

If so, rename to module to something else, such as Module1.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Colleen" <te*************@frontiernet.netwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...
Thanks!

I followed the procedure and when I attempted to use it I get the
following:

Compile Error: expected variable or procedure, not module.

When I click ok, fOSUsername on created by is highlighted.

I've never worked with these before, any suggestions?
Allen Browne wrote:
Use the BeforeUpdate event procedure of the *form* to write the value to
the
fields.

You can get the network user name from this link:
http://www.mvps.org/access/api/api0008.htm

Then in the BeforeUpdate event procedure of the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me.CreatedBy = fOSUserName()
Else
Me.UpdatedBy = fOSUserName()
End If
End Sub

"Colleen" <te*************@frontiernet.netwrote in message
news:11*********************@m73g2000cwd.googlegro ups.com...
Hello All,

I have a table for my employee data. I have a field that generates
date before update of latest changes made. I also have two fields
within called CreatedBy and UpdatedBy. I would like this autogenerated
but not sure how to make this happen. Since this over a network, the
system knows who is creating and working within a database, I'm at a
loss as to how I point to this to update the field.
Aug 8 '06 #6

P: n/a
NewRecord is a property: you need to use a dot (not a bang) for properties:

If Me.NewRecord Then

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Colleen" <te*************@frontiernet.netwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...
Boy Doggies I sure am learning things today! I really appreciate your
help!

I renamed the module, I had named it fOSUserName (duh!). Anyway, I now
get run-time error 2465-Microsoft Access can't find the field
'NewRecord' referred to in your expression. This is the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me!NewRecord Then
Me!CreatedBy = fOSUserName()
Else
Me!UpdatedBy = fOSUserName()
Me!DateLastAccessed = Now()
End If
End Sub

Suggestions?
Allen Browne wrote:
>Sounds like you named the module with the same name as the procedure.

If so, rename to module to something else, such as Module1.

"Colleen" <te*************@frontiernet.netwrote in message
news:11**********************@m73g2000cwd.googleg roups.com...
Thanks!

I followed the procedure and when I attempted to use it I get the
following:

Compile Error: expected variable or procedure, not module.

When I click ok, fOSUsername on created by is highlighted.

I've never worked with these before, any suggestions?
Allen Browne wrote:
Use the BeforeUpdate event procedure of the *form* to write the value
to
the
fields.

You can get the network user name from this link:
http://www.mvps.org/access/api/api0008.htm

Then in the BeforeUpdate event procedure of the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
Me.CreatedBy = fOSUserName()
Else
Me.UpdatedBy = fOSUserName()
End If
End Sub

"Colleen" <te*************@frontiernet.netwrote in message
news:11*********************@m73g2000cwd.googlegr oups.com...
Hello All,

I have a table for my employee data. I have a field that generates
date before update of latest changes made. I also have two fields
within called CreatedBy and UpdatedBy. I would like this
autogenerated
but not sure how to make this happen. Since this over a network,
the
system knows who is creating and working within a database, I'm at a
loss as to how I point to this to update the field.

Aug 9 '06 #7

P: n/a
Thanks again!

It works wonderfully!

You're Awesome!
Colleen wrote:
Boy Doggies I sure am learning things today! I really appreciate your
help!

I renamed the module, I had named it fOSUserName (duh!). Anyway, I now
get run-time error 2465-Microsoft Access can't find the field
'NewRecord' referred to in your expression. This is the code:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me!NewRecord Then
Me!CreatedBy = fOSUserName()
Else
Me!UpdatedBy = fOSUserName()
Me!DateLastAccessed = Now()
End If
End Sub

Suggestions?
Allen Browne wrote:
Sounds like you named the module with the same name as the procedure.

If so, rename to module to something else, such as Module1.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Colleen" <te*************@frontiernet.netwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...
Thanks!
>
I followed the procedure and when I attempted to use it I get the
following:
>
Compile Error: expected variable or procedure, not module.
>
When I click ok, fOSUsername on created by is highlighted.
>
I've never worked with these before, any suggestions?
>
>
Allen Browne wrote:
>Use the BeforeUpdate event procedure of the *form* to write the value to
>the
>fields.
>>
>You can get the network user name from this link:
> http://www.mvps.org/access/api/api0008.htm
>>
>Then in the BeforeUpdate event procedure of the form:
>>
> Private Sub Form_BeforeUpdate(Cancel As Integer)
> If Me.NewRecord Then
> Me.CreatedBy = fOSUserName()
> Else
> Me.UpdatedBy = fOSUserName()
> End If
> End Sub
>>
>"Colleen" <te*************@frontiernet.netwrote in message
>news:11*********************@m73g2000cwd.googlegr oups.com...
Hello All,
>
I have a table for my employee data. I have a field that generates
date before update of latest changes made. I also have two fields
within called CreatedBy and UpdatedBy. I would like this autogenerated
but not sure how to make this happen. Since this over a network, the
system knows who is creating and working within a database, I'm at a
loss as to how I point to this to update the field.
Aug 9 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.