2 forms, 1 subform... need help loading the correct subform data

[KelMcc]

OK, I have to forms that load the same subform. The subform, FSRComment, has a text field "SRNum". The text field is supposed to get its value from the the form that loads it.

I put this line in the Default Value box of the text field:

=[Forms]![FSREdit]![SRnum] Or [Forms]![FProjectEdit]![SRnum]

If I do just the stuff BEFORE the Or or the stuff AFTER it (meaning I delete the rest) it works in that case. I guess I thought the Or would still allow it to work. But it doesn't.

How do I get this subform text box to gets its value from either form??

Thanks!

 

[Tim K.]

What about using Parent as generic reference to the main form like this.

=Me.Parent![SRnum]

It should work?

 

[KelMcc]

Tim, Thanks but that didn't work.

I changed the value in the text field of the subforum to:
=Me.Parent![SRNum]

FYI, it changed it to this:
=[Me].[Parent]![SRNum]

And in the end, it is not picking up what I need it to. Other suggestions? Or maybe I did something wrong from your idea?

 

[KelMcc]

Apologies Tim, I realize I may have mistated how the subform is called. Its called by a button on the first form. Sorry about that.

Hopefully someone can point me in the right direction.

 

[Pat Hartman]

Use the OpenArgs argument of the OpenForm method to pass the value as the form is opened. then in the subform
the following will place the value in a field.
=Me.OpenArgs

 

[KelMcc]

Pat, I'm unfamiliar w/ OpenArgs, but I did try and figure it out from the help menu, but I didn't get too far.

Here's my code from the button that summons the pop-up:
---------------
Private Sub FSRAddComment_Click()
On Error GoTo Err_FSRAddComment_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "FSRComment"

stLinkCriteria = "[SRNumb]=" & "'" & Me![SRNum] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, , OpenArgs

Exit_FSRAddComment_Click:
Exit Sub

Err_FSRAddComment_Click:
MsgBox Err.Description
Resume Exit_FSRAddComment_Click

End Sub
--------------

For the pop-up form, FSRComment, I did add this:
=[Me].[OpenArgs]
as the default value.

So, I think I'm getting closer. Any further clarification/help would be great. Thanks!

 

[KelMcc]

OK, I'm tinkering w/ it as well as looking for other examples from other threads.

My code is now:
----

Private Sub FSRAddComment_Click()
On Error GoTo Err_FSRAddComment_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim OpenArgs As String

stDocName = "FSRComment"

stLinkCriteria = "[SRNumb]=" & "'" & Me![SRNum] & "'"
OpenArgs = Me.SRNum

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, , OpenArgs


Exit_FSRAddComment_Click:
Exit Sub

Err_FSRAddComment_Click:
MsgBox Err.Description
Resume Exit_FSRAddComment_Click

End Sub
--------

Also, the value field in question on the 2ndary form (the pop-up) is now set to:
[Me].[SRNumb]=[me].[OpenArgs]

Should I not use both the stLinkCriteria and OpenArgs? I think I understand the idea at work, I just don't know the syntax for doing this correctly.

 

[RichMorrison]

I handle this situation with "property" procedures. It is flexible and not difficult.

RichM

 

[Lyn Mac]

You can remove "Dim OpenArgs As String " this is not needed.


Replace:
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, , OpenArgs


With:
DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, , Me.SRNumb

Me.SRNumb is the OpenArgs


HTH,

Lyn

 

[Pat Hartman]

This:

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, , OpenArgs

Should be:

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, , Me.Name

You want to pass the name of the current form as the openArg. When the new form opens, you reference the passed argument as
Me.OpenArgs

So to return to the form that opened this one:

DoCmd.OpenForm Me.OpenArgs

 

[KelMcc]

OK, still having problems. Sorry I haven't looked at this in a couple weeks as I kinda got sidetracked...

Anyway, Lyn, I think I need the "Dim OpenArgs...", as when I removed it, I got a message that the form wouldn't load as it was read-only.

Here's my current code..

------------
Private Sub FSRAddComment_Click()
On Error GoTo Err_FSRAddComment_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim OpenArgs As String

stDocName = "FSRComment"

stLinkCriteria = "[SRNumb]=" & "'" & Me![SRNum] & "'"
OpenArgs = Me.SRNum

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, , Me.SRNum


Exit_FSRAddComment_Click:
Exit Sub

Err_FSRAddComment_Click:
MsgBox Err.Description
Resume Exit_FSRAddComment_Click

End Sub
--------

Then, on the form that is called, I have the "Default Value" as this:

[Me].[SRNumb]=[me].[OpenArgs]

NOTE: I've also tried (with and without the brackets):

Me.OpenArgs

--------

So, I click the button on form FSREdit and FSRComment loads, but the SRNumb field is still not populating...

I'm sure its just a piece of syntax or something, at this point.

 

[Pat Hartman]

Name is a property of the form so - Me.Name = the current form's name. I don't know what Me.SRNum refers to, but change it to be - Me.Name

 

[KelMcc]

Still getting the same problem.

This is the current code, based on how I understood Pat's suggestion. I bolded the 2 places I change Me.SRNumb to Me.Name. Do I also need to do something on the secondary form (the one I'm trying to call)?

--------

Private Sub FSRAddComment_Click()
On Error GoTo Err_FSRAddComment_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim OpenArgs As String

stDocName = "FSRComment"

stLinkCriteria = "[SRNumb]=" & "'" & Me![SRNum] & "'"
OpenArgs = Me.Name

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, , Me.Name

