By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,214 Members | 2,099 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,214 IT Pros & Developers. It's quick & easy.

Relationship problem when adding records

P: n/a
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
Aug 4 '06 #1
Share this Question
Share on Google+
7 Replies


P: n/a
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.
Aug 4 '06 #2

P: n/a
Ron
"salad" <oi*@vinegar.comwrote in message
news:KJ*****************@newsread1.news.pas.earthl ink.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?
Aug 4 '06 #3

P: n/a
Umm. That's what the "referential 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" <ro*******************@earthlink.comwrote in message
news:em*****************@newsread2.news.pas.earthl ink.net...
"salad" <oi*@vinegar.comwrote in message
news:KJ*****************@newsread1.news.pas.earthl ink.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?

Aug 5 '06 #4

P: n/a
Ron
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 "referential 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 "referential integrity" without having to edit/change something on
the invoice form?

ron

"Larry Linson" <bo*****@localhost.notwrote in message
news:_%UAg.236$7m5.32@trnddc05...
Umm. That's what the "referential 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" <ro*******************@earthlink.comwrote in message
news:em*****************@newsread2.news.pas.earthl ink.net...
>"salad" <oi*@vinegar.comwrote in message
news:KJ*****************@newsread1.news.pas.earth link.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?


Aug 5 '06 #5

P: n/a
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 "referential 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 "referential 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.SubformControlName.Visible = Not Me.NewRecord

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com
Aug 5 '06 #6

P: n/a
"Rick Brandt" <ri*********@hotmail.comwrote in
news:LB****************@newssvr27.news.prodigy.net :
For existing records you wll also need the following in your main
form Current event...

Me.SubformControlName.Visible = Not Me.NewRecord
I would use Me!SubformControlName.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/
Aug 5 '06 #7

P: n/a
Ron
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" <XX*******@dfenton.com.invalidwrote in message
news:Xn**********************************@127.0.0. 1...
"Rick Brandt" <ri*********@hotmail.comwrote in
news:LB****************@newssvr27.news.prodigy.net :
>For existing records you wll also need the following in your main
form Current event...

Me.SubformControlName.Visible = Not Me.NewRecord

I would use Me!SubformControlName.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/

Aug 9 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.