CODEWITHSUNDEEP
sqlvbams-access
OneLineAtTheTime
OneLineAtTheTime

Reputation: 43

MS Access - SQL Append Query Using Multiple Data Sources

I am firing up the below SQL Append Query and it works just fine. 

AppendSQL = "INSERT INTO Netting_Determinations_List ([Counterparty ID], " & _
                "[Counterparty Name], [Counterparty Type], [Counterparty Subtype]) " & _
            "SELECT Repository_Redux.[Counterparty ID], " & _
                "Repository_Redux.[Counterparty Name], " & _
                "Repository_Redux.[Counterparty Type], " & _
                "[Counterparty Subtype] " & _
            "FROM Repository_Redux " & _
            "WHERE Repository_Redux.[Counterparty ID] IN (" & strCriteria & ")"

The issue I'm facing is that I have other data points I would like to include in the above query and these data points are in text boxes in a form. Is it possible to run a whole SELECT command for those within the same query or should I run an update query after the above append query to get those values in?

EDIT - Here is the revised procedure implementing suggested solution:

Dim db As DAO.Database
Dim AppendQdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim AppendSQL As String

Set db = CurrentDb()
Set AppendQdf = db.QueryDefs("Qry_Append_Counterparty_Data")

For Each varItem In Me!Lst_CPList.ItemsSelected
   strCriteria = strCriteria & ",'" & Me!Lst_CPList.Column(0, varItem) & "'"
Next varItem

If Len(strCriteria) = 0 Then
   MsgBox "You did not select anything from the list." _
          , vbExclamation, "Nothing To Find!"
  Exit Sub
End If

strCriteria = Right(strCriteria, Len(strCriteria) - 1)

AppendSQL = "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]) " & _
        "SELECT Repository_Redux.[Counterparty ID], Repository_Redux.[Counterparty Name], Repository_Redux.[Counterparty Type], [Counterparty Subtype], [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 [Color], " & _
        "[Forms]![Frm_Master_Form]![Txt_Matrix_Clarification] AS [Matrix Clarification] FROM Repository_Redux " & _
        "WHERE Repository_Redux.[Counterparty ID] IN (" & strCriteria & ")"

Upvotes: 0

Views: 1086

Answers (2)

BIBD
BIBD

Reputation: 15384

You can put form fields into the query like this:

SELECT [forms]![form1].[text0] AS myfield 
from test;

You do require a table to select "from" even if you don't require anything from it, but in your case this should not be a problem.

Upvotes: 1

June7
June7

Reputation: 21370

Can concatenate reference to textbox.

AppendSQL = "INSERT INTO Netting_Determinations_List ([Counterparty ID], " & _
                "[Counterparty Name], [Counterparty Type], [Counterparty Subtype], " & _
                "[some field name]) " & _
            "SELECT Repository_Redux.[Counterparty ID], " & _
                "Repository_Redux.[Counterparty Name], " & _
                "Repository_Redux.[Counterparty Type], " & _
                "[Counterparty Subtype] " & _
                Me.textboxname & " AS F " & _
            "FROM Repository_Redux " & _
            "WHERE Repository_Redux.[Counterparty ID] IN (" & strCriteria & ")"

Upvotes: 2

Related Questions