Exit_FSRAddComment_Click:
Exit Sub

Err_FSRAddComment_Click:
MsgBox Err.Description
Resume Exit_FSRAddComment_Click

End Sub

--------

 

[Pat Hartman]

Remove the following line -
OpenArgs = Me.Name

You set the value of OpenArgs via the OpenForm method. So the following statement sets the OpenArgs for the form that is being opened, NOT the current form.

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, , Me.Name

 

[KelMcc]

Still no success.

Let me review, though, if I can't get it right w/ Pat's help, I'm probably beyond hope.

I have a form, FSREdit. It has a field, SRNum, which has a control SRNum (yes, I've learned my lesson long ago about smarter naming conventions , but it was too late to rework these ). It also has a button, FSRAddComment that, when selected, brings up a secondary form, FSRComment. FSRComment has a field, SRNumb, which has SRNumb as a control.

I want the field of FSRComment, SRNumb, to be populated w/ the data that is in SRNum of FSREdit. FSREdit is called by multiple forms, so I can't just put "=[Forms]![FSREdit]![SRnum]" as its default value.

So, to this end, I have the following code, which has been thoroughly massaged w/ the kind help of Pat and a couple others...
------------
Private Sub FSRAddComment_Click()
On Error GoTo Err_FSRAddComment_Click

Dim stDocName As String
Dim stLinkCriteria As String
Dim OpenArgs As String

stDocName = "FSRComment"

stLinkCriteria = "[SRNum]=" & "'" & Me![SRNum] & "'"

DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormAdd, , Me.Name

Exit_FSRAddComment_Click:
Exit Sub

Err_FSRAddComment_Click:
MsgBox Err.Description
Resume Exit_FSRAddComment_Click

End Sub
----------

In the secondary form, FSRComment, I have placed the following in the default value of SRNumb:
=[Me].[OpenArgs]

It still does not work, when the secondary form is opened the following displays in the SRNumb field: #Name?

I'm giving this thorough detail in the hope that at this point its just some bad syntax/typo on my part and that for fresh eyes it will be a real "duh!". Pat's been extremely helpful and patient, which I greatly appreciate.

FYI, I've tried the following:
*In the "On Open" properties of the form FSRComment I did try a "=Me.OpenARgs" but that just got me an error about not having a macro set up.

I'm doing all this to avoid having a "F**Comment" field for every main form I'm creating. At least, if I can't figure this out, I could do that as a fallback, but I'd prefer not to.

Anyway, thanks for the help everyone, esp. Pat. If someone can point out the "duh!" that I'm missing, that would be great!

 

[Pat Hartman]

If you open the form in add mode, you will never see any existing comments. Change the OpenForm Method to:

DoCmd.OpenForm stDocName, , , stLinkCriteria

And see if that solves your problem.

Get rid of -
In the secondary form, FSRComment, I have placed the following in the default value of SRNumb:
=[Me].[OpenArgs]

Instead, in the BeforeInsert event of the Form put:

Me.SRNumb = Eval("Forms!" & Me.OpenArgs & "!SRNumb")

 

[KelMcc]

Pat, I want the form to be add only. But, I did try what you suggesting and it didn't change anything.

Also, for the subform, I followed your suggested and removed the Me.OpenArgs from the field data default and put this...

Me.SRNumb = Eval("Forms!" & Me.OpenArgs & "!SRNumb")

...in the Before Insert field. Nothing. I then tried it in the On Open field and got a "macro [Me] not found" (or something like that). I also found I'd get that error when I had it in Before Insert and actually entered a field. I think tried to use the highlighted section as an "Event Procedure", but my inability to write it as code just gave me errors.

One thing I noticed, that in the subform's properties it had trapped the correct value in the Filter section. Is there syntax so that the default value of the field I want this information in could reference the Filter?

Sorry to have revived this one after so long, but I was "diverted" for a while.

 

[dgm]

Hi KelMcc.

May I ask a stupid question?

When you say you tried entering "=me.openargs" into the "On Open" properties of the form, did you enter it straight into the properties window?

If you did then this is your problem. You need to click on the down arrow on the right hand side of the properties window, across from "On Open", and select "[Event Procedure]". Then click on the three dots next to the arrow, which will take you into the code editor (Visual Basic). Then you should see something like this:

Private Sub Form_Open(Cancel As Integer)
    Me.SRNumb = Me.OpenArgs
End Sub

If you are already doing this than just ignore this question.

Dave

 

[KelMcc]

Ah! Sweet progress! It is not a stupid question! You are right, I was just adding it into the properties line, not as an event procedure.

I have now added it as you laid out, but I get this error:
-----
Run-time error '2448':

You can't assign a value to this object
-----

When I hit 'debug' its takes me to this part of the code:

"Me.SRNumb = Me.OpenArgs"



Sidenote: DGM, you're an RtCW fan? Ever DoD?

 

[dgm]

I have a form, FSREdit. It has a field, SRNum, which has a control SRNum ... It also has a button, FSRAddComment that, when selected, brings up a secondary form, FSRComment. FSRComment has a field, SRNumb, which has SRNumb as a control.

Check the name of your textbox (is it a textbox?). Make sure it is SRNumb. If I understand correctly, your field in your table is called SRNumb and your textbox is also called SRNumb. If for some reason your textbox isn't called SRNumb, then you would get this error as you are trying to assign the value to your table field and not the textbox. I think the reason being, you can't assign a value to a field in the open event????




*RtCW is the first online game I have played. And I can't get enough of it.

Dave