Test if record exists

[Badswell]

From a form, I have a button that does the following code to import a range from Excel into a specific Access table. I'm trying to figure out how to test to see if the record already exists in the table as to prevent key violation errors from occuring. Any ideas? I'm not sure where to start.

Private Sub imp_supp_info_Click()
If (TEST FOR RECORD EXISTANCE=TRUE) Then

MsgBox ("Supplier profile already exists.")​

Else

If txt_file_loc.Value = "" Then​

MsgBox ("No file was selected. Import canceled.")​

ElseIf txt_file_loc.Value <> "" Then​

DoCmd.TransferSpreadsheet, acSpreadsheetTypeExcel9, "ps_supplier_info", txt_file_loc.Value, True, "access_company_info"​

MsgBox ("Import of Supplier Profile Complete")​

Else​

MsgBox ("No file was selected. Import canceled.")​

End If​

End If
End Sub

 

[Mile-O]

Lookup up the DLookup() or DCount() in the Help Files

 

[Badswell]

I'm thinking this may be the wrong approach to my goal. As you know when a duplicate record is attempted to be imported, Access brings up its "Unable to Append" message, prompting the user to select Yes/No/Help in regards to proceeding. Selecting No brings up Run-Time Error 2501 - Transferspreadsheet action was canceled.

Is there a way to automatically make the the "Unable to Append" prompt select "No" and then after that, hide the Error #2501 message?

Thanks..

 

[Mile-O]

DoCmd.SetWarnings False
DoCmd.TransferSpreadsheet [i]....details...[/i]
DoCmd.SetWarnings True

 

[Badswell]

I've played with these warnings before, but what I run into is now there is no test on whether or not the import was sucessfull. I'd still like to put up a msgbox about the error, just not the default ones which would allow the user to get in and break things.

 

[Mile-O]

there is no test on whether or not the import was sucessfull. I'd still like to put up a msgbox about the error, just not the default ones which would allow the user to get in and break things.

Try this in a standalone module:

Public Function Transfer() As Boolean
    On Error Goto Err_Transfer

    DoCmd.TransferSpreadsheet ....details...

    Transfer = True

Exit_Transfer:
    Exit Function

Err_Transfer:
    Transfer = False
    Resume Exit_Transfer

End Function

And then, on the click event of your button:

If Transfer Then
    MsgBox "Transfer was successful.", vbExclamation
Else
    MsgBox "Transfer was not completeley successful.", vbExclamation
End If

 

[Badswell]

Thanks for the help on this Miles..I appreciate the patience..

That code does most of what I'm trying to accomplish. The run-time error message about the transferspread action being canceled is now hidden.

However, the message on "Microsoft Access was unable to append all the data to the table (3 records deleted, 0 were lost due to key violations)" still comes up prompting the user to choose Yes/No/Help.

Any idea on how I could always have this answered as No? If I could get this prompt answered No w/out the user having to even see this menu, I'd be set.

 

[Mile-O]

Public Function Transfer() As Boolean
    On Error Goto Err_Transfer

    DoCmd.SetWarnings False
    DoCmd.TransferSpreadsheet ....details...
    DoCmd.SetWarnings True

    Transfer = True

Exit_Transfer:
    Exit Function

Err_Transfer:
    Transfer = False
    Resume Exit_Transfer

End Function

Slight change.

 

[Badswell]

Almost there...although by just hiding the warnings around the transfer spreadsheet command, the import process still tries to import bad data (ie alpha characters in a numeric field) and now the Transfer boolean always is set to True, so the "successful" message is always returned. Even when there is a key violation or bad data types.

By turning the warnings off, it doesn't look like the error is being detected at all now. With the warnings turned on and selecting "no do not continue," then the "not successful" message will correctly show.

arg..

Module..

Global rfp_location As String
Global tbl_name As String
Global excel_range As String

Public Function Transfer() As Boolean
    On Error GoTo Err_Transfer

    DoCmd.SetWarnings False
    DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, tbl_name, rfp_location, True, excel_range
    DoCmd.SetWarnings True

    Transfer = True

Exit_Transfer:
    Exit Function

Err_Transfer:
    Transfer = False
    Resume Exit_Transfer

End Function

Click Event..

Private Sub imp_supp_info_Click()
    If txt_file_loc.Value = "" Then
        MsgBox ("No file was selected.  Import canceled.")
    ElseIf txt_file_loc.Value <> "" Then
        tbl_name = "ps_supplier_info"
        rfp_location = txt_file_loc.Value
        excel_range = "access_company_info"
        
        If Transfer = True Then
            MsgBox "Transfer was successful.", vbExclamation
        Else
            MsgBox "Transfer was not completeley successful.", vbExclamation
        End If
    Else
        MsgBox ("No file was selected.  Import canceled.")
    End If
End Sub