There’s nothing to get the current function name, but you can build a fairly lightweight tracing system using the fact that VBA object lifetimes are deterministic. For example, you can have a class called ‘Tracer’ with this code: Private proc_ As String Public Sub init(proc As String) proc_ = proc End Sub Private Sub Class_Terminate() … 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
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
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
Here is a way to catch most outcomes of interacting with the dialog; Dim value As String value = InputBox(“Please enter a #”, “Determine Limit”, 10000) If (StrPtr(value) = 0) Then MsgBox “You pressed cancel or [X]” ElseIf (value = “”) Then MsgBox “You did not enter anything” ElseIf (Val(value) = 0 And value <> … 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
I think you should avoid this path at all cost, and try and persuade management into redevelopment. It’s a bitter pill to swallow, but this is going to need to be redeveloped sooner or later, and you are just saving them time and money.
There is so much misinformation in the answers in this thread that I don’t know where to start. I just spent 4 points in reputation voting down the answers with misleading and wrong information in them. the Jet database engine (which is all that’s involved here, as the OP clarified with an edit) is by … Read more