I have a form that asks the user for input before showing a data entry
form
1 User selects Name
2 User selects Time - this is a drop down list from a table named time
3 User selects a section number
I assume this is a dropdown as well?
A subform populates and the user enters open or closed and a number
between 1- 10
What if the user does not make a selection from either Name or Time? Have
you included code to handle this situation?
This works however I have found that a user can select the same time
more than once in a 24hr period and the total is added to an existing
record. I need to create a query and place it in the before update of
my drop down box that doses the following.
Looks up the history table - looks at the section ID - looks at the
time & date and displays a message box that says the record already
exists select another time.
My first impulse was to ask why a unique index on section ID, time and date
wouldn't solve this problem for you. Do you have that index created now?
What are the data types of the time and date fields? Text? Number? Or
Date/Time?
PS. I hope you haven't made the mistake of actually calling these fields
"name", "date" and "time". These are reserved keywords, being the names of
VBA functions (Date() and Time() ) and a very prevalent property. Using
keywords for object names can cause problems that are extremely hard to
debug. If those are not the real names of the fields, help us out and tell
us the actual names.
It seems to me that the population of the subform seems to be the problem.
It sounds as if it is unbound - otherwise, wouldn't it populate with the
existing record when the user makes his selections in the main form? Instead
of just putting the selections into the unbound text boxes in the subform,
you should consider two alternatives:
1. bind the subform to the history table (I would think about using a
continuous form, or even a table grid here) and use the user's selections to
filter the data displayed. if no records meet the filter criteria, it
should present blank textboxes for adding a new record, without additional
code. I believe a form can be set to only allow new records to be created,
and to be nonupdateable otherwise. So, in the onclick event of the button
the user uses to finalize his selections (or the onchage event of the final
dropdown), put the code to turn the filter on in the subform and set the
filter property. There are several examples of this in online help, but we
cannot write it for you without some details about the data types.
2. If you want to continue using the unbound form, then do something similar
to the above. In the onclick event, query the history table and, if results
are returned, either display the message using MsgBox and either populate
the unbound textboxes with the existing data, setting them to readonly, or
simply set them to readonly without populating them. Your choice. If no data
is retrieved, set them to read-write and the process continues as before.
This can be complicated if this is a multi-user application. Nothing
prevents userB from entering information for the same date, time and section
while UserA is still entering his. This is where the unique index I
suggested above will help. Access will automatically raise an error if a
unique index is violated.
