Insert records into table using result of a query

[AndyWal6]

I have an Events table that records events with the fields:
[ID], [ContactID], [Event](Text), and [EventDate] it is used as a note pad to record calls made to and from contacts and other occurrences.

I also have a MailShot selection form for setting up MS Word mailings to groups of contacts, normally between two dates.
The query on the MailShot form selects Name and address etc. from the 'Contacts' table according to the settings on the MailShot form and contains the field [ContactID] and all relevant data for the mailshot.

Every thing works fine.

Now I want to enter a new text event (“Last Mailshot Sent”) in the [Event] field and the date in the [EventDate] field of the 'Events' Table for each contact in the MailShot query to keep a record of the last mailing date for each contact.
This will happen automatically when a MsgBox asking if you really want to create the MailShot is answered in the affirmative.
The code below seems to be right but throws an Error 'Data Mismatch in criteria' at the line marked <<<<
the code includes the QueryDef bit because it threw errors about Parameters being required.

Dim StrSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim ShtDate
Dim LngDate
ShtDate = Format$(Date, "\#mm\/dd\/yyyy\#")
LngDate = Format$(Date, "\#mm\/dd\/yyyy hh\:nn\:ss\#")

Set db = CurrentDb
Set qdf = db.QueryDefs("MailShotQuery")
qdf(0) = CBool([Forms]![MailshotForm]![Check0])
qdf(1) = CStr([Forms]![MailshotForm]![Combo2])
qdf(2) = CDate([Forms]![MailshotForm]![Start])
qdf(3) = CDate([Forms]![MailshotForm]![End])
Set rs = qdf.OpenRecordset(dbOpenDynaset) '<<< It breaks here <<<<<
rs.MoveFirst
Do While Not rs.EOF ' Start of loop, First Row

StrSQL = "INSERT INTO EventsTable (LeadID,Event,EventDate) " & _
"VALUES (" & rs![Lead No] & ",'Mailshot Sent '," & ShtDate & ")"
DoCmd.RunSQL StrSQL
rs.MoveNext ' Next Row
Loop

rs.Close
Set rs = Nothing
Set db = Nothing

Your Help would be much appreciated.
Andy.

[NeoPa]

I'm surprised lines #12 through #15 don't give you any issues. I'm not familiar with using parameter queries in code, but I've never heard of a QueryDef object presenting as an array or collection.

That said, if you want help with calling a QueryDef in a particular way then we're going to need to see at least its SQL.

