Changing criteria using VBA in a module, need help please

[EJP001ARB]

I am new at VBA and Access. I am trying to figure out how to change a criteria in a query using VBA in a module. I am trying to automate this because there are several different simple criteria that I need to print on separate reports and do not wish to do this manually (take too long). I cannot even begin to write the code because every attempt I have made has not worked. Help please.

Eileen

 

[JoaoTL]

Hi, just an idea, have you tried to change the criteria based in controls such Comboboxs or textboxes or whatever.

When you print a report you open a Form then the User can apply all filters that he chose from the controls.

Hope it helps Best Regards

---
JoaoTL
NOSPAM_mail@jtl.co.pt

http://www.jtl.co.pt

http://www.jtl.co.pt/Access

 

[EJP001ARB]

Thanks for responding JoaoTL!

I'm not sure if I understanf your response completely but there are no forms involved. I have a report which is separate by customer name, each customer name has an id number. I would like to automate the report to separate and print to a pdf file each customers information into a separate report. The problem is that I am trying to figure out a way to change the customer code in the query (ie Eileen = customer # 1, Bob = customer #2) automatically using a module.

So what I want to do is, change query crirteria for one field (customer number = 1), run, print, (loop), change criteria to next customer # (2), run, print, loop etc.

Would appreciate any additional help!


Eileen

 

[alr0]

If you are going to print all customers each time you might try grouping on customer with a new page for each.

If you need to do this in code it is probably easier to write SQL in the code rather than modify an existing query.

Writing code is different than other uses of Access and will require a bit of work to get started. I think you can do this with just report grouping functions.

Hope this helps,

Al Russcol

 

[JoaoTL]

Hi, you can try this function. **NOT TESTED***

' ***NOT TESTED ****

Function Whatever()

Dim db As Database
Dim rs As Recordset
Dim nCustumer, i As Integer

nCustumer = 0

Set db = CurrentDb()
Set rs = db.OpenRecordset("YourTable", dbOpenDynaset)

With rs
Do
nCustumer = nCustumer + 1
.FindFirst ("[custumer] =" & nCustumer)
If .NoMatch Then
i = i + 1
Else
DoCmd.OpenReport ("YouReports&quot, , , "[custumer] =" & nCustumer
End If
Loop Until .EOF = True
End With

MsgBox "Not Found: " & Str(i)

End Function Best Regards

---
JoaoTL
NOSPAM_mail@jtl.co.pt

http://www.jtl.co.pt

http://www.jtl.co.pt/Access

 

[JoaoTL]

Just an Observation, This procedure can be a little bit hard for your Computer Memory because it will open 1 report by customer.

Be careful Best Regards

---
JoaoTL
NOSPAM_mail@jtl.co.pt

http://www.jtl.co.pt

http://www.jtl.co.pt/Access

 

[Ninabunny]

Well...Eileen I am assuming you know how to input a box or brackets in the criteria of the query? If not then it can be complicated....if you are new at this.

 

[ChaseMIS]

Hello,
If you are trying to print a separate report for each customer it is actually fairly simple to loop through each one. I actually have to do this fairly often with financial reports at the bank where I work. I assume that you have a table listing the Customer's names and a unique identifying number as you mentioned in your second comment. Here is a simple example of the code.

Public Sub CustomerReports()

Dim CustomerNumber As Integer
Dim db As Database
Dim SqlString As String
Dim Qdf As QueryDef
Set db = CurrentDb

For CustomerNumber = 1 To 6

SqlString = "SELECT Tbl_CustomerInfo.Name, Tbl_CustomerInfrder, Tbl_CustomerInfo.Address, Tbl_CustomerInfo.Phone FROM Tbl_CustomerInfo WHERE (((Tbl_CustomerInfo.CustomerID)= " & CustomerNumber & &quot);"

Set Qdf = db.CreateQueryDef("CustomerReport", SqlString)

'''''''''''''''Code here would open up your report and export or print it to your .pdf file. I have never done this before (I normally export to Excel) but you can still use the CustomerNumber variable or any other variable you choose to name the report once it has been exported so as to differentiate it from the others. You could also just print it directly depending on your needs.'''''''''''''

DoCmd.DeleteObject acQuery, "CustomerReports"

Set Qdf = Nothing

NextCustomer:
Next CustomerNumber

MsgBox "All customer reports have now been exported."

End Sub

As I said, this is very simple, but what is does is loop as many times as you tell it to. If there are 25 customers then the code will say "For CustomerNumber = 1 To 25" Every time the code loops again it will put the next variable in as the criterion in your CustomerReport query. As I mentioned, once the query has been created, the report based off the query can be opened and manipulated as you see fit using the same variable that was used as the criterion for the query. Once the report has been printed or saved with a unique name (ie Customer#17 - from the CustomerNumber variable - "Customer#" & CustomerNumber) the code will delete the query that was created, loop to the next number and recreate the query with the next number as the criterion. If you want to see the code in action, create a rudimentary table named Tbl_CustomerInfo with the following fields: 1. Name, 2. CustomerID, 3. Order, 4. Address, 5. Phone. Make sure that the CustomerID field is a number and not text. Put in 6 rows of bogus data with the customer ID field in sequential order and step through it. You will see it create the query for customer 1, delete it then create the query for customer 2 etc. The only thing missing will be to export the data before the query is deleted. That will be up to you. Just keep playing with it though, that is how most people learn. The Access and VBA code can do almost anything that you want, it is just a matter of finding out how. In the end, you will find that the code will give you much more flexibility and allow you to use variables to automate tasks that otherwise would take hours of manual manipulation.

Good Luck,
Derek