Relationship problem when adding records

[Ron]

Hi All,

Using Access2000, winXP.

Table 1 = tblClients displayed on frmClients via qryClients. 2nd table =
tblInvoices shown on frmInvoices via qryInvoices. 2nd table = tblDetails
shown on subform(to frmInvoices) sfrmDetails via qryDetails.

Relationship built between tblClients/tblInvoices/tblDetails by ClientID.
Relationship between tblInvoices/tblDetails by InvoiceID. All works fine if
I input data into frmInvoice first and then sfrmDetails after. But, if I
take the defaults of frmInvoice and just want to put in data on sfrmDetails
first, everything freezes. Nothing gets added. InvoiceID and DetailID get
incremented, but never show up anywhere. I think it's probably a
relationship problem. tblInvoices doesn't get a new record prior to the prg
trying to store the new record into tblDetails.

What am I doing wrong? How can I get the tblInvoice table to add a record
prior to the first record being added by the subform for tblDetail table so
it's available for storing that InvoiceID to tblDetail?

Thanks in advance for any suggestions.
ron

[salad]

Re: Relationship problem when adding records

Ron wrote:

Hi All,
>
Using Access2000, winXP.
>
Table 1 = tblClients displayed on frmClients via qryClients. 2nd table =
tblInvoices shown on frmInvoices via qryInvoices. 2nd table = tblDetails
shown on subform(to frmInvoices) sfrmDetails via qryDetails.
>
Relationship built between tblClients/tblInvoices/tblDetails by ClientID.
Relationship between tblInvoices/tblDetails by InvoiceID. All works fine if
I input data into frmInvoice first and then sfrmDetails after. But, if I
take the defaults of frmInvoice and just want to put in data on sfrmDetails
first, everything freezes. Nothing gets added. InvoiceID and DetailID get
incremented, but never show up anywhere. I think it's probably a
relationship problem. tblInvoices doesn't get a new record prior to the prg
trying to store the new record into tblDetails.
>
What am I doing wrong? How can I get the tblInvoice table to add a record
prior to the first record being added by the subform for tblDetail table so
it's available for storing that InvoiceID to tblDetail?
>
Thanks in advance for any suggestions.
ron
>
>

Well, if the query expects a client, but there is no client ID, that may
be a problem. You might want to change the query. Open up the query in
the query builder, dbl-click on the relationship line between Invoice
and Client, and select the option for All Invoices and those in Clients
that match.

[Ron]

Re: Relationship problem when adding records

"salad" <oil@vinegar.co mwrote in message
news:KJMAg.3487 $xp2.1232@newsr ead1.news.pas.e arthlink.net...

Ron wrote:
>

>Hi All,
>>
>Using Access2000, winXP.
>>
>Table 1 = tblClients displayed on frmClients via qryClients. 2nd table =
>tblInvoices shown on frmInvoices via qryInvoices. 2nd table = tblDetails
>shown on subform(to frmInvoices) sfrmDetails via qryDetails.
>>
>Relationship built between tblClients/tblInvoices/tblDetails by ClientID.
>Relationship between tblInvoices/tblDetails by InvoiceID. All works fine
>if I input data into frmInvoice first and then sfrmDetails after. But,
>if I take the defaults of frmInvoice and just want to put in data on
>sfrmDetails first, everything freezes. Nothing gets added. InvoiceID
>and DetailID get incremented, but never show up anywhere. I think it's
>probably a relationship problem. tblInvoices doesn't get a new record
>prior to the prg trying to store the new record into tblDetails.
>>
>What am I doing wrong? How can I get the tblInvoice table to add a
>record prior to the first record being added by the subform for tblDetail
>table so it's available for storing that InvoiceID to tblDetail?
>>
>Thanks in advance for any suggestions.
>ron

Well, if the query expects a client, but there is no client ID, that may
be a problem. You might want to change the query. Open up the query in
the query builder, dbl-click on the relationship line between Invoice and
Client, and select the option for All Invoices and those in Clients that
match.

Thanks for the quick response.

The query for the detail subform is just on the tbldetail. No link between
the Invoice and Detail tables in the query itself--the relationships are all
set by Tools/Relationships. For criteria on the qryDetail I've got
[forms]![frmInvoice]![InvoiceID]. Take that out and I of course get all
the details in the entire table listed on this one new Invoice (which
doesn't have a number yet since it's not really saved).

What I think I need to do is add an invoice so it's got an InvoiceID prior
to that first detail being added...but how do I do that if my user doesn't
change anything on the frmInvoice?

[Larry Linson]

Re: Relationship problem when adding records

Umm. That's what the "referentia l integrity" option of relationships does,
in part,... protect you from inadvertently entering "orphan" records in the
many side of the relationship that do not have a "parent" on the one side.
It is usually considered a help, not a "problem." If you enter a batch of
child records, how do you later plan to make the connection to the parent
records when/if they are added; and how do you plan to assure that a parent
is, in fact, added for each "orphan child?"

