I've been at this for a couple of days now, but I think I may have solved the problem of writing dates back to the database when using UK formatting and the date changing to US format.
Once you have created your new date in UK format, before writing it to the database convert it to US format and use that format in the SQL statement, like this:
Code:
Private Sub btnUpdate_Click()
Dim dteCurrentDate As Date, dteNewDate As Date, dteUSDate As Date
'Get current date
dteCurrentDate = DLookup("CurrentDate", "tblDate", "ID=1")
MsgBox "Current Date: " & dteCurrentDate
'Calculate new date
dteNewDate = DateAdd("ww", 1, dteCurrentDate)
MsgBox "New Date: " & dteNewDate
'Convert date to US format for SQL statement
dteUSDate = Format(dteNewDate, "mm/dd/yyyy")
MsgBox "US Date: " & dteUSDate
'Update current date
DoCmd.RunSQL ("UPDATE tblDate SET CurrentDate = #" & dteUSDate & "# WHERE ID = 1")
'Refresh form
Me.Refresh
End Sub
If you now have a date that could be either US or UK format and you region is UK, the date is written in US format but displayed in UK format.
Hope this helps anybody else that has been banging their head on a wall for days!