Field Name - Auto Add Date if another Name Field = Specific Text (1 Viewer)

mcalpine

Registered User.
Local time
Today, 12:10
Joined
Apr 8, 2013
Messages
16
Hi,

I am new to the forum, and was looking for a little help with Access 2007.

I have field names as shown below

  • Status
  • Date Assigned
  • Time Assigned
I am looking for the time and date Fields to be filled in automatically if the Status Field within a new record show - Assigned.


Any help would be greatly appreciated.



Thank you


M
 

bob fitz

AWF VIP
Local time
Today, 12:10
Joined
May 23, 2011
Messages
4,717
Try the following code in the Before UpDate event of the form.
Code:
If Me.Status= "Assigned" then
  Me.[Date Assigned] = Date()
  Me.[Time Assigned] = Time()
Else
  Me.[Date Assigned] = Null
  Me.[Time Assigned] = Null
End If
 

bob fitz

AWF VIP
Local time
Today, 12:10
Joined
May 23, 2011
Messages
4,717
BTW, Welome to the forum.
 

mcalpine

Registered User.
Local time
Today, 12:10
Joined
Apr 8, 2013
Messages
16
Hi,

I have copied the code that you mentioned, but this does not seem to automatically update the time or date. It is possible something that i am doing wrong........

Thanks

M
 

bob fitz

AWF VIP
Local time
Today, 12:10
Joined
May 23, 2011
Messages
4,717
1)Have you put the code in the form's Before Update event?
2)Have you checked that the code runs when a record is Edited/Added?
 

mcalpine

Registered User.
Local time
Today, 12:10
Joined
Apr 8, 2013
Messages
16
Hi,

I have copied and paste the code you provided into the Before Update

Were would i find the Edited/Added?

Thanks

M
 

bob fitz

AWF VIP
Local time
Today, 12:10
Joined
May 23, 2011
Messages
4,717
Hi,
Were would i find the Edited/Added?M
The code is in the form's Before Upate event so it should run when you add a new record or when you save changes to an existing record. Change the code I gave you to:
Code:
If Me.Status= "Assigned" then
  Me.[Date Assigned] = Date()
  Me.[Time Assigned] = Time()
Else
  Me.[Date Assigned] = Null
  Me.[Time Assigned] = Null
End If
Msgbox "Code Ran"
If you to add a new record or edit an existing record you should see a message box that displays "Code Ran". Do you see this?
 

mcalpine

Registered User.
Local time
Today, 12:10
Joined
Apr 8, 2013
Messages
16
No. not getting box "Code Ran"

Still not Automatically inserting date or time? I have copied the code as shown below into before update event.
If Me.Status= "Assigned" then Me.[Date Assigned] = Date() Me.[Time Assigned] = Time() Else Me.[Date Assigned] = Null Me.[Time Assigned] = Null End If Msgbox "Code Ran"
 

bob fitz

AWF VIP
Local time
Today, 12:10
Joined
May 23, 2011
Messages
4,717
Clearly the code is not running. So lets start with the basics. Do you see [Event Procedure] as the setting for Before UpDate when you look a the forms Property Sheet?
 

mcalpine

Registered User.
Local time
Today, 12:10
Joined
Apr 8, 2013
Messages
16
no, I just pasted in the text that you gave me into the text box. do you want me to paste the text into the Event Procedure within the Before UpDate?
 

mcalpine

Registered User.
Local time
Today, 12:10
Joined
Apr 8, 2013
Messages
16
now showing runtime error 2465. Cannot find the 'I' field referred to in your expression.

when selecting Debug, the text highlighted in yellow is - Me.[Date Assigned] = Date
 

mcalpine

Registered User.
Local time
Today, 12:10
Joined
Apr 8, 2013
Messages
16
Complete code within VB

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Status = "Assigned" Then
Me.[Date Assigned] = Date
Me.[Time Assigned] = Time()
Else
Me.[Date Assigned] = Null
Me.[Time Assigned] = Null
End If
MsgBox "Code Ran"
End Sub
 

mcalpine

Registered User.
Local time
Today, 12:10
Joined
Apr 8, 2013
Messages
16
Hi,

As soon as i added the () they just Disappear ?
 

mcalpine

Registered User.
Local time
Today, 12:10
Joined
Apr 8, 2013
Messages
16
Hi Bob,

Managed to get it working. Me being Stupid.
I had spaced between my Field Names which was automatically filled in by underscores in access. I had to amend your code by adding underscore in-between my field names.

I am looking for another bit of help if possible with the code that you gave me.

is there a way rather than your code saying if the Status field = Assigned then automatically filling in the date and time. The code could be changed to say that if there is any text within the Status field it would automatically fill in the date and time?

Thanks again for your help........
 

mcalpine

Registered User.
Local time
Today, 12:10
Joined
Apr 8, 2013
Messages
16
Here is the code with the underscores that works with Assigned text within the Status Field

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.Engineer_Assigned = "Assigned" Then
Me.[Date__Assigned] = Date
Me.[Time_Assigned] = Time()
Else
Me.[Date__Assigned] = Null
Me.[Time_Assigned] = Null
End If
End Sub
 

bob fitz

AWF VIP
Local time
Today, 12:10
Joined
May 23, 2011
Messages
4,717
I'm glad that you have been able to get it working.
Try:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
  If Not IsNull(Me.Engineer_Assigned) Then
    Me.[Date__Assigned] = Date
    Me.[Time_Assigned] = Time()
  Else
    Me.[Date__Assigned] = Null
    Me.[Time_Assigned] = Null
  End If
End Sub
 

mcalpine

Registered User.
Local time
Today, 12:10
Joined
Apr 8, 2013
Messages
16
Hi Bob,

Perfect. You are a star......worked first time.......

Can i ask possible another stupid question....

Is there any way that i could give users access to input data into a specific form of the database only. i do not want to give them access to the admin side of the database, just a form to input data? I did not know if i would have to get someone to write a front end in vb or c# and create a desktop app to do this task, or if there were an easier way? Thanks
 

bob fitz

AWF VIP
Local time
Today, 12:10
Joined
May 23, 2011
Messages
4,717
It is not difficult to deal with this.
You speak about users, which suggests to me, that the db will be used by more than one person. An Access db that has multiple users should always be split. Some would argue that that every db should be split. A split db is one which has been divided into a "Back end" and a "Front end". The Back end just holds the tables. The Front end has everything else (forms, queries, reports etc). Every user has a copy of the Front end and each Front end has a link to the tables in the single Back end.
So, make a backup of your db first, just in case something goes wrong.
Then:
Split your db using the wizard.
Create a new blank db. Import into it, any forms, queries etc that you want the users to have. Give a copy of the new db to each user and link each one to the single Back end that was created when you made the split.
 

Users who are viewing this thread

Top Bottom