While I'm waiting for the information I'll just point out that only the hashes (#) need escaping in your date format strings and the hours needs to be "HH" (24 hr format) unless you don't care about ambiguous times or you plan to include AM or PM. "\#mm\/dd\/yyyy hh\:nn\:ss\#" can (should) be written as "\#mm/dd/yyyy HH:nn:ss\#". Other than that, your understanding of date format strings is probably better than most ;-)

[AndyWal6]

The Format for the date is Allen Brownes 'Format Dates for SQL'
The SQL for the MailShotQuery is as follows.
Many thanks for your help.

SELECT Contacts.[Lead No], IIf([Initial]>"",[Title] & " " & [Initial] & " " & [Surname],[Title] & " " & [Surname]) AS Name, Contacts.Title, Contacts.Surname, Contacts.Address1, Contacts.Address2, Towns.Town, Contacts.County, Contacts.PostCode, Contacts.Telephone, Contacts.Email, Contacts.Mobile, Contacts.DatabaseID, Contacts.LeadDate, Company.[Company Name], Company.Address1 AS CompAddr1, Company.Address2 AS CompAddr2, Company.Town AS CompTown, Company.County AS CompCounty, Company.PostCode AS CompPostCode, Company.e_mail, Company.Telephone, Company.Fax, Contacts.[Customer Type], [Forms]![MailShotForm]![Check0] AS Expr1
FROM Company, Towns INNER JOIN Contacts ON Towns.ID = Contacts.Town
WHERE (((Contacts.DatabaseID) Is Not Null) AND ((Contacts.LeadDate) Between [Forms]![MailShotForm]![Start] And [Forms]![MailShotForm]![End]) AND ((Contacts.[Customer Type])=[Forms]![MailShotForm]![Combo2]) AND (([Forms]![MailShotForm]![Check0])=-1)) OR (((Contacts.DatabaseID) Is Null) AND ((Contacts.LeadDate) Between [Forms]![MailShotForm]![Start] And [Forms]![MailShotForm]![End]) AND ((Contacts.[Customer Type])=[Forms]![MailShotForm]![Combo2]) AND (([Forms]![MailShotForm]![Check0])=0));

[NeoPa]

A number of little weirdnesses in here.Let's start by displaying the SQL in a readable form so we both know what we're referring to :

SELECT Contacts.[Lead No]
     , IIf([Initial]>"",[Title] & " " & [Initial] & " " & [Surname],[Title] & " " & [Surname]) AS Name
     , Contacts.Title
     , Contacts.Surname
     , Contacts.Address1
     , Contacts.Address2
     , Towns.Town
     , Contacts.County
     , Contacts.PostCode
     , Contacts.Telephone
     , Contacts.Email
     , Contacts.Mobile
     , Contacts.DatabaseID
     , Contacts.LeadDate
     , Company.[Company Name]
     , Company.Address1 AS CompAddr1
     , Company.Address2 AS CompAddr2
     , Company.Town AS CompTown
     , Company.County AS CompCounty
     , Company.PostCode AS CompPostCode
     , Company.e_mail
     , Company.Telephone
     , Company.Fax
     , Contacts.[Customer Type]
     , [Forms]![MailShotForm]![Check0] AS Expr1

FROM   Company
     , Towns INNER JOIN
       Contacts
  ON   Towns.ID = Contacts.Town

WHERE  (((Contacts.DatabaseID) Is Not Null)
  AND  ((Contacts.LeadDate) Between [Forms]![MailShotForm]![Start] And [Forms]![MailShotForm]![End])
  AND  ((Contacts.[Customer Type])=[Forms]![MailShotForm]![Combo2])
  AND  (([Forms]![MailShotForm]![Check0])=-1))
   OR  (((Contacts.DatabaseID) Is Null)
  AND  ((Contacts.LeadDate) Between [Forms]![MailShotForm]![Start] And [Forms]![MailShotForm]![End])
  AND  ((Contacts.[Customer Type])=[Forms]![MailShotForm]![Combo2])
  AND  (([Forms]![MailShotForm]![Check0])=0))

1. Name can be done (without the IIf()) as :
   

   [Title] & (" " + [Initial]) & " " & [Surname] AS [Name]

   See Using "&" and "+" in WHERE Clause.

2. [Forms]![MailShotForm]![Check0] AS Expr1

   Assuming there is a good reason for including this selection from your form, whay is there a need to check for it being either TRUE (-1) or FALSE (0) which you do? I don't mean checking either way, but checking both ways. I can think of no way a CheckBox control wouldn't be either one or the other (unless it allows not being set but that's still weird).
3. Assuming we have the QueryDef sorted out then, how do we call it in the code?
   
   I would suggest we refer to the various parameters by name to avoid the possibility (probability in your case it seems) that you're setting a different one from that which you expect. Your code is already careful to be explicit about the type of the values (although it doesn't check for NULLs).
   

   qdf("Forms!MailshotForm!Check0") = CBool([Forms]![MailshotForm]![Check0])
   qdf("Forms!MailshotForm!Combo2") = CStr([Forms]![MailshotForm]![Combo2])
   qdf("Forms!MailshotForm!Start") = CDate([Forms]![MailshotForm]![Start])
   qdf("Forms!MailshotForm!End") = CDate([Forms]![MailshotForm]![End])

That leaves us with potential NULL values. Are any of these controls susceptible to returning NULL values?

[NeoPa]

Hang on a second, I'm just kicking myself for missing this earlier.

I don't use parameter queries myself, but I do remember reading about them many years ago and from what I recall from that, if you want a parameter to behave in any way but one that can be inferred from the SQL, you need a PARAMETERS clause. I'm guessing that the dates can be inferred from being compared with date fields in your WHERE clause. The string is the default anyway, so what in the QueryDef tells SQL to expect Forms!MailshotF orm!Check0 to be a Boolean value? I would say Nothing. That's your specific problem I expect.

Before the SELECT in your QueryDef's SQL you need :

PARAMETERS Forms!MailshotForm!Check0 Boolean
         , Forms!MailshotForm!Start DateTime
         , Forms!MailshotForm!End DateTime;
SELECT ...

PS. That doesn't mean the other points are unimportant of course. You won't win any prizes (or friends) with code referring to qdf(0) if you know what I mean ;-)

