Getting data from another form

[jpl458]

I have a form that got crowded so I moved a portion of the form to a new from. Unfortunately, I was getting data from the moved portion when it was still on the main form . When the moved portion was on the main form in a textbox on GotFocus event I had the following line that worked:

'Me.numbercalledtb = Me.numbercalledcb()

Me.numbercb was acombobox

When I moved data to the new form, I Modified the control to be a listbox called Phones, and the new form is CCCarsPhonesfrm, and tried the following code:

Me.numbercalledtb = [Forms]![CCCarsPhonesfrm]![Phones].ItemData(0)

I get this error:

Tried several attempts ant getting the syntax right, and read articles, all to no avail.

Thanks

 

[The_Doc_Man]

When forms get crowded, you can try something called a TAB CONTROL. Look this up online. I'll give you the first reference but there should be quite a few references online.

TabControl object (Access)

Office VBA reference topic

learn.microsoft.com

In a tab control, you take a crowded form. You create two tabs. The tabs DO NOT have to occupy the whole form. You can put controls on either of the two tabs. When you put controls on the tabs, you click the tabs at the top of that control to select which tab is open. That tab "comes to the top" and hides any other tabs. If the tabs DON'T take up the whole form - and they DON'T have to - then you have three visual areas - non-tab, first tab, second tab. You CAN add more tabs. Each tab area is like a blank slate for controls.

Now this would be pointless for your problem except for one simple point. As long as the tabs are part of the same form, each control on each area is visible to the underlying event code WITHOUT SPECIAL REFERENCES. I.e. you don't have to say Me.Tab1.ControlA or Me.Tab2.ControlB because all controls on the form are programmatically visible regardless of which tab they are on. So there is no moving of data from one control to the other. Everything is visible.

Note that SUB-FORMS are still sub-forms. There is still a sub-form control and then a form that is loaded to that control. The same methods to diddle with a sub-form apply regardless of where the sub-form control was placed.

 

[June7]

Form used as subform is not in the active forms collection so your code cannot find it. A subform is created by installing a form in a subform container control. Referencing this subform must be via the container name which might be same as the form it holds depending how subform was created. I always name container different from the object it holds, like ctrCCC. If code is behind main form, consider:

reference to field
Me.ctrCCC!Phones

reference to control
Me.ctrCCC.Form.Phones

I agree with Doc - look into Tab control.

 

[jpl458]

Are you saying that there is now way to address the data in the list box on the new form from a textbox on the original form? If that is true then it's not a syntax problem, but a design issue. Is that correct? Then, is there a way to make the new form a sub-form of the original.

I have a form that got crowded so I moved a portion of the form to a new from. Unfortunately, I was getting data from the moved portion when it was still on the main form . When the moved portion was on the main form in a textbox on GotFocus event I had the following line that worked:

'Me.numbercalledtb = Me.numbercalledcb()

Me.numbercb was acombobox

When I moved data to the new form, I Modified the control to be a listbox called Phones, and the new form is CCCarsPhonesfrm, and tried the following code:

Me.numbercalledtb = [Forms]![CCCarsPhonesfrm]![Phones].ItemData(0)

I get this error:

View attachment 104960

Tried several attempts ant getting the syntax right, and read articles, all to no avail.

Thanks

I just looked at tab controls and I don't believe they are not what I need. I need to have empty space on the original form. Is the problem above a syntax error, and if so how do I fix it. I keep reading how to address data from one form to another, but none fit what I am trying to do, which is get the selected item from a list box that's on another form..

 

[Pat Hartman]

Splitting into two forms to update a single record is not a good solution. The tab solution is generally better. You can use a combination of mainform tabs bound to the main table query and tabs with subforms bound to child table queries. This form has 12 tabs. It allows me to spread out the data so that what the care managers need to see together is all on the same tab. Information that is always needed is at the top of the form. There are also various buttons to go to other related function areas.

 

[561414]

Are you saying that there is now way to address the data in the list box on the new form from a textbox on the original form? If that is true then it's not a syntax problem, but a design issue. Is that correct? Then, is there a way to make the new form a sub-form of the original.

I just looked at tab controls and I don't believe they are not what I need. I need to have empty space on the original form. Is the problem above a syntax error, and if so how do I fix it. I keep reading how to address data from one form to another, but none fit what I am trying to do, which is get the selected item from a list box that's on another form..

