Subform Combobox Autofill Another Subform Textbox

[BASSPU03]

Hello, folks. This is my first post and I only began to work extensively with Access about 3 weeks ago. I'm running Access 2003 on Windows XP.

I'd like a textbox in subform2 to reflect the value chosen from a combobox in subform1. FYI: Both of these subforms are linked to a main form with five fields (AutoNumber, Quantity, Resource, Type, and General Description; "AutoNumber" is the primary key).

My main form lists a resource (e.g. computer) and a series of tabs within the main form contain information about it. Each tab contains a subform. The first tab (named "Administration") is a summary of the proceeding tabs. It has a combobox called "Purchase Request by." I pull down this combobox and select "Jane Doe." The second tab is called "Purchase Request." Correspondingly, there is a textbox labeled "Purchase Request by" that I want to autofill with "Jane Doe" based on my selection from "Administration."

I know this isn't cosmically difficult, but I've exhausted my search capabilities and mainly have found examples of linking these same two types of inputs within the same form (not from subform to subform within tabs). I would appreciate any help and simple instructions. Whenever I come across simple VB or SQL responses without basic instruction, I get lost because I don't know those languages and/or how to implement them using Access. A clear explanation would really, really help.

Thanks in advance,
Benny

[Luke Bailey]

Hello, folks. This is my first post and I only began to work extensively with Access about 3 weeks ago. I'm running Access 2003 on Windows XP.

I'd like a textbox in subform2 to reflect the value chosen from a combobox in subform1. FYI: Both of these subforms are linked to a main form with five fields (AutoNumber, Quantity, Resource, Type, and General Description; "AutoNumber" is the primary key).

My main form lists a resource (e.g. computer) and a series of tabs within the main form contain information about it. Each tab contains a subform. The first tab (named "Administration") is a summary of the proceeding tabs. It has a combobox called "Purchase Request by." I pull down this combobox and select "Jane Doe." The second tab is called "Purchase Request." Correspondingly, there is a textbox labeled "Purchase Request by" that I want to autofill with "Jane Doe" based on my selection from "Administration."

I know this isn't cosmically difficult, but I've exhausted my search capabilities and mainly have found examples of linking these same two types of inputs within the same form (not from subform to subform within tabs). I would appreciate any help and simple instructions. Whenever I come across simple VB or SQL responses without basic instruction, I get lost because I don't know those languages and/or how to implement them using Access. A clear explanation would really, really help.

Thanks in advance,
Benny

Benny,

While it may be difficult to link the two subforms to each other it is fairly simple to link them to a field on the main form.

For example: You could set up a textbox on the main form that is not visible to the end user. This textbox would reference (equal) the value from subform1. Then you could set your subform2 to reference (equal) the textbox on the main form. You might need to set up some events to refresh your form after you enter data, but it should work.

[BASSPU03]

For example: You could set up a textbox on the main form that is not visible to the end user. This textbox would reference (equal) the value from subform1. Then you could set your subform2 to reference (equal) the textbox on the main form. You might need to set up some events to refresh your form after you enter data, but it should work.

Hi, Luke,

Thanks for your reply. I've entertained this idea for a while because linking to the main form is easy. However, there are about 8 tabs each with their own subform with controls that'll eventually draw information from the first tab's subform. So, using your suggestion, this would mean that I'd have to put 8 distinct hidden controls on the main form to have them display their respective data into each subform. While I'm not a fan of cluttering up my main form (only in design mode, of course), I'll have to give this a go if I can't find another solution.

I tried to have the textbox simply query the information from subform1's table, but I'm either doing it wrong and/or it doesn't work. If I make a simple query where the field is "PurchaseRequestby" and the table is "tblAdminTab," it seems logical that my textbox (when linked) should draw the information from subform1's combobox, but it doesn't. The textbox just remains blank. (Actually, I don't even see "Row Source Type" or "Row Source" fields in the properties for the textbox, so I'm not sure I even attempted linking the textbox.) However, if I make the textbox a combobox and link this same query to it, the combobox does display results. The bad thing is that it shows ALL records that have been input into the "PurchaseRequestby" column instead of just the desired selected/current value. And, most importantly, I still want the combobox to be a textbox.

Any further thoughts?

[Luke Bailey]

Probably the most simple approach that avoids any hidden textboxes is to just have the other tabs reference field from the first tab. Let's say I have the following:

A Form called: "Order Details"
A tab with a subform on it called: "Order Details Subform"
on that tab is a different subform called: "Orders Subform"
There is a field on "Order Details Subform" called "ProductID"

In order to reference that field on the second subform, I would create a textbox and set its control source to "= Forms![Order and Details]![Order Details Subform].Form![ProductID] "

