Re: Relationship problem when adding records

Giganews Newsgroups
Subject: Re: Relationship problem when adding records
Posted by:  Ron (ronSPAMBLOCKERwest7…@earthlink.com)
Date: Fri, 04 Aug 2006

"salad" <o…@vinegar.com> wrote in message
news:KJMAg.3487$xp2.12…@newsread1.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.
>>
>> 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?

Replies

In response to

Re: Relationship problem when adding records posted by salad on Fri, 04 Aug 2006