Append error statement workaround

[Roncc]

I would like to use a combobox selection as the parameter in a query. I have table called immigrantstbl which has a primary key field called aliennumber and a second field called casenumber. Members of the same family will each have a unique aliennumber but they will all have the same casenumber. I want to use a select query to select a specific case number and when I create a combobox on the field casenumber I find, not surprisingly, that there are multiple duplicates of the same number. To remove this duplication I decided to append the immigrantstbl to a new table tempimmigrantstbl, where the casenumber field is a primary key but, of course, I get an error message saying that not all the records could append because of the violation of the primary key constraints. I can say "Yes" (manually) to the "do I want to run the append query anyway" question, but I would like to be able to include an automatic "Yes" in the code. Any suggestions?

Thanks for all the help.

Ron C

[June7]

If you are manually running the append action from a query object in the NavigationPane, cannot prevent the popup warning. Use code instead (macro or VBA).

DoCmd.SetWarnings False
...
DoCmd.SetWarnings True

or

CurrentDb.Execute ...

If you have code, then post it for analysis.


Your object names would be easier to read if you use CamelCase, ex: AlienNumber, TempImmigrantsTBL. Most developers put the object type in prefix instead of suffix: tblTempImmigrants.

[Roncc]

Thanks for the information. I appreciate it, also for the guidance on nomenclature. Question: does code ignore case?

Ron C

[June7]

Access and VBA are not case sensitive by default.

So, in brief, yes.

[Micron]

I want to use a select query

To remove this duplication I decided to append...

Is this not 2 different situations? It reads like you made more work for yourself by dealing with the first problem in such a fashion. If the combo rowsource is based on a query, and that query returns duplicate values, then I think the right approach is to prevent that by modifying the combo query. Perhaps all you need is the SELECT DISTINCT predicate in your query properties (in design view, right click on query background if property sheet isn't visible). Try Distinct Rows or Distinct Values (you cannot have both) and see if either gives you a list of single choices, or post your combo rowsource sql with some info about what it's based on.

[June7]

Good points Micron.

Roncc, are you doing this with the goal of creating a table where the casenumber will be unique value and so then immigrantstbl will be a related dependent table? Do you want to generate an autonumber unique ID in the casenumber table that will then be saved into immigrantstbl as a foreign key?

Otherwise, as Micron states, the casenumber table is not necessary.

[Roncc]

Thank you.

Ron C