Replies

Thanks

Thanks for posting your solution. It looks interesting. I did not know how to do it.

When it uses Resolve to check its status what is it looking for? What sorts of thing is it finding that your parsing did not find?

For me checkboxes in 2010 behave just as they did in 2003, but always use the mdb format rather than the newer accdb.

Does it help if you remove the checkbox from the layout? Switch to Design View, selct the checkbox and use Remove Layout from the Arrange Tab. This does not remove it from the form. A layout is (sorry not sure how to describe it) a way managing the way things are laid out on the form. I always remove them.

In Access simple arithmetic on dates works OK (e.g. Date()-30) but I believe that this is not true with SQL Server.
I only every work with Access so I usually just use the arithmetic method, but I can understand why people who move between Access and SQL Server would probably use a method that works equally well in both – DateDiff.

Yes..you still open the filtered report (as RG said to do) but only in Preview Mode, then use the Docmd.SendObject while the report is open in Preview Mode, then Close it and move to the next record.

Here is a modifed version of RGs code, but I have not actually tried to run it, so please excuse any typos.

Code:

Sub EmailBills()

    Dim dbName As Database
    Dim rst As Recordset
    Dim lRecNo As Long
    Dim lBillCnt As Long
    Dim zWhere As String
    Dim zMsgBody As String
    Dim zEmail As String
    Dim zSubject As String
    Dim zDocname As String
    zDocname = “rptAnnualbilling”
    Forms![Switchboard].Visible = False
    If Not SetDateForBills() Then
        Forms![Switchboard].Visible = True
        Exit Sub
    End If
    Set dbName = CurrentDb()
    Set rst = dbName.OpenRecordset(“Owners”, dbOpenDynaset)
    rst.MoveFirst

    lBillCnt = 0
  
    Do While Not rs.EOF

        If rst![EMail]  “” Then

            zWhere = “[OwnerID] = ” & Str(rst![OwnerID])
            DoCmd.OpenReport zDocname, acPreview, , zWhere
            zEmail = rst![EMail]
            zSubject = “WPOA Annual Dues Statement: ” & rst![OwnerLName]
            zMsgBody = “Hi ” & rst![OwnerLName] & vbCrLf & “Please find your WPOA annual dues statement attached.”
            DoCmd.SendObject acReport, zDocname, acFormatPDF, zEmail, , , zSubject, zMsgBody, True
            DoCmd.CloseReport zDocname, acSaveNo
            lBillCnt = lBillCnt + 1  ‘*** Count Emails Created ***
        End If

        rst.MoveNext        ‘*** Move to Next Record ***
    Loop

    MsgBox Format(lBillCnt, “#,###”) & ” Email Bills Created.”
    Set rst = Nothing     ‘*** Close RecordSet ***
    
    Forms![Switchboard].Visible = True

End Sub     

Further to RG’s response, in Access 2010 you can remove all references to Outlook, and creating the PDF and just use code like this, assuming you have defined the variables, and assigned values to them

DoCmd.SendObject acReport, stDocName, acFormatPDF, strRepEmail, , , strSubject, strMessage, True

Do you use Zone Alarm Firewall?

I found this thread which might help.

Paid: DSum(“Amount”,[KYOWAPayment],”[KYOWAPayment]![Wire Date]<= [Received]")

The Syntax for DSUM is:DSum(Expr, Domain, Criteria)

Paid: DSum("Amount","KYOWAPayment","[Wire Date]<=#" & [Received] & "#")

But what is Received? Is that a field in the table KYOWAPayment ? Presumably WireDate is a field in KYOWAPayment.
You said that two tables are involved? Does the second table come into the DSUM at all?

To be able to Search for one city OR another you need two text boxes.

The SQL for the query looks like this:

Code:

SELECT tblPeople.*
FROM tblPeople
WHERE (((tblPeople.Suburb)=[Forms]![frmTestSearch]![txtsuburb]));

For an OR query it needs to look like this

Code:

SELECT tblPeople.*
FROM tblPeople
WHERE (((tblPeople.Suburb)=[Forms]![frmTestSearch]![txtsuburb1]) or ((tblPeople.Suburb)=[Forms]![frmTestSearch]![txtsuburb2])) ;

One option is a multiselect list box, listing the towns you have. But this requires VBA code. It can’t be done with a simple query.

You can tell the query to treat an empty text box to mean All Cities.

Code:

SELECT tblPeople.*
FROM tblPeople
WHERE (((tblPeople.Suburb)=[Forms]![frmTestSearch]![txtsuburb])) or   OR ((([Forms]![frmTestSearch]![txtsuburb]) Is Null));

That looks like this in the query grid.
30615-Citiesquery

Really flexible searching generally requires using VBA to write a Where clause

The short explanation is that you just can’t use form controls as parameters in queries used by DAO. DAO operates at a lower level and does not know about Access forms.

So generally the solution is to write the SQL on the fly using the value of the form parameter.
But it looks like you have found another way by specifying the prm.value.

I just want to make sure I am clear about this.

Your form has a number of controls on it. If you fill them all in, the query which uses them all as criteria works.

But you want to be able to fill in just some of the controls, and then you want only the ones with data in them to be used as criteria.

If that is right, then the only solution I know is one that involves a lot of VBA code.

When you have a query with criteria, the criteria make up the “Where” clause of the SQL of the query.

In the situation you describe you need to build the SQL in code, then build the Where clause looking at each of the controls in turn and using the ones that are not null.

We can go further and look at what that VBA code would look like if you want.

Does your export code use DAO?
Have you explicitly declared the parameters in the query Parameters Dialog?
30608-queryParameters

You don’t always need to do this, but if things are not working in can help.

Private Sub Button_OpenForm2_Click()
On Error GoTo Button_OpenForm2_Click_Err
DoCmd.OpenForm “Form2”, acNormal, “”, “[FieldA]=” & Form1.[FieldA], , acNormal
Form2.FieldA = Form1.FieldB

Button_OpenForm2_Click_Exit:
Exit Sub
Button_OpenForm2_Click_Err:
MsgBox Error$
Resume Button_OpenPkg_Click_Exit
End Sub

The syntax you need is:
Forms(“form2”).FieldB=me.FieldB

This code executes from form1, so Me is just a reference to the form the code executes from.

But does the record in table2 exist? Or do you need to create it?

I doube that I would be using three tables in the scenario you describe. You could have all the fields in the one table, and just leave them as null when they are not needed.

is there a way for a query that is executing before the transferspreadsheet that has a parameter asking for the same variable, meaning ANGB01 to automatically be part of the filename? I hope I am being clear!

If the query parameter came from a form rather than a query prompt then you could include it in the file name automatically, rather than having to use an Input box to ask for it again.

The SQL used for rsRen is based on some SQL that joins a whole pile of fields. When you join tables a record is normally returned only if there is a match on both sides of the join. It seems that you have nulls in some of the fields used for the joins.
Can you build a query in the query designer that select the records you want to use for rsRen? In the query designer you can double click the join line between tables to change the join type.

You have:

Debug.Print strNote, ContactName

StrNote is only assigned a value before the loop starts. What value do you want it to have at this point?
What type of thing is ContactName? It appears to be an indeclared variable? What do you want it to be? do you means RSRen(“contactName”)?