Please fix the combo box RowSource datatype binding bug.

The bug is as follows:
1. Create a table [Table1] with two columns:
- [NumberField], datatype: Number, Double
- [TextField], datatype: Short Text

Add 1 Row to the table:
[NumberField] = 10
[TextField] = "Some Text 10"

1. Create a combo box:



2. Set the RowSource of the combo box to this:
   SELECT NumberField, TextField FROM Table1;




3. Add a Button to the form: [cmbErrorExample]
   Add On Click Event:
   If Me.cmbErrorExample.RowSource = "SELECT NumberField, TextField FROM Table1;" Then
   Me.cmbErrorExample.RowSource = "SELECT TextField, NumberField FROM Table1;"
   Me.cmbErrorExample.DefaultValue = "Some Text 10"
   Me.cmbErrorExample.Value = "Some Text 10"
   Else
   Me.cmbErrorExample.RowSource = "SELECT NumberField, TextField FROM Table1;"
   Me.cmbErrorExample.DefaultValue = 10
   Me.cmbErrorExample.Value = 10
   End If

Now open the form:
Drop the combo box, you should see the one row with values:
10 | Some Text 10
Select the row - the value of the field becomes 10.

Now, click the button. The Rowsource should change to:
"SELECT TextField, NumberField FROM Table1;"

However, if the user has already made a selection as instructed above, error appers:

Error Occurred in Sub cmdToggleRowSourceClick of Module FormForm1

Error The value you entered isn't valid for this field.

OK

Expected: the code should be able to programmatically change the Rowsource of the combo box.

Actual: Because the unbound field has had a numeric value assigned as a value, the programmatic change of the RowSource of the combo box to a text value results in a type mismatch that should not be happening.

This used to be possible in Access 2003, but in Access 2019 the combo box appears to change itself to be bound to a numeric data type, even though it is an UNBOUND field.

This is a problem when creating forms that change combo box data sources programmatically.

This URL contains a .accdb with a sample form that demonstrates the issue:
https://1drv.ms/u/s!AhezOlimTLoah-V27m77K4MRlU5loQ

I've been looking more into Power Apps, and I think it would be great for you to work with the Power Apps team to provide a web and mobile solution to what Access can do today on the premises of a server. Because there is unfortunately no web version of Access, maybe Power Apps can help solve this problem? Ideally, we should be able to continue moving (and linking) Access data into SQL and then create a Power App on top of the "Access-created" data from SQL; essentially viewing a Power Application that looks exactly like access but online and on the phone.

I know it's hard to believe but there are a lot of us in the world (yes, even in the United States) that must occasionally work disconnected from a network. At our site we've nursed Jet 4.0 replication along for as long as we can and now it's starting to break as MS has backed away from supporting it. We are moving to a centralized SQL database with mobile hotspots but there are some places this simply will not work reliably. We will be forced to 'roll our own' replication method or bring on significant additional SQL expertise to see if it is even possible to reproduce our replication scheme using SQL. Jet replication worked great for us and Microsoft inexplicably decided to drop it.

1. When a query that selects identifiable rows joins to a query that aggregates data, allow rows in the identifiable part to be updated rather than making the query non-updateable. Extend this to heterogeneous joins so if one table in the join is updateable but another is not, allow updates to the updateable one.


2. Stop smothering expressions with excessive numbers of parentheses. There is no reason that I can think of that would require an individual column name to be enclosed all by itself in parentheses.


3. Stop reformatting MY SQL strings. I have a work-around for this but it is iffy. I switch to SQL view and save the query. As long as I never switch to QBE view again, Access doesn't normally rewrite my query but that isn't a guarantee.


4. Stop helping me by renaming all fields to Exprx when a table goes missing or at least keep track of it and fix them when the table is relinked.

MS Access's ACE SQL engine continues to run with a variant form of ANSI-89 with some ANSI-92 features while the rest of the relational database world has adopted more recent ANSI standards and at the very least fully comply to the universal, lowest common denominator standard accepted in the industry: ANSI-1992.

