Opening a file only if query results exist...

[RSW]

I inherited an Access database that has a switchboard with clickable links to Minitab statistical software executable macros. Long story short, Minitab breaks if certain involved Access queries contain no data (I'll be happy to provide more information if desired) Minitab customer support says the issue needs to be addressed in Acess.

What I would like to do (I think) is convert the hyperlinks into VBA-event buttons that run the query, check the results, and open the Minitab file if results exist, and pop-up an alert if results don't exist.

Is this the right way to handle things? Is it even possible? I have run Access queries from VBA functions before, but don't know the best way to actually analyze those results from in there. Are there any examples of anything like this? A Google search didn't help me...

Thanks in advance

 

[DCrake]

If the queries you are attempting to run are simple select queries or based on select queries I would consider to a DCount("*","QueryName") to check for existing records, then if any exist continue else exit.

 

[RSW]

OK, I'll check out DCount. I hadn't been familiar with that command. Thanks!

 

[DCrake]

As an example it would appear as such

If Nz(DCount("*","YourQueryNameHere"),0) = 0 Then
   MsgBox "No records match the criteria you have specified.",vbOkOnly,"Operation Abandoned"
   Exit Sub
End If

 

[vbaInet]

Oh, by the way does the DCount return Null as well? I don't think it does though.

 

[DCrake]

Yes Dcount() will return zero if no records match, however should there be anything wrong with the query in some way and the DCount() is not processed correctly then False will be returned. This is just a case of being over protective, but safe in the knowledge that it won't cause the app to fall over.

 

[RSW]

Hadn't been familiar with the Nz command, either. For the most part, I've only done the very most basic stuff in VBA. Thanks again!

 

[vbaInet]

Always good to be safe

But that would mask the problems wouldn't it?

 

[RSW]

If I put a build event on these hyperlinked buttons, do you know which will come first upon a click?

 

[RSW]

Looks like the build event works and the hyperlink no longer works. I'll have to add a hyperlink to the VBA, which should be simple.

 

[RSW]

OK, I stand corrected. The hyperlink does still work (I missed a security pop-up behind the scenes), so it needs to be removed since we don't always want to follow it.