I could use some help with a dcount criteria inside an Access Macro which I'm trying to use for a recordcount. I want a command to run if recordcount is greater than zero.
I have a split database deployed on a network. The Back End contains all of my tables and sits on the server, and there is a Front End available for my users to either user on the server or save locally. All queries/forms are set with admin permission, so whoever uses the database should be able to write to the tables and update the data, so I don't think security is my problem.
Users run a macro from a form that imports a new client portfolio from a CSV file into a "temp" table. The macro will eventually append the temp folder onto the "AllPortfolios" table. These two steps work great.
My problem is the intermediate step (lets call it "DeleteDuplicates"). I have a query that matches the Temp table to the AllPortfolios table on 3 criteria (using type 1 relationships). It then is supposed to delete all records from AllPortfolios that match on all 3 criteria. The intent is to prevent my users from accidentally inputing the same records repeatedly (a major problem in all previous versions of this db).
Several different scenarios with this process have run into snags. It worked great before I split the database. Now however it doesn't work as flawlessly. In the latest iteration it works well as long as there are some duplicates, but when there are no duplicates it fails.
Latest Plan:
-Select query called "FindDuplicates" matching the two tables on 3 fields, joined relationships returns * fields from the AllPortfolios table.
-Macro to run the whole import/scrub/append process
--1) SetWarnings Turns off warnings
--2) OpenQuery Delete query clears out the old TempPort table
--3) TransferText Imports the CSV file using import spec
--4) OpenQuery FindDuplicates table
--5) RunCommand.SelectAll (here I'm hoping for a criteria to only run when dcount >0 or something) It works if I don't use the criteria AND I have some duplicates. Crashes if no duplicates in AllPortfolios
--6) RunCommand.DeleteRecord (here I want to delete the selected records if dcount>0)
--7) Close FindDuplicates query
--8) OpenQuery - append TempPort this works
--9) OpenQuery delete query clears out new TempPort
--10) SetWarnings back on
Thanks in advance if anyone can help me...
Old Iterations:
- With no condition, the macro works great as long as there is some sort of duplicate record.
- First, I tried using a Delete Query built just like my new select query. This worked great until I split the database. It was a simple, fast solution that worked whether or not there were results to the query. Now, however that solution gives me a "query must be updatable" and won't work.
- Then I built a select query that did the same thing, with another query set up to delete the other query - also gave me the same error message about query must be updateable.
I have a split database deployed on a network. The Back End contains all of my tables and sits on the server, and there is a Front End available for my users to either user on the server or save locally. All queries/forms are set with admin permission, so whoever uses the database should be able to write to the tables and update the data, so I don't think security is my problem.
Users run a macro from a form that imports a new client portfolio from a CSV file into a "temp" table. The macro will eventually append the temp folder onto the "AllPortfolios" table. These two steps work great.
My problem is the intermediate step (lets call it "DeleteDuplicates"). I have a query that matches the Temp table to the AllPortfolios table on 3 criteria (using type 1 relationships). It then is supposed to delete all records from AllPortfolios that match on all 3 criteria. The intent is to prevent my users from accidentally inputing the same records repeatedly (a major problem in all previous versions of this db).
Several different scenarios with this process have run into snags. It worked great before I split the database. Now however it doesn't work as flawlessly. In the latest iteration it works well as long as there are some duplicates, but when there are no duplicates it fails.
Latest Plan:
-Select query called "FindDuplicates" matching the two tables on 3 fields, joined relationships returns * fields from the AllPortfolios table.
-Macro to run the whole import/scrub/append process
--1) SetWarnings Turns off warnings
--2) OpenQuery Delete query clears out the old TempPort table
--3) TransferText Imports the CSV file using import spec
--4) OpenQuery FindDuplicates table
--5) RunCommand.SelectAll (here I'm hoping for a criteria to only run when dcount >0 or something) It works if I don't use the criteria AND I have some duplicates. Crashes if no duplicates in AllPortfolios
--6) RunCommand.DeleteRecord (here I want to delete the selected records if dcount>0)
--7) Close FindDuplicates query
--8) OpenQuery - append TempPort this works
--9) OpenQuery delete query clears out new TempPort
--10) SetWarnings back on
Thanks in advance if anyone can help me...
Old Iterations:
- With no condition, the macro works great as long as there is some sort of duplicate record.
- First, I tried using a Delete Query built just like my new select query. This worked great until I split the database. It was a simple, fast solution that worked whether or not there were results to the query. Now, however that solution gives me a "query must be updatable" and won't work.
- Then I built a select query that did the same thing, with another query set up to delete the other query - also gave me the same error message about query must be updateable.
