[Gram123]

Hi,

I have an Access 97 database that deals with the repair of used vehicles and their movements through the repair program. Certain vehicles that have had previous damage require inspection by an insurance company and as such are put on hold.
If after 14 days these 'on hold' vehicles have still not received their insurance inspection, they are repaired regardless.

I have set up a query that shows all vehicles that are on Hold (signified by a letter "H" in a field called "Decision") and have reached the 14th day without inspection.
This allows me to identify and act upon the vehicles that have reached their maximum period 'on hold'.

What I need to know is, is there an easy way to flag these vehicles, so that when I open the database, any vehicle on day 14 will immediately show up, before I do any other work. If there are no vehicles on 14 days then I don't want the query to open.

I know I can simply add a macro to the AutoExec macro, which will open the query whenever the database is ran.
But can I specify within the AutoExec macro that I only want the query to run when vehicles reaches day 14?

The 14th day is calculated using an expression in the query:

On hold: Date()-([In Program])+1

The plus one is because the dates are inclusive.

The Criteria is set to >=14

In other words, if this query would have any records in it, show me those records, otherwise don't show me anything - I have no use for an empty query!

I tried messing about using the Where condition in the macro, but didn't really know what I was doing.
Bearing in mind that I know practically nothing about VB code, can anyone help?

In the future, if we end up getting lots of these 'on hold' vehicles, I will alter it so that a printable report runs instead of a query, but for the time being I just want the query results.

Thanks for any help in advance!

Gram

[downwitchyobadself]

Yes, well, I'm afraid you've just about hit the limit of what one can do with a macro... Can't tell you that for sure, because I haven't used macros in a long time, but since they're basically one-line programs, there's no way to do things like test for values.

That being said, as far as VBA goes, what you're trying to do is quite straightforward. Here's the code, which you can copy directly, changing the name of the query to whatever yours might be:


Public Function TestQueryOnStartup()

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("MyQueryName", dbOpenSnapshot)

If rst.RecordCount > 0 Then
DoCmd.OpenQuery "MyQuery"
Else
MsgBox "No matching records found."
End If

rst.Close

End Function

Here's what you do (I'm going to assume you're a total newbie to VBA, so take no offense, please):

1. Create a new module (DB window --> modules --> New) You should see a blank page saying Option Compare Database, maybe Option Explicit, too.

2. Paste the above code into that window, changing the query name and, if you want, the message text to show if no records are found.

3. This code uses DAO, or Data Access Objects, which we won't really go into right now. Suffice it to say that it's a collection of stuff not 100% a part of Access. So you need to set a "reference" to the code, to make it work. This is how you do that, still in the VBA Project Editor (or module design window, if you prefer): Tools --> References. Scroll down through the list of available references until you find Microsoft DAO 3.6 Object Library. Check its box. Click OK.

4. Now you need to test it to make sure the code's okay. Choose Debug --> Compile. This uses MS's programming engines to test the integrity of your code. If there's a problem, post back with the error message, but normally all should be well.

5. Now, in your AutoExec macro, you need to add the RunCode statement, with the name of the function including the parentheses, like so:

TestQueryOnStartup()

That's it.

What the code does, very briefly, is create a recordset, which is a kind of memory-only version of your query. It counts the number of records; if there's at least one, it opens the query on your screen. If not, it shows you a message so that you know the test has been run.

Hope this helps.

[Gram123]

Cheers Down,

This works except for one minor thing - the message box appears twice on opening the database.

Any ideas why, or if it can be altered to only appear once?

Cheers,

Gram

[Gram123]

The message box seems to appear once for the database opening, and once for the auto-exec switchboard form opening.
If I open the database but bypass the autoexec form (i.e. holding down shift whilst opening), and then open the Switchboard form from within the database, the message box only appears once!

The auto-exec macro is simply:

OpenForm (Switchboard)
Maximize
followed by your 'RunCode' line

I was wondering if this could be to do with the number of actions attached to the macro, so I tried it without the 'maximize', but the message still came up twice.
So I thought, could it be to do with the number of criteria in the query?
However, I've amended the query so that now it actually has more criteria:

[Decision] is "H"
And
Date() - ([In Program]) is >=14
Or
[Decision] is "H"
And Date() - ([Out Program]) is >1=4

and this made no difference, so I don't think it's that.

It's not a problem really, but I'm interested to know why it happens and if I can stop it I will.

Thanks
Gram

[downwitchyobadself]

There's two possible explanations (I'm assuming you're in 2000), and no, neither of them is your query. Change that all you like.

1. (Most likely) You have attached the autoexec macro as one of the startup events for your switchboard form: OnLoad, OnOpen, etc. Keep in mind that naming a macro AutoExec means Access will run it automatically at startup; you don't need any other reference to it at all. I assume that this is the cause because you say that you can open the form and macro still fires. Normally, AutoExec should only fire when you open the database. Look through the Events list on the properties for the form; make sure AutoExec shows up nowhere.

2. I suppose it could be a startup thing. From the db window, Tools --> Startup. Make sure Display form/page is set to (none). AutoExec loads your form for you; telling the startup properties to do it is unnecessary. But loading the form shouldn't cause AutoExec to fire, unless #1 is true.

Hope this helps.

[Gram123]

Cheers Down.

You were right - the OnOpen property for the Switchboard form was set to run the AutoExec macro, which as you rightly say, is unneccesary as the AutoExec automatically executes (duh!) when the db is opened.

I removed this and it now operates properly, with the msg box only appearing once.

Thanks again,

Gram