Solved Loop Crashing Access when updating record set (2 Viewers)

Isaac

Lifelong Learner
Local time
Yesterday, 19:00
Joined
Mar 14, 2017
Messages
4,339
Could you just use my method except substitute ClientName where I put ID ?
 

CharlesDavenport

New member
Local time
Today, 03:00
Joined
Dec 7, 2020
Messages
26
Could you just use my method except substitute ClientName where I put ID ?
That would then delete all rows with that ClientName including the non duplicates for other FormNumbers would it not? ie it would delete data which whilst a duplicate is not considered a duplicate for this purpose?

What I have to work out is a way of deleting duplicates only if the FormNumber, JobReference and ClientName match another record...
 

moke123

AWF VIP
Local time
Yesterday, 22:00
Joined
Jan 11, 2013
Messages
2,333
Why are there duplicates in the first place?
Seems to me it would be better to validate your data so they dont occur.
Code:
DCount("*", "YourTableName", "FormNumber = " & Me.FormNumber & " and JobReference = """ & Me.JobReference & """ and CompanyName = """ & Me.CompanyName & """")


Seems like a normalization issue.

Its simple enough to find the dupes (See attached)
 

Attachments

  • Dupes.accdb
    460 KB · Views: 20
Last edited:

CharlesDavenport

New member
Local time
Today, 03:00
Joined
Dec 7, 2020
Messages
26
Why are there duplicates in the first place?
Seems to me it would be better to validate your data so they dont occur.
Code:
DCount("*", "YourTableName", "FormNumber = " & Me.FormNumber & " and JobReference = """ & Me.JobReference & """ and CompanyName = """ & Me.CompanyName & """")


Seems like a normalization issue.

Its simple enough to find the dupes (See attached)
Thanks Moke 123, Ill look at this!

I cant find away to not have duplicates, basically a search is undertaken for a new client against an existing client list which returns a list of clients. These need to be saved and so are appended to a table and then vba code ads in the primary key for the new client (new client and existing client information is in two separate tables).

I therefore have a new table with the information in and as shown in table earlier in thread.

But there is then a way for the user to undertake a new search with new information as the project continues ie continually updating the search and this adds in the duplicates.... I therefore want to include in the VBA a duplicate check and either remove or a way to not add this information if there are duplicates.

Still havent got round to this as im putting it off and tackling other issues in the database which Im coming up against mainly automatic emails!
 

moke123

AWF VIP
Local time
Yesterday, 22:00
Joined
Jan 11, 2013
Messages
2,333
(new client and existing client information is in two separate tables).
Why do you have 2 tables? There should only be one table of clients whether they are new or not or even prospective clients.
The primary key for the client table should then be used as a foreign key in all other tables.

Normalized tables are the foundation of any database. Unless you have a good foundation anything else you try to do will be wrought with problems.

You should probably study up on DB normalization before doing anything else.

If you want to post a copy of your DB or a snapshot of your relationships I'm sure you'll get lots of help.
 

Users who are viewing this thread

Top Bottom