Replies

Here is a link to a site with a free Access97 or 2000 add-in that is supposed to do what you want: (http://www.trigeminal.com/index.asp?1033) without the parentheses. I have another possible solution for you: If you have a user who wants to create their own custom forms and reports, but they need to use the tables in an existing database, why not simply give them a front end, that only contains links to those tables? Then they can design their own queries, forms, and reports. If they must use some of your reports, they could use your front end when necessary (two shortcuts, one to your original front end, that you continue to modify as you see fit, and a second shortcut to their front end, that they control).

Do you really need to actually delete the table? If you’re just trying to start fresh, you could run a delete query and delete all the records instead.

If the value already exists, let the user select it from a dropdown list, then jump to that record, either on the same form, or open another form to that record.

Forget the variable. If there are only a few regions, have command buttons named for each region, that run a macro that opens your form where Region=RegionName. If there are too many regions, then use a form where the user enters the region as a parameter and clicks “okay”. Let this “okay” button run a macro which opens your form where Region=value from textbox on parameter form. Let the macro close the parameter form after it has opened the recordset form.

Since this is being shared over a network, the users must use the same mapped drive letter to point to the computer where your database resides. In other words, if the database is on a server named XYZ, and user number one has this mapped as I:XYZ, but user number two has it mapped as L:XYZ, you will have the problem you described. You need to consult with everyone who will use this database, and come up with a common mapped drive letter (everyone has XYZ mapped to the same letter).

You can try this: Instead of one report, make three more copies and alter the copy names by adding Qtr1, Qtr2, Qtr3, and Qtr4. Let all four reports point to the same query, but alter the week fields on the reports so that they have a default value of the appropriate week( you may need to use a select statement, where week1=1, week2=13 (for Qtr1)). I am at home and I can’t remember exactly what you need to do, but I’ve done this before and I’m positive this will work. Also, remove the parameters in the query. Let your four buttons open the reports. This way, the user won’t be asked for a parameter.

One quick thing to I’d try: Create a new report based on the same query (automatically using the wizard) and don’t worry about the design, just whether you can see the values you want. Then copy your code, but refer to this new report. This is just a quick test to eliminate the report as a suspect (if the new report works, then something may have corrupted the original report or the code). In the past, I have placed a copy of a working database onto a different computer, and some feature of the database no longer functioned properly. This has occurred even if the computer had the same version of Access and the same version of Windows. I have seen identical code (or macros, buttons, etc…) on seemingly identically setup computers, that works on one, but not on the other. If I delete the non working feature and recreate it from scratch, it then functions properly.

Similar to Jerry’s answer: Instead of using the field “Discounted” on the report, use an unbound textbox, with the following data source: “=IIf ([Discounted]>0,[Discounted],””)”. This means if TRUE show the discounted value, else show nothing (quotequote with no spaces between the quotes).

I’m not exactly sure what you’re trying to do, but I think you’re saying that you have an existing query, and you want a user to be able to select records based on the field of their choice (i.e. Begin_Date, Last_Name, SSN_Num). The user may know the last name of a customer, but not the social security number (or visa versa). Or they may need to select all data for a particular date. For this example, I would simply make three copies of the same query, but one query has “Enter Begin Date” as the criteria in the “Begin_Date” column, the second query has “Enter Last Name” as the criteria in the “Last_Name” column, and the third query has “Enter SSN Number” as the criteria in the “SSN_Num” column. On your form, have three buttons labeled appropriately so that users can then choose how they want to select data. If you just want to display the data, just have the buttons open the correct query. If you want the results on a form or report, things get a little more complicated, but I would just have three identical forms or reports (named slightly different) and let the buttons open the appropriate form/report, which would be based on the corresponding query.

It sounds like you’re only interested in fields with value = TRUE. If this is the case, then instead of trying to query for TRUE values, just select all records regardless of the values. Then you could base a report on the 49 fields, but in the report design use 49 text boxes, each with an IIF statement evaluating for TRUE. If the field = TRUE then display “TRUE” (or “Yes”), but if the field = FALSE display nothing (“”) (quotequote). You can also have a total of the TRUE values (running sum) for each field, if you need this information. This can provide a way to view the data you’re interested in (report preview), and of course you could actually print a hardcopy of data if needed. The results would never actually exist in a table, but that isn’t always necessary as long as you can simply run the report to view what you need to see, and calculate totals on the report, thus providing additional statistical information.