[AndyWal6]

Thank you for all your trouble NeoPa
I don't understand the qdef section either, I can only refer you to the answer I got from my research when the problem was [Error 3061 too few parameters, 4 Expected].
Check out this from Roger http://www.rogersaccesslibrary.com/f...rameters2k.zip'

With regard to the checkbox test for 0 or -1, I have attached pictures of the mailshot selection form and the query designer behind the create mailshot button.
The query has been whittled down to the bare necessities for clarity and renamed [MailshotEventUp dateQuery]
the check0 selects whether a contact bought from us or not.
The revised SQL for the mailshot query is as follows.

SELECT Contacts.[Lead No], Contacts.DatabaseID, Contacts.LeadDate, Contacts.[Customer Type], [Forms]![MailShotForm]![Check0] AS Expr1
FROM Contacts
WHERE (((Contacts.DatabaseID) Is Not Null) AND ((Contacts.LeadDate) Between [Forms]![MailShotForm]![Start] And [Forms]![MailShotForm]![End]) AND ((Contacts.[Customer Type])=[Forms]![MailShotForm]![Combo2]) AND (([Forms]![MailShotForm]![Check0])=-1)) OR (((Contacts.DatabaseID) Is Null) AND ((Contacts.LeadDate) Between [Forms]![MailShotForm]![Start] And [Forms]![MailShotForm]![End]) AND ((Contacts.[Customer Type])=[Forms]![MailShotForm]![Combo2]) AND (([Forms]![MailShotForm]![Check0])=0));

A Contact is a Contact, in my Contacts table,
A Customer is Contact with a DatabaseID added.
I hope this helps you understand where things are going wrong.
Many thanks. Andy.

**Edit**
[imgnothumb]http://bytes.com/attachments/attachment/5325d1313310399/untitled1.jpg[/imgnothumb]
[imgnothumb]http://bytes.com/attachments/attachment/5326d1313310399/untitled2.jpg[/imgnothumb]

[AndyWal6]

I have changed a lot since my last post.
The SQL for the MailshotEventUp dateQuery query is now as follows.

SELECT Contacts.[Lead No]
FROM Contacts
WHERE (((Contacts.LeadDate) Between [Forms]![MailShotForm]![DateStart] And [Forms]![MailShotForm]![DateEnd]) AND ((Contacts.[Customer Type])=[Forms]![MailShotForm]![Combo2]) AND ((IIf([DatabaseID],-1,0))=[Forms]![MailShotForm]![Check0]));

Greatly simplified, I hope you agree.
The Code that runs after the Create mailshot button is pressed is now throwing a Run-Time Error '13' Type mismatch
The code is as follows:

    
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim qdf As DAO.QueryDef
    Dim prm As Parameter
    Set db = CurrentDb
    Set qdf = db.QueryDefs("MailshotEventUpdateQuery")
    For Each prm In qdf.Parameters
        prm.Value = Eval(prm.Name)
    Next prm
    Set rst = qdf.OpenRecordset(dbOpenDynaset)

Can anyone please help
Thank you in advance. Andy

[AndyWal6]

I solved the problem my self.
I created a four field table to hold the values from the mailshot form and set that as its source. I then used the table as the source of the query. Works perfectly. and retains the last dates that you mailshoted.
Thanks for your help, Andy.

[NeoPa]

Andy, you haven't answered the original question, but you've moved the question around instead, which is not how things are supposed to work. What you selected as the Best Answer doesn't answer the question of the thread. I'm pleased you no longer have the problem, but that doesn't constitute an answer. If ever you need more than the original question, or you change your mind about how you want to do the job and another question comes up, that will generally require a new thread. It's perfectly permissible to include a link to the original where required.