Larry Linson
Microsoft Access MVP


"Ron" <ronSPAMBLOCKER west777@earthli nk.comwrote in message
news:emNAg.7952 $gF6.5939@newsr ead2.news.pas.e arthlink.net...

"salad" <oil@vinegar.co mwrote in message
news:KJMAg.3487 $xp2.1232@newsr ead1.news.pas.e arthlink.net...

>Ron wrote:
>>

>>Hi All,
>>>
>>Using Access2000, winXP.
>>>
>>Table 1 = tblClients displayed on frmClients via qryClients. 2nd table
>>= tblInvoices shown on frmInvoices via qryInvoices. 2nd table =
>>tblDetails shown on subform(to frmInvoices) sfrmDetails via qryDetails.
>>>
>>Relationshi p built between tblClients/tblInvoices/tblDetails by
>>ClientID. Relationship between tblInvoices/tblDetails by InvoiceID. All
>>works fine if I input data into frmInvoice first and then sfrmDetails
>>after. But, if I take the defaults of frmInvoice and just want to put
>>in data on sfrmDetails first, everything freezes. Nothing gets added.
>>InvoiceID and DetailID get incremented, but never show up anywhere. I
>>think it's probably a relationship problem. tblInvoices doesn't get a
>>new record prior to the prg trying to store the new record into
>>tblDetails.
>>>
>>What am I doing wrong? How can I get the tblInvoice table to add a
>>record prior to the first record being added by the subform for
>>tblDetail table so it's available for storing that InvoiceID to
>>tblDetail?
>>>
>>Thanks in advance for any suggestions.
>>ron

>Well, if the query expects a client, but there is no client ID, that may
>be a problem. You might want to change the query. Open up the query in
>the query builder, dbl-click on the relationship line between Invoice and
>Client, and select the option for All Invoices and those in Clients that
>match.

>
Thanks for the quick response.
>
The query for the detail subform is just on the tbldetail. No link
between the Invoice and Detail tables in the query itself--the
relationships are all set by Tools/Relationships. For criteria on the
qryDetail I've got [forms]![frmInvoice]![InvoiceID]. Take that out and I
of course get all the details in the entire table listed on this one new
Invoice (which doesn't have a number yet since it's not really saved).
>
What I think I need to do is add an invoice so it's got an InvoiceID prior
to that first detail being added...but how do I do that if my user doesn't
change anything on the frmInvoice?
>

[Ron]

Re: Relationship problem when adding records

Possibly I'm not being clear. I don't WANT to enter child records without
first entering the parent record. However, I can't get the parent record to
be saved if I have "referentia l integrity" checked.

Again, my problem is that I'm TRYING to add a parent record. But, my form
for the parent record has certain defaults for the parent(Invoice) that
pretty much fill out the necessary stuff for the parent. If my operators
don't change something (or add something, etc--in other words, activate the
insert for that parent record) on the parent form, and just want to add
details to it, it won't store anything at all. Freezes. Can't continue. I
know what the problem is...the parent record hasn't been "saved" so it
doesn't have an invoice number yet, so the child records (input on the
detail subform) can utilize that parent record's InvoiceID. That's what I'm
trying to figure out how to do. How can I arrange for the parent to be
saved so that the child records (ie, details) can utilize that number and I
can HAVE "referentia l integrity" without having to edit/change something on
the invoice form?

ron

"Larry Linson" <bouncer@localh ost.notwrote in message
news:_%UAg.236$ 7m5.32@trnddc05 ...

Umm. That's what the "referentia l integrity" option of relationships does,
in part,... protect you from inadvertently entering "orphan" records in
the many side of the relationship that do not have a "parent" on the one
side. It is usually considered a help, not a "problem." If you enter a
batch of child records, how do you later plan to make the connection to
the parent records when/if they are added; and how do you plan to assure
that a parent is, in fact, added for each "orphan child?"
>
Larry Linson
Microsoft Access MVP
>
>
"Ron" <ronSPAMBLOCKER west777@earthli nk.comwrote in message
news:emNAg.7952 $gF6.5939@newsr ead2.news.pas.e arthlink.net...

>"salad" <oil@vinegar.co mwrote in message
>news:KJMAg.348 7$xp2.1232@news read1.news.pas. earthlink.net.. .

