If Macro

[krismcmillan]

Hi

I wondering if somebody could help, I would like to use a IF macro to display two different reports if possible?

This is what I want to do...

If [Opengrn1]![STK Holding] / [Despatch]![Cartons Despatched] <= 2, then display a report to show all stock that won't last two weeks.

OR

If [Opengrn1]![STK Holding] / [Despatch]![Cartons Despatched] >= 2, then display a report to show all stock that will last more than two weeks.

Thanks.

 

[Isskint]

When you say macro, do you specifically want it as an Access macro or do you just want VBA macro?

You pretty much have it already for VBA. This code could go in the OnClick event of a Command Button for example;

If [Opengrn1]![STK Holding] / [Despatch]![Cartons Despatched] <= 2 Then
    DoCmd.OpenReport ReportName1
Else
    DoCmd.OpenReport ReportName2
End If

both [Opengrn1]![STK Holding] and [Despatch]![Cartons Despatched] would need to be available (eg open form(s), query(s) )

As an Access Macro, depends on the version of Access, but essentially chose IF (or IIF ) as the action, then supply your [Opengrn1]![STK Holding] / [Despatch]![Cartons Despatched] <= 2 as a criteria, select the relevant report name as the result, add an ELSE and add the other report name.

 

[krismcmillan]

I have just tried this and i get the following error, when using the Access Macro - as I'm sure what the full coding is for the VBA way.

Microsoft Access cannot parse the experssion: 'If{opengrn1]![STK holding] / [Despatch]![Cartons Despatched] <=2.

Click Ok to return to the action argument or conditional expression where the expression appears, and then correct the syntax.

I used the Expression Builder and select the tables and the columns for it.

 

[Isskint]

As a macro you should have something like the attached image. The thing is, for your expression to work both fields need to be 'available', so on an open form or in a query etc.

Where are you running this macro from and when?

 

[krismcmillan]

I have tried this again, as per the attached macro. But the attached error message appears.

 

[krismcmillan]

This is using the Expression Builder and selecting the tables and the columns for it.

 

[Isskint]

This would seem to be indicate one or other form/table/query ([Opengrn1] and/or [Despatch]) are not OPEN.

When you run this macro you are saying to look at the value in [Opengrn1]![STK Holding] divide it by the value in [Despatch]![Cartons Despatched]. Unless you have an open form/table/query with a record selected, the expression does not know which records to use in the calculation.

So again i ask where are you running this macro from & when and what are [Opengrn1] and [Despatch] (tables/queries/forms)?

 

[krismcmillan]

Sorry at the moment I'm just testing to see if it works. Then I plan to put a button a main page (form) and then link it to this macro.

Both opengrn1 & despatch are tables within the database.

 

[krismcmillan]

So is there anyway of fixing this macro??

 

[vbaInet]

What are Opengrn1 and Despatch? Tables?

 

[Isskint]

Sorry at the moment I'm just testing to see if it works. Then I plan to put a button a main page (form) and then link it to this macro.

Both opengrn1 & despatch are tables within the database.

ty kris.

you can not do what you want in this way. as previously mentioned, your macro needs to know which record to use. i guess opengrn1 is a table detailing stock levels against each product and despatch details items/orders despatched? for the macro to work it would need to know which product you are comparing.

to achieve your goal of printing a report dependent on that criteria will need 2 buttons. additionally you will need;

• a relationship between the 2 tables (product?)
• 1 query using the criteria >=2
• 1 query using the criteria <2
• 1 report based on query 1
• 1 report based on query 2

can you post a copy of your database?

 

[krismcmillan]

Hi Isskint

Thanks I will try that later today.

Please see attached a copy of the database.

 

[Isskint]

Hi kris

OK that clears up a few things. You can not make the decison which report to run in the way you have tried. Within the query it calculates against each record (which is fine) but in the OnClick event it has no single record to look at. There are many ways to achieve what you are looking for and i have demonstrated one in the attached.
I have copied your query Number of weeks Coverage and called it Number of weeks Coverage more 2 and renamed the origianl to Number of weeks Coverage Less 2. I have added criteria to the queries at Number of Weeks Cover for Thrapston: Int([Opengrn1]![STK Holding]/Despatch![Cartons Despatched]) , <2 for the less 2 and >= 2 for the more.
Next i copied the report and renamed it Number of Weeks Coverage more and reset the data source to the other query
Lastly i added an option group to your form Main Screen so the user can select which report to run.

 

[krismcmillan]

A big Thank you.

Another question I have got a excel spreadsheet, which has got 9 macro's on (due to the size of the spreadsheet). Is there a way of either linking these macro's together on a spreadsheet or can I link them to buttons on a form in this database?

 

[Isskint]

hi kris,

There is process known as automation where you can 'control' a spreadsheet from within Access.

You should really open a new ticket though as others may have more knowledge of this.