473,881 Members | 1,601 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
17 3296
2,653 Recognized Expert Specialist
Seems like your subform SourceObject is table/query. Isn't it?
Apr 6 '09 #11
675 Contributor
It was a Table, with only the table name in Record Source. That is the way the Wizard set me up. Seemed like the subform Order By property should allow me to display these records in my preferred order. Couldn't make that work.

Record Source is now a query "SELECT ... FROM ... ORDER BY Type, Location". That does work, and "If it ain't broke, don't fix it!" applies now. I will go that way.

But it does seem like if I wanted command buttons over each column, I should only have to change the Order By property and not have to run a query against the original table each time. Sort the 10 displayed records rather than SELECT from 50,000 records and then sort.
Apr 6 '09 #12
32,584 Recognized Expert Moderator MVP
I believe the ordering is not applied unless you set OrderByOn to True in your code :
Expand|Select|Wrap|Line Numbers
  1. {Form}.OrderBy = "{Field1},{Field2}"
  2. {Form}.OrderByOn = True
This property is not shown for forms in the Properties Window, so it's easy to miss.
Apr 7 '09 #13
675 Contributor
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     Me.OrderBy = "Type DESC"
  3.     Me.OrderByOn = True
  4. End Sub
This code did not sort the records on the subform. As a matter of fact, the first time the subform was updated (i.e. when the first record was displayed on the main form), it showed zero records. Changing to the 2nd record then displayed all the records for the subform. But not sorted! returning to the first record displayed correctly.

Using VBA to set OrderByOn, and the property sheet to set OrderBy did not work either.

I always seem to have trouble using subforms. I guess this thread started when I tried to avoid using a subform. Referencing controls on a subform gives me a headache.
Apr 7 '09 #14
32,584 Recognized Expert Moderator MVP
I'm afraid I haven't got an example to play with. I got the info from the help system. If I get some spare time I may see if I can knock something up, for curiosity's sake, but I don't use them much either :(
Apr 7 '09 #15
675 Contributor
Don't take your time. I'm going to use a query, not a table, to bind the form. This seems to work OK. At least for now I get predictable results and for this application the table is only a couple of thousand records, so efficiency not an issue.

But, if a form is bound to a dynaset, is there a guarantee that the records of the form are in the same order as the dynaset? For now, seems true!
Apr 7 '09 #16
32,584 Recognized Expert Moderator MVP
I would have expected the answer to be "No". After all there is an OrderBy property for the form, but your experience seems to indicate otherwise :S

It doesn't add up for me, but maybe I'm missing something.
Apr 7 '09 #17
675 Contributor
If the dynaset is not sorted, or sorted in an order that is incorrect for the form, then it makes sense that the form have an OrderBy property to sort the dynaset. I would expect this to work without the need for VBA coding.

As the sort order is not guaranteed in a Select Query, why would we assume that a form's dynaset have a predefined order. Viewing the table in datasheet view from the tables tab of the database window does not display the stored order.

Assume I create a table, and load it by typing rows in datasheet view. Assume the autonumber key field is random, not sequential. The keys are now out of order, although if I close & reopen the datasheet view, the rows will appear ordered. I believe that here the order is the order entered.

Assume I compact/repair this database. What is the order of this table now? All I can assume is that it is not in the order I want for my form.

I need my data in my order, and for now I will bind my data to my form by using a Select query as a Record Source.
Apr 8 '09 #18

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: 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: 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...
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: 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();...
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
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 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.