Form & Subform (AfterUpdate)

[Guest]

I have a main form with a field ContractNo and a Subform also with
ContractNo, these fields are the one that link them together.

My dilemma is, if No.1 (for example) is entered into the main form as
contract number, the subform does not automatically pick No.1 up, so it has
to be typed in as well.

What I would like to do therefore is, once No.1 is entered in the subforms
ContractNo field, the AfterUpdate would check the main forms ContractNo and
if they match allow the user to continue inputting information into the
subform. If they do not match, it would inform the user that the contract
numbers do not match and they should re-enter the ContractNo.

Any examples of code would be appreciated.

thank you

 

[Regan via AccessMonster.com]

I have a main form with a field ContractNo and a Subform also with
ContractNo, these fields are the one that link them together.

Im assuming you are useing this to add a new record
Try this

Private Sub ContractNo_AfterUpdate()

DoCmd.GoToControl "NameofSubFormHere"
DoCmd.GoToRecord , , acNewRec
Me!NameofSubformHere![ContractNo] = Me!ContractNo

End Sub

 

[Guest]

Thanks for the fast response, I pasted this code in and it came up with an
error.

What I am expecting the code to do is: if I enter 3 in as a Contract Number
on the main form, when I enter 3 in as the Contract Number on the sub form,
no message will be displayed, if I accidently entered 2 on the sub form, a
message would pop up informing me that the contract numbers do not match, am
I postive that I want to do this.

Hope this make it a bit clearer as to what I was after.

I have a main form with a field ContractNo and a Subform also with
ContractNo, these fields are the one that link them together.

Im assuming you are useing this to add a new record
Try this

Private Sub ContractNo_AfterUpdate()

DoCmd.GoToControl "NameofSubFormHere"
DoCmd.GoToRecord , , acNewRec
Me!NameofSubformHere![ContractNo] = Me!ContractNo

End Sub

 

[Keith Wilby]

I have a main form with a field ContractNo and a Subform also with
ContractNo, these fields are the one that link them together.

My dilemma is, if No.1 (for example) is entered into the main form as
contract number, the subform does not automatically pick No.1 up, so it
has
to be typed in as well.

Assuming there's a 1:M relationship between the parent and child tables,
have you set the relationship between the form PK and the sub-form FK? Open
the form in design view and go to the "Data" properties tab - the parent and
child should contain the appropriate references, in this case "ContractNo".

HTH - Keith.
www.keithwilby.com

 

[Guest]

Yes, to all your questions.
What's next, thanks

Assuming there's a 1:M relationship between the parent and child tables,
have you set the relationship between the form PK and the sub-form FK? Open
the form in design view and go to the "Data" properties tab - the parent and
child should contain the appropriate references, in this case "ContractNo".

HTH - Keith.
www.keithwilby.com

 

[Keith Wilby]

Yes, to all your questions.
What's next, thanks

All I can suggest is that you re-check everything, including that the
relationship in the "relationships" window has referential integrity
invoked. All of the apps I have set up in this way get the FK for free.

Regards,
Keith.

 

[Guest]

All I can suggest is that you re-check everything, including that the
relationship in the "relationships" window has referential integrity
invoked. All of the apps I have set up in this way get the FK for free.

Regards,
Keith.

I presume PK = Primary Key, but what is FK?

Without these can I not create a message to pop up informing the user that
the Contract numbers between the Main Form & Sub Form are different?

cheers

 

[Keith Wilby]

I presume PK = Primary Key, but what is FK?

Without these can I not create a message to pop up informing the user that
the Contract numbers between the Main Form & Sub Form are different?

FK is forign key or "child field" as is will be referred to on the data tab
of the main form. In a 1:M relationship, the FK is at the "many" side, so
if your tables and your form/subform are related correctly then you should
get the FK for free, thus relating the records in the subform (child) to the
main (parent) form.