Need to update an Access97 program to Access 2007

[Hilton]

I have a "program" here written entirely in MS Access, and I need to figure out how to update it. For background, the program controls a database of people and items for the shop owners and police, runs in Access 97, and ran on Windows 98. And has for the last 12 years.

They desperately need to get away from Acc97 and Win98 for obvious reasons, but nobody has been able to make this program work on another operating system. Now it's my turn! I've tried a couple things, nothing working yet. The only obvious thing I haven't directly tried is installing Office 97 on a modern Windows system, or installing Win98 in a virtual box.

Anyway, the scripting in this program controls everything. It directly and automatically interacts with two printers (one modern standard USB, one serial receipt printer) and a scanner (for ID).

Now the interesting part. In Access 97, the program hides all the standard controls and displays only its own toolbar, and the customer DB. using various buttons (ie. find customer, add customer, and some other stuff) will open up forms which show their own toolbars. For example, clicking find customer will give you a simple search box, then a results list, then a form displaying customer information which replaces the toolbar with its own with some other options, find item, add item, update record, etc.

I have unconfirmed information that this program also works fine in Office 2000, but in Office 2007 the toolbars will not show up (the Ribbon stays visible), rendering it unusable.

How can I fix this?

 

[gemma-the-husky]

access 97 shouldnt be a problem

its perfectly useable - certainly under XP operating system.
------------
but anyway, in A2007, just right click the menu ribbon, select hide ribbon, and select addins, and you should see your existing menubar.

 

[Hilton]

Is it possible to install Office 97 and Office 2007 at the same time?
It would be nice to leave a modern Office on the system and have Access 97 linked only to this program... or I guess I could install OO or something.

---
Okay, I had a whole bunch of stuff written up about different things about Add-ins. Couldn't find anything.

Thing is, only the Home tab (with most options disabled) on the ribbon was shown, and only Close and Exit on the office menu - not even Options. Couldn't find any further options in customizing to display anything else.

Until I checked Access Options > Current Database (getting there through Customize Ribbon).
Near the bottom is a section on Ribbon and Toolbar Options. It has a list of the six different toolbars, and the options: Allow Full Menus, Allow Default Shortcut Menus, Allow Built-in Toolbars.

I tried enabling all 3 and selecting the primary toolbar, but that seemed to stay stuck, would not change. I am now reading up on the options... unless someone has some more information!

 

[gemma-the-husky]

yes you can install A97 - you will need to rename a font called hatten.ttf first

(c:\windows\fonts) - other wise you get a spurious license error - this is documented - just google for it.


------------
just one further thought- i don't think A97 runs under windows 7

 

[Hilton]

Okay after my other stuff (see edited message above), the three options have no effects, except Allow Full Menus allowing the Create, External Data, and Database Tools tabs to display.
The Add-ins tab only displays if I specify a custom toolbar, which seems to stay stuck in place no matter where I go.

I am going to check that the appropriate toolbars are attached properly to the appropriate forms... any other suggestions?

 

[Hilton]

Okay, the toolbars ARE changing properly, but its because of VB code... however, certain functions are giving me RTEs.

Although I am versed in programming, I am unfamiliar with VB or Access. I am getting an error on a line:
If Me.CheckCustStatus = False Then

Is Me a standard object of any kind, or do I need to hunt down where it is from?

 

[boblarson]

Me is a shortcut keyword that refers to the current class object. In this case it appears to reference the current form (or report) that the checkbox is on.

You should open up the VBA window, go to TOOLS > REFERENCES and check to see if any are marked as MISSING. If so, uncheck those and then close the dialog and reopen the database.

 

[Hilton]

I've figured out more detail... assuming the debugger is stopping on the exact line giving the error, I am getting this:

Run-time error '2427':
You entered an expression that has no value.

on the bold line:

Private Sub Form_Load()
    Forms![frm Customer Detail].MenuBar = "Item Bar"
    
[B]    If Me.ChkCustStatus = False Then[/B]
      ...

