473,687 Members | 3,552 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

ComboBox or SubForm?

675 Contributor
I have a form, bound to tblMain. I have tblSub, which is related to tblMain with a foreign key, all nice and proper database design.

I want to display the rows of tblSub in a control, but neither ComboBox nor SubForm seem to be easy to implement.

The primary purpose of this control is that when a row is clicked, then "strWork = strWork & xxx" where xxx is the correct field of the subform, or the correct column of the combobox.

I will also need to edit, delete, and add rows, both by the user, and with VBA.

Use - when clicked, the .column contains the correct text
Update - Cannot know what to change, and will need another form to actually do any modification of the data. Note that this is not a "Not in List" issue as it may be an edit of a row that already exists, therefore not-in-list fires, but new row should not be added.

Use - there is no nice event for a row being clicked. OnCurrent fires for other reasons also, so the concatination creates errors. Will require OnClick for every field.
Update - easy, as all updatable fields can be displayed, unlocked, and enabled.

How should this problem be resolved?
Apr 2 '09 #1
17 3282
32,569 Recognized Expert Moderator MVP
I don't have a full solution for you OB, but with a subform, could you not add an unbound control (CheckBox, CommandButton, etc) that triggers the code instead of OnCurrent. Typically, just by clicking on the control, the desired record will be selected in the subform. Just an idea.
Apr 3 '09 #2
675 Contributor
Yes, of course I can do that. It is just that it takes space.

I have needed this solution in several databases, and others might also. I am fishing for ideas. Thank you for yours.
Apr 3 '09 #3
32,569 Recognized Expert Moderator MVP
I hear you OB.

I'm not aware of a more built-in alternative. Let's see what some of the other experts can come up with.
Apr 3 '09 #4
489 Recognized Expert Contributor
Hey OB,

If you use a subform in data-sheet view you can use the forms click event to fire your code. When a user clicks on the record-selector the event will fire.
Apr 4 '09 #5
675 Contributor
Thanks for another suggestion. Yes, either continuous forms or datasheet view will (optionally) have record selectors. The click event fires if the record selector is clicked. It does not fire if any field is clicked.

A combobox or listbox will fire a click if any column is clicked, not just the left side.

A subform is easier to correct/update, because cboControl.colu mn(2) = "xxx" produces an error, even though the control is bound. Subforms are harder to work with during design, however.
Apr 4 '09 #6
675 Contributor
I've decided that I need to go with a sub-form. I will have a click event on each textbox, and no record selectors, as space is a consideration. The subform will make it easier to add, delete, and update the records.

I used the wizard (access 2000) to create the subform control. I cannot display my records in the correct order.

1) Can I specify a sort order in design view? If yes, how?

2) From VBA, how do I change the sort order of a subform from code in the main form? From code in the subform?

Main form name = fMain; Sub-form name sfDetails
Apr 6 '09 #7
32,569 Recognized Expert Moderator MVP
The order records are shown in a form depends on the RecordSource. A query can have the ORDER BY clause set which will give you your required sorting.
Apr 6 '09 #8
32,569 Recognized Expert Moderator MVP
If you're talking about changing the sort order after the form has already been displayed, then each form has an OrderBy property. See Referring to Items on a Sub-Form for how to reference that from your main form. I don't know if a .Requery of the subform is required after changing the property.
Apr 6 '09 #9
675 Contributor
I've avoided using subforms because I can't make any changes without closing everything, doing my editing, and then restarting the program, but that is probably another thread.

So I'm really a beginner here. My subform (on the main form) has properties SourceObject and LinkChildFields which appear to be the determining factors as to what is displayed. I don't think these are what you refer to.

Within the subform itself, there is an OrderBy property. That seems to do nothing. Why not? So the only way is to work with the RecordSource property directly? This would therefore be true in both design view and VBA.
Apr 6 '09 #10

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

Similar topics

by: Nicolae Fieraru | last post by:
Hi All, I am trying to change the rowsource of a combobox when I click on it. I played with many events, associated with the form and the combobox, but still haven't figured out what is the way of doing it. I have a table with products, tblProducts, some of them are Active while others are Inactive. The form shows all the products purchased by a customer, both Active and Inactive in a ComboBox, cbProducts. My client wants to view all...
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"...
by: Tom | last post by:
I have the following tables: TblState StateID State TblCounty CountyID StateID County
by: bcreighton | last post by:
I have created a bound subform on an unbound masterform linked together with a common field (A store's identification number) using an unbound combobox on the masterform and an invisible field on the subquery, which filters for a particular store. The subform is in continuous mode. So, what you see when you select a store is a list of all the items that that have not been billed for. There is a simple yes no field that filters out all...
by: Mariel | last post by:
Hello, I hava a main form F1, form with a subform F2 in datasheet view. There is a combobox in the main form C1, and a combobox C2 in the subform. I want the dropdown options of the combobox in C2 to change depending on the value selected in C1. I've tried to specify the datasurce for the C2 values as:
by: ApexData | last post by:
I have a ComboBox, when dblClicked, displays the combobox's row content in a continuous subform. Changes can be made to this content in the subform and then closed. The ComboBox needs to be requeried to immediately reflect these changes. The problem is that I can't requery in the OnDblClick event of the ComboBox because the event completes once the subform is launched. I guess I could requery the combobox from the Close event of the...
by: ApexData | last post by:
COMBOBOX REFRESH DILEMMA ' I have been working for hours trying to figure out how to requery a combobox in a subform, from a Popup form ' that this subform had launched. Basically, I designed a form that pops up when the user dbl-clicks a combobox. ' This popup form displays the table that the combobox uses. The user can change the content, and the combobox ' rowsource in-turn will change. This all works great in my mainform, but...
by: BASSPU03 | last post by:
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....
by: Rosie | last post by:
I have a main form with header info w/ 'tHeader' as the control source. I have a subform with 'tDetail' as a control source. They're strung together by a field named MA_ID. This works fine. I have a field within the subform that I want to update via a combobox located on the same subform.
by: sirdevo | last post by:
There is one combobox on a subform named Report Soil Series Subform1. The combobox is named cboSoilSeries. I need to disable this so the user can't enter values. I could also disable the whole subform since this is the only control on the subform. I've tried this line of code but it says you can't disable the control while it has focus. Forms!!.Form.cboSoilSeries.Enabled = False The combobox does not have focus when I run that line of...
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...
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
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...
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
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...
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
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: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.