Open the form in design view
Open the properties box to look at the properties of the form.
Click in the RecordSource property (Data tab.)
Click the Build button (...) beside this.
Access opens the query builder, so you can build the SQL statement.
(If the RecordSource was a saved query it opens that.
If it was a table, it asks if you want to build a query instead.)
Behind the scenes, it saves this as a hidden query anyway, but it does work
as a SQL statement.
If it crashed Access, then something else is wrong.
Perhaps you tried to do this in Form view (not design view.)
Perhaps you omitted a field: Access can crash if a field suddenly disappears
or changes data type while the form is open.
Or perhaps it's a Name AutoCorrect error:
http://allenbrowne.com/bug-03.html
Or it could be a corrupt form. (Try a compact/repair.)
Once you have this working, your next question was about how to
programmatically change the RecordSource while the form is running. You
don't want to change the stub of the SQL statement (the SELECT and FROM
clauses), and probably not the tail either (ORDER BY, GROUP BY, etc.)
Therefore you just need to patch in the WHERE clause between the stub and
the tail. The code to do that looks like this:
Dim strWhere As String
Const strcStub = "SELECT [Record-Orders-Sales].*, ... FROM ... " &
vbCrLf
Const strcTail = "ORDER BY [Record-Orders-Sales].SalesOrderID;"
'Save any changes in progress
If Me.Dirty Then Me.Dirty = False
'Build the filter string as shown in the example database.
strWhere = "WHERE SomeField = 999 " & vbCrLf
'Now assign this to the form's RecordSource
Me.RecordSource = strcStub & strWhere & strcTail
Since you now have 2 techniques, the final question is which is better? To
apply a Filter, or to change the RecordSource? There are specific cases
where each is useful, but as a rule of thumb for starting out, apply a
filter if you want the user to be able to remove the filter easily, and
change the RecordSource if the user should not be able to get at the other
records, or if there are performance reasons for not loading the other
records.
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
markmarko said:
Thanks Jeanette,
I was hoping that was possible... I tested putting an sql statement
(copied
from a query) in the recordsource property directly, and it crashed
access.
So, I'll take your word for it that vba can do it.
Now on to the tricky part....
In order to 'filter' by Salesperson (Salesperson is related to Sales Order
via a junction table, since a Sales Order may have more than one
Salesperson). Here's the sql:
SELECT [Record-Orders-Sales].*, [Query-Junction-Cust_Account].*,
[Junction-SalesOrder_Contractors].AssociatedContractorID
FROM ([Record-Orders-Sales] INNER JOIN [Query-Junction-Cust_Account] ON
[Record-Orders-Sales].[AssociatedCustAcct#Junction] =
[Query-Junction-Cust_Account].JunctionID) INNER JOIN
[Junction-SalesOrder_Contractors] ON [Record-Orders-Sales].SalesOrderID =
[Junction-SalesOrder_Contractors].AssociatedSalesOrderID
WHERE
((([Junction-SalesOrder_Contractors].AssociatedContractorID)=[Forms]![Entry-SalesOrder]![Filter-SalesRep1]))
ORDER BY [Record-Orders-Sales].SalesOrderID;
In order to filter by 2 sales peeps, I'd want to add to the string ala
Allen
Browne's filter model, but the a bit I'd need to add would be 'injected'
into
the above sql, not at the end.
Here's what the sql would look like for filter by 2 sales peeps, (again,
from a query (that works)).
SELECT [Record-Orders-Sales].*, [Query-Junction-Cust_Account].*,
[Junction-SalesOrder_Contractors].AssociatedContractorID,
[Junction-SalesOrder_Contractors_1].AssociatedContractorID
FROM ([Junction-SalesOrder_Contractors] AS
[Junction-SalesOrder_Contractors_1] INNER JOIN ([Record-Orders-Sales]
INNER
JOIN [Query-Junction-Cust_Account] ON
[Record-Orders-Sales].[AssociatedCustAcct#Junction] =
[Query-Junction-Cust_Account].JunctionID) ON
[Junction-SalesOrder_Contractors_1].AssociatedSalesOrderID =
[Record-Orders-Sales].SalesOrderID) INNER JOIN
[Junction-SalesOrder_Contractors] ON [Record-Orders-Sales].SalesOrderID =
[Junction-SalesOrder_Contractors].AssociatedSalesOrderID
WHERE
((([Junction-SalesOrder_Contractors].AssociatedContractorID)=[Forms]![Entry-SalesOrder]![Filter-SalesRep1])
AND
(([Junction-SalesOrder_Contractors_1].AssociatedContractorID)=[Forms]![Entry-SalesOrder]![Filter-SalesRep2]))
ORDER BY [Record-Orders-Sales].SalesOrderID;
It's the same as the first version, with the
[Junction-SalesOrder_Contractors_1] bits added. The trick is that it's
added
kind of in the middle. For this one instance, I'm sure I could make it
work,
but I want to make a model that I could scale to allow building the
recordsource sql to include other factors, and need a systematic way to
add
to the sqlString.
Any advice?
