Code to check if query returns any records

[ChesterPTR]

Hello there.

I have a UNION query called qry_qtypersheet_check with two fields [Part_No] and [Qty_per_Sheet], it pulls data from 3 different quaries and show records WHERE Qty_per_Sheet IS NULL.

For database to process information properly, every Part_No needs to have Qty_per_Sheet assigned, but it may occur that person responsible of adding information do database did not add QTY for particular part yet.

So on the form I have command button with Event Procedure that perform some processing but before it does I want it to check if qry_qtypersheet_check returns any records. If it does I want to stop that procedure and open that query to show records with parts that are missing qty_per_sheet value. If query does not return any records, I want to continue with Event Procedure.

I hope I make enough sense.
Any help will be much appreciated.

 

[KenHigg]

One way would be to use a dcount()

 

[ChesterPTR]

One way would be to use a dcount()

Thanks KenHigg. It probably could, but I forgot to mention that I'm not so fluent with figuring out VB code, bigginer here

Should probably go something like:

Dim RCount As Long
 
RCount = DCount("Qty_per_Sheet", "qry_qtypersheet_check","IS NULL")
 
If RCount > 0 Then
MsgBox "Some Part are missing Qty Per Sheet. You must edit records before continuing"
DoCmd.OpenQuery "qry_qtypersheet_check_allWeeks"
Else
DoCmd.OpenQuery "qry_feed_wk1", acViewNormal, acEdit
DoCmd.OpenQuery "qry_feed_wk2", acViewNormal, acEdit
DoCmd.OpenQuery "qry_feed_wk3", acViewNormal, acEdit
DoCmd.GoToControl "qry_1st_wk_subform"
DoCmd.Requery ""
DoCmd.GoToControl "qry_2nd_wk_subform"
DoCmd.Requery ""
DoCmd.GoToControl "qry_3rd_wk_subform"
DoCmd.Requery ""
DoCmd.GoToControl "in_part_no"
End If

How does this look to you?

 

[DCrake]

RCount = DCount("Qty_per_Sheet", "qry_qtypersheet_check","IS NULL")

Should read

RCount = Nz(DCount("*", "qry_qtypersheet_check","Qty_per_Sheet Is Null"),0)

I have wrapped Nz() around it to prevent errors when no records are returned.


BTW what are your queries doing? or more preceisely what are you attempting to do?

DoCmd.OpenQuery "qry_feed_wk1", acViewNormal, acEdit
DoCmd.OpenQuery "qry_feed_wk2", acViewNormal, acEdit
DoCmd.OpenQuery "qry_feed_wk3", acViewNormal, acEdit

David

 

[ChesterPTR]

RCount = DCount("Qty_per_Sheet", "qry_qtypersheet_check","IS NULL")

Should read

RCount = Nz(DCount("*", "qry_qtypersheet_check","Qty_per_Sheet Is Null"),0)

I have wrapped Nz() around it to prevent errors when no records are returned.


BTW what are your queries doing? or more preceisely what are you attempting to do?
David

Dave, you are gineus. It did the trick and works great.
Another case solved.

PS. Since you asked, those queries are append queries and they transfer some data from one of table to 3 different ones to have separate data for each week. It's pretty complicated to explain full process I'm doing, I'm not sure I understand it now either

 

[DCrake]

If one query is reliant one the proceeding query it would be prudent to place DoEvents between each append query to ensure that they are completed before the subsequent one is commenced.

David

 

[ChesterPTR]

If one query is reliant one the proceeding query it would be prudent to place DoEvents between each append query to ensure that they are completed before the subsequent one is commenced.

David

I will gladly input something like that if it will increase reliability,
if only you could supply me with proper synthax.....

 

[LPurvis]

Just to throw it out there...

DCount should always return a value (unless an error occured - e.g. you passed non-valid object names). Whereas with DLookup to an aggregate query it would be a near mandatory. (That's worth considering BTW).
But then, in VBA, a Nz hit is going to be neither here nor there and so does no harm.

On the query processing side - I certainly agree that completion of the queries is an important factor.
OpenQuery is an Access method, VBA is a single thread process, but then Jet is not. Even execution of DoEvents isn't a guarantee that the query results will have committed.
You could consider using code like

With DBEngine
    .Workspaces(0)(0).Execute "qry_feed_wk1", 128
    .Idle 1
    .Workspaces(0)(0).Execute "qry_feed_wk2", 128
    .Idle 1
    .Workspaces(0)(0).Execute "qry_feed_wk3", 128
    .Idle 1
End With

Or with DoEvents in there too, if there's the chance of external influences.

99.9% of the time you'd have no issues anyway though. The queries don't run asyncronously ;-)

Cheers.

 

[DCrake]

My mistake, a DLookup returns False if no matching records found, not DCount etc. which will always return 0 (zero). Its force of habit.

David

 

[ChesterPTR]

Thanks guys. Sure I will check this out.

 

[LPurvis]

>> Its force of habit

I'd figured as much - and it's not a bad habit to have.
Better that than the alternative.