Double entry warning

[marcellvries]

Hi there,

I'm designing a planning system in Access. Last week I (with help from you) created a query that looks for double entries (

http://www.tek-tips.com/viewthread.cfm?qid=1479557&page=1).

Now I would like to create something that when a user adds a new event, the above query is executed and in case a double entry is found (that means that the new event shows up in the query), a message pops-up/the text is changed to red/something else.

I really have no clue at all how to do this and where to start. I hope your expertise can help me once again to get a step further.

With kind regards,

Marcell

 

[TheAceMan1]

How are ya marcellvries . . .

The key here is being able to hold in memory or textboxes [blue]PlanningID[/blue] or [blue]WerknemerID[/blue] or both, of the added record. This will allow you to [blue]open the query with a recordset[/blue] and perform a [blue]findfirst[/blue] pinging against the ID's. If a matching record is found a duplicate exist and you present your msgbox. Here's the idea:

[blue]   Dim db As DAO.Database, rst As DAO.Recordset
   Dim SQL As String, Cri As String
   
   Set db = CurrentDb
   SQL = "SELECT TP.PlanningID, " & _
                "TP.WerknemerID, " & _
                "TP.Bweergavedatum, " & _
                "TP.Edatum, " & _
                "TP.Btijd, " & _
                "TP.Etijd, " & _
                "TP1.PlanningID, " & _
                "TP1.WerknemerID, " & _
                "TP.Bdatum+TP.Btijd AS VolDatum " & _
         "FROM tblPlanning AS TP, tblPlanning AS TP1 " & _
         "WHERE (((Nz((TP1.Bweergavedatum + TP1.Btijd >= TP.Edatum + TP.Etijd) Or " & _
                     "(TP1.Edatum + TP1.Etijd <= TP.Bdatum + TP.Btijd) Or " & _
                     "(TP.WerknemerID <> TP1.WerknemerID) Or " & _
                     "(TP.PlanningID = TP1.PlanningID), False)) = False)) " & _
         "ORDER BY TP.PlanningID, TP1.PlanningID;"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If Not rst.BOF Then
      Cri = "[PlanningID] = '" & [purple][B][I]VariableName[/I][/B][/purple] & "' AND " & _
            "[WerknemerID] = '" & [purple][B][I]VariableName[/I][/B][/purple] & "'"
      rst.FindFirst Cri
      
      If Not rst.NoMatch Then
         [b]MsgBox "Duplicate Found!"[/b]
      End If
   End If
   
   Set rst = Nothing
   Set db = Nothing[/blue]

[blue]Your Thoughts? . . .[/blue]