You can reference a form control from any control in another form, it just requires the right nested syntax. I don't know what your form layout is but MAYBE Me.numbercalledtb = [Forms]![CCCarsPhonesfrm]![Phones].ItemData(0) should be Forms!CCCarsPhonesfrm.Form!Phones.ItemData(0)
Give it a go, or post a sample database. Do whatever design you feel comfortable with, but pivot if you see it's getting too hard.
If you need further assistance, let me know.

 

[The_Doc_Man]

Are you saying that there is now way to address the data in the list box on the new form from a textbox on the original form?

Not at all. Every control on every open form in your current workspace can be directly addressed if you know the names. My suggestion was that rather than create a new form, use a TAB control to "extend" the total size of the form so you can fit more on it, thus avoiding the complications of having two forms that are technically independent of one another to solve a problem of a complex record.

 

[Pat Hartman]

The problem with editing the same record on two open forms is you end up conflicting with yourself. If you use a tab form, all the fields are on ONE open form at the same time so there is no conflict. All your validation is in one place also.

 

[jpl458]

You can reference a form control from any control in another form, it just requires the right nested syntax. I don't know what your form layout is but MAYBE Me.numbercalledtb = [Forms]![CCCarsPhonesfrm]![Phones].ItemData(0) should be Forms!CCCarsPhonesfrm.Form!Phones.ItemData(0)
Give it a go, or post a sample database. Do whatever design you feel comfortable with, but pivot if you see it's getting too hard.
If you need further assistance, let me know.

Thanks you for the direct answer. I will let you know shortly how it goes.

Thanks again

 

[jpl458]

Thanks you for the direct answer. I will let you know shortly how it goes.

Thanks again

It worked, but I don't understand why. You inserted a Forms! before Phones, but Phones is a control on the form (a listbox). Why would you put a Forms! before a control?

But, there is a minor problem after the code worked. I am getting the data from a list box :

Phone numbers from that list box are selected and put into a textbox (numbercalled) in the data entry part of the form in 2 ways.
In the Got Focus event, the top item in the list box is selected using:

Me.numbercalledtb = Forms!CCCarsPhonesfrm.Form!Phones.ItemData(0)

But, if the user selects a different phone number from the list and double clicks the numbercalled text box the the selected number is moved to the text box using:

Dim i As Integer
For i = 0 To Forms!CCCarsPhonesfrm.Form!Phones.ListCount - 1
        Debug.Print i
    If Forms!CCCarsPhonesfrm.Form!Phones.Selected(i) Then
        Me.numbercalled = Forms!CCCarsPhonesfrm.Form!Phones.ItemData(i)
        Debug.Print Me.numbercalled
    End If
Next i

It all works fine except in the double click event the selected phone number does not appear in the text box till I tab out of it, the the new phone number is properly displayed. Only 2 events exist for the text box, ON Got Focus, and On Dbl Click.

After proof reading my post, I went back and rant the Dbl click event again and Debug printed the value in me.numbercalled, and that number is correct, it just doesn't display in the text box till I tab out. Is puzzelment, no?

Doc, really appreciated your concise answer.

 

[561414]

Why do you have Me.numbercalledtb in the Got Focus event and Me.numbercalled in the Dbl Click event? Or are these two different forms?
It works fine in the attached database, but my naming is correct

People like to use this link as reference for referencing controls: http://access.mvps.org/access/forms/frm0031.htm

 

[jpl458]

Why do you have Me.numbercalledtb in the Got Focus event and Me.numbercalled in the Dbl Click event? Or are these two different forms?
It works fine in the attached database, but my naming is correct

People like to use this link as reference for referencing controls: http://access.mvps.org/access/forms/frm0031.htm

When the user enters the Me.numbercalled TB, a standard number is displayed. They can then select a phone number from a lit box that contains other phone numbers then dbl click the text box have that number displayed. It's fewer keystroked then entering complete phone numbers. Will read the link you enclosed

 

[561414]

When the user enters the Me.numbercalled TB, a standard number is displayed. They can then select a phone number from a lit box that contains other phone numbers then dbl click the text box have that number displayed. It's fewer keystroked then entering complete phone numbers. Will read the link you enclosed

Yeah, the sample database does this, but it does not require you to tab away for it to display the number, as soon as the user double clicks on the textbox, the selected value from the listbox appears in the textbox. You said you had that problem, that's why I pointed out that your GotFocus event refers to control name numbercalledtb, while your DblClick event refers to numbercalled. The control names are different. I'm not sure if that's the problem or if you still have the problem.

 

[jpl458]

Spelling errors! arrrrrg! Just fixed it and you were more observant than me. You were right.

Thanks a lot.