Re: Data Entry Problem - Form/Subforms

Tech-Archive recommends: Speed Up your PC by fixing your registry

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

Date: Tue, 14 Sep 2004 00:21:41 +0800

No probs, sample db available if required.

Jamie :o)

"D'Lilah" <DLilah@discussions.microsoft.com> wrote in message
news:C024DAA8-2B25-4C50-94B7-5214591FE357@microsoft.com...
> Jamie:
>
> I can't thank you enough for the help. I probably won't be able to try
> your
> suggestions for a little while, but I will definitely let you know how I
> make
> out. Once again, thanks soooo much for the guidance.
>
> "Jamie Richards" wrote:
>
>> 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 ... > One, tblClientID, is strictly to assign a client ID to each client. ... >> It sounds as if you have a relationship constraint between tblClientID ...
    (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)