Liv,
Sure, I could use a query but then, I don’t learn anything. On the other hand, if I can’t get this to work soon, I’ll be taking your advice. Thanks for responding.
Wayne,
Thanks for taking the time to respond. Made the change you suggested and obtained the same performance I have always gotten. It occurs to me that the problem is not the way I am trying to implement DLookup() but something else. Maybe I do not understand how to put together a table. Here’s what I am trying to do.
Among my responsibilities is writing documentation and the process is somewhat cyclic in nature: write documents, submit to review, respond to review comments, edit document, resubmit to review, and so on until all reviewers are satisfied and everyone is in agreement. I decided to put comments and responses in a database so I could track their status more easily. I have a form that presents the comment, my response, and check boxes to indicate whether my response has been accepted and whether it has been edited back into the document.
What is missing is a text box containing the document section that the comment was written against. Among my tables is one (tblComments) the contain a code (numeric) indicating the document section, the comment (memo), and my response (memo) to the comment. There is another table (tblDocSections) that contains a numeric field for the section code and a memo field for text of the section. SecCode is a primary key in tblDocSections.
DLookup() actually is working but not the way I expected. The first few comments are general in nature and do not pertain to a specific section. I have a specific code for the and the memo field for the section text contains an appropriate remark. When I open the form, DLookup() returns a null for this value. As I step through the comments, the general section text does not show up until entry 17, long after it should appear. Once I hit that point, the general section text appears two or three more time in a row (as it should, all the general comments are sequentially at the beginning of the table). As I continue through the list, DLookup() again returns nulls until I get to around comment 50. At that point it returns the text of the next section. Of course, this is way past the item it should be coordinated with. DLookup() then returns nulls through the rest of the list.
The behavior is that of an array index not getting set to what it should be. I have tried two different formats for the section code and get identical results with either format: same error at the same locations in the list of comments. If I go into the comments list and change a section code to one of the sections that does get returned, then that text does show up at the comment where I changed the code. All of this suggests to me that DLookup() is working and that I actually have a different problem related to table structure.
I know this is a lot to ask but I really am stuck. Any suggestions will be greatly appreciated.
I just made the following observation: When the SecCode is 100, DLookup() returns the text for SecCode 10, when it is 200, DLookup() returns the text for SecCode 20. This ought to be a clue but I need some direction. Thanks.
