MS Access - SQL Append Query Using Multiple Data Sources

Question

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 & ")"

You can just use frmForm!txtBox.Text instead of the string. — braX
– braX, Commented Jan 5, 2018 at 18:27
Yes but how? I can't put it in the same SELECT statement as above because the FROM of that statement refers to a table. Can I build a separate SELECT statement for the forms data and nest it within the above append query? — OneLineAtTheTime
– OneLineAtTheTime, Commented Jan 5, 2018 at 18:39
I would like to include in the above query and these data points ... how? Within the IN clause? Certainly doable! Use your own answer in your last question. — Parfait
– Parfait, Commented Jan 5, 2018 at 19:05

June7 · Accepted Answer · 2018-01-05 19:12:29Z

2

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 & ")"

answered Jan 5, 2018 at 19:12

June7

21.7k8 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

OneLineAtTheTime Over a year ago

Thanks June7. So I can reference this textboxes within the SELECT statements even though they are not sourced from the table indicated in the FROM clause? This seems to be what you suggest. It's a conceptual question, I just didn't know this was possible!

June7 Over a year ago

Yes, this is same as a calculated field in a query object. Constructing an SQL string with code allows concatenating whatever you want - literal text or variables. Every record will have the same value provided by the textbox.

OneLineAtTheTime Over a year ago

Works perfectly. Thanks man. I'll post the complete code once I'm done

BIBD · Accepted Answer · 2018-01-05 19:14:16Z

1

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.

answered Jan 5, 2018 at 19:14

BIBD

15.5k26 gold badges89 silver badges142 bronze badges

2 Comments

OneLineAtTheTime Over a year ago

Yes but the FROM clause would be referencing to a table and the form fields I am trying to put in do not come from that table but from a form. Would that not throw off the select statement?

BIBD Over a year ago

Shouldn't... you should get the same value (whatever is in the field) for every row in the table "test".

Collectives™ on Stack Overflow

MS Access - SQL Append Query Using Multiple Data Sources

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related