>>Ron wrote:
>>>
>>>Hi All,
>>>>
>>>Using Access2000, winXP.
>>>>
>>>Table 1 = tblClients displayed on frmClients via qryClients. 2nd table
>>>= tblInvoices shown on frmInvoices via qryInvoices. 2nd table =
>>>tblDetails shown on subform(to frmInvoices) sfrmDetails via qryDetails.
>>>>
>>>Relationsh ip built between tblClients/tblInvoices/tblDetails by
>>>ClientID. Relationship between tblInvoices/tblDetails by InvoiceID.
>>>All works fine if I input data into frmInvoice first and then
>>>sfrmDetail s after. But, if I take the defaults of frmInvoice and just
>>>want to put in data on sfrmDetails first, everything freezes. Nothing
>>>gets added. InvoiceID and DetailID get incremented, but never show up
>>>anywhere. I think it's probably a relationship problem. tblInvoices
>>>doesn't get a new record prior to the prg trying to store the new
>>>record into tblDetails.
>>>>
>>>What am I doing wrong? How can I get the tblInvoice table to add a
>>>record prior to the first record being added by the subform for
>>>tblDetail table so it's available for storing that InvoiceID to
>>>tblDetail?
>>>>
>>>Thanks in advance for any suggestions.
>>>ron
>>Well, if the query expects a client, but there is no client ID, that may
>>be a problem. You might want to change the query. Open up the query in
>>the query builder, dbl-click on the relationship line between Invoice
>>and Client, and select the option for All Invoices and those in Clients
>>that match.

>>
>Thanks for the quick response.
>>
>The query for the detail subform is just on the tbldetail. No link
>between the Invoice and Detail tables in the query itself--the
>relationship s are all set by Tools/Relationships. For criteria on the
>qryDetail I've got [forms]![frmInvoice]![InvoiceID]. Take that out and
>I of course get all the details in the entire table listed on this one
>new Invoice (which doesn't have a number yet since it's not really
>saved).
>>
>What I think I need to do is add an invoice so it's got an InvoiceID
>prior to that first detail being added...but how do I do that if my user
>doesn't change anything on the frmInvoice?
>>

>
>

[Rick Brandt]

Re: Relationship problem when adding records

Ron wrote:

Possibly I'm not being clear. I don't WANT to enter child records
without first entering the parent record. However, I can't get the
parent record to be saved if I have "referentia l integrity" checked.
>
Again, my problem is that I'm TRYING to add a parent record. But, my
form for the parent record has certain defaults for the
parent(Invoice) that pretty much fill out the necessary stuff for the
parent. If my operators don't change something (or add something,
etc--in other words, activate the insert for that parent record) on
the parent form, and just want to add details to it, it won't store
anything at all. Freezes. Can't continue. I know what the problem
is...the parent record hasn't been "saved" so it doesn't have an
invoice number yet, so the child records (input on the detail
subform) can utilize that parent record's InvoiceID. That's what I'm
trying to figure out how to do. How can I arrange for the parent to
be saved so that the child records (ie, details) can utilize that
number and I can HAVE "referentia l integrity" without having to
edit/change something on the invoice form?

Your users need to enter at least one field in the main form. Set the subform's
visible property to No and use the BeforeInsert event of the main form to make
it visible. As soon as they make a keystroke in the main form the subform will
become visible and since they dirtied the main form it will save when they move
to the subform.

For existing records you wll also need the following in your main form Current
event...

Me.SubformContr olName.Visible = Not Me.NewRecord

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

[David W. Fenton]

Re: Relationship problem when adding records

"Rick Brandt" <rickbrandt2@ho tmail.comwrote in
news:LB4Bg.1311 $1f6.350@newssv r27.news.prodig y.net:

For existing records you wll also need the following in your main
form Current event...
>
Me.SubformContr olName.Visible = Not Me.NewRecord

I would use Me!SubformContr olName.Enabled instead, since I think
users would find it disconcerting to not be able to see the subform.
On the other hand, then they might get confused about why they can't
enter data, so you'd end up having to create a conditional error
message. I think it's more user-friendly to show the subform and do
the extra work to create the conditional error message, but I can
see why going the easy route would be preferred by some.

You could also use same code in the parent form's OnInsert event to
reveal/enable the subform.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[Ron]

Re: Relationship problem when adding records

Thanks for the responses, Rick and David. I ended up using .enabled instead
of .visible. Seems less upsetting to the input people than not letting them
see the subforms (there are 2, actually) at all.

Anyway, thought I'd just let you know the problem was solved by your
suggestions. Again, thanks for spending your time, and donating your
knowledge as you do here. I've found this NG to be invaluable! Maybe some
day, I can be good enough at all this to attempt to help someone as well.

Thanks!
ron

"David W. Fenton" <XXXusenet@dfen ton.com.invalid wrote in message
news:Xns9816CB6 CF7D11f99a49ed1 d0c49c5bbb2@127 .0.0.1...

"Rick Brandt" <rickbrandt2@ho tmail.comwrote in
news:LB4Bg.1311 $1f6.350@newssv r27.news.prodig y.net:
>

>For existing records you wll also need the following in your main
>form Current event...
>>
>Me.SubformCont rolName.Visible = Not Me.NewRecord

>
I would use Me!SubformContr olName.Enabled instead, since I think
users would find it disconcerting to not be able to see the subform.
On the other hand, then they might get confused about why they can't
enter data, so you'd end up having to create a conditional error
message. I think it's more user-friendly to show the subform and do
the extra work to create the conditional error message, but I can
see why going the easy route would be preferred by some.
>
You could also use same code in the parent form's OnInsert event to
reveal/enable the subform.
>
--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/