CODEWITHSUNDEEP
sqlms-accessvba
OneLineAtTheTime
OneLineAtTheTime

Reputation: 43

MsgBox and SQL 'Insert Into' query

In a MS-Access DB I have the below SQL query fired from a VBA module that appends a record given the conditions stated in the WHERE clause. My question is, can I insert a MsgBox that informs the user if the record already exists or weather is a new record? Currently the query will append a record if the value for the field called 'Counterparty ID' is already in the destination table, and won't if not. While the mechanics are correct, I would like to notify the user whether he/she has tried to insert a duplicate record. Is this somehow possible? I assume it would have to happen within the query itself.

"INSERT INTO Netting_Determinations_List ([Counterparty ID], [Counterparty Name], [Counterparty Type], [Counterparty Subtype],[DTCC_AVOX_Registered_LEI_CICI], " & _
        "[Data Point 1],[Data Point 2],[Data Point 3],[Data Point 4],[Data Point 5], [Matrix Legal Form], [Matrix Governing/Authorizing Power], " & _
        "[OnBoardings Color Determination], [Matrix Clarification],  [Audit_Append_User], [Audit_Append_Date], [Audit_Append_Time]) " & _
        "SELECT Repository_Redux.[Counterparty ID], Repository_Redux.[Counterparty Name], Repository_Redux.[Counterparty Type], Repository_Redux.[Counterparty Subtype], Repository_Redux.[DTCC_AVOX_Registered_LEI_CICI], " & _
        "[Forms]![Frm_Master_Form]![Txt_Input_1] AS [Data Point 1], [Forms]![Frm_Master_Form]![Txt_Input_2] AS [Data Point 2], " & _
        "[Forms]![Frm_Master_Form]![Txt_Input_3] AS [Data Point 3], [Forms]![Frm_Master_Form]![Txt_Input_4] AS [Data Point 4], " & _
        "[Forms]![Frm_Master_Form]![Txt_Input_5] AS [Data Point 5], [Forms]![Frm_Master_Form]![Cbo_LegalForm] AS [Matrix Legal Form], " & _
        "[Forms]![Frm_Master_Form]![Cbo_Status] AS [Matrix Governing/Authorizing Power], [Forms]![Frm_Master_Form]![Txt_Color] AS [OnBoardings Color Determination], " & _
        "[Forms]![Frm_Master_Form]![Txt_Matrix_Clarification] AS [Matrix Clarification], '" & strUsername & "' AS [Audit_Append_User], " & _
        "'" & StrDate & "' AS [Audit_Append_Date], '" & StrTime & "' AS [Audit_Append_Time] FROM Repository_Redux " & _
        "WHERE Repository_Redux.[Counterparty ID] IN (" & strCriteria & ") AND NOT EXISTS (SELECT [Counterparty ID] FROM Netting_Determinations_List WHERE [Counterparty ID] = (" & strCriteria & "))"

Upvotes: 0

Views: 580

Answers (2)

SunKnight0
SunKnight0

Reputation: 3351

This is how I would go about it:

If Nz(DLookup("Counterparty ID","Netting_Determinations_List","[Counterparty ID]=" & strCriteria),0)=0 then
--Record does not exists, insert
else
--record exists notify user
end if

Don't bother trying to find out if a record was inserted. Much easier to check for the record first and take action accordingly.

Upvotes: 2

Donald Varona
Donald Varona

Reputation: 29

MS Access is notoriously simplistic in how it handles records. For example, you can call an insert from VBA (Database.Execute "insert...") and it might silently fail to insert; you have to go verify yourself that what you wanted written was actually inserted. (The DoSQL action will pop up dialogs, however.)

Since you are using VBA, I'd recommend that you first do the test in SQL yourself. Either create a query object or execute the query text that selects whether the record exists (Recordset.RecordCount > 0), and then pop up a dialog informing the user about it. You can't write this into the query text.

Note that you might have to do a .MoveLast and .MoveFirst to get the actual record count, depending on the recordset type.

Upvotes: 0

Related Questions