CODEWITHSUNDEEP
ms-accessvba
SaladSnake
SaladSnake

Reputation: 167

Adding values into a table with a query

So all I'd like to do is add two queries into a table called tmpGroupSearch. I'm not sure how to do it wit out creating a record set and looping through every record and individually adding them in. I'm thinking there is a much easier way to do this I just can't find the proper structure.

Here are my queries:

"SELECT tblGroupHeader.GroupName" _
                           & ", tblGroupHeader.GroupNum" _
                           & ", tblAlsoKnown.AlsoKnown" _
                    & " FROM tblGroupHeader" _
                       & " LEFT JOIN tblAlsoKnown ON tblGroupHeader.GroupNum = tblAlsoKnown.GroupNum" _
                    & " WHERE tblGroupHeader.GroupName like '" & txtgroupSearch.Value & "*'" _
                          & " OR tblGroupHeader.GroupNum like '" & txtgroupSearch.Value & "*';"


"Select * FROM tblActionLog WHERE AlsoKnown LIKE '" & txtgroupSearch.Value & "*';"

Upvotes: 0

Views: 56

Answers (3)

HansUp
HansUp

Reputation: 97101

Test this parameter query in the Access query designer and adjust as needed so that it returns the row set you expect for the pSearchText parameter value you supply.

PARAMETERS pSearchText Text ( 255 );
SELECT gh.GroupName, gh.GroupNum, ak.AlsoKnown
FROM
    tblGroupHeader AS gh
    LEFT JOIN tblAlsoKnown AS ak
    ON gh.GroupNum = ak.GroupNum
WHERE
       gh.GroupName Like "'" & pSearchText & "*'"
    OR gh.GroupNum Like "'" & pSearchText & "*'"
UNION ALL
SELECT al.GroupName, al.GroupNum, al.AlsoKnown
FROM tblActionLog AS al
WHERE al.AlsoKnown Like "'" & pSearchText & "*'"

Once you have it returning the correct data, convert it to an INSERT query by changing the beginning of the statement to this ...

PARAMETERS pSearchText Text ( 255 );
INSERT INTO tmpGroupSearch (GroupName, GroupNum, AlsoKnown)
SELECT gh.GroupName, gh.GroupNum, ak.AlsoKnown
... and the rest

Save that query as qryGroupSearchAppend. Then you can execute that query from your VBA code:

Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.QueryDefs("qryGroupSearchAppend")
qdf.Parameters("pSearchText").Value = Me.txtgroupSearch.Value
qdf.Execute dbFailOnError

Upvotes: 1

pteranodon
pteranodon

Reputation: 2059

You can follow an INSERT INTO clause with a list of values, like @Asad shows, or also a SELECT query. Do yourself a favor and always list the field names in your SQL or you are just creating time bombs for the future.

Dim strSelect1 As String
Dim strSelect2 As String

strSelect1 = "SELECT tblGroupHeader.GroupName" _
  & ", tblGroupHeader.GroupNum" _
  & ", tblAlsoKnown.AlsoKnown" _
  & " FROM tblGroupHeader" _
  & " LEFT JOIN tblAlsoKnown ON tblGroupHeader.GroupNum = tblAlsoKnown.GroupNum" _
  & " WHERE tblGroupHeader.GroupName like '" & txtgroupSearch.Value & "*'" _
  & " OR tblGroupHeader.GroupNum like '" & txtgroupSearch.Value & "*';"

strSelect2 = "Select * FROM tblActionLog WHERE AlsoKnown LIKE '" & txtgroupSearch.Value & "*';"

Dim strInsert1 As String
Dim strInsert2 As String

strInsert1 = "INSERT INTO tmpGroupSearch (GroupName, GroupNum, AlsoKnown) " & strSelect1

'the next version is valid SQL, but *dangerous* because field names are not enumerated
strInsert2 = "INSERT INTO tmpGroupSearch " & strSelect2

Upvotes: 2

Asad
Asad

Reputation: 63

It is possible to write the INSERT INTO statement in two forms.

The first form does not specify the column names where the data will be inserted, only their values:

INSERT INTO table_name
VALUES (value1,value2,value3,...);

The second form specifies both the column names and the values to be inserted:

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);

Upvotes: 1

Related Questions