You want to use TEXT as the Control Source of a Control on your Form but you keep getting an error OR it doesn’t show as expected because your text contains an apostrophe or quotation mark(s). For help see chart below…
Control Source Property
Result
Explanation
=”This is literal text.”
This is literal text.
Literal text goes in quotes.
=”Here is a “word” in quotes”
Access thinks the quote finishes before word, and does not know what to do with the remaining characters.
=”Here is a “”word”” in quotes”
Here is a “word” in quotes
You must double-up the quote character inside quotes.
=”Here is a “”word”””
Here is a “word”
The doubled-up quotes after word plus the closing quote gives you 3 in a row.
When you have a Log On Form you can indicate what is to be entered by using the Format Property of the Control. By doing it this way the only value stored in the Table is what is entered, Enter Password is only for display.
In the Format Property of the Text or Memo field: ;@;”Enter Password” or @;”Enter Password”
If you want it to be in red use: @;[Red]”Enter Password”
This topic comes up from time to time in Forums and the answer usually involves a Temp Table or adding blank records or digging up that Microsoft® Report Utilities, How to Print a Constant Number of Lines per Group, sample which involved adding the controls you wanted to *hide* to the Function. I thought why not make something a little more flexible and without the need for Temp Tables, adding a bunch of blank records or adjusting the code each time. To read more click here.
***10.14.2018 tlkpUnitConversions has new entries and now includes Categories and Temperature conversions!
While I was working on my Grocery List to Recipe database (don’t ask, only been working on it for a year) I realized there was going to be a need for conversions and why not share? It’s pretty basic and does not include ALL conversions but just open tlkpUnitConversions and add new ones there. To download the sample click here.
When updating a record on a Continuous Form it has a tendency to scroll back up to the top. For End Users this can be problematic as they have now lost their place and have to scroll down to find it. Here’s two slightly different Function to prevent that…
This one will cause the record to float to the top of the Form but the scrollbar maintains its position…
Function fRequeryPlus(frm As Form)
'To use fRequeryPlus Me
Dim intRecordID As Integer
intRecordID = frm.CurrentRecord
frm.Requery
DoCmd.GoToRecord , , acGoTo, intRecordID
End Function
This one will keep record and scrollbar position intact…
Function fRefreshPlus(frm As Form)
'To use fRefreshPlus Me
Dim intRecordID As Integer
intRecordID = frm.CurrentRecord
frm.Refresh
DoCmd.GoToRecord , , acGoTo, intRecordID
End Function
Note, if you are deleting a record then only fRequeryPlus() will work UNLESS you only mark the record for deletion and delete upon closing the Form, in that case you can use fRefreshPlus(). Other than that you will see #DELETED in the controls for that record.
So, you checked Use as Form and Report Icon under Options > Current Database for your customized Icon to show on your Forms and Reports but when you opened your Form (or Report) there’s no icon…
Well, here’s the thing, if you choose No in the Properties window for the Form (or Report) under the Control Box then your Icon will not show:
Once you flip that to Yes, it shows. Yep, apparently that property is basically applied to the entire menu bar on the Form. So, if you REALLY want your Icon to show you need to set the Control Box to Yes and the Close Button to No. While it leaves the Close button exposed, clicking on it does nothing.
Tip: If you want the Icon to show but don’t really want any wording you can just put a space in the Caption Property.
Sometimes you want to load some data before your database opens? You don’t want your Users wondering what’s happening, so here’s a way to keep them advised of the progress. (To download this example click here.)
To use in your database, import the basProgressBar and frmSplash into your database. Then make design changes to frmSplash to coordinate with your database. (Note, do not remove or change the name of the Objects on frmSplash without changing the names (or making adjustments) throughout the code.) Enjoy!
There are many examples of how to handle new entries to Combo Boxes, a.k.a. Not_In_List events. However, the one that is not so easily found is how to handle entries that include more than just what’s in the Combo Box. For example, you’re entering an Order from a new Client and you need more than just the Client’s name… you want to enter their address, phone number, etc. To do that we just need to add/open a Form and add our information…
Private Sub cboClientID_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboClientID_NotInList
Dim intAnswer As Integer
intAnswer = MsgBox("Would you like to add this Client to the list?", vbYesNo + vbQuestion, "Not in List")
If intAnswer = vbYes Then
DoCmd.RunCommand acCmdUndo
DoCmd.OpenForm "sfrClientProfile", , , , acFormAdd, acDialog
Response = acDataErrAdded
Else
DoCmd.RunCommand acCmdUndo
Response = acDataErrContinue
End If
Exit_cboClientID_NotInList:
Exit Sub
Err_cboClientID_NotInList:
MsgBox Err.Description
Resume Exit_cboClientID_NotInList
End Sub
Note: Copy/paste everything between the Private Sub and End Sub and don’t forget to change the Form name to match your own.
Access MVP (2010-2015)
Recent Comments
No comments.