DLookup: Check if a record exists before adding to a different table

[nkbyrr]

I have very limited knowledge of VBA. I've been trying to adapt code found throughout these forums to my needs but haven't been able to get anything to work. At this point I am trying to start on a clean slate.

I have a database that keeps track of orders throughout a warehouse. Each order is assigned a unique number. At each "station" the box is scanned. In this problem, I have station names: Ready for Pick Up, and Picked Up. When the user goes to create a record of where the order is at they must select a form: either "Ready for Pickup" or "Picked Up." Upon opening one of those forms they are presented with a blank text box in which they click on and then scan the box, inputting the order number and submitting. I would like to prevent users from being able to scan the order as "Picked up" if the order was never scanned in as "Ready for Pickup."

There are two tables: "Ready for Pickup" and "Picked Up", each with the relate field "Order Number"

What I want to happen is if the user opens the "Picked Up" form scans a box as "Picked Up", the program checks to see if there is record of it in the "Ready for Pickup" table. If there is no record, pop up a message box notifying the user. If there is a record of it being scanned as "Ready for Pick Up" I would like it to simply add the record to the "Picked Up" table.



Let me know if you need any clarification and thanks for the help!

[orange]

Could you post a copy of your relationships window? That would show readers your tables, fields and relationships.
Please expand your tables so all fields are visible.

You should avoid embedded spaces in field and objects names.

I see things like
Orders
Warehouse
Stations

Can you tell us about your database and the "business" of your organization i plain English.
No need to tell us about the forms etc. Just a high level description of your business -- until we all understand what you are trying to do.

[Missinglinq]

For Order Number that is defined as a Number

Private Sub Order_Number_BeforeUpdate(Cancel As Integer)
If DCount("*", "Ready for Pickup", "[Order_Number] = " & Me.Order_Number) < 1 Then
  Cancel = True
  Me.Undo
  MsgBox "There is No Ready For Pickup Record for this Order Number!"
 End If
End Sub

For Order Number defined as Text

Private Sub Order_Number_BeforeUpdate(Cancel As Integer)
 If DCount("*", "Ready for Pickup", "[Order_Number] = '" & Me.Order_Number & "'") < 1 Then
  Cancel = True
  Me.Undo
  MsgBox "There is No Ready For Pickup Record for this Order Number!"
 End If
End Sub

Note that because of the Space in the Field name Order Number, when going from the Property Pane's Events Tab to the Code Module, Access places an Underscore between the two components; you really should eliminate Spaces from Field/Control names, as suggested above.

Linq ;0)>

[nkbyrr]

Unfortunately almost all fields and object names have spaces. I've learned since that that was a bad idea and am working on changing all of that.

We are a college bookstore. Customers are able to order textbooks online and pick them up (we typically deal with 6,000 or so orders in the months of August and January). The idea of this database is to keep better track of orders and where it is at in the fulfillment stage (both time and location). If we are unable to find an order when the person comes to pick it up we are able to search the order number to see when and where it is at in the process.

Here is a synopsis of the whole process and what I refer to as "stations": the order first gets entered into the system, then processed, then boxed (ready for pickup), and picked up. It can also be declined, become a hold, returned, or cancelled.

Here are the relationships

[nkbyrr]

Missinglinq, this is close. Two issues: I am getting a variable not defined for the Cancel = True. I removed the Cancel = True just to see if the rest works and it does. As soon as the form opens the msgbox pops up. I think that has to do with the VBA code I have to make the textbox go to the next record upon loading.

Changing event to before update instead of on Enter gives a "Procedure or declaration does not match description of event or procedure having the same name"
Below is all of the code.

Option Explicit

Private Sub Form_Load()
    DoCmd.GoToRecord , , acNewRec
End Sub

Private Sub Order_Number_Enter()
If DCount("*", "Ready for Pickup", "[Order_Number] = '" & Me.Order_Number & "'") < 1 Then
  Cancel = True
  Me.Undo
  MsgBox "There is No Ready For Pickup Record for this Order Number!"
 End If
End Sub

Edit:
I realized the order number is defined as text and used

Private Sub Order_Number_BeforeUpdate(Cancel As Integer)
 If DCount("*", "Ready for Pickup", "[Order_Number] = '" & Me.Order_Number & "'") < 1 Then
  Cancel = True
  Me.Undo
  MsgBox "There is No Ready For Pickup Record for this Order Number!"
 End If 
End Sub

But this does nothing, it allows me to add anything. The format of the order number is "WEB0000000" if that matters

[Missinglinq]

I am getting a variable not defined for the Cancel = True. I removed the Cancel = True just to see if the rest works and it does. As soon as the form opens the msgbox pops up. I think that has to do with the VBA code I have to make the textbox go to the next record upon loading.

Changing event to before update instead of on Enter gives a "Procedure or declaration does not match description of event or procedure having the same name"

The first problem is because you didn't have the code in the BeforeUpdate event of Order_Number, as the example showed.

The second problem indicates that you already have a Sub named Order_Number_BeforeUpdate; you can only have one! If you have other code for this event, you need to place both codes in a single Sub.

Linq ;0)>

[nkbyrr]

Thanks for helping out!
Just went through all of my code and this is the only place with the sub Order_Number_BeforeUpdate

Now the code in your example is the same for both defined as number and text unless I am misreading.

[trevor40]

Private Sub Order_Number_Enter()
If DCount("*", "Ready for Pickup", "[Order_Number] = '" & Me.Order_Number & "'") < 1 Then
Cancel = True
Me.Undo
MsgBox "There is No Ready For Pickup Record for this Order Number!"
End If
End Sub


is it possible that you first have to convert the field me.order_number into a number format and then check if it's less than one.
try to trim the field to just the number part and then format it as number and last check if it's < 1

[Missinglinq]

Now the code in your example is the same for both defined as number and text unless I am misreading.

Sorry! Got confused when copying and pasting! The above code for Order_Number defined as a Number has been corrected.

Linq ;0)>

[nkbyrr]

I got it working through trimming and formatting and using:

If DCount("*", "Ready for Pickup", "[Order_Number] = '" & Me.Order_Number & "'") < 1 Then
Cancel = True
Me.Undo
MsgBox "There is No Ready For Pickup Record for this Order Number!"
End If
End Sub

For some reason the code below isn't working right. When I enter an order number that does not exist in Ready for Pickup, it allows it be added to the table and does not pop the message box.

Private Sub Order_Number_BeforeUpdate(Cancel As Integer)
 If DCount("*", "Ready for Pickup", "[Order_Number] = '" & Me.Order_Number & "'") < 1 Then
  Cancel = True
  Me.Undo
  MsgBox "There is No Ready For Pickup Record for this Order Number!"
 End If 
End Sub

Thanks to you both!