Update a different table

[BRABUS]

Ok this is probably childsplay to most of you.

I currently have a form that I use to enter data to a table called FRED. On this form I have a buttom that sets the date and time value on the table called FRED to the current date and time using NOW(). That works fine.

Now at the same time using the field called WRNumber on this form I would like to set the date and time on another table called JANE. The table called JANE uses WRNumber as its key and is unique on this table.

Does that make sense? Can anyone point me in the right direction please.

 

[DCrake]

If you are using the Now() function to date/time stamp the creation of the new record why not go to the default value of the field and set it to Now() for both Jane and Fred.

David

 

[BRABUS]

Thats fine on FRED but on JANE how do I find the correct record using the WRNumber from FRED and add the time stamp to that record.

Sorry to sound so dim but I think I am

 

[DCrake]

Use a DLookup()

Look in fred for the wrnumber and see what the date was and update jane with the same date.

 

[BRABUS]

Sorry I am not making myself clear - I do appologise. I dont know how to write the time into the record on JANE as its not linked to the form I have open.

 

[DCrake]

DoCmd.SetWarnings False
DoCmd.RunSQL "Update JANE Set YourDatefield = #" Now() & "# Where WRNumber=" & Me.WNNumber
DoCmd.SetWarnings True

All you need to do is to invoke this code after you have added the record to FRED. Wheere Me.WNNumber is the PK in Jane

 

[BRABUS]

Thanks David i can now get my date to save to the second table. It does throw up an odd issue though.

If the day of the month is 10 or greater then it saves it correctly as dd/mm/yyyy if however the day number is 9 or less it saves it as mm/dd/yyyy

I have checked all of my regional settings and they are spot on for the UK. I am now completely flummoxed.

 

[DCrake]

Thats Microsoft for you.It's a case of americanism you may need to save your data as Medium Date to ensure that it stores it correctly or change it store it mmddyyyy.

Have you got a format on your field properties? dd/mm/yyyy
Input mask 99/99/0000;0;_
Also tell it save it as Format(Date,"dd/mm/yyyy")

David

 

[BRABUS]

Cheers David, at least it wasn't me going mad.

I finally managed it by setting the date as dd/mmm/yyyy

SQL = "UPDATE [TABLE - WR & WBS Codes] SET [TABLE - WR & WBS Codes].[DateWRADatabaseUpdated] = #" & Format(Me.[WRA Database Updated], "dd/mmm/yyyy") & "# WHERE [TABLE - WR & WBS Codes].[WR Code] = '" & [WR Number] & "'"


I am a happy bunny again - thanks for all your help