Creating a new record containing values based on queries of other tables in MS-Access

Question

I want to create a new record in the mailers table, where three fields would contain values: contacts_first_filter_id, mailer_states_id, created_at. And two of those values are based off queries from other tables and the last one is just the current time.

I've tried multiple ways to achieve this but none have worked. First I tried creating a query directly within sql design:

INSERT INTO mailers ( contacts_first_filter_id, mailer_states_id, created_at )
VALUES (DLookup("id","update_mailer_step_two"), DLookup("id","mailer_states" & "mailer_state = 'sent'"), Now());

This gives error 'unknown'.

I also tried putting this in VBA editor and triggering it on a button click:

Private Sub Command6_Click()


        Dim CFF_ID As String, MS_ID As String, strSQL As String

        CFF_ID = "SELECT update_mailer_step_two.id FROM update_mailer_step_two"

        MS_ID = "SELECT mailer_states.id FROM mailer_states WHERE mailer_states.mailer_state = 'sent'"

        strSQL = "INSERT INTO mailers ( contacts_first_filter_id, mailer_states_id, created_at )VALUES ((" & CFF_ID & "),(" & MS_ID & "),NOW())"

        DoCmd.RunSQL strSQL

End Sub

This gives error: "Query input must contain at least one table or query".

I tried the recommendation below using INNER JOIN but while this produces no error, it appends 0 rows, presumably because there is no INNER JOIN link between update_mailer_step_two and mailer_states:

INSERT INTO mailers ( contacts_first_filter_id, mailer_states_id, created_at )
SELECT update_mailer_step_two.id, mailer_states.id, Now()
FROM update_mailer_step_two INNER JOIN mailer_states ON update_mailer_step_two.id = mailer_states.ID
WHERE mailer_states.mailer_state = 'sent';

mailer_states table is just a table that contains a mailer_state field with text. It's pretty much just a lookup table that never changes.

update_mailer_step_2 contains one field called id, which contains a bunch of ids associated with contacts in database. Hence, there's no relationship at all between mailer_states and update_mailer_step_2.

** What I thought I would be able to do is when I create update_mailer_step_two, add a new column and default it with a value corresponding to mailer_states so that the INNER JOIN would work. Unfortunately, it appears you cannot do this in Access!

Another stackoverflow post mentioned you can:

SQL to add column with default value - Access 2003

But it is not working for me.

I have run out of ideas of how to place a query based off other table as a value to a query that is intended to create a new record in a table.

Thanks for response.

Answer 1

Try your 2 DLookup statements in the Immediate Window (Ctrl+g).

? DLookup("id","update_mailer_step_two")
? DLookup("id","mailer_states", "mailer_state = 'sent'")

If they work (don't throw errors) and return what you want, try incorporating them into your subroutine.

Private Sub Command6_Click()
    Dim CFF_ID As Long, MS_ID As Long, strSQL As String
    Dim db As DAO.Database
    CFF_ID = DLookup("id","update_mailer_step_two")
    MS_ID = DLookup("id","mailer_states", "mailer_state = 'sent'")
    strSQL = "INSERT INTO mailers (contacts_first_filter_id, mailer_states_id, created_at) " & _
        "VALUES (" & CFF_ID & ", " & MS_ID & ", NOW())"
    'DoCmd.RunSQL strSQL '
    Debug.Print strSQL 'look in Immediate Window to see the completed INSERT statement '
    Set db = CurrentDb
    db.Execute strSQL, dbFailOnError
    Set db = Nothing
End Sub

That untested code assumes numeric data types for CFF_ID, MS_ID, the contacts_first_filter_id and mailer_states_id fields in mailers, and the id fields in the update_mailer_step_two and mailer_states tables. If any of those are actually text, you'll have to make adjustments to the code or tell us which is which.

Edit: The intent of the Debug.Print line is to allow you to view the completed INSERT statement you're asking the db to execute. If it fails, you can copy the statement text and paste it into SQL View of a new query. There you can tweak it so that Access will accept it. Then adjust your code to produce the same working INSERT statement.

Answer 2

Your first approach (using a query containing calls to DLookup) should in principle work, but it has a syntax error. The suggestion by @HansUp to run each DLookup individually is a good one, and would help you to identify this (in the Visual Basic Editor, press Ctrl-G to show the intermediate window; the question mark before each expression in @HansUp's example is required).

Specifically, the ampersand in the second DLookup call is the string concatenation character, meaning that your current call is equivalent to:

DLookup("id","mailer_statesmailer_state = 'sent'")

Instead, change it to:

DLookup("id","mailer_states", "mailer_state = 'sent'")

EXTRA

To insert one new row for each row in update_mailer_step_two, try this:

INSERT INTO mailers ( contacts_first_filter_id, mailer_states_id, created_at )
SELECT update_mailer_step_two.id,
       DLookup("id","mailer_states", "mailer_state = 'sent'"),
       Now()
FROM update_mailer_step_two

Answer 3

You're trying to insert the results of a select statement into a table. However, the select statement independently selects from two different tables.

I don't think this will work.

Mainly because how does it handle the two independent results. Does it create a list with the cross, inner join, etc.

I would create a query that selects from both, and then insert that query into your table. Or you could inner join the selects and forward that join into your values.

Answer 4

I think your button click sample also has syntax errors when you build the string. This should work (with the rest of the code, assuming that your DLoookups work correctly, as noted by HansUp):

strSql = "INSERT INTO mailers (contacts_first_filter_id,  mailer_states_id," & _
       " created_at) VALUES (" & CFF_ID & ", " & MS_ID & ", #" & Now() & "#)"

I'm not sure whether or not the extra parens that I removed would have any effect, and the whitew space differencwe should be no difference, but my version lets VBA evaluate Now() and build the value into the string. Access uses # to quote dates.