Search results

I tried using the built-in function InStrRev in a query. Even though this function is documented in the VBA help system, I get an "undefined function" error message when I run the query. I'm using Access 2000 and my references are VBA, Access 9.0 Object Library, OLE Automation, MS ActiveX Data...

I have a form that displays selected fields from a single table. The primary key field (Account) is shown in a combo box. The BoundColumn = 1 (the key = the 1st field). I want the user to be able to type in a key value and then have the form display the other fields from that record. But...

I’d like to get a really good grounding in Access VBA programming. Over the years I’ve encountered it only in a piecemeal, haphazard fashion and am constantly confused by how to go about accomplishing particular tasks. The syntax especialy is confusing. I bought the “Access 2000 VBA Handbook”...

Reply: This is the last piece of the puzzle. By changing all the arguments to Variant, I can use IsNull in the function. The function now produces a result for all records. (I still kept Optional on all the arguments). Knowing how Access treats data types in arguments will make writing...

Reply #2: I've found that the "#Error" occurs whenever the table field is null. At least 1 of the 11 fields is null in almost every record. I thought making the arguments all optional would take care of this situation. Even after specifying default values for every argument (plus the data...

Reply: The function is designed to produce a code-value that indicates what action is to be taken with each of our accounts. All the arguments are optional and have their data type declared (no variants). I have a query whose source is a table with 157,000 records. One column has the function...

Reply: I got past a major hump with your help plus KnowledgeBase article 208401. That article lists the default values for each data type when optional arguments are not inialized with a default value. For string variables, test against zero-length string instead of Null. For numbers, test...

Reply: The function was made to produce an action-code that depends on 9 table fields. Several of the checks in the code test whether a field is null. So I followed your suggestion and made all the arguments Optional except the first. But when I test in the Immediate Window and skip an...

I wrote a new function that has several arguments. The function runs in a query that uses table fields as the arguments. The fields are text, dates and numbers. All I get is "#Error". What happens to the function when a field value is null? Does the function crash and display "#Error"? The...

I've made a survey in Access 2000. A series of forms has the questions on them. To answer each question, the person clicks an option button for "Agree", "Strongly Agree", "Disagree" or "Strongly Disagree". After answering all 80 questions, the person has created a record with 80 fields that...

I have an Access 2000 database on a network drive that is used by several people. This database has a single report which each person uses to print their own data. But the users would also like to save a snapshot of their report. I could add a button on the main form that would run a macro...

I have a subform that displays 20 detail records. When I change a control value on the main form, I want the subform records to change to show the new set of 20 records matching the main form. The subform has a filter to restrict its records to those that match the values in the main form. I...

After reading your replies and rethinking the whole structure, I realized it didn't have to be so complicated. The solution was to just use a simple textbox whose Control Source is the custom function. Only one calculated value was needed. thanks for your time.

Manually resizing seems to fix the problem. Thanks!

I had to make a custom function to calculate the value. A query uses this function and specifies that the control for 1 of the field values must equal the table value. This query is the Row Source for the calculated control (a listbox). The After Update event of each of the 2 field controls...

I've been using Access 2000 at work for 9 months (Windows 98SE). Recently, whenever I click on Help, the help window appears minimized at the top of the screen. I always have to maximize it myself. I never had to do this before. This behavior does not happen in Word or Excel. Both regular...

I have a form that displays 20 records at a time. On each line, 2 controls display table fields & 1 control displays a value calculated from the other 2. I want the value in that control to be recalculated & displayed whenever either of the other 2 values are changed by the form user. Only the...

I have a table with a date field and a currency field. I need to get a count of all the records where the date falls within various ranges. I could make a totals query for each range and then use those queries as the source for a combined query. But is there a simpler way? There are a lot of...

How can you see what users are still using a database? This would be helpful when trying to get people to exit a database so changes or fixes can be made.