Ledger No. duplication

[Guest]

Because of the need of one Report I have set the Indexed property to 'Yes
(Duplicates allowed)' for ' Ledger No.' in the Table design.

This however conflicts with my Form for creating new ledger accounts - where
I need to avoid duplication of ledger No.s

Is there any way that I can enforce 'No duplication', in my Form when
creating a new Ledger account.

 

[Douglas J Steele]

If you've already got duplicates in the field, then you cannot create a
unique index on the field. However, if the field's supposed to be unique,
you should try and remove any duplicates so that you can use a unique index.

If that's not possible, you could put code in the form's BeforeInsert event
to check whether or not the Ledger No.'s already been used. It can be as
simple as:

If DCount("[Ledger No]", "MyTable", "[Ledger No] = " & Me.txtLedgerNo) >
0 Then
MsgBox "That's a Duplicate Ledger Number"
Cancel = True
End If

(replace [Ledger No] with the actual field name, MyTable with the actual
table name, and Me.txtLedgerNo with the name of the control on the form that
holds the field.)

 

[John Vinson]

Because of the need of one Report I have set the Indexed property to 'Yes
(Duplicates allowed)'

Report layout should NOT drive table design! If the Ledger No. needs
to be unique within the table (as I would guess that it does), the
solution is to change the way the report is generated, not mess up
your table structure.

What is there about the report which requires two records in the table
with the same ledger number?

John W. Vinson[MVP]

 

[Guest]

Thank you Doug - it is working fine for me, and has resolved a very serious
flaw in my system.
Again thanks
--
Glynn

If you've already got duplicates in the field, then you cannot create a
unique index on the field. However, if the field's supposed to be unique,
you should try and remove any duplicates so that you can use a unique index.

If that's not possible, you could put code in the form's BeforeInsert event
to check whether or not the Ledger No.'s already been used. It can be as
simple as:

If DCount("[Ledger No]", "MyTable", "[Ledger No] = " & Me.txtLedgerNo) >
0 Then
MsgBox "That's a Duplicate Ledger Number"
Cancel = True
End If

(replace [Ledger No] with the actual field name, MyTable with the actual
table name, and Me.txtLedgerNo with the name of the control on the form that
holds the field.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Because of the need of one Report I have set the Indexed property to 'Yes
(Duplicates allowed)' for ' Ledger No.' in the Table design.

This however conflicts with my Form for creating new ledger accounts - where
I need to avoid duplication of ledger No.s

Is there any way that I can enforce 'No duplication', in my Form when
creating a new Ledger account.

 

[Guest]

John - I have used 'DCount' as suggested by Doug Steele, and it is working
for me.

This has however been a salutory lesson - in form and report design.
Unfortunately my system (Trust Accounting) is driven by legislation where
legal perspectives over-rides accounting and/or system logic.

Thanks for your input.