Recordset Syntax Problem 1

[rosieb]

I'm trying to prevent creation of duplicate records in a time-recording database.

I get a "Run-time error 13 Type mismatch"

My code is:

Private Sub cboStaff_Exit(Cancel As Integer)

Dim rsClone As DAO.Recordset
Set rsClone = Me.RecordsetClone
rsClone.FindFirst "StaffID = " & Me![cboStaff] & "" And "RecDate = " & Me![RecDate] & ""

If Me.RecordsetClone.NoMatch Then

Me!AddTime.SetFocus

Else
MsgBox "You Have Already Created an Entry for This Date.", vbOKOnly, "Duplicate Entry Found"
Me.Undo

End If

End Sub

StaffID is a number, RecDate is a Short Date
It seems to be the second criterion which is causing the problem.

Can anyone help me to identify my mistake?

 

[glalsop]

Try something like this:

rsClone.FindFirst "StaffID = " & Me.cboStaff & "And RecDate = #" & Me.RecDate & "#"

You have to include # around all dates and ' around all string variables.

Hope that helps

-Gary

 

[rosieb]

Gary

Thanks, but something's still not quite right.


When I use your line, I get no error message but it doesn't pick up the duplicates. It also desn't recognise the And as an operator.

I tried:
rsClone.FindFirst "StaffID = " & Me.cboStaff & "" And RecDate = #" & Me.RecDate & "#"

But I get a Compile error. Expected Expression.

(If I crop it before the And, the first half seems to work OK)

Any suggestions?

Rosie

 

[rosieb]

Tried again with the following:

rsClone.FindFirst "StaffID = " & Me.cboStaff & "" And "RecDate = #" & Me.RecDate & "#"

This gets rid of the compile error, but brings back the type mismatch error.

Any ideas?

 

[glalsop]

Hmmm... Here is some code that I am using to do the exact same thing you are trying to do (with your field names substituted). I have it in the Form_BeforeUpdate event. It works in my app Put your table name in the bold area

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim msg As String

msg = "A record already exists for employee " & Me.cboStaff & " on " & Me.RecDate) & Chr(13) & Chr(13) & "Do you want to enter another record?"

Set dbs = CurrentDb()
Set rs = dbs.OpenRecordset("SELECT * FROM SubstituteYourTableName WHERE ((StaffID = '" & Me.cboStaff & "') AND (RecDate = #" & Me.RecDate & "#));&quot

If rs.RecordCount > 0 Then
'There is already a record for this employee on this date
If MsgBox(msg, vbYesNo, "Duplicate Record&quot = vbYes Then
'we want another record, do nothing
Else
Me.Undo
End If
End If

Exit_frm_BeforeUpdate:
Exit Sub

Err_frm_BeforeUpdate:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Exit_frm_BeforeUpdate

End Sub


Hope that helps

-Gary

 

[glalsop]

Oops, get rid of the ) after Me.RecDate in the nsg assignment statement.

 

[glalsop]

Also get rid of the single quotes in this statement:

Set rs = dbs.OpenRecordset("SELECT * FROM SubstituteYourTableName WHERE ((StaffID = '" & Me.cboStaff & "') AND (RecDate = #" & Me.RecDate & "#));&quot

My StaffID is a text field, yours is a number.

 

[rosieb]

Gary

This is weird, when I use your code as is (with the changes), it doesn't recognise the duplicates.

The minute I remove the AND RecDate portion, it starts to recognise the duplicate StaffIDs.

As it works on your database, the problem's obviously not the syntax now. I think I'm going to take it home tonight and try again.

Thanks for your help

Rosie

PS I'm rumming '97 - that wouldn't have any bearing would it?

 

[glalsop]

The only reason I can think of that would cause it to recognize duplicates when you remove the date portion is that you have duplicate staff records but not duplicate date records for those staff members.

In other words my procedure allows you to enter as many records for employee 1111 as you like, as long as the cooresponding date in those records are all different. This is what you want?

Let me know how it goes, I am working on a very similar project to you right now (Time tracking)

-Gary

 

[rosieb]

Gary

I'm afraid that's not the answer, I've now got 6 entries with the same StaffID and Date.

I think we're doing pretty much the same thing, in mine each staff member should have a separate record for each day worked. That, in turn, links to multiple activity records.

(I'm sure, it's something really stupid that I'm doing)

I'll keep you posted.

Cheers

Rosie

 

[GTLoco99]

Since you are using And, it will only find duplicates where both the StafID and the Date have dulpicates. This will not work for you if I understand correctly, you have something like this

Staff ID Date
6 1/1/03
6 1/2/03
6 1/3/03
etc...

You have multiple records for a staff member, all under the same date. In this case, you need to use an Or. That way if, there are multiple staff entries, or multiple date entries, the statement is True, and should remove the duplicates.

Thanks
Glen

 

[rosieb]

Glen

Thanks for joining in....

I have:

StaffID RecDate
1 4/12/03
1 4/12/03
1 4/12/03

So the AND should work fine.

For some reason it's just not picking up the

RecDate = #" & Me.RecDate & "#

part, I keep returning to the fact that while trying a different, but similar, approach above - I got a "type mismatch" but both are set to Short Date.

I'm going to carry on poking it with a sharp stick, but if you've got any ideas....

Thamks

Rosie

 

[rosieb]

Well, I'm getting there...

I just tried putting a specific date into the code, it didn't work until I put it in in US format mm/dd/yyyy, as opposed to my usual UK format dd/mm/yyyy.

So, my database has developed a split personality, part US part UK. I'm at home now, so I know all my global settings are UK. But at least I know roughly where the problem lies.

Gary, thanks for all your help.

Rosie

 

[glalsop]

Ahhhh...

I must say I was perplexed. How can the exact same code work for me and not you? It sounds now like you just need to standardize the way that you input data with the way that your table wants the data. Or change the way that your table wants the data

If this has the potential to be a significant problem for your users you may want to look into using the date time picker control.

-Gary

 

[glalsop]

This looks helpful if your users may be using different regional settings...

http://support.microsoft.com/default.aspx?scid=kb;en-us;210069&Product=acc2000

 

[rosieb]

Gary

Thanks for that. I must admit I'm giving up now, it's nearly 10pm and I've been wrestling with this, on and off, all day. I'll attack it again tomorrow, the link looks useful. At least now I know what the cause is, which is 90% of finding a solution.

Cheers

Rosie

 

[rosieb]

For any other Brits who may be interested, I found a solution, not pretty, but it seems to work....

The SELECT statement insists on a US date, so using DatePart I've created a calculated control (hidden) which translates the date to US format and substituted that into the SELECT statement.

I'm sure there's a more elegant solution!

Rosie