Pass a value from a sub-form to a pop-up form

[techexpressinc]

I am trying to pass a value without success.



From form code:

Private Sub AddNewWorkItem_Click()
Dim StrPlanID As String
 Response = MsgBox("Please confirm the adding of a record.", vbYesNo + vbQuestion, "... Adding a new Work Activity Record ...")
If Response = vbYes Then
'   StrPlanID = Me.PlanNumberMatched
  StrPlanID = "123456"
  DoCmd.OpenForm "frmPopUpPlanDocWorkTracking", acNormal, , , acFormAdd, , StrPlanID
Else
 StrPlanID = "123"
 
End If
End Sub

To form code:

Private Sub Form_Load()
Dim StrPlanID As String
  If Nz(Me.OpenArgs, "") <> "" Then
    DoCmd.GoToRecord , , acNewRec
     StrPlanID = Me.OpenArgs
  End If
' DoCmd.MoveSize left, right, width, height
   DoCmd.MoveSize 2400, 1550, 10522, 4444
   
End Sub

Can I then reference the value StrPlanid in the sub-form?

Russ

[Rod]

Hi Russ,

Not too sure I understand your problem: there is no sub form (that you've told us about) but rather a form procedure that opens another form called, frmPopUpPlanDocWorkTracking. Without checking the number of commas I can see nothing wrong with your OpenArgs mechanism - is it working?

Now you ask:

Can I then reference the value StrPlanid in the sub-form?

Assuming that by sub form you mean the form containing the procedure that opens the pop-up form, then no, not as things stand. In the calling form you have declaced StrPlanID within the procedure; this means the variable is available only within that procedure. To overcome this you need to declare it in the declarations section at the start of the module, and moreover declare it as 'Public.'

The same conditions apply if you want to refer to StrPlanID in the pop-up form from the calling form (although I can't see why you would need to do this); the variable must be decaled as 'Public' in the declarations section.

[techexpressinc]

How do I make the field 'Public' in the declarations section?

[Rod]

Place the declaration before all procedures. See text in red below.

Option Compare Database
Option Explicit
Public StrPlanID As String

Private Sub AddNewWorkItem_Click()
    Dim Response As Long
 Response = MsgBox("Please confirm the adding of a record.", vbYesNo + vbQuestion, "... Adding a new Work Activity Record ...")
If Response = vbYes Then
'   StrPlanID = Me.PlanNumberMatched
  StrPlanID = "123456"
  DoCmd.OpenForm "frmPopUpPlanDocWorkTracking", acNormal, , , acFormAdd, , StrPlanID
Else
 StrPlanID = "123"
End If
End Sub

[Rod]

The

DoCmd.GoToRecord , , acNewRec

in your Form_Load event is probably redundant as you have specified acFormAdd in the open command.

[techexpressinc]

Rod - thanks for the help. I attached a screen image of the VBA code for the event to open the form2 for adding a new record.

I add code to make the field strplanid a public variable. But, it doe not display on the form2.

Any ideas where I am dropping the ball?

Thanks
Russ

[Missinglinq]

As I said in your other thread, here

http://www.utteraccess.com/forum/Pas...601.html&st=10

you're declaring a Variable with the same name as a Field in your underlying Query:

Dim StrPlanID As String

and then assigning the OpenArgs to that Variable:

StrPlanID = Me.OpenArgs

You need to assign it to the Control on the popup Form that holds the Field's Value, which, per your attached file on the other forum, is actually named Text596:

Me.Text596 = Me.OpenArgs

Linq ;0)>

[techexpressinc]

Sorry to such a pain. I would attached the whole db but it is very large with many objects.

I am trying to pass the planid from a subform to a pop-up form. From the pop-up form I want to add a new record.

The pop-form has a query behind it and the field name in the query and table where the row to be added is a field name of "cont#".

I am getting the attached error. It is flagging the Me.Cont# line. The field is Cont# on a query attached to the form. I change the value on the load of the pop-up to me.cont# for the pass field.

Below is the from form code - see the Private Sub AddNewWorkItem that is where I am trying to pass the planid to the pop-up form:

 Option Compare Database
Option Explicit
Public StrPlanID As String
Private Sub AddNewWorkItem_Click()
Dim Response As Long
Response = MsgBox("Please confirm the adding of a record.", vbYesNo + vbQuestion, "... Adding a new Work Activity Record ...")
If Response = vbYes Then
'   StrPlanID = Me.PlanNumberMatched
  StrPlanID = "123456"
  DoCmd.OpenForm "frmPopUpPlanDocWorkTracking", acNormal, , , acFormAdd, , StrPlanID
Else
 StrPlanID = "123"
End If
End Sub
Private Sub EditWorkItem_Click()
' pop-up window - Aug.2012
 DoCmd.OpenForm "frmPopUpPlanDocWorkTracking", , , "[IDMainTable]=" & Me!IDMainTable, windowmode:=acDialog
End Sub

Below is the to form code that is to write the new row:

Private Sub Form_Load()
  If Nz(Me.OpenArgs, "") <> "" Then
    Me.cont# = Me.OpenArgs
  '  DoCmd.GoToRecord , , acNewRec
  End If
' DoCmd.MoveSize left, right, width, height
   DoCmd.MoveSize 2400, 1550, 10522, 4444
End Sub

Thanks for keeping with me on this problem.

Russ

[Rod]

Russ,

Forget my previous post about declaring public variables and revert your code to the original. I thought you wanted to access the value of StrPlanID from the pop-up form; I must admit it didn't make much sense as you were already passing the value in the OpenArgs.

OK, please follow Missinglinq's advice. The value of a variable may be accessed and updated in a number of places. Of interest here are:

1. As the value of a bound control on the form. It does not matter what the name of this control is; it only matters that it is bound to the variable of interest. By default Access generates the control name to be the same as the name of the column name in the underlying result set which leads to confusion. Many experienced Access users do not use the default control name but instead prefix the column name with an indication of the type of control. For example, if the column name is cont# then the control would be named txtcont# for a text box, cbocont# for a combo box, etc. Now most Access objects have a default property (controls are objects) and the default is usually the value. Thus if you were to be pedantically explicit, you should refer to the value in the control as, for example Me.txtcont#.Value. However because of the default property you may use Me.txtcont# or even just txtcont#. Now if the control has the same name as the column you could use cont# which also happens to be the name of the column.
2. As the value in the underlying result set. As explained above if you accept Access' default control names then the same name applies to two different things: the control (and by default its value) and the column in the result set. So how does Access know which you mean? Well it doesn't matter for bound controls because Access synchronises the two values; if you change the control's value then Access updates the result set and vice versa. What it does mean is that you can update a value in the result set via VBA without it being displayed on the form. Next time you're in the VBA coding window and have typed 'Me.' just browse through the list of possibilities presented by Intellisense. You will find all the column names plus the names of all the form's controls.

By the term result set I mean the rows and columns returned from the table or query to which the form is bound

OK, this hasn't got you any further in solving your problem but you must be crystal clear in your mind about names.

I understand what you want to do is:

1. Allow the user to click a command button on a form. This happens in your case to be a sub form but this is irrelevant unless you want to retrieve values in the form from outside.
2. A pop-up form is displayed positioned for entry of a new record. Hopefully this form is also modal. Is this pop-up form used in other circumstances where it is not in data entry mode? The pop-up form is bound to a query.
3. Pass, through OpenArgs, a value from the calling form to the pop-up form and poke this value into one of the pop-up form's controls.

This is a routine mechanism but you are having difficulty with the 'poke' and Access is reporting a 'Method or data member not found' for the following line:

Me.cont# = Me.OpenArgs

This error occurs most often after typos or renaming tasks; Access can find nothing named 'cont#' in the 'Me.' domain. However it might occur when there is more than one thing named 'cont#.' This could occur if there is a column in the result set of that name and also a control of the same name that is not bound to the result set; as Missinglinq picturesquely puts it: the Access Gnomes are confused, so to get their own back they issue a misleading error message.

After this prolonged diatribe, I suggest you check that the control on the pop-up form (presumably called cont#) is bound to the column in the result set (also presumably called cont#). I echo Missinglinq's comment that it is very difficult to solve problems with partial information. We need in addition the pop-up form design and the query (if separate).

Let us know how you get on.

[techexpressinc]

Rod - not solved yet, thank you for your insight and patience. Yes, you understand what I am attempting to do.

1) The pop-up form is "modal", which from what I read means in control, and the other window is not updateable.
2) I decided to go with two pop-up forms one form editing and another one for adding a row. This is more for me, for now because of the controls. The current pop-up form for editing is working.

