Re: Data Entry Problem - Form/Subforms

From: Jamie Richards (junkmail_at_westnet.com.au)
Date: 09/13/04 

Date: Mon, 13 Sep 2004 22:49:33 +0800

Hi again,

Sorry, but this will be a little lengthy, but my wife is sleeping so I have
the time ;o)

Looking at the structure of your two tables I would be inclined to put
everything into one, with an extra field that links parents and children
together. This table would look something like:

Name:
tblClients

Fields:
ClientID, ClientGroupID, LName, FName, MName, Deceased

Data would look like this:

ClientID: 101 (this is just a random number I picked, use
whatever you're using)
ClientGroupID: 10 (same again here...)
LName: Bloggs
FName: Jane
MName: Sarah
Deceased: False

If Jane had a daughter, you would enter all of the details particular to the
daughter, only the ClientGroupID would be the same as Jane's (as they belong
to the same family). In this way, you can add family members ad infinitum
if you want, without needing another table.

When you set up the table, set the primary key o compise of both ClientID
and ClientGroupID. However, if this will create problems for you when
trying to link the Clients table to other tables in your database, use a
constraint on the ClientGroupID instead, which enforces uniqueness of
ClientID and ClientGroupID for every row. How? Use the indexes section in
table design view (the little lightening bolt icon). When viewing the
indexes ensure the ClientGrooupID field is visible. In the "Index Name"
column call it "idxClientGroupID". Set the properties of the index in the
lower part of the window to; Primary: No, Unique: Yes, Ignore Nulls: No.
Your table will now force you to have a unique ClientID and ClientGroupID
for every row.

Modify/re-create your form to run off the single table. This should get rid
of your problems and still afford you a relatively efficient means of
managing you data.

If I haven't been clear, please re-post and I'll clarify.

Jamie

"D'Lilah" <DLilah@discussions.microsoft.com> wrote in message
news:76F577A2-AB0F-448E-A338-81F4C2F11F3A@microsoft.com...
> Jamie:
>
> Thank you so much for the detailed response and explanation. Everything
> you
> have said makes sense and it's about what I suspected, but I have no idea
> how
> to fix it.
>
> First of all, I do want the DataEntry property set to yes. I have a
> different form to deal with viewing and changing existing records and this
> form is strictly for adding new records.
>
> I believe the two tables I am dealing with need to be in separate tables.
> One, tblClientID, is strictly to assign a client ID to each client. The
> reason why I have TblNames separately is because each client ID pertains
> to
> more than one individual. I am dealing with clients that are mainly
> married
> couples and sometimes their children. I have one client ID per family and
> then a separate "Client #" to identify each family member (which I need in
> order to connect documents created for each family member to them). Here
> are
> the fields in my two tables:
>
> TblClientID: ClientID, Comments
> TblNames: ClientID, Client#, LName, FName, MName, Deceased
>
> Do you believe there is a better way of handling this? I am open to any
> suggestions.
>
> If I have the table structure done properly, how would I go about forcing
> a
> refresh or executing a save command. I have tried executing a save
> command,
> but could not figure out how to do it correctly.
>
> I guess I could always put something in the Comments field and solve the
> problem, but I would prefer not to.
>
> Any help would be greatly appreciated, since I have been struggling with
> this for quite some time.
>
> "Jamie Richards" wrote:
>
>> Hi,
>>
>> It sounds as if you have a relationship constraint between tblClientID
>> and
>> the other tables which requires a record to exist in the one side of the
>> relationship (in this case tblClientID) before a related record can be
>> created in a table on the many side (e.g. tblNames).
>>
>> The reason that you are unable to skip the [not required] field called
>> Comments and go straight to your subform is because no record has been
>> created on the "one side" (tblClientID). The default value that appears
>> is
>> merely that, a default, it's presence does not signify that a new record
>> has
>> been inserted. As soon as you enter comments, the data is written to
>> tblClientID and viola, you have satisfied the relationship constraint and
>> are able to enter data into your related subforms. Am I making sense?
>>
>> I doubt that the DataEntry property of the main form has anything to do
>> with
>> this problem, however you should be aware that setting this value to yes
>> will prevent your users from viewing existing records. The DataEntry
>> property, when set to yes, tells Access to ignore existing records and
>> have
>> the form open to receive data only.
>>
>> I am not sure why you have what *seems* to be related data split into
>> separate tables, but if there are no other data items you need in your
>> tblClientID table, you could force a refresh of the main form before
>> entering data into the subform, or execute a save command so that a
>> record
>> is created for you. I personally would not do this and would (with
>> respect)
>> have a look at your tables and see if each one represents a single
>> subject
>> and it's characteristics. It might save you a bit of grief later.
>>
>> Hope all this babble somehow helps.
>>
>>
>> Jamie
>>
>>
>> "D'Lilah" <DLilah@discussions.microsoft.com> wrote in message
>> news:D3CFD754-0AB5-4E39-9601-80F7446240FF@microsoft.com...
>> >I have a main Form with Subforms which are linked by "ClientID". The
>> >record
>> > source for the Form is "TblClientID" and the Subforms are "TblNames"
>> > and
>> > "TblAddresses". "ClientID" has a default value that is calculated by
>> > adding
>> > 1 to the highest ClientID number in TblClientID
>> > (=DMax("[ClientID]","TblClientID")+1).
>> >
>> > When I open the main Form (Data Entry property is set to "Yes"), the
>> > default
>> > value in the ClientID is correct, but when I try to add a record to a
>> > Subform, I get the following error "You cannot add or change a record
>> > because
>> > a related record is required in "TblClientID".
>> >
>> > There is a "Comment" Control on the main Form as well, which is not
>> > required. However, if I enter data into the "Comment" Control, I do
>> > not
>> > get
>> > this error and am able to add records into all of the Subforms.
>> > Additionally, using the same form with the Data Entry property set to
>> > "No", I
>> > can add new records to the Subforms.
>> >
>> > Can anyone help me figure this out? I am new to code but I can
>> > struggle
>> > through with some guidance. Any help would be greatly appreciated. If
>> > more
>> > information is needed, please let me know. Thank you in advance.
>>
>>
>>

Relevant Pages

  • Re: Data Entry Problem - Form/Subforms
    ... use the command in your newsreader that replies to me only. ... Jamie ... only the ClientGroupID would be the same as Jane's (as they ... you can add family members ad ...
    (microsoft.public.access.forms)
  • Re: Data Entry Problem - Form/Subforms
    ... I have made a sample copy of this database in A2K. ... only the ClientGroupID would be the same as Jane's (as they ... you can add family members ad ...
    (microsoft.public.access.forms)
  • Re: Data Entry Problem - Form/Subforms
    ... only the ClientGroupID would be the same as Jane's (as they belong ... you can add family members ad infinitum ... I do want the DataEntry property set to yes. ... >> different form to deal with viewing and changing existing records and this ...
    (microsoft.public.access.forms)
  • Re: Data Entry Problem - Form/Subforms
    ... only the ClientGroupID would be the same as Jane's (as they ... you can add family members ad ...
    (microsoft.public.access.forms)