Solved Guided data entry (1 Viewer)

Anthony.DG

Registered User.
Local time
Today, 05:15
Joined
Oct 18, 2019
Messages
27
I have a form for customer orders and once the order is complete the user is supposed to enter the date in the "completion date" and then check the box that says complete(Y/N). Well, some people forget to enter the date and it causes some information to go missing on the sales report. I was wondering, is there a way to set it so that if someone clicks the "completed" checkbox they have to make sure that the date is entered? I dont have the foggiest on how to set that. Or would someone have another suggestion to handle the issue?
 

plog

Banishment Pending
Local time
Today, 07:15
Joined
May 11, 2011
Messages
11,613
You shouldn't store duplicate data in a database, and that's what you are effectively doing. There's no need for that checkbox--if there's a CompletionDate value then its completed, if no value then its not completed.

So, I would ditch the Completed checkbox and have the user just enter a date in CompletedDate. You could even make it simpler by having the user click a 'Completed' button which would automaticaly complete that field with today's date.
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:15
Joined
Jan 23, 2006
Messages
15,364
Does the Completed Date represent the actual calendar date when the Order is completed? If so, then I agree with plog.
Or could someone go in at a different Date and backdate the completion date? Say it is now Sept; could someone go to a record and set the Completion Date to June 30 for example.
 

conception_native_0123

Well-known member
Local time
Today, 07:15
Joined
Mar 13, 2021
Messages
1,826
You shouldn't store duplicate data in a database, and that's what you are effectively doing. There's no need for that checkbox--if there's a CompletionDate value then its completed, if no value then its not completed.
i'm not sure this is actually true. anthony, i think what you need to do is use a form event of somekind. beforeupdate perhaps? use code to stop the user from closing the form or going to the next record if a date is not entered in the box:
Code:
if isnull(me.dateField) then
   msgbox "you must enter a completion date before completing this sales order!"
   exit sub
end if
   'more code here if needed
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:15
Joined
Feb 28, 2001
Messages
27,001
The typical way to prevent someone from doing what you describe is have code in the Form_BeforeUpdate event to test for completion and if not complete, you issue a Cancel = True in that _BeforeUpdate event, which would block the update and thus block navigation and exiting.

Depending on other factors, you don't need a "Closed" flag if you also need a date. However, if there are other ways you can leave the form where the date is OK but something else isn't, then the _BeforeUpdate event is the best place to test for what you need.

As to the code? I'll have to make up some names, but you'll get the idea. In form design mode with the Event Property Sheet open, find the _BeforeUpdate slot, click on the down arrow to "Event Code" and then put in something similar to this. However, you have to use the names of your fields.

Code:
Private Sub Form_BeforeUpdate( Cancel as Integer )
    Cancel = Cancel Or Nz( [RequieredAmount], 0 ) = 0     'amount cannot be 0
    Cancel = Cancel Or NZ( [RequiredProdID], 0 ) = 0        'product cannot be 0
    Cancel = Cancel Or NZ( [CustomerName], "" ) = ""      'test for text items, too
    Cancel = Cancel Or Nz( [EntryDate], 0 ) = 0                  'entry date cannot be 0

...  (as many of these as needed)

    If Cancel Then 
        MsgBox "Form is incomplete; please check all required fields", vbOKOnly, "Cannot save record at this time."
    End If
End Sub

If your user tries to save an incomplete record, a message box will pop up and tell him/her that they aren't done yet. There are other embellishments but this is the basic idea.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 20:15
Joined
May 7, 2009
Messages
19,175
you can create a Data Macro (Before Change) on your table.
bring your table in Design view.
on the Ribbon->Table Design->Create Data Macros->Before Change.

15.09.2021_08.06.33_REC.png
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:15
Joined
Feb 19, 2002
Messages
42,981
The question is, what constitutes "complete" If there is some way to automatically determine that an order is complete, you can use the method suggested by arnel or you can set the complete date in the Form's BeforeUpdate event. If only the user knows when the order is complete, you need some indication from him that the order is complete. Use ONLY a date and NOT both a date and a flag. In the places where you need to know if an order is complete or not, check the date. If the date is not null, the order is complete. If the date is null, the order is incompleted.
 

Anthony.DG

Registered User.
Local time
Today, 05:15
Joined
Oct 18, 2019
Messages
27
The typical way to prevent someone from doing what you describe is have code in the Form_BeforeUpdate event to test for completion and if not complete, you issue a Cancel = True in that _BeforeUpdate event, which would block the update and thus block navigation and exiting.

Depending on other factors, you don't need a "Closed" flag if you also need a date. However, if there are other ways you can leave the form where the date is OK but something else isn't, then the _BeforeUpdate event is the best place to test for what you need.

As to the code? I'll have to make up some names, but you'll get the idea. In form design mode with the Event Property Sheet open, find the _BeforeUpdate slot, click on the down arrow to "Event Code" and then put in something similar to this. However, you have to use the names of your fields.

Code:
Private Sub Form_BeforeUpdate( Cancel as Integer )
    Cancel = Cancel Or Nz( [RequieredAmount], 0 ) = 0     'amount cannot be 0
    Cancel = Cancel Or NZ( [RequiredProdID], 0 ) = 0        'product cannot be 0
    Cancel = Cancel Or NZ( [CustomerName], "" ) = ""      'test for text items, too
    Cancel = Cancel Or Nz( [EntryDate], 0 ) = 0                  'entry date cannot be 0

...  (as many of these as needed)

    If Cancel Then
        MsgBox "Form is incomplete; please check all required fields", vbOKOnly, "Cannot save record at this time."
    End If
End Sub

If your user tries to save an incomplete record, a message box will pop up and tell him/her that they aren't done yet. There are other embellishments but this is the basic idea.
This is beautiful. Exactly what I needed. Thank you for the guidance everyone! HAPPY HOLIDAYS ALL!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:15
Joined
Feb 28, 2001
Messages
27,001
You are welcome. Happy Holidays and best wishes for a prosperous new year.
 

Users who are viewing this thread

Top Bottom