Automatic Update of Field in form

L

lhcpr

Hello all,

I am relatively new to access and have a question which has bee
puzzling me. The example below is very simplified to simulate m
predicament.

Here it is:

1) I have 2 tables with the fields as below:

a)_Customer_Info_
*ID #: * Primary Key; Autonumber
*Case Type:* Combo Box, three choice
*Provider:* Combo Box, three choices

b)_Detailed_History_
ID #:[/B] PRIMARY KEY; NUMBER
*COMPLETE:* CHECK BOX/YES-NO
*RECEIVED:* CHECK BOX/YES-NO
*VERIFIED:* CHECK BOX/YES-NO

THE TABLES ARE JOINED BY A RELATIONSHIP BETWEEN _CUSTOMER_INFO:_ *ID#
AND _DETAILED_HISTORY:_ ID

2) I have created a main form using the Customer Info Table and
separate Detailed History Form.

3) I have created a command button on the main Customer Info Form t
open up the Detailed History Form

4) What I am having problems with is getting the ID # field (fro
Customer Info Form) automatically inserted into ID # field (on th
Detailed History form) after it has been opened by clicking the butto
on the main. The idea is that I would then manually fill out th
remaining information.

Since they are related, shouldn't it be possible to do this??

Could anyone give me some suggestions/ideas/improvements to get this t
work?????

Cheers

lhcp
 
R

Rick Brandt

lhcpr said:
2) I have created a main form using the Customer Info Table and a
separate Detailed History Form.

3) I have created a command button on the main Customer Info Form to
open up the Detailed History Form

4) What I am having problems with is getting the ID # field (from
Customer Info Form) automatically inserted into ID # field (on the
Detailed History form) after it has been opened by clicking the button
on the main. The idea is that I would then manually fill out the
remaining information.

Since they are related, shouldn't it be possible to do this??
Click to expand...

What you desire is automatically done for you when you use a main form with an
embedded subform. In that setup the subform control has two properties
MasterLink and ChildLink that tell Access what field or fields make up the
relationship between the two forms. This provides two desired behaviors.

1) Records shown in the subform are automatically filtered to show only records
related to the record currently displayed in the main form.

2) Any new records created in the subform will automatically inherit values for
the linking fields from the related fields in the main form.

If you do not have strong reasons for opening the second form as a separate form
I would consider changing to a form with a subform as this makes things so much
easier. You could use a TabControl with two pages and place the subform on one
of the pages if real estate is an issue. If you really want to keep it as a
separate form, then you have to do some additional work to take care of the
things that would be automatic with a subform.

Your command button that opens the form can use the optional WHERE argument to
filter the second form so that it only shows records related to the first one.
You might already be doing this as this is one of the choices from the command
button wizard. However that only takes care of existing records. For NEW
records to also inherit the proper related field values you need to either set
the DefaultValue properties for those controls in the Open event of the second
form or you can simply reference the first form in the DefaultVlaue property
directly. For example, you could have a DefaultValue property for the ID #
control on the second form of...

=Forms!MainFormName![ID #]

This would work as long as the Details form will only be used when the main form
is also opened. If you ever need to use the detail form when the main form is
not opened then a reference like that would produce an error.

You might also want to open the second form using the acDialog argument. That
would force the user to do what they want with the second form and close it
before they would be allowed to do anything else. If you don't do this then the
second form could be opened, then the main form moved to another record, and
then an entry made in the second form. In that case the DefaultValue used might
not be what the user expected.
 
L

lhcpr

Rick, thanks for the reply.

Adding a subform would be the easiest way to solve my problems I agree
My main concern was adding a nice interface and functionality. I di
have a play around with the Where filter etc. but kept getting a ms
like

"cannot add or change a record because related record is required i
table 'main'.

This attack looked like what I wanted and worked well until I tried t
close the form and I would get the msg.

I will give your suggestions a try.

Cheers,

lhcp
 
Top