Assign Subform record same ID number as Main form record ID number 1

[jer007]

In my main form, frmApplication, I have a command button to open frmAdminReview with the following code:

Dim stDocName As String
Dim stLinkCriteria As String
Me.Refresh 'refresh the record
stDocName = "frmAdminReview"

stLinkCriteria = "[AdminReviewID]=" & "" & Me![txt_ApplicationID] & ""
DoCmd.OpenForm stDocName, , , stLinkCriteria

The problem I am having is getting AdminReviewID to have the same Value as txt_ApplicationID so that only the record in the subform that matches the record in the main form is displayed.

To solve this problem I added to following code to the On Current for frmAdminReview:
AdminReviewID.Value = [Forms]![frmApplication]![txt_ApplicationID]
This seems to work however, if I try to open frmAdminReview without using the command button in frmApplication I get a Run-time error '2450'

If you have any better ideas on how to assign the ID from frmApplication to frmAdmin review I would appriciate it.

thanks for the help,

Jeremy

 

[RoyVidar]

Try using the openargs of the openform method:

[tt]..., , , stLinkCriteria,,, Me![txt_ApplicationID] [/tt]

Then in the other form (on current?), use the openargs to assign values:

[tt]if not isnull(me.openargs) then
me!AdminReviewID.Value = me.openargs
end if[/tt]

- perhaps some additional testing whether there is a previous value in the control...

Roy-Vidar

 

[KenReay]

Hi

You used the term 'sub form' but what you are using is not a sub form. You can place a sub form control on a form, and within that subform control you can have a form. The sub form control has properties Master/Child fields, which if you set them correctly will keep the id numbers in sync

In the example you quote, the second form should open filtered to contain only rows with the same id, you should only need to worry about setting the id when you create a new row in the second form so you would use code like

If me.NewRecord Then
AdminReviewID.Value = [Forms]![frmApplication]![txt_ApplicationID]
end if

you might want to test to see if the 'main' form is open first see teh northwind example database for a function isLoaded() which does this your code would then become

If IsLoaded("frmApplication") Then
If me.NewRecord Then
AdminReviewID.Value = [Forms]![frmApplication]![txt_ApplicationID]
end if
end if


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[KUZZ]

exactly, link the form and subform on it using both your primary keys and your ID field. That will ensure sync between the two. Are you sure that you are actually talking about a SUBFORM?

Good luck,
Kuzz

"Time spent debating the impossible subtracts from the time during which you
can try to accomplish it."

 

[jer007]

Roy-Vidar

Thanks for the suggestion, it works perfectly now!