Date format 2

[MKH2001]

All I have run into an issue with my database.

My DB is running on a Citrix box and I have recently found that I am getting records slip through duplicate record trap.

Private Sub Date_AfterUpdate()
If Not IsNull(DLookup("[UNITID]", "[tbl_BCCInspection]", "[UNITID]='" & Forms![frm_BCCInspectInput]![UNITID] & "'")) And Not IsNull(DLookup("[date]", "[tbl_BCCInspection]", "[date]=#" & Forms![frm_BCCInspectInput]![date] & "#")) Then
If Me.NewRecord Then
Select Case MsgBox("This is a duplicate record! Do you wish to DELETE this record?", vbYesNo + vbQuestion, "THIS IS A DUPLICATE RECORD")
Case vbYes: 'Delete the record
        Me.UNITID.SetFocus
        DoCmd.RunCommand acCmdUndo
        Me.tbProperSave.Value = "No"
    MsgBox "The duplicate has been deleted!"
Case vbNo: 'Do NOT delete the record
    DoCmd.CancelEvent
    MsgBox "You are continuing input of a duplicate record!"
Case Else: 'Trap any other errors that could occur
    'Do Nothing
End Select
End If
End If
End Sub

This means that the trap has missed a date that appears entered 09/08/2005
and yet if I entered 08/09/2005 on my form it is picked up.

This has raised issues with integrity of existing data in the DB as I have found that several users have had their Locale settings (on the Citrix box) set to US as opposed to United Kingdom.

I need to
1: Impose a rule that will mean all data in the tables is entered correct irespective of locale setting.
2: If storage of my date is not a problem (and it is the duplicate trap script instead?) rectify that so that it will be able to deal with the issue.
3: Establish the damage to the integrity of my existing data and rectify where possible.....

Any idea?

 

[PHV]

Simply replace this:
"[date]=#" & Forms![frm_BCCInspectInput]![date] & "#"
with this:
"[date]=#" & Format(Forms![frm_BCCInspectInput]![date], "yyyy-mm-dd") & "#"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MKH2001]

Many thanks on the fast response.
Unfortunately this alteration gives me the following error below:

Private Sub Date_AfterUpdate()
If Not IsNull(DLookup("[UNITID]", "[tbl_BCCInspection]", "[UNITID]='" & Forms![frm_BCCInspectInput]![UNITID] & "'")) And Not IsNull([COLOR=red]DLookup[/color]("[date]=#" & Format(Forms![frm_BCCInspectInput]![date], "yyyy-mm-dd") & "#")) Then
If Me.NewRecord Then
Select Case MsgBox("This is a duplicate record! Do you wish to DELETE this record?", vbYesNo + vbQuestion, "THIS IS A DUPLICATE RECORD")
Case vbYes: 'Delete the record
        Me.UNITID.SetFocus
        DoCmd.RunCommand acCmdUndo
        Me.tbProperSave.Value = "No"
    MsgBox "The duplicate has been deleted!"
Case vbNo: 'Do NOT delete the record
    DoCmd.CancelEvent
    MsgBox "You are continuing input of a duplicate record!"
Case Else: 'Trap any other errors that could occur
    'Do Nothing
End Select
End If
End If
End Sub

Fails at DLookup in red.
And I end up with the following error message:

Compile error
Argument not optional

 

[Golom]

You replaced EVERYTHING in the DLookup. I think PHV meant that you should replace only the third parameter.

 

[PHV]

I suggested you to modify the criteria of your DLookUp, not the whole !
If Not IsNull(DLookup("[UNITID]", "[tbl_BCCInspection]", "[UNITID]='" & Forms![frm_BCCInspectInput]![UNITID] & "'")) And Not IsNull(DLookup("[date]", "[tbl_BCCInspection]", [!]"[date]=#" & Format(Forms![frm_BCCInspectInput]![date], "yyyy-mm-dd") & "#"[/!])) Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MKH2001]

Thank you worked like a dream

 

[MKH2001]

