Need Help Placing Condition 1

[Sharonky]

I have created a popup form (Access 2000) and it pops up if the text entered on the main form matches the Vehicle Vin # (text) in underlying table before the table is saved. (I don't want to prevent duplicates, just warn the data entry person that a duplicate exists and show details.) My problem is that the form pops up all the time - even if there isn't any matching text and it is blank. I'm using a macro (I know, I know I should be using VBA but this almost works.) In the main form, frmLoans, VehicleVin text field I have the macro in the "Before Update event" to have the popup form using this Where: statement--
[Forms]![frmLoans]![VehicleVin].[Text]=[qryValVinCurrent]![VehicleVin].
My macro condition is DCount("[LoanID]","qryValVinCurrent&quot> 0 but the timing is off. I only want it to open IF there is at least one match. There are 330 records in the results of my query without the filter and if I set the greater than to >330 in the condition, the popup form
doesn't open which is making me believe I'm on the right track. I just can't get the condition to grab the result AFTER the WHERE statement. I've tried a dozen combinations of moving the conditions to the popup form, to the query, and back to the mainform, but either get errors or the form regardless of number of
records.
Thanks for helping a novice.
P.S. I can do a little VBA if that's what I need to do, but please know I'm learning mostly by reading, trying and retrying. I've learned alot from all of your posts!
Sharon

 

[jrbarnett]

Try putting this code in the afterupdate event (and clear your macro):

VBA code:

If DCount ("*", "qryValVinCurrent", "VehicleVIN='" & [Forms]![frmLoans]![VehicleVin].[Text] & "'") > 0 Then
  DoCmd.OpenForm "yourform", mode:=acPopup
End If

 

[Sharonky]

jrbarnett,
Yes, it's almost there! It doesn't pop up if there is no match at all but since I've removed my macro, my WHERE statement is gone and my popup isn't being filtered by the WHERE statement to show just the one(s) that match. It brings up all 330 records. Can you tell me where to put the WHERE statement back in?

 

[jrbarnett]

Apologies for the delay in responding. My query has the same where clause as your macro. The where clause is the third part of the DCount function. The syntax is:

=Dcount(Fieldname, Tablename, Condition)

So if you want to restrict it further, you can extend the condition part to add extra arguments for example X=1 And Y =2

John

 

[Sharonky]

The single/double quotes have me confused. The macro way brings us the popup regardless if the count is greater than 0 but when the VIN#'s match, it does filter to just that VIN # and displays the matching detail records. Your SQL statement opens the popup only if there is a match, but it doesn't filter the results to match - all 330 records are listed. I can't seem to grasp how to get the best of both worlds and do what I want to do which is popup only if there is a match and show just those that match.