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

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" <johnboy7676@yahoo.com> wrote in message 
news:bskdo39i9dakies98al05uc12grjgudeam@4ax.com...
>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 

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


>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" <johnboy7676@yahoo.com> wrote in message 
>news:bskdo39i9dakies98al05uc12grjgudeam@4ax.com...
>>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 
>

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.


>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" <johnboy7676@yahoo.com> wrote in message 
>news:bskdo39i9dakies98al05uc12grjgudeam@4ax.com...
>>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 
>