Help Using DlookUp on a Form

  • Thread starter Thread starter Radar
  • Start date Start date
R

Radar

Need help with DLookup in forms beforeupdate
I have a table called tblStoreOrders with a key field name [Store] ( a
numeric field)
My form name is frmStoreOrders with a combo box call "Store" (This has
all the availible store numbers)
I want to check to see if this store has already placed an on the
same date. or a duplicate record, based on the same date of entry.
I tried first just looking for the store only. no luck.

DLookup("[Store]", "[tblStoreOrders]", "[Store] =
Forms![frmstoreOrders!store]")
error Expecting =
Please Help!
 
Radar said:
Need help with DLookup in forms beforeupdate
I have a table called tblStoreOrders with a key field name [Store] ( a
numeric field)
My form name is frmStoreOrders with a combo box call "Store" (This has
all the availible store numbers)
I want to check to see if this store has already placed an on the
same date. or a duplicate record, based on the same date of entry.
I tried first just looking for the store only. no luck.

DLookup("[Store]", "[tblStoreOrders]", "[Store] =
Forms![frmstoreOrders!store]")
error Expecting =
Please Help!
Click to expand...

If store is numeric, try this:

DLookup("[Store]", "[tblStoreOrders]", "[Store] =" &
Forms![frmstoreOrders!store])

If it's text, try this:

DLookup("[Store]", "[tblStoreOrders]", "[Store] = '" &
Forms![frmstoreOrders!store] & "'")

Tom Lake
 
Ok guys for give me. I now that I don't know as much as I thought.
I've tried different methods and I am knot sure how to write the code.
I even tried strStore = Dlookup()
What I need is, if the data or store already exist then, I need for
that record to apear, if not then alow then entry.
I don't know if I should put the code on the forms beforeupdate or the
[Store] 's beforupdate
Please help guys'
Thanks


Radar said:
Need help with DLookup in forms beforeupdate
I have a table called tblStoreOrders with a key field name [Store] ( a
numeric field)
My form name is frmStoreOrders with a combo box call "Store" (This has
all the availible store numbers)
I want to check to see if this store has already placed an on the
same date. or a duplicate record, based on the same date of entry.
I tried first just looking for the store only. no luck.

DLookup("[Store]", "[tblStoreOrders]", "[Store] =
Forms![frmstoreOrders!store]")
error Expecting =
Please Help!
Click to expand...

If store is numeric, try this:

DLookup("[Store]", "[tblStoreOrders]", "[Store] =" &
Forms![frmstoreOrders!store])

If it's text, try this:

DLookup("[Store]", "[tblStoreOrders]", "[Store] = '" &
Forms![frmstoreOrders!store] & "'")

Tom Lake
Click to expand...
 
Ok guys for give me. I now that I don't know as much as I thought.
I've tried different methods and I am knot sure how to write the code.
I even tried strStore = Dlookup()
What I need is, if the data or store already exist then, I need for
that record to apear, if not then alow then entry.
I don't know if I should put the code on the forms beforeupdate or the
[Store] 's beforupdate
Please help guys'
Thanks
Click to expand...

Which event is appropriate depends on the context. If the user enters
a store (?) into a textbox, do they need to be warned right then,
before entering the rest of the record? If so, use the Store control's
BeforeUpdate event. Or is it a unique *record* that's important? If
so, use the Form's BeforeUpdate.

In either case the logic is similar: attempt to look up the value
which should be unique; if the lookup finds nothing, DLookUp will
return NULL and all is well. If it finds something, it isn't NULL and
you need to warn the user and Cancel the update. In the case of a Form
you'ld do something like this (I'm assuming that Store is a text field
hence the ' delimiters):

Private Sub Form_BeforeUpdate(Cancel as Integer)
If Not IsNull(DLookUp("[Store]", "[tablename]", _
"[Store] = '" & Me!txtStore & "'") Then
MsgBox "This store already exists", vbOKOnly
Cancel = True ' Cancel the addition of this record
Me.Undo ' erase the form
<<<<<<<OR>>>>>>>>>
Me!txtStore.Undo ' just erase the Store field
End If
End Sub


John W. Vinson[MVP]
 
Back
Top