Append to a related table

0
votes

I have two related table, i want this error/message to appear when appending to my second table. "You cannot add or change a record because records is required in table ".

I already set the relationship to Enforce Referential Integrity, Cascade update Related Fields and Cascade Delete Related Records

Here is my code

Private Sub cmdImport_Click()

On Error GoTo ErrHandler

Dim ImportExcelFile As String


ImportExcelFile = SelectFile
If ImportExcelFile = "" Then Exit Sub

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Temp-Member", ImportExcelFile, True

DoCmd.OpenQuery "AppendtoMember"

MsgBox "Import complete", vbInformation, "Import"

DoCmd.OpenQuery "DeleteTemp-Member"

ExitSubError:
    Exit Sub

ErrHandler:
MsgBox Err.Description & Err.Number, vbCritical, "Error"

End Sub
2
ms-accessappend
Access should already generate that message if user tries to enter record in second table without a parent record. - June7
Thank you, there is a message when i am manually entering the data. But sometimes i have external data, when i am trying to import/append, there is no message. - Edsel
What method are you using to import? - June7
I'm using Do.Cmd TransferSpreadsheet to import in temporary table then append to my table - Edsel
Okay, what method to append data? Edit question to post your code. - June7

2 Answers

0
votes

To customize message when append action fails, use Execute method and error handler. If any record fails, none will insert. 

CurrentDb.Execute "AppendtoMember", dbFailOnError
0
votes

I solved it, i put a look up value field to "MemberID" which is my second table(my primary key to first table and foreign key to second table)and set "Limit to List" to Yes

I also put your suggestion to my code to use-CurrentDb.Execute "AppendtoMember", dbFailOnError.

Thank you