I am not getting the new screen plan number populated however I think it is a minor bug. For the time being I put the plan number the screen for the user to manually populate.

Below is the code on the sub-from behind click on add a record:

Private Sub AddNewWorkItem_Click()
Dim Response As Long
Dim StrPlanID As String
Response = MsgBox("Please confirm the adding of a record.", vbYesNo + vbQuestion, "... Adding a new Work Activity Record ...")
If Response = vbYes Then
'   StrPlanID = Me.PlanNumberMatched
  StrPlanID = "123456"
  DoCmd.OpenForm "frmPopUpPlanDocWorkTrackingNew", acNormal, , , acFormAdd, , StrPlanID
Else
 StrPlanID = "123"
End If
End Sub

Pop-up Form on load event code:

Private Sub Form_Load()
  If Nz(Me.OpenArgs, "") <> "" Then
    Cont# = Me.OpenArgs
  '  DoCmd.GoToRecord , , acNewRec
  End If
' DoCmd.MoveSize left, right, width, height
   DoCmd.MoveSize 2400, 1550, 10822, 5144
End Sub

Attached is a zipped DB with the following objects:
1) tables and queries to support the two forms
2) Subform calling the pop-up form
3) Pop-up form

Thanks again, Russ

[Rod]

Will take a look over the next two days. Hopefully we'll have it solved within that time.

