dating it now, not later

[ldodge]

Hi all--
I am building a table that for now has 3 fields: Number, Date_of_Test, and Time_of_Test. What I am trying to do is when a number is enter into the first record, the date and time automatically fill in the remaining records. It works except the date / time always go to the next record. On my design sheet I have the default value as = Date() and = Time() (respectively). I just can't figure out what I am doing wrong. ANY thoughts would be greatly appreciated. Thanks in advance.
Lawrence

[alansidman]

Are you using a form to enter your data. What is the property setting for the form control for the date and time?

Alan

[ldodge]

Hi Alan--
As of right now it is just a table, no form; that will come eventually, but for now I need to get the date stamp to work. So for now there is no form control to have properties.

[alansidman]

I just tested and now understand exactly what is happening. I think that to cure this you will have to remove the default time and date. When you build your form, you will need to put some code in the after update event for the first control you enter data into. Basically, the code will say, if the first control is null, do nothing, however, if it contains data, then populate the date and time control with Date() and Time() respectively.

You have much more control over the data manipulation and input when you use Forms versus directly working in tables.

Alan

[ldodge]

Thanks, I will try that
Lawrence

[ldodge]

Hi Alan--
OK that was wayyyy to deep for me. I am not familar with VBA at all. Could you please show me what the code should look like and with your permission (and my gratitude) I will copy/paste it into place.
Thanks
LCD

[alansidman]

With your form open in design view, right click on the control you wish to use as the first field. Select Properties, Select the Event Tab. Select After Update. Click on the ellipsis (...). Select the Code option. Paste in the code I gave you changing the control names to correspond to yours. You will also need to change the name of the after update command to agree with your control name. Post back if you have any issues.

Private Sub FName_AfterUpdate()
        Me.Tdate.Value = Date
        Me.Dtime.Value = Time()
End Sub

Alan

[ldodge]

Well it is working better, I am getting the date / time on the right line; but it seems the date / time comes in before I add the number i.e. as soon as I hit the new record button on the form it appears even before I add the number. The problem with that is I am afraid the new record button will be pressed before they are actually ready to insert the number (and other pending testing information) Any thoughts
LCD

[alansidman]

Are you able to post your db with dummy data? If you do, make sure to do a compact and repair before you post it. Not sure what is happening that would cause that? Did you remove the default values from the design in the table?

Alan

[ldodge]

Hi Alan--
Here it is (I think). It doesn't have any data as I am just starting to create it. Please feel free to live out your dreams of being a red pen slashing editor

[ldodge]

Forgot to upload

[alansidman]

I changed your Number field to an ID field with autonumber and then inserted a new field called TestNumber. I think that the term Number is a reserved name in Access and that may have been causing the issue. I then created a new form using the updated table as the record source. It now works as designed.

Here is the db back and a also a site listing reserved names and characters.

http://office.microsoft.com/en-us/ac...010030643.aspx

Alan

[ldodge]

oK, I'm stupid, but this doesn't work. I enter a number in the TestNumber field and nothing happens. What am I doing wrong?

[alansidman]

Now I am stumped, because, I just re-opened the db and entered a couple of numbers in a couple of records and the it immediately populated the Date and Time fields. I then checked the table and it was updated properly.

Alan

[ldodge]

I don't know why, but it isn't doing that here. I enter a number and nothing happens in the form or table even after I save or refresh all. Could it be something with my Access software or my computer settings?
thanks for your help
LCD