473,847 Members | 1,525 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Subform Combobox Autofill Another Subform Textbox

55 New Member
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
Oct 29 '07 #1
13 6237
Luke Bailey
21 New Member
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.
Oct 30 '07 #2
BASSPU03
55 New Member
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 "PurchaseReques tby" and the table is "tblAdminTa b," 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 "PurchaseReques tby" column instead of just the desired selected/current value. And, most importantly, I still want the combobox to be a textbox.

Any further thoughts?
Oct 30 '07 #3
Luke Bailey
21 New Member
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
Oct 31 '07 #4
BASSPU03
55 New Member
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.
Nov 1 '07 #5
BASSPU03
55 New Member
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:
Expand|Select|Wrap|Line Numbers
  1. =Forms!frmViewAllResources!sfrmAdminTab.Form!PurchaseRequestby
[It automatically dropped the brackets.]

Main form: "frmViewAllReso urces"
1st tabbed subform: "sfrmAdminT ab"
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).
Nov 1 '07 #6
BASSPU03
55 New Member
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.
Nov 1 '07 #7
NeoPa
32,584 Recognized Expert Moderator MVP
That's a little bug in the forum software. It's being looked at :)
Nov 1 '07 #8
BASSPU03
55 New Member
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:
Expand|Select|Wrap|Line Numbers
  1. =Forms!frmViewAllResources!sfrmAdminTab.Form!PurchaseRequestby
  2.  
This is the "template" version of what I did above:
Expand|Select|Wrap|Line Numbers
  1. =Forms!FORMNAME!SUBFORMNAME.Form!COMBOBOXFIELD
  2.  
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
Nov 5 '07 #9
NeoPa
32,584 Recognized Expert Moderator MVP
...
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).
Nov 5 '07 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

3
4511
by: Nicolae Fieraru | last post by:
Hi All, I have a problem and I can't figure out how to solve it. My database has three tables: tblCustomers, with CustomerId and CustomerName tblProducts, with ProductId and ProductCode tblPurchases, with PurchaseId, CustomerId and ProductId I created a form with a subform which shows all the customers, one at a time
1
3105
by: New2Access | last post by:
I posted this yesterday but perhaps I wasn't clear enough. I have a table called History with 4 fields "Week" "Employee" "Project" and "Hours" Each of those fields (except Hours) are based on seperate tables with a 1 to many relationship to the History table. I have a form called WeeklyInput for data entry into the History table. There is a combobox for "Weeks" which defaults to the current week. There is a textbox for "Employee"...
1
3090
by: shumaker | last post by:
I've loked over older messages on this, but haven't found a solution. I have a datasheet of records, and each record has a subform that can be viewed with the expand '+' symbol. The subform simply shows addition fields of the record it is expanded from. I used the subform control wizard to tell it how to link the records when creating the subform. There is only one control in the subform that the users can edit, a textbox. All the...
2
6400
by: DBQueen | last post by:
Hi - In my A2K database, I have a form with 2 Unbound Comboboxes, both of which open up forms to a particular record. One of these comboboxes works perfectly. For some reason the 2nd one WILL NOT autofill. If I enter a number to lookup by typing it in, it ALWAYS says it's not in the list (Error 2237) - but if I select the item, it works fine. I only have 1 field in this combobox - the SampleID. It is set up with the following...
2
2713
by: Andre Ranieri | last post by:
I'm retouching our corporate web site that, among other things, allows customers to log in and pay their invoices online. I noticed that on the checkout page, the credit card number textbox autofills with past entries for that textbox. My development workstation has the Google Toolbar installed; I'm not sure whether the autofill functionality is native to the browser or coming from the Google Toolbar. In either case, is there any way...
18
3617
by: Robert Jacobs | last post by:
Please advise... I currently have a Mainform (Customers) and a Subform (Service Requests) with a one to many relationship (one customer, many service requests) with a CustomerID that is unique for each customer and is autofilled on the subform. Subform contains field called TicketID (which there could be multiple instances of and is not referenced on the mainform at all). I want to be able to type in a TicketID and have the mainform...
0
2063
by: Randy | last post by:
Hi, I have some comboboxes that are created dynamically at run time based on user actions. I found a procedure on a message board to autofill the combobox text from the dataview that the comboboxes are bound to. It works perfectly on the comboboxes that I have created at design time, but the ones that I create runtime behave differently. The problem is that when the user tabs onto the combobox, the box stays blank, which is fine. ...
11
9500
by: Beany | last post by:
Hi, Can some please provide me with the code for counting records in a subform and displaying it in a textbox? Does this code go into the control source of the textbox? My subform is called 'Asset subform' within the form 'Form1'.. the textbox will be in the same form. Also im showing records in a subform by selecting a user from a combobox...
4
5800
by: mgstlucia | last post by:
I have a Form (frmsOrders) with a subform (frmOrdersDetails Subform). I am trying to automatically fill in the Item Description, Unit and Price when choosing the SKU# from a combobox. I have this working perfectly for the Customer and Address info in the main part of the form, but the same syntax does not work in the Subform. I have read the 'How to refer to items in Subform' article in the How To section and many posts on the subject. I...
0
9892
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10996
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10718
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10347
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9497
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7889
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7062
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5915
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4541
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.