Solved No data entry after a certain time ? (1 Viewer)

Emma35

Registered User.
Local time
Today, 02:20
Joined
Sep 18, 2012
Messages
455
Hi All,
I'm working on a maintenance ordering system and one of the conditions is that nobody can place an order for that particular day after 10am that morning. If it's after 10am they can place an order for the following day no problem. Is there a way i can prevent a record being added to the database if it's after 10am and also have a message appear on the screen saying something like "No orders accepted after 10am. You may place an order for tomorrow instead". However, if the order is ok and placed before 10am, as per the rules, i'd like the message to say "Order accepted".

Hope i've explained that ok
Any help would be great,
Cheers Em
 

bob fitz

AWF VIP
Local time
Today, 09:20
Joined
May 23, 2011
Messages
4,717
IMHO form validation is best done in the forms Before Upadate event.
See: http://www.baldyweb.com/BeforeUpdate.htm

EDIT Added code sample:
Perhaps something like:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord And Time() > "10:00" Then
        MsgBox "Bookings can NOT be make after 10:00" & vbCrLf & _
            "Please book for tomorrow.", , "Booking Cancelled"
        Cancel = True
   
    Else
        MsgBox "Thank you.", , "Booking Confirmed"
    End If

End Sub

We would need more information about your db for more specific help
 
Last edited:

Emma35

Registered User.
Local time
Today, 02:20
Joined
Sep 18, 2012
Messages
455
IMHO form validation is best done in the forms Before Upadate event.
See: http://www.baldyweb.com/BeforeUpdate.htm

EDIT Added code sample:
Perhaps something like:
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord And Time() > "10:00" Then
        MsgBox "Bookings can NOT be make after 10:00" & vbCrLf & _
            "Please book for tomorrow.", , "Booking Cancelled"
        Cancel = True
  
    Else
        MsgBox "Thank you.", , "Booking Confirmed"
    End If

End Sub

We would need more information about your db for more specific help

Thanks bob.....i'm just starting and the tables are below

tbl_Orders
OrderID (PK Autonumber)
TodaysDate (Date/Time)
TimeNow (Date/Time)
OrderedBy (Short Text)
PreferredTime (Short Text) Note: Time for job (half hour windows) eg: 11.00 - 11.30am

tbl_OrderDetails
OrderDetailsID (PK Autonumber)
OrderID (FK Number)
OrderedItems (Short Text)

One to many relationship between OrderID in tbl_Orders and OrderID in tbl_OrderDetails
 

bob fitz

AWF VIP
Local time
Today, 09:20
Joined
May 23, 2011
Messages
4,717
Emma

1) Did you try the code that I gave you in my last post and if so, does it meet your requirement.

2)Your OrderDetails table has a text field for the item ordered. This would normally be a number field which would be used to hold the Primary Key value from the Products table. This would be the foreign key.
I would make the same kind of changes to your "OrderedBy" field in the "Orders" table and create a "Personel" table.
 

Emma35

Registered User.
Local time
Today, 02:20
Joined
Sep 18, 2012
Messages
455
bob...sorry i got busy in work after my initial post so no i haven't tried it yet. I will create a form first thing in the morning and add your code and get back to you. Ok my normalization is probably not quite there yet....would it make a big difference if i just left things the way they are or should i really make Products and Personnel tables ?
 

bob fitz

AWF VIP
Local time
Today, 09:20
Joined
May 23, 2011
Messages
4,717
bob...sorry i got busy in work after my initial post so no i haven't tried it yet. I will create a form first thing in the morning and add your code and get back to you. Ok my normalization is probably not quite there yet....would it make a big difference if i just left things the way they are or should i really make Products and Personnel tables ?
IMHO Simple answer is yes. I wouldn't go further without the required tables. You will need them and then any queries and forms created would need to be redesigned.
Let us know if you need help with the table designs.
 

bob fitz

AWF VIP
Local time
Today, 09:20
Joined
May 23, 2011
Messages
4,717
Emma
Having told you in my last post that you shouldn't proceed with the development, I have not taken my own advice :eek:

I have no real knowledge of what your application needs to do but (yes there's always a but) I was at a bit of a loose end this afternoon, so I've played around a little. I have attached a db which, if nothing else, should help you with some understanding of other tables which I thought you would need. Without a better understanding of what your business requirement is, I can't comment on what other tables may be needed.

Have a look at it and see if it helps at all. Ask questions if you have any. Hope this helps :).
 

Attachments

  • Orders01Bob.zip
    60 KB · Views: 90

Pat Hartman

