1. What does a "type mismatch" error mean?
It means that you are trying to compare apples with oranges!
You may have a statement such as:
If member-ID = user-request-ID THEN...
where you are trying to compare two data items.
The error means, quite simply, that the two values are not the same data type. Possibly one is text and the other is numeric.
The same error will occur for assignment statements where you are assigning a value to an item of a different data type, or if you are calling a procedure which is expecting a certain datatype but the value passed is not of the expected type (e.g. a form textbox value that is null or not date/numeric, and which has not been validated first). There will be many other situations when this can occur! Use the debugger to check the values in the variable(s) concerned.
2. Why do I get a "Type mismatch" error trying to use Recordsets in Access 2000?
The "Further VBA" Trainer was developed using Access 97, which used Data Access Objects when accessing tables in VBA code. In section 6, for example, the code in Figure 6.1 contains the line
Dim BookingTableRecords as Recordset.
This is fine in Access 97 but will cause the "Type Mismatch" error in Access 2000, as Access 2000 uses ActiveX Data Objects (with, confusingly, very similar initials).
To adjust code so that it works in Access 2000, do:
· Add the letters DAO so that the code reads like:
Dim <Recordset name> as DAO.Recordset
(replacing <Recordset name> with the correct name)
· Still in the code window, choose Tools-->References then ensure that the item 'MS DAO 3.6 Object library' is selected in the list presented; this is not referenced by default.
The code shown in the "Further VBA" Trainer should now work in Access 2000.
3. Why does my list box click event code no longer work?
If you have created a form object such as a command button, then delete the button, the code is left in the form module. If you create another button with the same name, the new button click event will be linked to the code, as the names are the same. This can be handy if you want to redo a form object, as you do not have to redo code.
However, if you delete and recreate a list box, the old code may not always be picked up. So, to re-link it to the list box, go to the object property box and simply recreate the relevant event; this should then link to the code.
Back to Top
4. How can I create a multi-purpose query/report which allows the user to enter a variable number of values each time?
Note - the suggestions below mainly use Query Design and the Forms Collection - VBA only comes into play for checking date ranges or for setting the sort order at run-time for a report.
Click here for an Example database - this is the result of following the instructions below, plus some extra bells and whistles.
Click here for explanatory notes - this explains about the extra bells and whistles.
The Query
When a system is up and running, the user may want to do various one-off queries. The information from these queries may be wanted quickly, and the user will not want to go through negotiations with the system developer to produce the report and arrange a price. It is useful to provide a general-purpose query if possible. For example, the Chelmer Leisure Management (see scenario in Access Further Skills textbook) may want queries such as:
Details of all members with a particular category, with certain dates of birth, with certain sporting interests, by surname, by gender, etc.
Rather than having lots of separate queries, it is possible to create a query that picks up the required values from a form via parameter values in text and combo boxes, etc.
30/3/2006. See the Getting Started VBA Trainer section 7.3.5 for how to save this query dynaset to an Excel spreadsheet.
Try the following:
· Create a form with unbound text boxes that are to contain the user's criteria for a selection of fields from the membership table. Make sure that the text boxes are the same data type as the corresponding field on the membership table.
· Create a query to select all the fields from the membership table.
· Add a command button to the form to run the query. Clicking on the button will bring up a list of all the members. The user can then simply look at this, or copy/paste it into a spreadsheet for further analysis. You could also use VBA code to export the data to an Excel spreadsheet; look up DoCmd.TransferSpreadsheet - it's very easy to use.
· Querying on a numeric field:
Enter [forms]![Memb query]![category no] as the criterion for the category number field, enter the number 2 in the category field on the form and run the query again. Only those records for members with category number = 2 will now be listed. But if you delete the category value and run the query, nothing at all is listed! (as there are no members with a category number field that is null). To correct this, change the query criterion to:
Like [forms]![Memb query]![category no] & * (* is the wildcard character)
Now, leaving the field blank will list all categories and entering a number will list just the required category. Have a look at the SQL to see how the query works.
Note that this uses the 'Forms Collection'. This is how Access allows you to reference fields on an open form. If you try running the query when the form is closed, little parameter boxes will pop up for you to enter the values in.
· The example database shows a method using a drop-down box based on a Union query.
· Querying on the starting characters of a text field:
Enter the following for the criterion for the last name:
Like [forms]![Memb query]![Lastname] & "*"
Now, entering a category of 1 and a lastname of J will list all members with a category of 1 and lastname beginning with J.
This is also useful for postcode analysis, e.g. listing all members in the CH1 area of Chelmer.
· Note that if the user leaves the parameter box blank then this criterion will not select rows where this field is Null as "LIKE '*' " does not select Null values. There must be a non-Null entry in every row for this field. It would therefore be useful to set a default value for the field and to set the Required property to Yes. This comment amended 30/3/2006.
· Querying on characters within a text field:
Enter the following for the criterion for the sporting interests field:
Like "*" & [forms]![memb query]![sporting interests] & "*"
and try this out with, say, aerobics (or even 'aer').
· Note that if the user leaves the parameter box blank then this criterion will not select rows where this field is Null as "LIKE '*' " does not select Null values. There must be a non-Null entry in every row for this field. It would therefore be useful to set a default value for the field and to set the Required property to Yes. This comment amended 30/3/2006.
· Querying on a yes/no field:
Enter the following for the criterion for the sex field:
Like [forms]![memb query]![sex] & "*"
This field is (confusingly!) specified as a yes/no field in the book, so the entry in the form field has also to be yes/no here. It would be better if this was simply M or F in a single-character field, and the user would then select via a combo box.
· The example database shows a method using a drop-down box based on a table.
· Querying on a specific value in a date field:
Enter the following for the criterion for the date of birth field:
Like [forms]![memb query]![date of birth] & "*"
Now you can specify a particular date or all dates.
· Querying on a range of dates.
In some cases, the user may want a range of dates, for example:
Between [forms]![memb query]![date of birth1] And [forms]![memb query]![date of birth2]
but in this case the user must enter values in both dates. It is not possible to use the wildcard * with 'between' or <, =, > for a date field. Access Help states: "Wildcard characters are meant to be used with text data types, although you can sometimes use them successfully with other data types such as dates, if you don't change the regional settings for these data types".
However, the following is a way round this, by using hidden fields on the form, in addition to the ones above, and VBA code:
(i) create two new text fields on the form, dob1 and dob2 and delete the labels. Format as date fields.
(ii) change the query criterion for the date of birth field to
Between [forms]![memb query]![dob1] And [forms]![memb query]![dob2]
(iii) code the following in the command button click event code, before the command to run the query:
If Not [Date of Birth1] Then ' put the user's value in the
dob1 = [Date of Birth1] ' query field
Else ' or set a default early date
dob1 = #1/1/1001#
End If
If Not [date of birth2] Then ' as above, but set a
dob2 = [date of birth2] ' default late date
Else
dob2 = #12/31/9999#
End If
Now run the query and experiment - watch the values that get set in the dob1 and dob2 fields.
Finally - change the 'visible' property for dob1 and dob2 to 'No', so that the fields are now 'hidden'.
· Querying on a mixture of parameters or none.
Now try entering a mixture of values in the fields on the form, and see how the query picks up the values and selects appropriate records. Leaving all fields blank will select all records.
All the above has been based on just one table, but it is also applicable to a form based on a query joining two or more tables.
The Report
As a report can be based on a query, then, instead of having the form command button run a variable parameter query, as done above, choose to run a report instead.
The report header should list the parameter values that the user entered in the form fields. This is very straightforward. Create unbound text box field controls in the report header and enter Forms![form name]![field name] in the control to pick up the value from the form (with the appropriate form and field names, of course!). If there is no value in the form, then this will show as blank.
The example database demonstrates all this, with the parameters shown in the report header and with an option for the user to specify the sort order at run time.
Back to Top
5. How do I update a table value via a form? For example, subtracting quantity sold from the stock total.
The form shown above is a very simple form based on a table and using three fields, StockNo, Description and QtyInStock. The form is to be used to record sales data.
The form fields for the table fields are locked (by setting the appropriate field property to No) to prevent the user changing them. As these form fields are bound to the table, any change to the value on the form will also change the underlying table row.
The AmtSold field is an unbound text box - i.e. it is not bound to any table.
The ConfirmSale button is a non-Wizard command button (use non-Wizard buttons for purposes that the Wizards do not cover).
The VBA code is as follows:
· Form Load event (for when the form is opened):
[AmtSold].SetFocus 'move cursor to total sold field
[ConfirmSale].Enabled = False 'disable sale button until sale entered
· Before Update event for AmtSold field (for when user enters a value in the amount sold field):
[ConfirmSale].Enabled = True 'enable sale button
· Click event for the ConfirmSale button: (for when use clicks on the button)
[QtyInStock] = [QtyInStock] - [AmtSold] 'adjust stock total
[AmtSold].SetFocus 'move cursor from button to
[ConfirmSale].Enabled = False 'stop user hitting button twice
·So, when the form is opened the sale button is disabled. When a sale is recorded, the button is enabled so that the user can click on it to confirm the sale. This activates code that updates the quantity in stock; as the form field is bound to the table row, this will automatically update the table. The button is then disabled to stop the user from clicking on it again; alternatively, the form could be closed.
·It is also normal to have the unit price on the form plus a calculated field to show the value of the sale (quantity x unit price). See Access FAQ 10 How do I make a calculated field in a query, form or report?
In a properly designed system, the stock code number would be typed in (simulating a scanner) and the form opened showing just that record. There would also be checks (in the ConfirmSale click event) such as:
· AmtSold > 0
· AmtSold not null
· AmtSold <= QtyInStock
The 'Getting Started' VBA Trainer covers validation of data entered into fields on a form.
Back to Top
6. How do I create my own keys? For example, membership key = M123456.
See the creating custom primary keys example database.
Back to Top
7. How can I change the size of a form at run-time?
Example - suppose you want a form to open so that it is always maximised:
· In the Form_Load event, code: DoCmd.Maximise
This will maximise the size of the form - and of all other windows (apart from pop-up windows).
· In the Form_Close event, code: DoCmd.Restore
This will restore the form (and all other windows) to their previous size.
Look up Maximise and Restore in Help.
Also look up Minimise and SelectObject.
Back to Top
8. How can I cater for production of (mailing) letters? changed 15/12/2003
Using a fixed letter set up as a report is a method often used by students. The report is designed with the letter text in a text box on the report, and with the variable data picked up from a bound table or query. This is very inflexible, as the user will have to go back to the developer to get the letter changed. This method may or not require VBA, depending on the complexity of the report.
The Mailing Letter example database demonstrates how to:
· create individual and bulk letters by typing the text in a form, picking items from drop-down boxes, etc.
· use Mail Merge (with a simple query and one that picks up a parameter value from a form)
All except the simple query Mail Merge use VBA.
Back to Top
9. I'm using the debugger for VBA code and get the message "The value in the field or record violates the validation rule for the record or field". What does this mean?
This has happened when one decides to stop the debugger, after replying 'Yes' to 'This command will stop the debugger'.
If you then run the code without the debugger everything is OK.
So it would appear to be caused by the stopping of the debugger, not an error in your code (I think...).
Back to Top
10. How do I pass an array as a parameter?
The two methods below show how to specify and use an array as a parameter in a private or public sub procedure or function. The example given is of a Public procedure, but this method also applies to Private procedures and/or Functions.
Method 1:
Public Sub myProc(prmArray() As <datatype>)
· Where <datatype> = the datatype as in the declaration of the array, or Variant.
· Note that the actual bounds of the array are not specified; the open and close brackets () indicate that this is an array.
· The parameter as passed to the procedure must be an array or else the calling code will not compile: "Type mismatch: array or user-defined type expected".
Method 2:
Public Sub myProc(prmArray as Variant)
· A Variant variable can cope with any datatype, including an array. Note that there are no () after the array name.
· The calling code will compile if the parameter passed to the procedure is not an array (compare with Method 1).
· The procedure must therefore check at run-time that the parameter is an array:
If [Not] IsArray(prmArray) then ...
Both Methods:
The array definition in the procedure header does not specify the bounds of the array, but Access provides two useful functions LBound and UBound which determine these at run-time:
For IntCount = LBound(prmArray) to UBound(prmArray) ...
Back to Top
11. What does the message "Compile Error: Expected variable or procedure, not module" mean?
This can occur when you have a module name the same name as a function (so Access gets confused, understandably!). Change the module name.
See also Access FAQ 26 What does the message Undefined function '<name>' in expression" mean? .
Back to Top
12. What does the message "Run Time Error 2001 - You cancelled the previous operation" mean?
This message is far from helpful, as it bears no obvious relation to the problem!
It can occur in VBA when Access cannot make sense of part of an embedded SQL statement, or when elements within a Domain Aggregate Function are incorrectly specified.
Example:
Look at the Staff Holiday Booking example database. The Holiday form uses a DSum statement to add up the total holiday days booked:
Forms![Staff]!txtSumOfDays = DSum("days", "qryHolDates", "[staffId] = " & Me!staffId)
If you change the "days" to "day" for the first parameter to the DSum statement then you will duly get the error message, as there is no field called "day" on qryHolDates.
The solution is to look carefully at each element of the SQL or Function, and check that they are entirely correct. Put all SQL in string variables, then you can check the contents at run (i.e. failure) time in the Debugger. It could also be useful to put the 3rd parameter (the SQL criterion) for a Domain Aggregate Function in a variable for checking at run/failure time.
Tip. it can be useful to try the function without the optional 3rd parameter (the SQL criterion). If it does not fail, then the fault is in the criterion, so check that carefully. If it still fails, then the fault lies with one (or both!) of the first two parameters.
See the end of section 6.6 of the 'Getting Started' VBA Trainer for a list of things that cause errors in embedded SQL - many of these also apply to Domain Aggregate Function code.
You may also get this error message when you hit OK on an unexpected parameter box, without actually entering a value. The parameter boxes (as you should know) pop up when Access cannot find the item specified - the usual cause is a misspelled name, or an incorrect name for a form or field.
Back to Top
13. Why doesn't the Round function round up numbers all ending in 5? Sometimes it rounds up and sometimes it rounds down.
Examples: Round(4.5,0) returns 4
Round(3.5,0) returns 4
The answer can be found at http://www.tek-tips.com/faqs.cfm?fid=3734 . The text at this site as at 11:13 6/12/2004 is copied below (as links can sometimes be removed).
(My thanks to Matthew Dean for pointing this site out to me, when I asked him why Round was apparently inconsistent/wrong).
http://www.techonthenet.com/access/functions/numeric/round.htm has a really neat solution! 8/3/2005.
"If you wanted to round 12.65 to 1 decimal place in the traditional sense (where 12.65 rounded to 1 decimal place is 12.7, instead of 12.6), try adding 0.000001 to your number before applying the Round function:
Round(12.45+0.000001,1)
By adding the 0.000001, the expression that you are rounding will end in 1, instead of 5...causing the Round function to round in the traditional way.
And the 0.000001 does not significantly affect the value of your expression so you shouldn't introduce any calculation errors."
|
Start of text from tek-tips site....................................
The Round() function rounds 2.45 to 2.4 and is correct. Why?
faq705-3734
|
|
Before you use the Round() function in Access, please read this FAQ or you may introduce calculation errors into your application:
Syntax: Round(expression[, numdecimalplaces])
The Access Round() function appears to be a handy way of rounding decimal values to a given number of decimal places. For example, Round(2.341, 2) returns 2.34.
However, it does not perform rounding the way you might expect. Take this example:
Round(2.385,2)
You would expect this to return 2.39, but if you open an immediate window and type ?Round(2.385,2) you will get the following output:
2.38 (not 2.39)
Even though the last digit was 5, it rounded DOWN.
If this is not the behaviour you expected, then here’s the explanation:
The Round() function does a round-to-even, not the more well-known round-to-larger. If the argument ends in 5, the number may be rounded up or down to achieve an even rightmost figure.
So in our example above:
2.385 could be rounded to either 2.38 or 2.39. The former has an even rightmost figure, so this is the result.
The reasoning behind this is to eliminate cumulative errors over a series of calculations. Put another way, 100 half pennies should round to be equal 50 cents, not 100 cents.
A RoundToLarger() function:
Public Function RoundToLarger(dblInput As Double, intDecimals As Integer) As Double
'Implements a variant of the Round() function, that rounds-to-larger
'rather than rounds-to-even, like in Excel:
Dim strFormatString As String 'Format string
Dim strResult As String
'If input is zero, just return zero. Else format as appropriate:
If dblInput <> 0 Then
strFormatString = "#." & String(intDecimals, "#")
If Right(strFormatString, 1) = "." Then
strResult = Format(dblInput, "#")
Else
strResult = Format(dblInput, strFormatString)
End If
Else
strResult = "0"
End If
'If the result is zero, Format() will return "." - change this to "0":
If strResult = "." Then
strResult = "0"
End If
RoundToLarger = CDbl(strResult)
End Function
Acknowledgement: Thanks to “hnawebmaster” for help in compiling this FAQ.
Dave Mc Donald
www.mcdonaldit.com
End of text from site....................................
|
31/1/2005. I think there is an error in this function if the result is zero. The lines:
'If the result is zero, Format() will return "." - change this to "0":
If strResult = "." Or strResult = "" Then
need changing as shown above in red, or the run time error 13 'Type Mismatch' may occur.
I have also had a go at my own function, but it's dependant on the maximum values allowed by the data types, so will not work for numbers with more than 15 digits in total. The code is shown below, for those who are interested.
Public Function myRound(prmNumber As Double, prmPlaces As Integer) As Double
'own function to round 5-9 up and 0-4 down
'VBA Round function rounds-to-even for .5.
'IMPORTANT - if prmNumber has more than 15 digits (regardless of the where the dec. pt is)
' then the Double datatype would appear to truncate the least significant digits
'try testing with ?myround(12345678901.12345,2) and look at the value passed in prmNumber
Dim dblIntegerPart As Double 'to store the required digits
Dim dblDecimalPart As Double 'to store the surplus decimal places
Dim dblTemp As Double 'for intermediate calc only
'move the decimal point by the required number of digits
'example, 4.45 required to 1 decimal place gives 45.5
dblTemp = prmNumber * (10 ^ prmPlaces) 'note: 10^0 = 1
'truncate the value to get rid of the unwanted decimal places
dblIntegerPart = Fix(dblTemp)
'subtract truncated value from temporary number to find out what the unwanted digits are
dblDecimalPart = dblTemp - dblIntegerPart
'are the unwanted digits +0.5 to +0.99999etc or -0.5 to -0.99999?
If Abs(dblDecimalPart) >= 0.5 Then
If prmNumber < 0 Then
dblIntegerPart = dblIntegerPart - 1 'subtract 1 for -ve number
Else
dblIntegerPart = dblIntegerPart + 1 'add 1 for +ve number
End If
End If
'put decimal point back in original position and return the rounded value
myRound = dblIntegerPart / (10 ^ prmPlaces)
End Function
Back to Top
14. How can I add a criterion to a Crosstab query?
See also Access FAQ 17 What is a CROSSTAB query?.
NOTE added 12/4/2005 - this item also applies to 3-D charts. If you look at the SQL generated by the Chart Wizard you will see that it is a TRANSFORM query. If you wanted to add a criterion, then see the information below.
Simple literal criterion.
To add a criterion with a literal value specified in the query is fairly simple. Add the column to the query, set the grouping to ‘Where’ and add the value in the criteria cell. The SQL shown in Access FAQ 17 with a criterion of [Category No] = 1 will then be (Where clause shown in bold):
TRANSFORM Count(Membership.[Category No]) AS [CountOfCategory No]
SELECT Year([Date of Birth]) AS [Year of Birth]
FROM [Membership Category] INNER JOIN Membership ON [Membership Category].[Category No] = Membership.[Category No]
WHERE (((Membership.[Category No])=1))
GROUP BY Year([Date of Birth])
PIVOT [Membership Category].[Category Type];
Parameter criterion.
However, if you used a criterion that used a parameter such as [Please enter Category No] or a Forms Collection reference such as forms!frmcategory![Category No], your query would fail with a message similar to:
The Microsoft Jet database engine does not recognise “[Please enter Category No]” as a valid field name or expression.
or
The Microsoft Jet database engine does not recognise “forms!frmcategory![Category No]” as a valid field name or expression.
A way round this is to put the criteria value(s) in a table and use IN for the criterion expression.
Example (using the query from Access FAQ 17 as above):
1. Create a new table called TempCat, with a numeric field called Category No.
2. Create a form with an unbound textbox called txtCategoryNo.
2.1 In the txtCategoryNo_AfterUpdate event code:
DoCmd.RunSQL "DELETE * FROM TempCat"
DoCmd.RunSQL "INSERT INTO TempCat VALUES (" & txtCategoryNo & ")"
This will delete any previous rows from the table, and add the new value from the textbox to the table. See Part 6 of the Getting Started VBA Trainer for information about the DoCmd.RunSQL statement (which includes information about suppressing the various information messages that you will get when deleting/inserting table rows)..
3. Alter the Crosstab query criterion to IN (SELECT [Category No] from TempCat), so that the SQL now looks like:
TRANSFORM Count(Membership.[Category No]) AS [CountOfCategory No]
SELECT Year([Date of Birth]) AS [Year of Birth]
FROM [Membership Category] INNER JOIN Membership ON [Membership Category].[Category No] = Membership.[Category No]
WHERE (((Membership.[Category No]) IN (SELECT [Category No] from TempCat)))
GROUP BY Year([Date of Birth]), Membership.[Category No]
PIVOT [Membership Category].[Category Type];
The query will now pick up the required parameter value, which can be varied by changing the value in the textbox.
Tables used to store parameter values do not have to have just one row (though the SELECT SQL in the IN clause must select only one column). The SQL statements can generate several rows in a table. For example, if you wish to select values between a pair of dates, and if the date parameters on a form are called txtStartDate and txtEndDate, then the code to generate rows in a table called TempDate which has just one (date datatype) column could be as shown below. Note that dates in SQL statements must be in USA format!
Dim dtDate As Date
Dim strDate As String
'delete previous rows from table
DoCmd.RunSQL "DELETE * FROM TempDate"
dtDate = CDate(txtStartDate) 'set to start date on form
Do Until dtDate > CDate(txtEndDate) 'loop until end date has been passed
'dates in SQL statements must be in USA format
'so must reverse day and month before putting in SQL statement
strDate = Month(dtDate) & "/" & Day(dtDate) & "/" & Year(dtDate)
'write date to temp table
DoCmd.RunSQL "INSERT INTO TempDate VALUES (#" & strDate & "#)"
dtDate = DateAdd("d", 1, dtDate) 'add one day
Loop
Access Help.
Look up “Create a parameter query” in Access Help.
Additional note - 1st March 2005
A possible public function to do this date-reversal (and to be used wherever needed) is:
Public Function myUSADate(prmUKDate As Date) As Date
'Convert date from dd/mm/yyyy (UK) format to mm/dd/yyyy (USA) format
Dim strDate As String
strDate = Month(prmUKDate) & "/" & Day(prmUKDate) & "/" & Year(prmUKDate)
myUSADate = CDate(strDate)
End Function
And it would be used in an embedded SQL statement as follows:
DoCmd.RunSQL "INSERT INTO TempDate VALUES (#" & myUSADate(dtDate) & "#)"
Use the Debugger Immediate Window to test the function, and specify dates as dd mmm yyyy to avoid confusion, as Access also dates dates specified as dd/mm/yy in this window to be USA format. For example:
?myUSADate(#1 mar 2005#)
Further note - 6th April 2006
If you create a wizard combo box to find records using a date field, then Access generates the following code to convert the dates to USA format:
"#" & Format(Me![cboFindDate], "mm\/dd\/yyyy") & "#"
(where cboFindDate is the name of the combo box)
Back to Top
15. Why does a calculated date give the wrong result in an SQL statement?
Access 2002 Help says:
“You must use English (United States) date formats in SQL statements in Visual Basic. However, you can use international date formats in the query design grid.”
(Search with ‘dates in SQL’)
If you have a form with a date field called txtStartdate, a variable called dtDate of Date datatype, and a table called TempDate with just one column for a date, then the following statements will all insert the correct date in the destination table:
dtDate = txtStartDate
DoCmd.RunSQL "INSERT INTO TempDate VALUES (#" & dtDate & "#)" ‘from variable
DoCmd.RunSQL "INSERT INTO TempDate VALUES (#" & txtStartDate & "#)" ‘from form
DoCmd.RunSQL "INSERT INTO TempDate VALUES (#" & Date & "#)" ‘today’s date
However, if you want to do a calculation on a date (calculate a date a library book is due back, for example) statements such as
dtDate = DateAdd("ww", 2, Date) ‘add 2 weeks to today
'dtDate = DateAdd("d", 14, Date) ‘alternative method
DoCmd.RunSQL "INSERT INTO TempDate VALUES (#" & dtDate & "#)"
will treat the date returned from the DateAdd statement as being in mm/dd/yyyy format (I.e. USA format). So you will need to reverse the day and month elements of the date, as demonstrated in VBA FAQ 14 above. Putting the date in a textbox on a form and then using the value from that textbox does not work, even though the textbox displays in correct UK format!
If the day number value of the date is greater than 12 (20/2/2005 for example), Access seems to realise that the date is in UK format and will make adjustments itself (20th February 2005). But if not (3/5/2005 for example), it will assume USA format (5th March 2005 rather than 3rd May 2005).
Back to Top
16. Why don't the Date and Time functions work in my module?
Some students have experienced problems where a call in VBA code to the Date() or Time() function returns Null or an invalid/incorrect value (and this can cause a run-time failure 'invalid use of Null' in some cases).
The problem seems to occur in a form or report module where there is a table, report or form field called Date or Time. If you code something like:
Dim dtDate as Date
dtDate = Date() 'get system date
then Access appears to refer to the field called Date, not to its own function. (It would be handy if this situation gave rise to a compilation error or a specific run-time error, then at least you know what was going on).
Solution 1.
Don't use the names Date and Time for table, form or report fields.
(The Chelmer Leisure database from McBride does use these names, unfortunately).
Solution 2.
Create your own functions in a separate Access module. As the module is not bound to a table or query, there is no conflict between names, and Access will use its own functions correctly. Code the following two procedures:
Public Function myDate() As Date
myDate = Date
End Function
'-----------------------------------------------------------
Public Function myTime() As Date
myTime = Time()
End Function
Then code as follows to get the system date and time:
Dim dtDate as Date
Dim dtTime as Date
dtDate = myDate() 'get system date
dtTime = myTime() 'get system time
Back to Top
17. Why doesn't my 'On Error' code trap the error?
Check that you are using the same version of MS Access as MS Office.
For example, if you are using MS Access 2003 with MS Office 2002, then the 'On Error' code does not appear to be invoked; any error condition is intercepted by Access with the usual Access error message and the code will fail.
However, if you use an Access 2002 MDE file created with Access 2002, and then run it on a machine that has Access 2003 with Office 2002, all appears to work OK.
Back to Top
18. How can I get totals on a form?
You may have something like an Order form with a separate (or sub) form for each Order Line, and want to show the order totals (from counting/adding information from the order lines) on the Order form.
Look at the 'Crash Course' in Access Basics document.
The relevant bit is section 5.5, Exercise Frm4, 'put totals on a form', part (b). The example here counts up a total number of attendees at a class (using the Chelmer Leisure scenario), using DCount. If you want to add up order totals (i.e. total cost of the order) you will need to use DSum to sum up the appropriate value.
Look also at the VBA Trainers and the Example Databases for examples of use of these functions.
Back to Top
19. How can I copy details from one form to another?
This is very easily done using the Forms Collection.
There are examples of use in sections 4.3.8 and 8.4.6.2 of the 'Getting Started' VBA Trainer (available from the Student Advice Centre) and Appendix I discusses this Collection briefly.
Suppose (for example) you have a customer form which opens an Order form, and you want to copy customer details to the Order form automatically. You would code the assignment statements in the Form_Load event for the Order form.
Look also at the Example Databases for examples of use of the Forms Collection.
20. What does “Run-time error ‘3061’: Too few parameters. Expected 1” mean?
I have had this error when using the OpenRecordset method (DAO code) when I have misspelled a table field name in the SQL. After experimenting a bit, I found that it also occurs with some SQL syntax errors.
If you misspell the table name with SQL in DAO code then you may get run-time error 3078 instead; this has a very clear error message explaining the likely cause of the error.