Automatically populate fields in seperate forms that have a relationship

[l3111]

Hi there!!!!

I am in a right pickle!!! I have two forms - a referrals form and an other services form. Once a referral has been entered, an referral reference is assigned to it by means of an autonumber. this is the primary key of this referrals table. I have created a relationship between this field and one with the same name in the other services table. my aim is that when a referral is complete and a user uses the command button that opens the other service form, it automatically populates the referral reference field with the SAME reference number from the referral.
i have trying to complete this for weeks and would really appreciate some help. from what i have found a query seems to the best bet but i have NO IDEA how to design one to do this?

Damsel in Distress!!

l3111, Manchester, UK

[Bob Fitz]

If you create a subform control ("other services" form) on the "referals" form the reference number from the first form should be entered be access for you.

[l3111]

But thn my form won't fit on ONE page, and disabling the scroll will cause more problems than its worth if it can even be done. is there no other way to have the form pop up with a greyed out field with the number already entered?!

l3111, Manchester, Uk

[Bob Fitz]

Hi
As "screen space" is limited you could create a Tab control (which could be almost as large as your current refferal form) on the refferral form. Place all your current controls on the first page of the tab and place the subform on the 2nd tab. Clicking on the second tab to show the subform would be no different to clicking on a button.

[l3111]

Could this not be a problem with my relationships?
i have linked the referral autonumbers, but its the RAISE number [this is an ID number tht is assigned to tht person whn thy are in the organisation] that i need to autopopulate from the first form
however the referral autonumber [ a unique reference for tht particular referral] is not autopopulating either!
also if the referral form is opened and the cmd button for other services is clicked, it needs to bring up the related data to that RAISE Number & Referral autonumber

i need BOTH to autopopulate
and BOTH to link

HOW?!

[Bob Fitz]

I don't think you have a problem with the table relationships. You have a Primary Key (AutoNumber) in the Referral table and a field in the "other services" table that you are using for the relationship. This appears to be correct to me. I assume this is a one to many relationship.

If you choose to use a sub form, as I suggested in my last post, the Primary Key (AutoNumber) in the Referral table will be entered into the foreign key field in the "other services" table each time you create a new record in the sub form. For this to happen you just need to set the "Link Child Fields" and the "Link Master Fields" properties of the sub form. If you need to "auto populate" any other field values on the sub form, this would be done with VBA code in the sub form’s Before Update property.

If you choose to open the "sub form" as a separate form then ALL fields that are to be "auto populated" (including the foreign key) would need to be done with code.

I still feel the best way would be by using a sub form on a tab control but the choice is of course yours.

If you need more help with either option I am happy to help if I can.

Hope this helps.

Bob