Warn of table duplicates 1

[osi2301]

I have a scheduling database and would like to develop a pop-up warning whenever there is an attempt to schedule two peole to work the same shift.

I have a table with a field called ROSTERID, SCHEDULEID and a field called PATTERN. There can be many SCHEDULEIDs for each ROSTERID. There is only one SCHEDULEID for each PATTERN.

I want the database to locate and warn of any duplicates in PATTERN that is linked to a specific ROSTERID.

I use the dlookup function to find and and warn of duplicate PATTERNS but it warns of ALL duplicate PATTERNS regardless of the ROSTERID. How can I make it specific to only hte ROSTERID?

 

[TheAceMan1]

How are ya osi2301 . . . . .

What are the [blue]data types[/blue] of the fields (string, numeric . . .)?

See Ya! . . . . . .

 

[osi2301]

Sorry..Should have included that info.

RosterID is a number field(Primary key). ScheduleID is a number field and the pattern field is a test field.

 

[osi2301]

Let me try that again

RosterID is a number field(Primary key). ScheduleID is a number field and the pattern field is a TEXT field.

 

[ZmrAbdulla]

You could use two more than one criteria in the DLookUp.

________________________________________________________________________
Zameer Abdulla
Visit Me
No two children are alike - particularly if one is yours and the other isn't.

 

[osi2301]

Thats the problem I'm having. I know you can use two criteria in dlookup but I'm not sure how. Could you point me in the initial direction. Sorry for being so dumb?

 

[ZmrAbdulla]

Have a look at this link

http://www.mvps.org/access/general/gen0018.htm

________________________________________________________________________
Zameer Abdulla
Visit Me
No two children are alike - particularly if one is yours and the other isn't.

 

[TheAceMan1]

osi2301 . . . . .

Try this:

[blue]   Dim Criteria as string

   Criteria = "[ROSTERID]=" & Me!ROSTERID & " AND " & _
              "[SCHEDULEID]=" & Me!SCHEDULEID & " AND " & _
              "[PATTERN] = '" & Me!PATTERN & "'"
   
   If Not IsNull(DLookUp("[PATTERN]", "[purple][b]TableName[/b][/purple]", Criteria)) Then
      [green]'Your Message Here![/green]
   End If[/blue]

See Ya! . . . . . .

 

[osi2301]

Sorry that didn't work. The criteria is a string as well as integer. would that be part of the problem. In other words, the rosterID and scheduleID are both number fields but the pattern is a text field. I notice that you've set the criteria as a sring?

 

[TheAceMan1]

[blue]I want the database to locate and warn of any duplicates in PATTERN that is linked to a specific ROSTERID.[/blue]

I believe I misinterpreted. Change to:

[blue]   Dim Criteria as string

   Criteria = "[ROSTERID]=" & Me!ROSTERID & " AND " & _
              "[PATTERN] = '" & Me!PATTERN & "'"
   
   If Not IsNull(DLookUp("[PATTERN]", "[purple][b]TableName[/b][/purple]", Criteria)) Then
      MsgBox "Duplicate Pattern Found!"
   End If[/blue]

[ol][li]Did you substitute the proper [blue]table name?[/blue][/li]
[li]Are all the fields in the same table?[/li]
[li]Are the [blue]Control Names[/blue] on the form the same as the [blue]Field Names?[/blue] . . . Check the [purple]Name Property[/purple] of each control to be sure!)[/li][/ol]
. . . and yes . . . if you check VBA help on DLookUp, the criteria arguement is a string! It is in fact the same as the Where Clause of an SQL (without the where).

See Ya! . . . . . .

 

[osi2301]

The blockhead has struck again. It was my mistake. I had been working with different methods for a week and it was in the NAME PROPERTY. It works now.