Super Moderator
Staff member
Local time
Today, 05:20
Joined
Feb 19, 2002
Messages
42,981
If Me.NewRecord And Time() > "10:00" Then

I would make this statement more specific in case you are not using a 24 hour clock..

If Me.NewRecord And Time() > "10:00 AM" Then
 

Emma35

Registered User.
Local time
Today, 02:20
Joined
Sep 18, 2012
Messages
455
I've just had a look at your database bob and J***s you don't mess about even when you're 'at a loose end'. I could spend a month trying to get something to look and work like that and still not achieve it !. I'm going to have a close look at your table structure and see if i can improve mine. Thanks for putting your time into this for me.
The code you provided in the BeforeUpdate event works up to a point in that it prevents a user from making an order after 10am, however if i change the date to tomorrow, it still won't allow me to make an order. Also, if i go to close the form after being told i can't make an order, the pop up message appears again and again until i eventually get the 'You can't save the record at this time' message.. Is it possible to prevent that from happening as i'm trying to keep things as simple as possible and encourage people to use the system ?
Thanks again

Pat...thanks for the suggestion, i'll make the adjustment to the code
 

bob fitz

AWF VIP
Local time
Today, 09:20
Joined
May 23, 2011
Messages
4,717
Emma
Thank you for your kind words.
Copy/Paste the following code in the Before Update event of the form called "frmOrders" in the db I posted and then test it
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord And Time() > "10:00 AM" And Me.OrdDate <= Date Then
        MsgBox "Bookings can NOT be make after 10:00" & vbCrLf & _
            "Please book for tomorrow.", , "Booking Cancelled"
        Cancel = True
 
    Else
        MsgBox "Thank you.", , "Booking Confirmed"
    End If

End Sub
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:20
Joined
May 7, 2009
Messages
19,170
Time() function returns a Real Time, so you need to Delimit it with #, eg:

Time() > #10:00 AM#
 

bob fitz

AWF VIP
Local time
Today, 09:20
Joined
May 23, 2011
Messages
4,717
Time() function returns a Real Time, so you need to Delimit it with #, eg:

Time() > #10:00 AM#
:unsure: Seems to work in the attached db without "#"
 

Attachments

  • Orders02Bob.zip
    89.4 KB · Views: 106

Emma35

Registered User.
Local time
Today, 02:20
Joined
Sep 18, 2012
Messages
455
Thanks bob it seems to have solved the problem. I'm attaching my database (it looks like nothing at the moment). I have a form and a subform for adding the order items. It's not the same as yours so i'm getting the 'Order Confirmed' message before i even select the items i need ?. I haven't followed any of your suggestions for changes to the tables etc as yet (but i will).....but i might have the code in the wrong place on my own version.
 

Attachments

  • Test1.zip
    38.7 KB · Views: 90

bob fitz

AWF VIP
Local time
Today, 09:20
Joined
May 23, 2011
Messages
4,717
.... i'm getting the 'Order Confirmed' message before i even select the items i need ?. ....
Yes. Same in the db that I gave you.
You have a one to many relationship between the Orders table and the OrderDetails table. You can't have a record in the Detail table until you have a record in the Order table to relate it to.
 

Emma35

Registered User.
Local time
Today, 02:20
Joined
Sep 18, 2012
Messages
455
Could i put the code into the OnClick event of a Save button on the main form or something ?
 

bob fitz

AWF VIP
Local time
Today, 09:20
Joined
May 23, 2011
Messages
4,717
Could i put the code into the OnClick event of a Save button on the main form or something ?
I would strongly advise you against. IMHO A forms BeforeUpdate event is the best place for data validation because it's the event that must run before a record is saved.

Also, a "Save" button is superfluous, since records are saved by many options e.g. moving to another record, closing a form, moving the focus off the form. With that knowledge, why would you want one? ;)
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:20
Joined
May 7, 2009
Messages
19,170
Having a save button is clear of the intent, to save the record.
If you have a relationship, and your form/subform was created by the Wizard, moving to the subform, will not Automatically save the New record of the Parent form.
 

Emma35

Registered User.
Local time
Today, 02:20
Joined
Sep 18, 2012
Messages
455
Ok was just thinking out loud. I always found that if i didn't include a save button, people would always ask for one. I know they're unnecessary.

If i switched the code to the BeforeUpdate event of the subform maybe ?
 

Rene vK

Member
Local time
Today, 10:20
Joined
Mar 3, 2013
Messages
123
Just my penny.... Isn't it much easier to have all choices in a single (unbound)form? Like use a non-visible combo / listbox that becomes visible after the timecheck? I think this is a very complicated way to get a result.
 

Users who are viewing this thread

Top Bottom