Limiting the number of duplicates

[ArcherSteve]

Is there anyway to limit the number of duplicates in a table? For example, I have a column that is named "Line". I want to be able to limit a specific entry in this column to only 28 times. So that the same information (let's say "Saturday 9:30") can only be entered 28 times in that column. Once you try to exceed that number, a message will come up and stop you. I am using a form to enter data into this table if that helps at all. Please, I am still a newbie at MS Access and any help is more than greatly appreciated.

 

[pono1]

Steve,

One way to do this is to use the DCOUNT function and code. Carrying on with your example, make sure the Line field is indexed in your table. Then in the BeforeUpdate Event of your form, put code like the following to count the entries in your table and if your max is reached halt the save.

Private Sub Form_BeforeUpdate(Cancel As Integer)

    Dim MyCount As Long

'Don't check on the current rec if value hasn't changed.
    If Me.txtLine.value = Me.TxtLine.OldValue Then
        Exit Sub 'done...
    End If

'................................
'NOTE:
'DCount here assumes "Line" is a string.
'Formatting is different for date and numbers.
'................................

'Count the number of entries in the table
'matching the textbox entry on the form.
    MyCount = DCount("[Line]", "[TaBLeNAME]", "[Line]=" & "'" & me.txtLine & "'")
    'Line = Name of field in table
    'txtLine = name of textbox on form
    'TableName = table that feeds form.
    'Me = the form to which this code module is attached.
  
'No more than 28 allowed...
    If MyCount >= 28 Then

'Tell Access to hold it and not save this record.
    Cancel = True

'Tell user what's happening...
    MsgBox "Save cancelled. Too many of these things entered.", vbOKOnly, "Have a nice day"
    
    End If

End Sub

Regards,
Tim

 

[ArcherSteve]

wow, thank you so much.

 

[ArcherSteve]

error message

I keep on getting an error message on the

  If Me.txtLine.value = Me.TxtLine.OldValue Then

line of the code. Of course i changed the "me" and "txtLines"

 

[ArcherSteve]

ok well nothing i have tried works (sorry, still learning)

the table's field is set to Indexed: Yes (duplicates ok)

Name of field in table is "Line"
name of textbox on form is "Line"
table that feeds form is "Archer's Data"
the form to which this code module is attached is "Yo"

thanks again for your help

 

[pono1]

Steve,

The code, in more than one place, is looking for a textbox on your form named txtLine. Like here...

'Is the current value of the txtLine textbox the same
'as when this record was first loaded into the form?

	If me.txtLine.Value = me.txtLine.OldValue Then

'more code below...

But you wrote that your textbox is named Line. Therefore, either change the code to look for a textbox named Line or -- better --change the actual textbox's name to txtLine. The textbox name does not have to match the field's name to which it is connected.

Regards,
Tim

 

[ArcherSteve]

heh....makes sense....thanks again

 

[ArcherSteve]

i'm sorry but i still can't get it to work and access 2002 bible is really helping...

this is the line I am getting an error on

    If [Imput Archer's Data (Multiple Lines)].txtLine.Value = [Imput Archer's Data (Multiple Lines)].txtLine.OldValue Then

The error message says "Microsoft Access can't find the field "|" referred to in your expression."

I changed the txt box's name like you said in you previous response.

I know it's gotta be something simple that i just can't find.

Please help (again)

Thank you

 

[ArcherSteve]

I got it!!!! Thank you soooo much! All i did was change the name of the form to "Me" and bam! it worked!!! Thank you sooo much!