Unanswered: Unexpected Results With Access 2000 Form & Query
I just started using Access 2000 and converted an Access 97 database which is giving me unexpected results from my (old) form and query. My form has a text box that is referenced in a query when I click the command button. The text should appear in the query output and is not used as criteria. The command button starts a macro which runs the queries. In the Field row, the syntax to make the text appear in the query results is MyText: [Forms]![MyForm]![MyTextBox].
I also have a combo box that gives me the same problem. When I view the query results, these two columns, that should display text from the text box and text from the combo box, are blank. I observed the same results when running the application in Access 2000 before converting.
I made a new form with a text box and combo box and a command button to run a query that references these controls; it worked perfectly! So after using the Access 97 application for about a year, why does Access 2000 fail to do what is expected with an old form. It would take many, many hours to rebuild the application from scratch.
Try just redoing the .Rowsource for the listbox etc. I think you're experiencing a DAO conversion problem which does not always yeild an error message. In 2K, you have to reference DAO explicitly or it may not always work (i.e. Dim Db as DAO.Database, Rst as DAO.Recordset etc.) Access uses DAO natively for all that it does and perhaps some of your controls' source strings did not convert correctly. Try it on one and see. You should not have to build the whole thing from scratch.
Thanks, but that did not work. The combo box has a rowsource which I removed and then added back. The text box has no rowsource. I tried deleting the text box, then compacting the database and added the text box back again using the same control name. Still the query will not pick up what is in the text box or combo box.
The only thing you might also check is the references under Tools on the code page. Access 2000 may not have properly picked up all of the references to allow your form to work. I have had this happen to me and when it did, I frequently couldn't use the TRIM() function [which is one of the basic built-in functions of access]. I had to go back to the references and usually found the word "MISSING" next to one or another. I would clear the checkbox next to it and scroll down to find the same item (usually with a higher version number) and check that. Following that procedure usually got evrything back up to speed. Compare the new DB you said you made with the old converted one in terms of these references and see if you see any differences.
After further research, I see that my problem is in using OutputTo from a macro. Previewing of the query results, by adding an OPENQUERY to the macro, shows that the text box and combo box text values are displayed in the query results. When I use OutputTo to export the select query results to a text file, the two text items do not make it across. I will continue to try to solve this.
I tried your suggestion to look at refrences from TOOLS while editing a Module, but there was nothing obvious to me in the huge list, that anything was missing. I have checked 1) Visual Basic for Applications, 2) Microsoft Access 9.0 Object Library 3) Microsoft DAO 3.6 Object Library 4) OLE Automation, and 5) Microsoft Visual Basic for Applications Extensibility 5.3.
My query was unable to export from text controls on a form using a query and OutputTo. The columns in the output were blank, and I found the solution. After examining the query results put into a table, I saw in table design mode that the fields that should have contained information from the text controls were of the type BINARY.
The solution I used was to convert using CStr in the query field row, such as
This issue came up when I upgraded to Access 2000.