FORCE an entry into a subform

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

I created a database that keeps track of phone calls at a
small help desk, I have form that puts in the caller, etc
and then a subform that tracks the history of the call,
because a call can potentially he handled by more than one
technician.

Here is my concern.... it works nice....
but it's possible for a user to enter something into the
form without entering their info into the subform (which I
Don't want to happen)

I want to ensure that when a new record is created in the
parent form, the subform has a minimum of one record.

although I am hardly an expert, I do know some VBA. any
ideas as to how I can enforce this? I know of
GoToControl, which brings them to the appropriate field,
but it doesn't force at least one entry. I also tried
the "required" property, which also does not work.

Any ideas would be appreciated.

Thanks!
Don
 
May be many ways to do this, but an initial, easy one would be to have
default values for at least one field in the child table. When your user is
taken to the subform's new record, that default value will be put into the
field(s); when the user leaves the subform, the record will be saved.
 
You may use the function of DCOUNT to check whether the foreign table contains any record of the main tabl
let's say main table is
Foreign table is

Before_Update_Even
dim x as intege
x = Dcount("Fieldname","B","UniqueID") 'UniqueID is an unique number to identity each single recor
if x = 0 the
cancel = tru
end i

MCP - Access and SQL Serve
----- Don wrote: ----

I created a database that keeps track of phone calls at a
small help desk, I have form that puts in the caller, etc
and then a subform that tracks the history of the call,
because a call can potentially he handled by more than one
technician

Here is my concern.... it works nice...
but it's possible for a user to enter something into the
form without entering their info into the subform (which I
Don't want to happen

I want to ensure that when a new record is created in the
parent form, the subform has a minimum of one record

although I am hardly an expert, I do know some VBA. any
ideas as to how I can enforce this? I know of
GoToControl, which brings them to the appropriate field,
but it doesn't force at least one entry. I also tried
the "required" property, which also does not work

Any ideas would be appreciated

Thanks
Do
 
What is it that the user must enter into the subform's record? A specific
value in a specific field? Anything at all? What are you using for a primary
key? To give you some other ideas, it'll help to know what you want the user
to have entered.
 
Thanks Ken....

my main table (tblIssues) has IssueID as the primary key,
the child table (tblComments) has CommentID as the primary
key with IssueID has the foreign key.

In the Subform I have TechID, and Date populated
automatically using the BeforeInsert Event. CommentID and
IssueID are hidden of course. (so these only get
populated when a user presses a key on the subform).

Comment is the third field where I want to force a user to
enter something if a ticket is created in the master
table. Essentially, I don't want a user to exit or
navigate to another record without forcing them to enter a
minimum of one comment.

Am I making sense?
 
I take it that you're using default values in some of the subform's controls
to get the date, etc. Thus, I'm guessing that you're getting a child record
created ok, but that the comments field isn't always getting a value. Do you
assume that the user will navigate to the subform, or do you take the user
there by programmatically setting the focus into the subform?

My first suggestion generically would be this: In whichever action occurs
to create/save a new parent record, use code to move the focus to the
control in the subform that is bound to that comment field. Then put code on
the BeforeUpdate event of that control that won't let the person leave the
control until something has been entered into the control (you could check
that the length of the control's value concatenated with the "" (empty
string) value is greater than zero to let the user leave the control),
telling the user this message if he/she tries to do that.

However, be ready for a frustrated user who may just "turn off" the form by
closing it or by closing the database...which can lead to all kinds of
problems.

Another way you can do this would be a bit indirect. On the event that saves
the parent record, you could display an input box that asks for a comment;
then you could write that comment to the desired field. A bit more obvious
as to what you want to do, but it adds extra steps that don't necessarily
add much "panache" to your database.

A third way that also may frustrate a user who isn't familar with the
navigation and requirements of your form would be to put code on the
subform's BeforeUpdate event that tests for the contents of the control that
is bound to the comments field (similarly to what I put in my first
suggestion), and, if it's empty, cancel the update event and put the cursor
into that control and tell the user that he/she must enter a comment.

Hope these give you some ideas.....
 
Back
Top