Hmmm seems to have developed another error here =(

The trap, rather then stopping duplicates of units entered with the same UnitID and Date.... now seems to be stopping the user from entering units with seemingly unrelated dates.

I cannot see a pattern on how it is doing this.... but it is true that if I enter (for example) 28/02/06 (for 28th February) the trap is stopping the entry even though there is no entry for the unit for this date.

It does this for anything from 13th February.

However if it is a date between 01/02 - 12/02/2006 it is not causing a problem.

Any idea why this would be and how to rectify it?

 

[MKH2001]

Just figured..... appears that my command is not looking at whether the UnitID AND Date is the same.
It seems to be checking the Unit ID and then checking the Date seperate.

So it is checking if there is any unitid the same or any date the same...and then returning a duplicate, rather then checking if their is a matching UNITID and Date the same before returning the duplicate. =(

 

[MKH2001]

Noone know how I handle this?

Think I need to employ multiple criteria in a single dlookup however no matter what syntax I try I am either getting a type mismatch or too many arguments error

 

[lespaul]

you should just need to add some parens:

If Not (Dlookup#1 AND Dlookup#2)

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[MKH2001]

Still having issues with this =(

My current code is

Private Sub Date_AfterUpdate()
If Not IsNull(DLookUp("UNITID","tbl_AnnualServInsp","UNITID = '" & Forms!frm_AVServInspect!UNITID & "'" And "date = #" & Format(Forms!frm_AVServInspect!date,"dd-mm-yyyy") & "#")) Then
Select Case MsgBox("This is a duplicate record! Do you wish to DELETE this record?", vbYesNo + vbQuestion, "THIS IS A DUPLICATE RECORD")
Case vbYes: 'Delete the record
        Me.UNITID.SetFocus
        DoCmd.RunCommand acCmdUndo
        Me.tbProperSave.Value = "No"
    MsgBox "The duplicate has been deleted!"
Case vbNo: 'Do NOT delete the record
    DoCmd.CancelEvent
    MsgBox "You are continuing input of a duplicate record!"
Case Else: 'Trap any other errors that could occur
    'Do Nothing
End Select
End If
End Sub

When attempting to rectify I am fluctuating between my current type mismatch error to the usual syntax error to error 2001 You have canceled your previous action.

Any ideas?

 

[MKH2001]

Still stuck on this query if there are any other thoughts out there.

 

[lespaul]

Is date the name of the field in the table and of the control on the form? I would highly suggest changing this if possible since date is a keyword in both Access and VBA.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[MKH2001]

Yes date is the name of the table field and the form control.... but I dont know if it is possible to change that at this point.

Obviously the control on the form would be easy enough.... however the field itself (and this is only one table which i need some sort of duplication <where date and UNITID is the same then it is the same record and therefore a duplicate> there is about 5 others each with alot of import scripting and reporting scripting already done which would need rewriting if this had to be altered for the field itself.

However if that IS what the problem is then I would have to do that I guess.

I guess I could rig up a smaller test table to check it out though.

Ta for the suggestion.

 

[lespaul]

the other solution is when used in a query surround the offending word with brakets, but I don't know if that works in VBA or not.

Private Sub Date_AfterUpdate()
If Not IsNull(DLookUp("UNITID","tbl_AnnualServInsp","UNITID = '" & Forms!frm_AVServInspect!UNITID & "'" And "[b][[/b]date[b]][/b] = #" & Format(Forms!frm_AVServInspect!date,"dd-mm-yyyy") & "#")) Then
Select Case MsgBox("This is a duplicate record! Do you wish to DELETE this record?", vbYesNo + vbQuestion, "THIS IS A DUPLICATE RECORD")
Case vbYes: 'Delete the record
        Me.UNITID.SetFocus
        DoCmd.RunCommand acCmdUndo
        Me.tbProperSave.Value = "No"
    MsgBox "The duplicate has been deleted!"
Case vbNo: 'Do NOT delete the record
    DoCmd.CancelEvent
    MsgBox "You are continuing input of a duplicate record!"
Case Else: 'Trap any other errors that could occur
    'Do Nothing
End Select
End If
End Sub

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[PHV]

The And wasn't inside the quotes ...
If Not IsNull(DLookUp("UNITID","tbl_AnnualServInsp","UNITID = '" & Forms!frm_AVServInspect!UNITID & "'[!] And [date][/!] = #" & Format(Forms!frm_AVServInspect!date,"dd-mm-yyyy") & "#")) Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PHV]

Furthermore, to avoid ambiguity on date:
If Not IsNull(DLookUp("UNITID","tbl_AnnualServInsp","UNITID = '" & Forms!frm_AVServInspect!UNITID & "' And [date] = #" & Format(Forms!frm_AVServInspect!date,"[!]yyyy[/!]-mm-[!]dd[/!]") & "#")) Then

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[MKH2001]

Seems to be working.... and I thought I had tried all the variations possible. Including brackets around things or not. And would either get one or the other working or else get an error.

Myst have had a sneakly lil quatation hiding in there somewhere.

This works like a charm.

Looks like it is going to be a good christmas after all =)

Thanks guys