Form/Report names changing randomly (1 Viewer)

[cnstarz]

In my database, my "switchboard" consists of two listboxes: "Available Forms" and "Available Reports". The Available Forms listbox lists all the forms that can be accessed, and Available Forms listbox lists all the reports that can be accessed. I did this so that I wouldn't have to create buttons for each new form or report. They all are automatically listed in the listbox for the user to double-click on to open.

Since all my form and report names are not user-friendly (ie: fmComplicatedAndUglyName, rpComplicatedAndUglyReport) I want a way for the db admin to easily assign captions for each form that the average database user would find intuitive and easy to understand (ie: "Car Maintenance" instead of fmCarMaintenance).

To do this, I built a table called tbDBObjectsCaptions consisting of these fields:

dbObject_ID (PK - Long Integer)
Caption (Text)

Next, I have an unbound form (fmDBObjectsCaptions) consisting of two subforms:
1) sbfDBObjectsCaptions_Forms, which lists all Forms with captions
2) sbfDBObjectsCaptions_Reports, which lists all Reports with captions.

Each subform's record source is tbDBObjectsCaptions with an Inner Join between the table and the MSysObjects table so that I can show only forms (Type field in MSysObjects = -32768) or only reports (Type field in MSysObjects = -32764). So, the record source looks like:

SELECT tbDBObjectsCaptions.dbObject_ID, tbDBObjectsCaptions.Caption
FROM tbDBObjectsCaptions INNER JOIN MSysObjects ON tbDBObjectsCaptions.dbObject_ID = MSysObjects.Id
WHERE (((MSysObjects.Type)=-32768));

(Except the Report's subform record source Where statement would have "-32764" instead of "-32768".)

Each subform also consists of a "Caption" textbox and a combobox that lists all the forms or reports in MSysObjects. The Row Source for those comboboxes are:

SELECT MSysObjects.Id, MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "*sbf*") AND ((MSysObjects.Type)=-32768));

(Except the Report's combobox row source Where statement would have "-32764" instead of "-32768".)

My first day playing with fmDBObjectsCaptions went fine. Both subforms' comboboxes list either Reports or Forms and would easily let me choose a form or report. The subforms would record the same "Id" from the MSysObjects table into the tbDBObjectsCaptions table and each Caption I typed in was recorded into the tbDBObjectsCaptions table for each "Id" I chose. It worked just fine.

However, the next day, I noticed that the captions I assigned for forms/reports were now assigned to different forms and reports! In fact, the fmDBObjectsCaptions no longer shows any captioned reports as their Types have somehow changed from -32764 to -32768 and are therefore now displayed in the Forms subform.

Either the dbObject_ID is somehow mysteriously changing for each record in the tbDBObjectsCaptions table or the Id is somehow mysteriously changing for each record in the MSysObjects table. I don't know how or why but that's what's happening. So now, in the fmDBObjectsCaptions form, my Forms subform is displaying the wrong captions for the forms, and is also displaying records that were originally Reports (items that have captions but blank comboboxes were originally entered in the Reports subform).

I attached a copy of the database so you can get a better picture of what I'm talking about. Has anyone else run into this before, or does anyone else have a different solution that allows for easy Admin'ing of displayed form/report names?

Note: in this database the switchboard doesn't exist, only the fmDBObjectsCaptions form exists which is where you can see the problem.

 

[GinaWhipp]

Hmm, problem number one is you are using the word *Caption* as a field name in a table and it is a Reserved WOrd for Access which confuses Access. Change that name, I would suggest bracketing but in because of how you are using it I suggest changing it. See if just doing that corrects your issue.

Oh, and for future reference, here's a complete list of Reserved Words...
http://allenbrowne.com/AppIssueBadWord.html

 

[cnstarz]

Thanks for the reply! In my real database (on a separate network so I had to make the one in OP from scratch) the table's field is actually called "ObjectCaption". So I dont think that's the issue. Good catch though!

 

[GinaWhipp]

So, I changed it in your table and am now looking at your sample...

 

[GinaWhipp]

Well, after much research and then I tested the theory, this is why the ID's change, see...
http://databaseadvisors.com/pipermail/accessd/2003-November/033127.html

So, you can't count on those ID's unless you finished deleting and adding objects.

 

[cnstarz]

Dang it Microsoft!! Y U NO AUTONUMBER?

 

[GinaWhipp]

I had the same thought!

 

[cnstarz]

So, would you or anyone else have any thoughts on other ways to accomplish this?

 

[GinaWhipp]

You need to create your own Menu/Switchboard a seperate table the has the Object and then Name you want to show. You can use a list box but just not the MSysObject table. So, the same principal but with your own table where you enter the Object names.

 

[cnstarz]

Hmmm, that sounds like that would require a bit of manual work to maintain. I wonder if the "DateCreate" field from MSysObjects could be use in ID'ing each Form and Report. After all, I won't ever be creating more than 1 form or report in a second, so there's a very miniscule chance of having two Forms or Reports with the same exact timestamp down to the second. Hmmmm....

 

[GinaWhipp]

It really isn't I use what I mentioned above, see
http://www.access-diva.com/f3.html

Mine does do a bit more which is why I added buttons and there is a section where the Admin can go change the names of the Objects as they appear on the Main Menu. However, you don't need to add the buttons you can just have a list box and a combo box.