Me.ChkCustStatus is a checkbox on the form. This form displays a customer record, however in THIS particular instance, it is called via the "Add Customer" button, not loading a database field. The form loads fine when viewing existing records.

Is it possible that ChkCustStatus is being initialized NULL, thus causing a no value error with VB(?), and if so, how can I get around this check?

(Sorry, I am a programmer but rarely use VB, and am doing this as a favour for a friend who runs a shop using this software).

 

[boblarson]

Try this:

If Nz(Me.ChkCustStatus, False) = False Then

 

[gemma-the-husky]

these errors dont seem to be related to using A907, or A2007. They seem to be general errors that would occur anyway.

Has something else changed

--------------
Secondly, were you able to get A97 installed on your new computer.

 

[Hilton]

Nothing has changed at all. Perhaps Access 97 just handled that null error differently (or not at all).

I will try that tomorrow when I have the computer again.

 

[Hilton]

That line still gives an RTE with the Nz() function, except now it is:

Run-time error '-2147352567 (80020009)'
You entered an expression that has no value.

Another effect of the switch to Office 2007 I've found is that the Close buttons on the forms no longer work. (Not the titlebar ones, they work fine, but rather a secondary Close button on the form)

 

[gemma-the-husky]

looks like you need a crash course in vba etc.

there are some differences between A97 and A2007 - so its possible that changing systems may have an effect on a programme

the "no value" error may well relate to a form having no data.

if a form has no data, and will not accept new items (either because you explicitly set allow additions to false, or because it uses a non-updateable query) , then your form controls all become undefined, have no value, and cannot be referenced.

maybe the new version is not correctly connected to the backend data - that might produce this sort of error also.

 

[Hilton]

I believe I mentioned earlier that this had to do with an Add New Entry form. It is the same form used to edit the entries. Do I need to manually give each field a value when opening the form for a new entry? I thought VBA would be mature enough to deal with null values on its own. Every other bleedin language does.

I don't think it's a backend connection problem; most everything else works fine data-wise.

The only things not working properly that I've found are this form (but only when called for a new entry; works fine when loading an entry from the database), and Close buttons.

 

[gemma-the-husky]

its not a null value thats the problem - its that the record source of the form doesnt permit you to add new items - or maybe the form is set to allow additions = false

try it - just open the query directly, and try to add an item, or edit an item. if the query is non-updateable you wont be able to do either.

and thats why access is giving you the errors - you havent got any records on the form, and you cant edit anything either - its not nulls - its that you have no data available to edit, null or otherwise - everything is just undefined - and you get this unusual error number

 

[Hilton]

Hmm. Looking into the form details, and I the following under the Data section:

Record Source: Customers
Recordset Type: Dynaset
Fetch Defaults: Yes
Filter: [Customer ID]=[Forms]![frm Search Results]![Customer ID]
Filter On Load: No
Order By: <blank>
Order By On Load: Yes
Data Entry: No
Allow Additions: Yes
Allow Deletions: Yes
Allow Edits: Yes
Allow Filters: Yes
Record Locks: No Locks


Tried setting Data Entry to true, that didn't work. In fact, it made things worse - the form would NEVER load, as opposed to only not work for new additions. Also, you are correct, I cannot seem to make changes to any record using the form - which I *COULD* in Access 97. Obviously A2007 is far too different. It may not be a square-peg-through-a-round-hole problem, but I can't force an octagonal peg through a round hole without some major changes either.

This is getting maddening. I think I am just going to dig up my copy of Office 97 and install that, or rebuild the whole thing in something other than Access. In the time I've spent trying to fix this, I could have rebuilt the thing twice using other methods...

 

[gemma-the-husky]

well, its definitely somethnig to do with a non-updateable query, then - but I am not sure why it should be different in A2007, compared with earlier versions.

i would take a copy of the query that drives the form, and try to delete tables from it, (or start simply, and gradually add the tables) to try and identify exactly which change makes the query non-updateable