Im building a database that uses the date of the record as the primary key, the table is set not to allow duplicates. I originally built in a warning messaage that would display if a date was selected that was already present, this would then return you to the form and blank the date. This worked fine but now I would like the script to open the original record on that date, the problem is im getting a data type mismatch.
Heres my code
LTotal = DCount("Datebox", "HaemDailyRota1", "Date1 = Datebox.value")
If LTotal > 0 Then
DoCmd.GoToRecord , , "Date1 = #" & Me.[Datebox] & "#"
'Message box warning of duplication
MsgBox "Daily Rota " _
& SID & " has already been entered." _
& vbCr & vbCr & "Please edit the current rota.", _
vbInformation, "Duplicate Information"
So this script worked fine untill I added the DoCmd.go to record bit, now this generates a data type mismatch, any ideas?
Moving to another record based on a criteria goes like this:
Dim rst as DAO.RecordSet
Dim strCriteria As String
strCriteria = ...
Set rst = Me.RecordSetClone
If rst.NoMatch = False Then
Me.Bookmark = rst.Bookmark
' No matching record
Set rst = Nothing
Are there any other options? I have a 64bit versionof office and from what I gather I cannot run the 360.dll file required for the DAO 3.6 object library. I've tried adding it in the referances but it fails to load.
Ok thanks for that, now I have it so it moves to the line Me.Bookmark = Rst.Bookmark and now it gives error 3022, and a long message about the changes not being allowed as they will create duplicate record in index of tabe!
Your code seems confusing to me. It looks like you were trying to do twice the same thing (check for the existence of a record with a matching date) using two different methods simultaneously (DCount() and FindFirst).
Locates the first record in a dynaset- or snapshot-type Recordset object that satisfies the specified criteria and makes that record the current record (Microsoft Access workspaces only).
expression: A variable that represents a Recordset object.
Criteria: Required (String) A String used to locate the record. It is like the WHERE clause in an SQL statement, but without the word WHERE.
Also, I don't see how the code you posted could trigger an error because of duplicate key values as there is no instruction in it that tries to modify the data.
Anyway, here's a rewritten version of the function, as far as I can understand what you try to achieve:
Private Sub Datebox_AfterUpdate()
Dim Rst As DAO.Recordset
Dim stCritera As String
Set Rst = Me.RecordsetClone
strCriteria = "Date1 = #" & Format(Me.Datebox.Value, "mm/dd/yyyy") & "#"
If Rst.NoMatch = False Then ' --> Duplicate found:
' ==> at least one record found with [HaemDailyRota1].[Date1] = Me.Datebox.Value.
'Message box warning of duplication.
MsgBox "Daily Rota " & Date1 & " has already been entered." & vbCr & vbCr & "Please edit the current rota.", _
vbInformation, "Duplicate Information"
' Move the current record to the one with [Date1] = Me.Datebox.Value.
Me.Bookmark = Rst.Bookmark
' * TO DO: Do something when matching record exists.
Else ' --> No duplicate found:
' ==> No record found with [HaemDailyRota1].[Date1] = Me.Datebox.Value.
' * TO DO: Do something when no matching record.
Set Rst = Nothing
Hi Thanks for your efforts to help me with this, I have just tried the edited code you provided and it works all the way down to Me.Bookmark = Rst.Bookmark, and again tells me I cannot continue as it will create a duplicate record, is the form attempting to save, before moving to the book mark? Do I need to put something in to stop it trying save the duplicate record?
You should first Undo the changes in Datebox (if it's bound to Date1) if you want to move to another record. The error occurs because you're using a bound form with the textbox Datebox bound to Date1. When you move from one record to another (that's what Me.Bookmark = Rst.Bookmark does) Access first try to save the changed data in the current record.