Being a highly used, worldwide database for small to medium-sized shops and its popularity with the Office suite, enhancing Access's dialect to industry standards will add value to current and future users including the Microsoft brand. Even SQLite, a similar, very popular file-level database as open-source counterpart to Access, has a richer, more expressive updated SQL dialect.

Such specific features that would greatly enhance the expressiveness, portability, and efficiency of Access queries would include:

ANSI-1992

• Support of other joins - FULL OUTER JOIN, NATURAL JOIN, and CROSS JOIN; with use of USING clause;


• Support of CASE statements as more enhanced, portable version than IIF() especially for nested logic;


• Support of distinct aggregate functions: COUNT (DISTINCT ...), AVG (DISTINCT ...), SUM (DISTINCT ...);


• Support of wildcards operator, %, in GUI rather than * or use of ALIKE or restricted to ODBC/OLEDB queries;


• Support of CAST for data type changes as opposed to the non-portable C-functions: CStr, CDbl, CLong, CDate, etc.;


• Support of COALESCE for handling of NULL across columns as opposed to nested IIF(..IS NULL) or NZ();


• Elimination of the parentheses requirements for more than one JOIN which often frustrate many new users and require the use of Access GUI's Design View to build complex queries;


• Support of ON clause conditions on single table fields rather than joining tables first and then running WHERE.

Other ANSI

• ANSI-1999: Common Table Expressions (CTE) using WITH clause for more readable, maintainable, and less repetitive SQL code than subqueries and derived tables (supported in SQLite as of version 3.8.3, released 2014-02-03);


• ANSI-2003: Window functions using OVER and PARTITION BY clauses to supplant the domain (DSum, DMax, DCount, etc.) functions with more efficiency than correlated subqueries (supported in SQLite as of version 3.25, released 2018-09-15);


• ANSI-2003: Support for MERGE statement for synchronous UPDATE and INSERT needs based on conditional logic.

Other recommendations

• Support of EXPLAIN <QUERY> to allow users evaluate query operations especially use of indexes;


• Support of CREATE DATABASE as opposed to requiring code for creating .mdb/.accdb files;


• Support of CREATE TABLE AS instead of only SELECT * INTO for clarity of DDL action;


• Support of CREATE VIEW AS instead of requiring code with QueryDefs for creating stored queries;


• Distribute the ACE engine (.dll) as a shared object (.so) with CLI (similar to SQLite) for Unix (Mac, Linux) users (e.g., SQL Server 2016/2017 is now supported on Linux).

With backwards compatibility, above changes is a challenge but such features can be integrated with newer versions of the ACE engine (.dll) to be distributed to users, or with add-on patches, or entirely new Access version.

Additionally, though Microsoft strategically intends to keep SQL Server as its flagship database system and upgrading Access may somewhat interfere with this objective, arguably Access can be situated as a complement and not substitute to the enterprise server RDBMS. Rather than advancing Access’ front end GUI features for use as a complement to Excel, consider strengthening MS Access as a standalone, small-scale database engine product.

My idea is similar to the one called "Provide a native grid control similar to the visual studio gridview or even the old flexgrid" EXCEPT I want it to be a BOUND crosstab that is editable. This blog describes how to fake an editable crosstab, but it is a lot of work http://alisadev.blogspot.com/2009/06/how-to-fake-editable-crosstab-in.html. My best example is that for a school, they want to view the grades in a grid, but be able to edit them. Classes go down the side, Quarters go across the top, and Grades will be in the middle. When the user is hand-entering the Grades for Quarter 2, the user wants to be able to see the existing grade for Q1 next to it. I could create this by dumping the data into temp tables and creating a crosstab view, but then it is not editable because the crosstab is not bound back to the underlying normalized tables. This is not summary data (like # sales per month per employee), but just data from normalized tables being presented in an editable grid. The solution / process would be to take the data from normalized tables, de-normalize it and present it in an editable crosstab (with maybe a temp table behind it), and then normalize the data again and save it back to the source tables.