As far as answers go, you didn't respond (positively or negatively) to post #5 where, to my understanding, a full and correct answer was given. As this may be what people are looking for when they look at this thread I'd be grateful if you could post what you discovered when you tried this suggested solution out (I have no suspicion that you knowingly ignored it but there is no response so I must assume you overlooked it or failed to understand it - though I'm sure you would have posted to say so in the latter situation - as a matter of basic courtesy). Threads where an answer is confirmed to work are so much more useful than those without.

[AndyWal6]

NeoPa
The solution to my problem was quite simple in the end and I have attached the code and SQL of the query below.
I bound the Mailshot form to a table called MailformData and used the fields of this form as the criteria of the MailShotQuery which I renamed MailShotEventUp dateQuery and whittled down to the basics.

SELECT Contacts.[Lead No], Contacts.LeadDate, Contacts.[Customer Type], IIf([DatabaseID],-1,0) AS Expr1
FROM Contacts, Mailformdata
WHERE (((Contacts.LeadDate) Between [Mailformdata]![BegDate] And [Mailformdata]![FinDate]) AND ((Contacts.[Customer Type])=[Mailformdata]![LType]) AND ((IIf([DatabaseID],-1,0))=[Mailformdata]![Buyer]));

This runs perfectly when called from code:

Private Sub btnClickHere_Click()
    Me.Dirty = False
    Dim filepath As String
    Dim StrSQL As String
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
     'Dim ShtDate
     'Dim LngDate
     'ShtDate = Format$(Date, "\#mm\/dd\/yy\#")
     'LngDate = Format$(Date, "\#mm\/dd\/yy hh\:nn\#")

    If IsNull([DateStart]) Or IsNull([DateEnd]) Then
        MsgBox "You must enter both beginnin and ending dates."
        DoCmd.GoToControl "DateStart"
        Exit Sub
    Else
        If [DateStart] > [DateEnd] Then
            MsgBox "The Ending date must be greater than the Beginning date."
            DoCmd.GoToControl "DateStart"
        Exit Sub
    End If
    End If
    
If MsgBox("Do You Want to Mail-Shot Now?") = vbOK Then
    
    If Me.Check18 = -1 Then 'Unbound check box on Mailshot Form
        Set db = CurrentDb
        Set rst = db.OpenRecordset("MailshotEventUpdateQuery")
            DoCmd.SetWarnings False
            rst.MoveFirst
                    Do While Not rst.EOF ' Start of loop
                        
                        StrSQL = "INSERT INTO EventsTable (LeadID,Event) " & _
                        "VALUES (" & rst![Lead No] & ",'Last Mailshot Sent')"
                        DoCmd.RunSQL (StrSQL)
                        
                    rst.MoveNext ' Next Row
                    Loop
            DoCmd.SetWarnings True
            rst.Close
        Set rst = Nothing
        Set db = Nothing
    End If

[I]'This bit creates an excel sheet containing the results of the Mailshot query for the canvassers to follow up. I hate Excell[/I]
     
    If Me.Check16 = -1 Then 'Unbound checkbox on Mailshot Form
        filepath = DLookup("[DefaultFolder]", "Company") & "\" & "MailshotSpreadSheets\NewMailshot.xls"
        DoCmd.OutputTo acQuery, "MailShotExportQuery", acFormatXLS, filepath, False
    End If

[I]'This bit creates an rtf with the names and addresses for the Mailshot.Doc document to merge. The document is called by the Hyperlink.[/I]
    
    DoCmd.OutputTo acQuery, "MailShotQuery", acFormatRTF, LettersPath & "MailShotData.rtf", False
    btnClickHere.HyperlinkAddress = LettersPath & "Mailshot.Doc"
    
    Else
    btnClickHere.HyperlinkAddress = ""
    Exit Sub
End If
End Sub

The original question was was solved as the code now runs with no sign of an error.

** Edit to show picture **
[imgnothumb]http://bytes.com/attachments/attachment/5330d1313445836/mailshot-form.jpg[/imgnothumb]