BTW: Welcome to [blue]Tek-Tips![/blue] Do have a look at one of the links at the bottom of my post. The links will help you [blue]ask better questions[/blue], get [blue]quick responses[/blue], [blue]better answers[/blue], and insite into [blue]etiquette[/blue] here in the forums. Again . . . Welcome to [blue]Tek-Tips![/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[marcellvries]

Hi TheAceMan1,

Thank you for your quick reply. I'm not sure if I understand everything correctly but this is my idea:

I think that I have to execute the above code in the BeforeInsert part of the form. Is that correct?

In the Load part of the form I have to declare the variables:

Dim varPlanningID as String
Dim varWerknemerID as String

Then at the top of your code I have to add:

varPlanningID = Me.txtPlanningID.Value
varWerknemerID = Me.txtWerknemerID.Value

Just to be sure: The query doesn't look for a duplicate PlanningID right? Because that will not happen since PLanningID is of the type autonumber.

Thank you in advance. I will read the FAQs in the bottom of your post immediately

With kind regards,

Marcell de Vries

 

[marcellvries]

One thing I forgot. Eventually it would be nice to see in advance who is available during a specific time. For example that when you enter the start and end time of a new event, people who are still free appear in green while people who are occupied appear in red.

I think this is still far away and my previous question remains, but maybe it's good to mention it anyway so that you know what I'm planning to create eventually.

Thank you in advance.

With kind regards,

marcellvries

 

[TheAceMan1]

marcellvries . . .

[blue]I think that I have to execute the above code in the BeforeInsert part of the form. Is that correct?[/blue]

No! Reason being focus is in transit and the currently saved record has lost focus. Hard to know what the ID's were without extra code. At present I'm recommending the [blue]forms AfterUpdate[/blue] event (record is saved and current record still has the focus).

Special Note: I'm assuming your query returns all duplicates, no matter how many! If this is not correct please advise . . .

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[marcellvries]

Hi TheAceMan1,

I thought about the matter and looked at the query that I already had. When a 'clashing' event occurs it appears in the existing query as follows:

tblPlanning.PlanningID tblPlanning.WerknemerID tblPlanning_1.PlanningID tblPlanning_1.WerknemerID
158 7 159 7
159 7 158 7


Therefore I think it should be enough to check on PlanningID alone. Is that correct?

I tried the following code but I receive an error 3079 on line rst.FindFirst Cri. It says that "The specified field PlanningID could refer to more than one table listed in the FROM clause of your SQL statement.":

Option Compare Database
Dim varPlanningID As String
Option Explicit

Private Sub Form_BeforeUpdate(Cancel As Integer)

varPlanningID = Me.PlanningID.Value

   Dim db As DAO.Database, rst As DAO.Recordset
   Dim SQL As String, Cri As String
   
   Set db = CurrentDb
   SQL = "SELECT tblPlanning.PlanningID, " & _
                "tblPlanning.WerknemerID, " & _
                "tblPlanning.Bweergavedatum, " & _
                "tblPlanning.Edatum, " & _
                "tblPlanning.Btijd, " & _
                "tblPlanning.Etijd, " & _
                "tblPlanning_1.PlanningID, " & _
                "tblPlanning_1.WerknemerID, " & _
                "tblPlanning.Bdatum+tblPlanning.Btijd AS VolDatum " & _
         "FROM tblPlanning, tblPlanning AS tblPlanning_1 " & _
         "WHERE (((Nz((tblPlanning_1.Bweergavedatum + tblPlanning_1.Btijd >= tblPlanning.Edatum + tblPlanning.Etijd) Or " & _
                     "(tblPlanning_1.Edatum + tblPlanning_1.Etijd <= tblPlanning.Bdatum + tblPlanning.Btijd) Or " & _
                     "(tblPlanning.WerknemerID <> tblPlanning_1.WerknemerID) Or " & _
                     "(tblPlanning.PlanningID = tblPlanning_1.PlanningID), False)) = False)) " & _
         "ORDER BY tblPlanning.PlanningID, tblPlanning_1.PlanningID;"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If Not rst.BOF Then
      Cri = "[PlanningID]= '" & varPlanningID & "'"
      rst.FindFirst Cri
      
      If Not rst.NoMatch Then
         MsgBox "Duplicate Found!"
      End If
   End If
   
   Set rst = Nothing
   Set db = Nothing
End Sub

Sorry for the multiple replies but as I keep trying I find out more and more everytime.

With kind regards,

Marcellvries

 

[marcellvries]

Hi,

Thank you very much for your reply.

Yes, the query returns all duplicates. In my previous post (which I was typing at the moment you sent your reply) you can see that in case of one clashing event, it appears two times in the query. So when there are two clashes, it appears four times in the query, like:

tblPlanning.PlanningID tblPlanning.WerknemerID Bweergavedatum Edatum Btijd Etijd tblPlanning_1.PlanningID tblPlanning_1.WerknemerID VolDatum
158 7 27-6-2008 28-6-2008 23:00 4:00 159 7 27-6-2008 23:00:00
159 7 27-6-2008 27-6-2008 20:00 23:15 158 7 27-6-2008 20:00:00
167 2 28-6-2008 29-6-2008 22:00 3:00 168 2 28-6-2008 22:00:00
168 2 28-6-2008 28-6-2008 20:00 22:15 167 2 28-6-2008 20:00:00

I will move the code to the form afterupdate event. However the error 3079 still remains and I don't know whatto change.

With kind regards,

Marcellvries

 

[marcellvries]

Hi TheAceMan1,

Finally I have something that works. I removed "tblPlanning_1.WerknemerID, " & _ from the SELECT statement, and I changed the Cri statement to Cri = "[PlanningID]=" & varPlanningID[/red] (since it is a numeric field).

The code now looks like this:

Private Sub Form_AfterUpdate()
varPlanningID = Me.PlanningID.Value

   Dim db As DAO.Database, rst As DAO.Recordset
   Dim SQL As String, Cri As String
   
   Set db = CurrentDb
   SQL = "SELECT tblPlanning.PlanningID, " & _
                "tblPlanning.WerknemerID, " & _
                "tblPlanning.Bweergavedatum, " & _
                "tblPlanning.Edatum, " & _
                "tblPlanning.Btijd, " & _
                "tblPlanning.Etijd, " & _
                "tblPlanning_1.WerknemerID, " & _
                "tblPlanning.Bdatum+tblPlanning.Btijd AS VolDatum " & _
         "FROM tblPlanning, tblPlanning AS tblPlanning_1 " & _
         "WHERE (((Nz((tblPlanning_1.Bweergavedatum + tblPlanning_1.Btijd >= tblPlanning.Edatum + tblPlanning.Etijd) Or " & _
                     "(tblPlanning_1.Edatum + tblPlanning_1.Etijd <= tblPlanning.Bdatum + tblPlanning.Btijd) Or " & _
                     "(tblPlanning.WerknemerID <> tblPlanning_1.WerknemerID) Or " & _
                     "(tblPlanning.PlanningID = tblPlanning_1.PlanningID), False)) = False)) " & _
         "ORDER BY tblPlanning.PlanningID, tblPlanning_1.PlanningID;"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If Not rst.BOF Then
      Cri = "[PlanningID]=" & varPlanningID
      rst.FindFirst (Cri)
      If Not rst.NoMatch Then
         MsgBox "Duplicate Found!"
      End If
   End If
   
   Set rst = Nothing
   Set db = Nothing
End Sub

I will try to adjust some things to make everything more user-friendly. If I come up with any problems I will let you know.

Thank you very much for your help so far!

With kind regards,

marcellvries