Query Not Found

[brooke48]

I have inherited a membership database which seems to have lost some of its functionality. Specifically, there is a Check Box labelled "Convert To Retired" on the MAIN MEMBER FORM which generates the run-time error '7874' ...Access can't find object 'CONVERT_TO_RETIRED'. This refers to an Update Query CONVERT_TO_RETIRED.



The Build Event code for this Query shows 3 tables - Members (Member No., Personal Details, etc,) Commitments (Member No., Commit No., Date Due) and Commitment Types (Commitment Number, Description, Amount, etc.). The fields Member No in Members and Commitments are joined and the field Commit No in the Commitments table is joined to the field Commitment Number in the Commitment Types table.

The Build Event code for the Check Box is:

Private Sub CONVERT_TO_RETIRED_CLICK()
DoCmd.OpenQuery "CONVERT_TO_RETIRED"
DoCmd.Close
End Sub

Can anyone show me how to tackle this run-time error please ?

[TheShabz]

Well, unlike many of Microsoft's error messages, this one is pretty self explanatory. It cannot find a query named "CONVERT_TO_RETIRED" to run. Check to make sure it exists in your query list and that the name matches EXACTLY.

[RuralGuy]

Does the "CONVERT_TO_RETIRED" query show in your list of queries? I would start by importing your db into a clean, empty new db and see if the problem follows.

[brooke48]

Corrected that (removed space) - thanks. Next problem is the running of the Query. The Command Button initiates the Query OK but first the query takes about 10 mins to run and then halts with the message "Not enough space on Temporary Disk". Physical space is really not the problem (20 Gb free). The db has 2,864 records which are all presumably being updated here. I just want the current record to be updated. To recap - I call up a particular record on the Main Member Form and then select the "Retired" check box to run the update on the Commitments Sub Form.

How do I restrict the update query action to the selected record ?

[TheShabz]

Temporary Disk is your RAM, not HD space. If you have a particularly large table you are querying off of (millions of records), you can run out of RAM pretty quickly. I've had to get my computer updated at work a couple time to get around this.

Please post the SQL you currently have so we can look at it.

[brooke48]

Thanks TheShabz. There are only 2,864 records in the members' table not millions and I've got 2GB of RAM and a 2 GHz speed processor so I think my system ought to be able to cope with this task - even if the query searches all records !

Here's the SQL from the query:

UPDATE COMMITMENTS AS COMMITMENTS_1, MEMBERS AS MEMBERS_1, [COMMITMENT TYPES] INNER JOIN (MEMBERS INNER JOIN COMMITMENTS ON MEMBERS.[MEMBER NUMBER] = COMMITMENTS.[MEMBER NO]) ON [COMMITMENT TYPES].[COMMITMENT NUMBER] = COMMITMENTS.[COMMIT NO] SET COMMITMENTS.[COMMIT NO] = 127;


COMMIT NO 127 is the new value I want to apply to the COMMIT NO. field in the table COMMITMENTS. It is one of the values contained in the COMMITMENT TYPES table. Can you help with the problem of updating this field for just the current record ?

[TheShabz]

I get the feeling theres something larger at play here. As I understand it, that checkbox takes a certain member or members which are passed in and makes them as "retired." I dont see how that query accomplishes the task. Am I missing something here?

[brooke48]

If you are - I definitely must be ! The checkbox should run the update query which changes the value in the commitment number field of the table COMMITMENTS - only for the current record. The values are stored in another table - COMMITMENT TYPES.
When this value is changed (COMMIT NO), the COMMITMENTS sub-form displays this value as "Retired" and other values are also adjusted - amount, year etc - all of which are taken from the COMMITMENT TYPE table row for the "Retired" value.

[IMG]file:///C:/Users/Richard/AppData/Local/Temp/moz-screenshot.png[/IMG]I notice there is a "Convert to Retired" field in the Members table. This is a Yes/No checkbox which presumably is linked to the COMMITMENTS and COMMITMENTS TYPE tables (they are in the relationships diagram).

Sorry if this is hazy - I'm coming at this as a novice and guessing what might be useful to describe.

[TheShabz]

so if commit no = 127, it means the record is "retired"? thats an odd way of doing it. alos, your screenshot failed. can you put up the db?

[brooke48]

Sorry, how do I put up the db ? If there is a more sensible way of achieving this, I'd love to try it - really want to learn here.

[RuralGuy]

Sorry, how do I put up the db ? If there is a more sensible way of achieving this, I'd love to try it - really want to learn here.

First, Compact and Repair your db. Then ZIP it up. Then use the advanced "Reply Button" and scroll down to "Manage Attachments" and upload the zip file.

[brooke48]

Here's the zipped db.

[TheShabz]

Ok. I'll answer in 2 parts. 1st part is how I think this should be done. Second will be looking at your db (which i will do Tuesday as that's when I will be back at work and have access to all my toys).

1. As I understand it, you have a list of members, thus you have a "members" table. I would have a field in that "members" table called "StatusInd", where the "Ind" means "indicator". You would then assign a value to multiple statuses. For example, 1=active 2=away 3=retired 4=revoked etc. With that, you would create an UPDATE query, that would update the StatusInd field to 3 (in this case) WHERE MemberID = Forms!frmName!cmbMember. I assume there being a combobox named cmbMember that holds member names, and has memberID as a hidden field that is asigned to it. The user selects the name, checks the check box a you mentioned, and clicks a button to run the update query.

This is how I would do it and I believe this is how most everyone else would as well. I will take a look at the db on Tuesday and give more input then unless someone beats me to it. I hope this clears things up from my point of view.

[brooke48]

This sounds very logical and a little less complicated than how the db seems to be constructed. Thanks for taking this trouble.

[brooke48]

A little puzzled with the cmbMember idea - if this holds members' names it would be a duplication of the Members Table wouldn't it or am I missing the point ?