How look to see if values exist in a table not bound to current open form

[SteveL]

I have a form named "frmOpenOrders". On this form is a field named "txtOrder_Qty". I am trying to write code for an afterupdate event of this field to do one of two thing:

Scenario 1: If values DO NOT exist in ANY of 5 fields in a totally separate table named "tblPartsMasters", then I want a 2nd form to open, leaving the "frmOpenOrders" open. The 2nd form to be opened is named "frmPartsMastersEditFromOpenOrdView". The 5 fields in the table named "tblPartsMasters" are "BestPrice1", "Q1D1Price", "Q2D1Price", "Q3D1Price", "Q4D1Price".

Scenario 2: If values DO EXIST IN ANY of the 5 fields, "BestPrice1", "Q1D1Price", "Q2D1Price", "Q3D1Price", or "Q4D1Price" in "tblPartsMasters", then the 2nd form I want to open, again leaving the 1st form open, is named "PriceInfo".

 

[Mike375]

If I am reading your post correctly then there is a crude way to do that.

Make a query on the other table with Count for each field. Then a query on that query with a calculated field that adds the result for [CountofBestPrice1] + [CountofQ1D1Price] etc. The answer will be 0 or >0

Make a form based on that query.

Add an unbound textbox to your mainform or add a field to the table supporting the form if you want to record the result that existed at that time.

In your AfterUpdate Even first have it open the form you just made which will have the result of Count in a field (0 or >0) and then set the value of the textbox on your main form with the value of the Count field on the other form.

Have a close form action to now close this form.

You will now have either 0 or >0 in your text box on the main form. From then on your open form action is open frmPartsMastersEditFromOpenOrdView for 0 and open PriceInfo >0

There are undoubtedly much better ways but that should get you there.

 

[WayneRyan]

Steve,

If DCount("[BestPrice1]", "tblPartsMasters", "Nz([BestPrice1]) = 0 And " & _
                                             "Nz([Q1D1Price])  = 0 And " & _
                                             "Nz([Q2D1Price])  = 0 And " & _
                                             "Nz([Q3D1Price])  = 0 And " & _
                                             "Nz([Q4D1Price])  = 0) > 0 Then
   DoCmd.OpenForm "frmPartsMastersEditFromOpenOrdView"
Else
   DoCmd.OpenForm "PriceInfo"
End If

Note: Only "counting" on field [BestPrice1] because we need a field, we could use "[*]"

hth,
Wayne