Access Sequential Dates

Question

I am trying to get access to auto date an order entry. So 1/1/14 is a Monday and they are selling someone hot dog that day. 1/2/14 is Tuesday and they are selling them one Hamburger. Each day of the week is always assigned a specific food. People submit preorders and we have to enter the data into the database before it is served. I have a subtable for each order content and when adding them I want Access to automatically put the next date so I don't have to type in the 1/1/14, 1/2/14, etc.

Also it would be great if access automatically did not use weekend dates.

I am not able to code at all, and could not find any information on this so it might not even be possible.

Thanks in advance!

Answer 1

Private Sub Form_AfterInsert()

If Weekday([Food per Order.Serve Date], vbMonday) < 5 Then

    [Food per Order.Serve Date].DefaultValue = """" & DateAdd("d", 1, [Food per Order.Serve Date]) & """"

Else

    [Food per Order.Serve Date].DefaultValue = """" & DateAdd("d", 3, [Food per Order.Serve Date]) & """"

End If

End Sub

This is what I have in the code builder. It isn't quite working. Any time I try to change something it highlights one of the lines yellow and comes up with an error.

Answer 2

If you are unfamiliar with entering code into a form's or control's event procedures, this is how it's done in form design view:

1.  Select the form or control as appropriate and open its properties sheet if it's not already open.

2.  Select the relevant event property and select the 'build' button (the one on the right with 3 dots).

3.  Select Code Builder in the dialogue and click OK.  This step won't be necessary if you've set up Access to use event procedures by default.

4.  The VBA editor window will open at the event procedure with the first and last lines already in place.  Enter or paste in the code as new lines between these.

Answer 3

What we gave you was VBA code not macros. You shouldn't have clicked on Data Macros, but opened your subform in design mode and used the After Insert event.

Answer 4

I put in the formula with the quotes and it didn't quite work. I think I might not be entering it right. From the design view I clicked Create Data Macros and then I clicked on After Insert.

I think I might not be using the correct Actions. What is the way I need to be doing it?

Thanks!

Answer 5

Note that the AfterInsert event procedure in which the code goes is the subform's event procedure, not the parent form's.  This means that the subform must be a form object, not simply the related table.  The form object can be in datasheet view, however.  You can delete the table subform and replace it with a new subform control by using the control wizard, selecting subform/subreport from the Controls area of the Design ribbon with the parent form in design view.