Create "One" Record from the "Many" Table

I have a Relationship between two tables. TableOne holds the client data. 
TableMany holds the InvoiceData. Referential Integrity enforced.

InvoiceForm based on a query with data from the two tables, linked by 
ClientNumber from TableMany.

When an invoice is created, the user enters ClientNumber and the client 
details appear. My question is about creating an invoice for a client not in 
TableOne. Is there some way to have the Client Record in TableOne created if 
it does not already exist, without opening the Client Data Input Form? Just 
the ClientNumber Field needs to be populated, all other Fields are on the 
Invoice Form (from TableOne) and can be filled in if the ClientNumber in the 
TableOne exists.


0
Utf
11/6/2007 5:38:01 AM
access 16762 articles. 3 followers. Follow

5 Replies
709 Views

Similar Articles

[PageSpeed] 46

include the ClientNumber field from TableOne (the primary key, i presume?)
in the query bound to InvoiceForm.

hth


"Tom Ventouris" <TomVentouris@discussions.microsoft.com> wrote in message
news:4D09DF30-C87E-45A4-957C-5E4393EE030F@microsoft.com...
> I have a Relationship between two tables. TableOne holds the client data.
> TableMany holds the InvoiceData. Referential Integrity enforced.
>
> InvoiceForm based on a query with data from the two tables, linked by
> ClientNumber from TableMany.
>
> When an invoice is created, the user enters ClientNumber and the client
> details appear. My question is about creating an invoice for a client not
in
> TableOne. Is there some way to have the Client Record in TableOne created
if
> it does not already exist, without opening the Client Data Input Form?
Just
> the ClientNumber Field needs to be populated, all other Fields are on the
> Invoice Form (from TableOne) and can be filled in if the ClientNumber in
the
> TableOne exists.
>
>


0
tina
11/6/2007 6:20:42 AM
Thanks Tina.

It is already there. I still get the error that the system cannot find a 
matching record in TableOne.

"tina" wrote:

> include the ClientNumber field from TableOne (the primary key, i presume?)
> in the query bound to InvoiceForm.
> 
> hth
> 
> 
> "Tom Ventouris" <TomVentouris@discussions.microsoft.com> wrote in message
> news:4D09DF30-C87E-45A4-957C-5E4393EE030F@microsoft.com...
> > I have a Relationship between two tables. TableOne holds the client data.
> > TableMany holds the InvoiceData. Referential Integrity enforced.
> >
> > InvoiceForm based on a query with data from the two tables, linked by
> > ClientNumber from TableMany.
> >
> > When an invoice is created, the user enters ClientNumber and the client
> > details appear. My question is about creating an invoice for a client not
> in
> > TableOne. Is there some way to have the Client Record in TableOne created
> if
> > it does not already exist, without opening the Client Data Input Form?
> Just
> > the ClientNumber Field needs to be populated, all other Fields are on the
> > Invoice Form (from TableOne) and can be filled in if the ClientNumber in
> the
> > TableOne exists.
> >
> >
> 
> 
> 
0
Utf
11/6/2007 6:43:00 AM
On Mon, 5 Nov 2007 21:38:01 -0800, Tom Ventouris
<TomVentouris@discussions.microsoft.com> wrote:

>I have a Relationship between two tables. TableOne holds the client data. 
>TableMany holds the InvoiceData. Referential Integrity enforced.
>
>InvoiceForm based on a query with data from the two tables, linked by 
>ClientNumber from TableMany.
>
>When an invoice is created, the user enters ClientNumber and the client 
>details appear. My question is about creating an invoice for a client not in 
>TableOne. Is there some way to have the Client Record in TableOne created if 
>it does not already exist, without opening the Client Data Input Form? Just 
>the ClientNumber Field needs to be populated, all other Fields are on the 
>Invoice Form (from TableOne) and can be filled in if the ClientNumber in the 
>TableOne exists.
>

By far the simplest way to do this is to *NOT* base your Invoice Form on a
query joining the two tables; instead, use your Client Data Input Form with a
Subform based on the invoice table. Use the ClientID as the master/child link
field to automatically populate the client number, and put a combo box or
other appropriate search tool on the master form to find the desired client
record (or jump to the new record if it's a new client).

             John W. Vinson [MVP]
0
John
11/6/2007 6:55:07 AM
Thnaks

"John W. Vinson" wrote:

> On Mon, 5 Nov 2007 21:38:01 -0800, Tom Ventouris
> <TomVentouris@discussions.microsoft.com> wrote:
> 
> >I have a Relationship between two tables. TableOne holds the client data. 
> >TableMany holds the InvoiceData. Referential Integrity enforced.
> >
> >InvoiceForm based on a query with data from the two tables, linked by 
> >ClientNumber from TableMany.
> >
> >When an invoice is created, the user enters ClientNumber and the client 
> >details appear. My question is about creating an invoice for a client not in 
> >TableOne. Is there some way to have the Client Record in TableOne created if 
> >it does not already exist, without opening the Client Data Input Form? Just 
> >the ClientNumber Field needs to be populated, all other Fields are on the 
> >Invoice Form (from TableOne) and can be filled in if the ClientNumber in the 
> >TableOne exists.
> >
> 
> By far the simplest way to do this is to *NOT* base your Invoice Form on a
> query joining the two tables; instead, use your Client Data Input Form with a
> Subform based on the invoice table. Use the ClientID as the master/child link
> field to automatically populate the client number, and put a combo box or
> other appropriate search tool on the master form to find the desired client
> record (or jump to the new record if it's a new client).
> 
>              John W. Vinson [MVP]
> 
0
Utf
11/6/2007 1:40:00 PM
"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> 
???????:ar30j3dgcgkmm9ffo52g8irmv9m4ajcl13@4ax.com...
> On Mon, 5 Nov 2007 21:38:01 -0800, Tom Ventouris
> <TomVentouris@discussions.microsoft.com> wrote:
>
>>I have a Relationship between two tables. TableOne holds the client data.
>>TableMany holds the InvoiceData. Referential Integrity enforced.
>>
>>InvoiceForm based on a query with data from the two tables, linked by
>>ClientNumber from TableMany.
>>
>>When an invoice is created, the user enters ClientNumber and the client
>>details appear. My question is about creating an invoice for a client not 
>>in
>>TableOne. Is there some way to have the Client Record in TableOne created 
>>if
>>it does not already exist, without opening the Client Data Input Form? 
>>Just
>>the ClientNumber Field needs to be populated, all other Fields are on the
>>Invoice Form (from TableOne) and can be filled in if the ClientNumber in 
>>the
>>TableOne exists.
>>
>
> By far the simplest way to do this is to *NOT* base your Invoice Form on a
> query joining the two tables; instead, use your Client Data Input Form 
> with a
> Subform based on the invoice table. Use the ClientID as the master/child 
> link
> field to automatically populate the client number, and put a combo box or
> other appropriate search tool on the master form to find the desired 
> client
> record (or jump to the new record if it's a new client).
>
>             John W. Vinson [MVP] 


0
koko11222
11/16/2007 9:38:06 PM
Reply:

Similar Artilces: