Multiple Field append query

  • Thread starter Thread starter Jeremy Dove
  • Start date Start date
J

Jeremy Dove

I have a multiple field primary key.

I have 1 main table (contracts) and related tables
(customer, production, service, and billing)

i had it when i was using a single field primary key i
could click a button and using append queries create
corresponding records in each of the related tables
whenever i made a new record in the contracts table. now
with the need to make a multiple field primary i am at a
loss on how to do this. Can anyone help?

Thanks
 
Jeremy

Not sure I understand -- why do you feel it is necessary to "create
corresponding records in each of the related tables"?

Typically, in a one-many relationship (or four), you'd use a
mainform/subform design, linked on the appropriate key field(s). Adding a
record to the main table doesn't require any sub-table entries -- but adding
a sub-table row DOES require a main table row/ID.
 
The reason for creating the records on the related tables
is this.

each of those tables holds certain information. what i am
trying to do is when someone is on the main page they can
go to another form and add info for this record and save
to the related table.

for example. euser is creating a new contract and she
wants to add billing info. so when she goes to the
billing page it needs to already have the appropriate
record id displayed so she can be sure she is entering in
the right record but also so that it does tie the record
to the corresponding or related records.

the append query has been the only way i know how to make
this successful. let me know if you have any ideas.
Thanks
 
Jeremy

The ideas I offered in my response are just how I'd approach this. Perhaps
one of the other readers can offer something more like what you seem to have
decided on.
 
I just reread your comments. I can use the form/subform
and that is a good idea.

I can use that on 3 of the 4 screens. I have to figure a
way of working out the last one because the service form
is based off a query.

Now here is a question for you. Is there any way to
change the subform that is shown on the page by pushing a
button or do you just make another page?
 
Jeremy
(see in-line responses)

Jeremy Dove said:
I just reread your comments. I can use the form/subform
and that is a good idea.

I can use that on 3 of the 4 screens. I have to figure a
way of working out the last one because the service form
is based off a query.
Click to expand...

I don't understand why you feel a main/subform approach won't work with a
query. Are you saying that the information on your "Service form" is NOT in
a table? Is there a reason your "Service form" would not serve as a
subform?
Now here is a question for you. Is there any way to
change the subform that is shown on the page by pushing a
button or do you just make another page?
Click to expand...

"Pages" and forms are not the same, in Access. Are you asking how to show a
different subform on the same form? Have you considered using a "tab
control" to have several apparent <tabs>, and placing a subform on each tab?
 
I was able to get the subform based on a query to pull
data. However I have 2 new issues.

1. I can't select any of the fields in the subform by
mouse click. I have to tab through them. This isn't the
case with any of the other subforms. Do you have any ideas

2. When accessing any of the subforms. When you tab
through all of the fields it will just keep cycling
through the subform. Do you have any idea on how to make
it so that it finishes going through the rest of the
master form?

Also your idea about the tabs is a great idea. However I
don't know how to do that. Can you enlighten me or send
me in the right direction?

Thanks again for all of your help.

Jeremy
 
Jeremy

For future consideration -- when you change topic like this, but don't start
a new thread, you don't get as many "eyeballs".

#1 - don't know.
#2 - if the form that is used as a subform is set to cycle on "Current
Record", it won't leave. But you could add code behind the "last" control
on the subform, in the ?OnLostFocus or ?OnExit event. You would use that
code to force the focus to another control outside of the subform.
 
Again thanks for your help. And thanks for the heads up
about starting the new thread. I will post a new thread
about the query question.

Thanks again.
 
Back
Top