Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Dlookup not working

Posted on 2004-10-22
7
Medium Priority
?
492 Views
Last Modified: 2006-11-17
I have an impending disaster guys!  I have a purchase order system that if an order is over a certain value a manager is required to enter an authorisation number.  The manager obtains this from a table and ticks a box to a) issue it, and b) so that it cannot be re-used.

The authorisation field on the form takes this number, looks up the check table (called pincheck) and if it exists, and has not been used up, it inserts it and allows the user to move through the form.

This is the code on the after_update event of the authorisation number field (which is only made visible if the order value is >50):

Dim sPin As String

sPin = Nz(DLookup("pin", "pincheck", "not deleted"))

If sPin = Me.Text62 Then
    MsgBox "Valid PIN entered - please proceed with order", vbInformation, "RF Materials Ordering"
    Me.Order_number.Visible = True
Else
    MsgBox "Invalid PIN entered - please check and try again", vbCritical, "RF Materials Ordering"
   
End If

It's behaviour is erratic - now it seems to have stopped working.  Any help REALLY needed guys!

Thanks
Lapchien


0
Comment
Question by:Lapchien
  • 3
  • 2
  • 2
7 Comments
 

Author Comment

by:Lapchien
ID: 12379693
NB - the pincheck table contains 4 columns - auto (PK), pin, usedup and deleted.

0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12379694
Try:

If Not IsNull(DLookUp("[pin]","pincheck","[pin]='" & Me!Text62 & "'")) Then
   'Valid PIN entered
Else
   'Invalid PIN entered
End If
0
 
LVL 41

Accepted Solution

by:
shanesuebsahakarn earned 2000 total points
ID: 12379701
Note - this:

sPin = Nz(DLookup("pin", "pincheck", "not deleted"))

returns the *first* PIN in the table where the deleted field is False, regardless of what that pin might be. My code should be modified to take this into account:

If Not IsNull(DLookUp("[pin]","pincheck","[pin]='" & Me!Text62 & "' AND [Deleted]=False")) Then
   'Valid PIN entered
Else
   'Invalid PIN entered
End If
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Lapchien
ID: 12379703
Thanks Sahne - when the manager issues the auth nbr he also checks 'deleted' to stop it from being issued again and to stop it from being a valid number...

0
 
LVL 41

Expert Comment

by:shanesuebsahakarn
ID: 12379724
The above should do it.
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12379727
Hi Lapchien,
What about using the following instead:

sPin = Nz(DLookup("pin", "pincheck", "pin =" & Me!Text62 ))
0
 
LVL 15

Expert Comment

by:will_scarlet7
ID: 12379754
I sure was the turttle that time...
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question