[Rod]

Hi Russ,

Well that took much less time than anticipated. Let me deal with the points one by one.

• The 'Method or data member not found' message is occurring because cont# is not a member of the 'Me' domain. Thus specifying Me.cont# causes the error while specifying cont# is OK because it is a member of the result set. I was wrong in thinking the result set was included in the 'Me' domain even if Intellisense suggests it is - different parts of Access/VBA using different validation routines?
• Access/VBA is quite happy to update cont# but despite my best efforts (requery, refresh and repaint) I could not get the new value displayed in the bound control which remained stubbornly as Null. The value in the result set was my new value as demonstrated by Debug.Print but my fear is that when Access tries to save the record the Null in the control will override the value in the result set.
• So in order to get the argument displayed and make it 'stick,' it is necessary to use the control name: Me.StrPlanID = Me.OpenArgs.
• Beware of another little Access quirk. When you return to form view after editing the VBA code and click on the [Add Work Item] command button, Access ignores the acFormAdd parameter and presents the pop-up not in data entry mode. You need to close it and start again from the beginning. Yes, I reckon it's a bug and without realising it you can spend ages chasing your own tail.
• So why was the passed argument not showing? Well, I don't have a definite explanation. There is a fault with the underlying query design that once I corrected, everything worked.

• The picture shows your design. However the direction of the join is wrong. Either make it 1:m from work type to document tracking or make it 'only select if both equal.' After I changed this all seems to work.
• I changed the code in the Form_Load event to be what I consider safer. I don't think I have to explain it to you.

Private Sub Form_Load()
    If Me.NewRecord Then
        If Nz(Me.OpenArgs, "") <> "" Then
            Me.StrPlanID = Me.OpenArgs
        Else
            MsgBox "No argument passed! What to do?"
            DoCmd.Close acForm, Me.Name
        End If
    End If
    'DoCmd.MoveSize left, right, width, height
    DoCmd.MoveSize 2400, 1550, 10822, 5144
End Sub

Hope this solves your problem.

[techexpressinc]

Rod - thanks for you continued support. I tried your code and had "me.strplanid" not identified.

WORKS!
The crazy field name was probably part of the problem a field name of “cont#”.
_________
From Form – Add Rec Button – On Click event:

Private Sub AddNewWorkItem_Click()
Dim Response As Long
Dim StrPlanID As String
Response = MsgBox("Please confirm the adding of a record.", vbYesNo + vbQuestion, "... Adding a new Work Activity Record ...")
If Response = vbYes Then
    StrPlanID = Me.PlanNumberMatched
    ' StrPlanID = "123456"
    DoCmd.OpenForm "frmPopUpPlanDocWorkTrackingNew", acNormal, , , acFormAdd, , StrPlanID
Else
    StrPlanID = "123"
End If
End Sub

__________
To Form – On Load event:

Private Sub Form_Load()
Dim StrPlanID As String
    If Me.NewRecord Then
        If Nz(Me.OpenArgs, "") <> "" Then
            StrPlanID = Me.OpenArgs
        Else
            MsgBox "No argument passed! What to do?"
            DoCmd.Close acForm, Me.Name
        End If
    End If
    'DoCmd.MoveSize left, right, width, height
    DoCmd.MoveSize 2400, 1550, 10822, 5144
End Sub

__________________________________________
To Form – On Current event:

Private Sub Form_Current()
'   cont# = Me.OpenArgs
   Me.StrPlanID = Me.OpenArgs
End Sub

The order of Events on a new form are:

1. Open → Load → Resize → Activate → Current ...