========= QUESTION --------- What are some ways I can manipulate strings within VBA? ========= ANSWER --------- see EXAMPLES ========= EXAMPLES --------- Title field has values like: Company Manager, Company Assoc, Company Director, etc...Remove Company: =IIf([Title] is null,"",Replace([Title],"Company ","")) ========= APPLIES TO / KEY WORDS --------- Microsoft Access String Manipulation Strings VBA [...]
========= QUESTION --------- How can I use VBA to loop through and rename a long list of files in several sub-directories? ========= ANSWER --------- see EXAMPLES ========= EXAMPLES --------- Private Sub RenameFiles_Click() Dim fName As String Dim fName_New Dim myPath As String Dim myCounter As String DoCmd.Hourglass True DoCmd.SetWarnings False Set [...]
========= QUESTION --------- How do I format numbers, dates, etc in my VBA code? ========= ANSWER ---------see EXAMPLES ========= EXAMPLES --------- Format(rs!Store, "0000") Formats a number field that may contain only one digit (i.e. 2) to four digits with leading zeros (i.e. 0002) ---------= Format(Now(), "hh:mm AMPM")If the current time is 5:04:23 in [...]
========= QUESTION --------- How do I add a record to a table using SQL? ========= ANSWER --------- see EXAMPLES ========= EXAMPLES --------- SQLTxt = "INSERT INTO 0000T_Log (Dt, Log) VALUES (#" & Now() & "#, '" & Me.CurrentStatus & "')"CurrentDb.Execute SQLTxt ========= APPLIES TO / KEY WORDS --------- Microsoft Access SQL Insert [...]
========= QUESTION --------- I have a database that I use on a regular basis, and when I am finished working with that database, I invariably always move on to the same second database file. Is there a way for me to open another file using a button or sub within VBA? ========= ANSWER --------- [...]
========= QUESTION --------- Is there a Trunc or Truncate function in Microsoft Access, like there is in Microsoft Excel? ========= ANSWER --------- No - but you can write your own very easily!In Microsoft Access, create a new Module, and enter the following code: Function Trunc(vNum As Double, vDec As Integer) As Double Trunc = [...]
========= QUESTION --------- When synchronizing in Microsoft Access, I get the following error: ERROR: -2147467259 - File sharing lock count exceeded. Increase MaxLocksPerFile registry Entry. ========= ANSWER --------- Adjust the registry entry MaxLocksPerFile to increase the maximum number - see: http://support.microsoft.com/kb/815281 (Method 1) Start button, Run Type: RegEditClick OK--- For Access 2000, Access 2002, [...]
========= QUESTION --------- I have a table with one record per entity, and a field with the number drawing entries they have. How do I take this and create a table with one record for each of those drawing entries? ========= ANSWER --------- Use the code in EXAMPLES below to open each record in [...]
========= QUESTION --------- Can I create directories from within my VBA code? ========= ANSWER --------- see EXAMPLES ========= EXAMPLES --------- MkDir "c:TOTNExamples" --------- If Len(Dir("c:TOTNExamples", vbDirectory)) = 0 Then MkDir "c:TOTNExamples"End If ========= APPLIES TO / KEY WORDS --------- VBA Directory Directories md MakeDirectory Make Directory ========= REF --------- http://www.techonthenet.com/access/functions/file/mkdir.php [...]
========= QUESTION --------- How do I ignore an error for one section of code, but then disable ignoring errors for other sections of code? ========= ANSWER --------- See EXAMPLES ========= EXAMPLES --------- To ignore errors: On Error Resume Next To turn off ignoring errors: On Error GoTo 0 ========= APPLIES TO / [...]