The only problem with this is that if you are using this in a second subform (like you've described) then every record in the second subform will hold this value. If that's what you want then you're good!

Hope this helps!

~Luke

[BASSPU03]

In order to reference that field on the second subform, I would create a textbox and set its control source to "= Forms![Order and Details]![Order Details Subform].Form![ProductID] "

Thanks for your thorough reply. Is there any chance that I can add the name of the other subform after "[Order Details Subform]" and have it retrieve the information from there? I know these are meant to be cascading--sort of like nested folders in a Windows directory. Unfortunately, I only have one textbox that'll need to pull the selected value from the combobox, so your suggestion, although great, wouldn't do the trick.

In fact, I have a "Summary" tab with a paragraph of text (not in a textbox) that's surrounded by actual textboxes, each should draw their respective information from comboboxes and textboxes in the other subform tabs.

Example:
[Product/Service] received from [ABC Shipping Co.] on [12/20/2006]. [Product] is [New] and has a useful life of [7 years].

I figure that once I learn the procedure (if there is one), I'll be able to apply it to any number of textboxes and comboboxes.

Here's to wishful thinking.

[BASSPU03]

Luke, I tried your suggestion anyway to see if it would do anything at all. No error is generated, which is always a good sign, but the textbox didn't draw a thing. This is what I put into the textbox's control source:

=Forms!frmViewAllResources!sfrmAdminTab.Form!PurchaseRequestby

[It automatically dropped the brackets.]

Main form: "frmViewAllResources"
1st tabbed subform: "sfrmAdminTab"
2nd tabbed subform: "sfrmPRTab"

So, I have a textbox on sfrmPRTab with your recommendation above and nothing happens. I'm hopeful that, because there isn't an error, it might eventually work. I'm not sure I understand what the difference (if there is one) would be between a subform nested within a subform (such as in your example) and a subform beside another subform in a set of tabs (as in my case).

[BASSPU03]

FYI: Sorry, but I don't know why my previous posting input a space within the word 'Purchase.' This isn't the case in the actual code in my DB.

[NeoPa]

That's a little bug in the forum software. It's being looked at :)

[BASSPU03]

OK, I think I've gotten the selection from the combobox to autofill into the other subform's textbox, but I've got one problem. First, this is what I have in the Control Source for the textbox:

Specific:

=Forms!frmViewAllResources!sfrmAdminTab.Form!PurchaseRequestby
 

This is the "template" version of what I did above:

=Forms!FORMNAME!SUBFORMNAME.Form!COMBOBOXFIELD
 

Only change the capital letters to suit your forms.
FORMNAME = the name of your main form
SUBFORMNAME = the tabbed subform, which is within the main form
COMBOBOXFIELD = the field that the combobox writes to on the table

Basically, all I'm making the textbox do is refer to the same field as the combobox. The reason it lists the specific value (and not just all the values in that field) is because I have the subforms linked through AutoNumber primary and foreign keys.

Now, here's the problem. The textbox only reflects the combobox's selected value after I close the form or move to the next record. In other words, it displays the value only after I open the record again. How can I make the textbox reflect the value immediately? Somehow, inserting this exact same code into the BeforeUpdate, AfterUpdate, or OnFocus properties doesn't do the trick. Any help?

Thanks,
Benny

[NeoPa]

...
Somehow, inserting this exact same code into the BeforeUpdate, AfterUpdate, or OnFocus properties doesn't do the trick. Any help?

Thanks,
Benny

Which control are these events from?
It should be the AfterUpdate of the ComboBox I would think (NOT the TextBox control).

[BASSPU03]

Which control are these events from?
It should be the AfterUpdate of the ComboBox I would think (NOT the TextBox control).

Ah, you're totally right. However, shouldn't the code be different in the combobox? That is, shouldn't it "instruct" the textbox somehow?

[NeoPa]

In the code for the ComboBox (AfterUpdate - catches the operator making a selection) go to the TextBox and ensure it is up-to-date. Either by changing the Source formula or simply by passing the value in.

[BASSPU03]

In the code for the ComboBox (AfterUpdate - catches the operator making a selection) go to the TextBox and ensure it is up-to-date. Either by changing the Source formula or simply by passing the value in.

I'm sorry but I don't understand what I'm supposed to put into the AfterUpdate property of the ComboBox. The Textbox already has the...

=Forms!frmViewAllResources!sfrmAdminTab.Form!PurchaseRequestby
 

...in it. Is this what you mean by checking to see that it's up to date?

Again, I'm sorry. I'm not a total noob but I'm definitely on that side of the spectrum.

Cheers,
Benny

[NeoPa]

There may be a better way of handling this using the available functionality of forms, but I'm not very experienced in that area. What I know how to do is to set tha value of the TextBox control in the AfterUpdate event procedure.

Private Sub PurchaseRequestBy_AfterUpdate()
  Me.TextBox = Me.PurchaseRequestBy
End Sub

I haven't got the name of your TextBox so you'll need to fill that bit in yourself.