How do I Append a table without duplicating records? 2

[SALEEYOUNG]

I have a form which calls from a combo box (thats another question) certain information. The field in the underlying table of the combo box has no duplicates. Now the form's combo box is not restricted to list. So, if the info does not show up from the combo box one can enter new information. However, I need this new info to append to the table. The problem is that some records can have the same combo box info so I need to only add records to the underlying table if it is not a duplicate of whats already there. Otherwords, I need to write a query, macro, or code to compare the new info with the already in place info and to add a new record only if it does not exist in the underlying table. Excuse me if I ramble, but I have been working on this most of the day and I am very tired. I know this must be simple, but my brain is fried! Any help would be greatly appreciated. Thank you.

 

[PHV]

In the table design view create an index not allowing duplicates.
You may also play with the DLookUp function to compare the new info with the already in place.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SALEEYOUNG]

Thank you very much. You know what my major problem is. I know how to write code to find fields that are equal but for the life of me I can not figure out how to write code to find fields that are not equal!

 

[PHV]

I know how to write code to find fields that are equal
So, if you find equals field you don't insert the new info.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheAceMan1]

How are ya SALEEYOUNG . . . . .

Try this in the BeforeUpdate event of the combobox ([blue]you![/blue] substitute proper names in [purple]purple[/purple]):

[blue]   Dim Criteria As String, DL As String
   Dim Msg As String, Style As Integer, Title As String
   
   DL = vbNewLine & vbNewLine
   Criteria = "[[purple][b]FieldName[/b][/purple]] ='" & Me![purple][b]ComboboxName[/b][/purple].Column(0) & "'"
   
   If Not IsNull(DLookup("[[purple][b]PrimaryKeyName[/b][/purple]]", "T[purple][b]ableName[/b][/purple]", Criteria)) Then
      Msg = "'" & Me![purple][b]ComboboxName[/b][/purple].Column(0) & "' already exists!" & DL & _
            "Duplicates Not Allowed!" & DL & _
            "Remove or try again with different data . . ."
      Style = vbctitical + vbOKOnly
      Title = "Duplicate Data Error! . . ."
      MsgBox Msg, Style, Title
      Cancel = True
   End If[/blue]

Two things:
[ol][li]If the data in the combobox is numeric, remove the single quotations.[/li]
[li]You may have to play with the column number [blue]Column([purple]?[/purple])[/blue] to get it right . . . .[/li][/ol]

See Ya! . . . . . .

 

[SALEEYOUNG]

Thanks PHV for your response. Believe it or not I did figure out about if there are equal duplicates then, etc. However, I use to program in a different Db language and it seemed to be that the methods for writing code is in reverse as this program. I'm not saying it is better just something I was use to. OK, so now I just have to reverse my Thinking!!!! May take awhile, I programmed in that language for almost ten years.Thanks again.