Dlookup function in vba

[TobyMace]

Hi All,

I am trying to add a Field [Previous_Returns] that looks up the value of Field [Serial_Number] to see if it has been entered before.
If it has been entered before then [Previous_Returns] should equal [RER_Number] of the previous entry/entries of [Serial_Number].
If it has not been entered before then [Previous_Returns] should read "None".
Also if the lookup finds multiple entries is there a way to easily and clearly display these?

If this made no sense I apologise I always find it hard to type what I mean!

Thanks in advance!

 

[Pat Hartman]

You do not need to store the values of the reference numbers since you can easily find them with a query whenever you need them.

 

[TobyMace]

By a query you mean by defining a serial number in a dialog box or something?
We can sometimes get numerous records at once to enter and don't want to be searching for each serial number as it can be quite time consuming.
I wanted this to work so I can work of this and it can "alert" me when a previous record has been found.

 

[Pat Hartman]

Your code can use DCount() to find out of the serial number has already been entered and now allow it to be entered again if you want or simply display a message and allow the save anyway. I only said that you should not save the data, not that you should not validate. Unless you want the person to actually stop and look at the previous return, just displaying the number of previous returns should be sufficient.

 

[TobyMace]

Sorry it's been a long day I'm not fully understanding what you are proposing?
I need it so that the [RER_Number](s) are visible after entering a serial number on a new record?

 

[Pat Hartman]

If you need the reference numbers to be visible, create a small subform. Bind the subform to a query that finds all the other instances of that serial number.

Select RefNum From sometable Where SerialNumber = Forms!mymainform!txtSerialNumber

If you want to be really slick, you can add code into the click event of the subform's RefNum control that opens a form with the details of the previous return.

 

[Mark_]

To shed some light on what Pat is recommending, you can have code similar to the following called when you load a record;

Dim aiPrevious As YourChosenNumberType
aiPrevious = DCount("FieldName","TableORQuery","Criteria = ")
IF aiPrevious = 0 THEN
   Me.Display.Caption = "No previous"
ELSE
   Me.Display.Caption = "There are " & aiPrevious & " previous returns."
END IF

Just make sure to fill in YOUR fields, YOUR table, and YOUR criteria.

 

[TobyMace]

Thank you both for your help! It is now working!

 

[TobyMace]

Hi guys,

It has been decided by the powers that be, that they want to know the [RER_Number]'s at the end of the row so they can quickly search it up if needs be when entering a new record.
Is this possible?

Thanks.

 

[Mark_]

I would put a subform on that shows ALL ref numbers.