I'm sorry I haven't reply to this for several days, but I had some other emergencies.
Actually, what I was trying at my spare time, was what Vlad suggested in his last post. It seems to be working as I like to. I'll post any updates as soon as I can
In the meanwhile could you help me with an update query I want to use for that?
I want to update a record in my main table with data from my temporary record. The two tables have identical structure, and the records in question have the same PK
The code I'm using is as follows:
Code:
Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT * FROM tbl_TEMP_Meds_Changes WHERE Med_StopDate IS NOT NULL AND ID IS NOT NULL AND New_Entry = TRUE;"
Set rs = CurrentDb.OpenRecordset(strSQL)
With rs
If Not .BOF And Not .EOF Then
.MoveLast
.MoveFirst
While (Not .EOF)
dbs.Execute "UPDATE tbl_Meds_Changes " & _
"SET tbl_Meds_Changes.Med_EntryDate = " & rs.Fields("Med_EntryDate") & "WHERE tbl_Meds_Changes.MedChange_ID = " & rs.Fields("ID") & ";"
.MoveNext
Wend
End If
.Close
End With
The problem is that while my temporary table stores and displays Med_EntryDate correctly (it is a date in short format), when the actual update takes place, the main tables stores a "30/12/1899" value. I tried a
Code:
Debug.Print rs.Fields("Med_EntryDate")
and it does display the date correctly.
Both temp and main tables have the according field set to date, short.
When I try to update other, non-date fields everything works fine. Any ideas?