As long as the column only contains numeric values with no alpha’s then you can use: NewNumeroCommande = Dmax(“[Numero de Commande]”, “Demande”) + 1 Note: This is not my recommended method but just an in-built option of Access, if possible then you can save yourself the hassle by using Autonumbering, or alternatively you could have … Read more
This technique is known as cascading combobox. A very common topic. Be aware that if combobox RowSource uses an alias (displays text when saving ID) the text will not be available for display in all records when the list is filtered. This is especially disconcerting for users of continuous/datasheet view form. Could have code that … Read more
I find the answers to this question to be problematic, confusing and incomplete, so I’ll make an effort to do better. Q1: How can we make sure that the write-user can make changes to the table data while other users use the data? Do the read-users put locks on tables? Does the write-user have to … Read more
You can use IN: SELECT t1.*, t2.* FROM T1 INNER JOIN (SELECT * FROM atable IN ‘C:\Docs\DB2.mdb’) t2 ON t1.ID=t2.ID EDIT: sc = “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\docs\other.mdb” cn.open sc s=”SELECT * FROM t1 INNER JOIN ” _ & “[MS Access;PWD=databasePWD;DATABASE=C:\docs\db.mdb].t2 ON t1.ID=t2.ID” rs.Open s, cn EDIT 2: You can use the aliases to identify which database a … Read more
You can use Allen Browne’s Search criteria form example as the basis for your search form. He provides a sample database in Access 2000 format and also a page which displays the code behind the form. Edit: You wanted your query results presented as a datasheet in the same form. AFAIK, there is no way … Read more
Indoors today waiting out the hurricane, so I figured I’d create this custom solution. Answers for all of these steps are found elsewhere, but it was not simple to sort through all of the contrived solutions so I hope this answer is more useful all around. The basic answer for changing the rows to columns … Read more
You can use the StrComp() function with vbBinaryCompare for a case-sensitive comparison. Here is an example from the Immediate window to show how StrComp() works. See the Access help topic for more details. ? StrComp(“a”, “A”, vbBinaryCompare) 1 ? StrComp(“a”, “A”,vbTextCompare) 0 StrComp() returns 0 if the first two arguments evaluate as equal, 1 or … Read more
I used a subquery for the GROUP BY which computes the Sum of Err for each group. Then I added the ConcatRelated function (from Allen Browne) with the fields returned by the subquery. This is the query and the output (based on your sample data in make_table_bp) from the query: SELECT sub.[Name], sub.Cat, sub.[Desc], sub.Thresh, … Read more
You could write it this way: SELECT Distinct tbl.Chain, tbl.Store, ConcatRelated(“Warehouse”,”tbl”,”Chain=””” & [Chain] & “”” and Store=””” & [Store] & “”””) AS Warehouses FROM tbl;
The best solution is obviously to use Windows security. If that is not suitable, here is a possible alternative trick, exploiting the fact that Access remembers all opened connections until the program is closed: copy the connect string of one of your tables create a passthru queries “ptqConnect” and enter any fast SQL statement in … Read more