How to add a custom button on the command bar to run code on a form. (Please see Q-Built Solutions "Custom Command Bar Button" article.
Top
How to add buttons to a custom command bar.
Q: I've replaced the built-in menu and toolbar with my own custom toolbar. How can I add some of the built-in functionality to my custom toolbar? Specifically, how can I add buttons for "Filter by Form" and "Toggle Filter" to turn the filter off?
A: Right-click on the menu bar and select Customize... on the pop-up menu to open the Customize dialog window. Select the "Commands" tab and then select the "Records" item in the left pane. Select the "Filter by form" icon in the right pane and drag it with your mouse to your custom menu bar. Select the "Toggle Filter" icon in the right pane and drag it with your mouse to your custom menu bar. Close the Customize dialog window to save your changes.
Top
How to "auto-complete" a form, with and without code. (See Q-Built Solutions "Auto-Complete Form" article.)
Top
How to change the number of Undo Levels.
Q: Microsoft Access 2002 allows up to 20 Undo Levels. Is there a way to change this so that only a few levels of Undo will be saved?
A: Yes. This requires a modification of the Windows Registry.
Top
How to create a hot key for a control on a form that has had the label deleted previously. See Q-Built Solutions Fix Its Tips.
Top
How to filter a form from an item selected in a combo box.
Q: I want my users to use a form that they can filter records that they have entered. The field that holds the user's name is Entered_By. How can I set up a combo box to allow the users to filter the form based upon the selection in the combo box?
A: Create a query such as the following:
SELECT DISTINCT Entered_By
FROM tblMyTable
WHERE (ISNULL(Entered_By) = FALSE)
ORDER BY Entered_By;
Replace tblMyTable with the name of your table. Save the query and name it.
Your form must be bound to the table or query that holds the records you want to filter. Open your form in Design View and open the Properties dialog window. Create a new, unbound combo box. On the "Data" tab of the Properties dialog window, select the Row Source Property combo box and select the name of your new query. Select the "Event" tab and select the OnAfterUpdate( ) event's build button and paste the following code into the form's code module:
Private Sub cboEntered_By_AfterUpdate()
On Error GoTo ErrHandler
DoCmd.OpenForm Me.Name, , , "Entered_By = '" & _
Me!cboEntered_By.Column(0) & "'"
Exit Sub
ErrHandler:
MsgBox "Error in cboEntered_By_AfterUpdate( ) in" & vbCrLf _
& Me.Name & " form." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & Err.Description
Err.Clear
End Sub
. . . where cboEntered_By is the name of the combo box, and the Entered_By field is a text data type, not a numerical data type.
Save and compile the code, then return to the form and close the Properties dialog window and open the form in Form View. Select an item in the combo box to display the filtered records.
Top
How to pause execution of code in the current form until another form closes.
Q: I have a set of steps that need to be executed after another form opens. These steps depend on user input from the other form. After the other form opens, the code executes without waiting for the user to finish typing information on the other form. How do I get my code to pause until the other form closes?
A: In the OpenForm( ) method of DoCmd, use the acDialog option to open the other form. The focus will change to the other form when it opens and will not return to the current form until after the other form is closed. The line of code immediately after the line of code that opened the other form will then commence execution. Until the other form closes, this line of code in your current form will not execute, thereby "pausing" the code in the current form.
Example syntax:
DoCmd.OpenForm "frmGetUserInput", , , , , acDialog
where "frmGetUserInput" is the name of your other form that collects the user input needed before your code continues to execute.
Top
How to use a form to update records from a table in a remote database, without linking to that table or writing VBA code for an ADO or a DAO connection.
Q: I have a database with linked tables in two different databases. Whenever I refresh the table links, I have to run the Linked Table Manager twice, once for the tables in one database, then the other. Only one of the tables, tblOrders, is in the second database, and only one form is using this linked table as a record source. I'd like to separate this table from the other linked tables, but I'm not a programmer, so I don't want to write the VBA code for an ADO or a DAO connection. Is there another way to do this?
A: Yes. One can use a query selecting the records from the remote data source, then set the record source of the form with the name of this query. An example of the syntax for the query on the remote database would be:
SELECT *
FROM tblOrders IN '' [;DATABASE=C:\Data\MyDB.mdb;]
ORDER BY OrderDate;
After saving and naming this query, open the form in Design View and open the Properties dialog window. Select the "Data" tab. Select the "Record Source" combo box and scroll down and select the new query's name. Save the form, then close the Properties dialog window. Open the form in Form View and you'll see all of your records just as if the form was using a query based upon a local table or a locally linked table as the record source.
Top
How to use a form to update records from a table in a remote database with a database password, without linking to that table or writing VBA code for an ADO or a DAO connection, and hide that password from the user.
Q: I have a database that needs a linked table in a database with a database password. The problem is that users can open the system table in the unsecured database and read the password. Even if I create a query on the other database that doesn't use a linked table, the users can read the password in the query's properties. Is there a way to hide this password but still have a bound form using this table as the record source?
A: Yes. One can open the form in Design View, then open the Properties dialog window and select the "Data" tab and set the record source of the form with the SQL statement that uses the remote database table as the data source. An example of the syntax for the query on the remote database with the database password would be:
SELECT *
FROM tblOrders IN '' [;DATABASE=C:\Data\MyDB.mdb;PWD=myPswd]
ORDER BY OrderDate;
For more security, instead of setting the form's record source property in the form's property dialog window, assign this SQL statement as a string to the form's record source property in the form's OnOpen( ) or OnLoad( ) event in the form's module. For example:
Me.RecordSource = "SELECT * " & _
"FROM tblOrders " & _
"IN '' [;DATABASE=C:\Data\MyDB.mdb;PWD=myPswd]" & _
"ORDER BY OrderDate;"
Next, select the "Other" tab in the form's Properties dialog window and change the "Shortcut Menu" Property to "No" and the "Allow Design Changes" Property to "Design View Only." Then close the Properties dialog window, save the form and close it.
Then compile the front end of the application into an *.MDE database file and distribute the front end to the users.
Top
What the Revert item on the File menu is for.
Q: I notice a "Revert" item on the File menu. What does it do or what is it for?
A: It's a "do over." When you make changes to the form or report while it is in Design View, you can change your mind and go back (revert) to the original form or report before you started making these changes.
Copyright © 2004 - 2006 Q-Built Solutions. All rights reserved.
Top
|
