check table for record

[John]

I have an unbound Form, with an unbound textBox: txtReceipt. When I put a
value there, in the BeforeUpdate event, I'm trying to check in tblTransactions
to make sure that that Receipt number has not been used yet, this fiscal year
(check records after 11/1/2007). Basically, if that receipt number has been
used, msgbox "that number already used, you still want to use this number?"

Don't know how to begin, do I use a recordset, or some sort of query? Thanks
for any pointers.

John

 

[Jeanette Cunningham]

Hi John,
DLookup or DCount is the tool to do this.
VBA help on DLookup / DCount will explain how to look it up.

For example if the count of receipt numbers the same as the one entered in
the textbox is 0 then you know you are not adding a duplicate.
More importantly - have you checked if the before update event fires for an
unbound textbox on an unbound form?

Jeanette Cunningham

 

[John]

I had thought about using DMax to compare, if Dlookup or DCount works better,
I'll use one of them. I wasn't sure about how to do either the recordset, or
one of the methods you listed, because of having two criteria: where
me.txtReceipt = tblTransactions.Receipt AND tblTransactions.PostDate > 11/1/07

I know could have the Dlookup (or Dcount or Dmax) look at a query instead of the
table, but was curious as to how to do it.

I wondered about the beforeUpdate event firing for an unbound text box, but the
vbe window popped up complaining about my rotten code. Also, I tried just a
simple msgbox, which did pop up, so I assume the event does fire.

Thanks

 

[John]

Just to follow-up, this seems to work ok:

Private Sub txtREC_BeforeUpdate(Cancel As Integer)

If Not IsNull(DLookup("[Receipt]", "tblTransactions", _
"[Receipt] = " & Forms.frmEnterReceiptsCharges.txtREC _
& " AND [TransActDate] > #11/1/2007#")) Then

Select Case MsgBox("The Receipt you entered has already been used." _
& " Either the prior Receipt usage was in error, or this Receipt is in
error. " _
& "If you want to accept this Receipt, click Yes. Otherwise, click No to
cancel. ", vbYesNo Or vbExclamation Or vbDefaultButton2, "Receipt already used,
continue anyway?")
Case vbYes
'just continue
Case vbNo
Cancel = True
End Select
End If
End Sub

Thanks for the pointer.