Access 2K on Windows XP producing #Name error 2

[Keetso]

Hi.

I recently developed an Access 2000 database for a client. She recently upgraded her computer from Windows 98SE to Windows XP. The program ran perfectly on her Win 98 OS but is now getting a #Name error in a combo box. This combo box uses a "month" table to allow the user to "enter" a month (January, February, etc) for each record.

RowSource is: SELECT [tblMonthsOfTheYear].[MonthFULLName] FROM tblMonthsOfTheYear ORDER BY [tblMonthsOfTheYear].[orderby];

Default Value is: =monthname(DatePart("m",Date()))

This allows the new record to list the current month in the field.

I copied this database back to a Win98 machine with no changes made and it works perfectly.

Is there something here that Win XP / Access 2000 doesn't like?

Any ideas on how to troubleshoot / fix this?

TIA

K

 

[Lightning]

Sounds like a missing Reference. Open any Code Module and then Click TOOLS|REFERENCES and check for any references marked MISSING.

HTH
Lightning

 

[Keetso]

Hi. Thanks for the reply.

I'll look to see if it is a missing reference?

Is this a known issue with Access 2000 running on Win XP?

I have to assume so since I can copy this database onto a Win98 box and it runs perfectly.

K

 

[Lightning]

No, it's not an issue of XP vs 98, it's an issue of what Object Libraries are available on each machine. Different versions of Access/Office/Windows have different versions of Object Libraries.

For instance, Access 97 is Version 8, and contains default References to the Version 8 Object library. Access XP is version 10, and therefore contains default references to the version 10 object library. If your database is specifically looking for the version 8 object library, it will not recognise the version 10 object library as a replacement, and will simply mark the version 8 library as "Missing".

HTH
Lightning

 

[Keetso]

Lightning.

You deserve a star for patiently explaining this to me.

Is there a way to "update" the object reference libraries or import the object libraries from my Win 98 machine so things will run smoothly again or do I just need to re-link (for lack of better words)the offending text boxes / combo boxes / etc?

Bottom line, what's the best way to make this database run correctly again?

Again, many thanks for your patience!

K

 

[Lightning]

Open the database on any machine that it runs correctly on. Open the References dialog box and check to see which reference or Object libraries are checked. These are the libraries that are required by the database. Make a note of the Name and File path for each object library.

Now open the database on the machine where it will not run correctly. Check the references/Object Libraries. Uncheck any that are marked missing. Now you have two choices

1 Browse the list to find the equivalent references to those displayed in the correctly operating version

2 If the references are not listed, Use the browse button to look for the references using the list you noted down from the correctly operating PC. Once all the required references are checked, the application should work correctly. If it does not, the problem is somewhere else and we need to look further into the problem.

HTH
Lightning

 

[Keetso]

Hi Lightning.

The references are identical on both machines (the one it works on and the one it doesn't) and there are no references marked missing.

Any other ideas?

Just for point of reference, the combo box having problems on the XP machine:

Name: cboWhichRptMonth
Control Source: MonthLONG
Decimal Places: Auto
Row Source Type: Table/Query
Row Source: SELECT [tblMonthsOfTheYear].[MonthFULLName] FROM tblMonthsOfTheYear ORDER BY [tblMonthsOfTheYear].[orderby];
Default Value: =monthname(DatePart("m",Date()))

K

 

[Keetso]

One addition.

The problem seems to be in the Default Value " =monthname(datepart("m",Date())) ". I removed this and the error is gone but so is the current month showing as the default value. So now the questions are these:

1) Why does this not work in XP
2) How do I allow the current month to appear as the default value?

K

 

[Lightning]

Keetso

Row Source:  SELECT [tblMonthsOfTheYear].[MonthFULLName] FROM tblMonthsOfTheYear ORDER BY [tblMonthsOfTheYear].[orderby]; 


Default Value:  =monthname(DatePart("m",Date()))

should be

Row Source:  SELECT [tblMonthsOfTheYear].[MonthFULLName] FROM tblMonthsOfTheYear ORDER BY [tblMonthsOfTheYear; 

Default Value:  =DatePart("m",Date())

See if that resolves the error.

HTH
Lightning

 

[Keetso]

I'm not sure this will solve the problem since this would return a numerical equivalent of the current month (i.e. a "6" instead of "May") and would generate an error because the field is a "text" field, correct? Thus, I had used the function "monthname" to take the "6" and convert it to "May", etc.

The real question is, with all things being equal (references, etc), why does this function work in an Access 2000/Win 98 environment but not in an Access 2000/Win XP environment?

 

[Keetso]

Lightning?

Have I annoyed you to the point of abandonment?
<kidding!>

This problem is still driving me crazy and hope to get some additional input.

Again - Many, many thanks in advance!

K

 

[Lightning]

No, you haven't annoyed me. No, I haven't abandoned you! I've just had to do some crass commercial work and prove to my boss just how indispensible I am!

Down to business.
After checking the MS Knowledge Base the problem is resolved as a known problem with Access. You cannot use The FormatCurrency(), FormatDateTime(), FormatNumber(), FormatPercent(), InStrRev(), MonthName(), Replace(), Round(), StrReverse() and WeekdayName() functions as expressions. Their solution is to write a custom VBA function to achieve what you are after.

http://support.microsoft.com/default.aspx?scid=kb;en-us;225956&Product=acc2000

Assuming that you are using an input form for this table, as a work-around you could put a routine into the OnCurrent event of your form to add the month to the appropriate field when the form moves to each new record. Something like

Private Sub YourForm_OnCurrent()
    If IsNull(Me.cboWhichRptMonth) or Me.cboWhichRptMonth  = "" Then
        Me.cboWhichRptMonth = MonthName(DatePart("m",Date))
    End If
End Sub

should do it.

HTH
Lightning

 

[Keetso]

Hi Lightning.

Thanks again for all your help! You've been an amazing source of knowledge and help. I really appreciate it.

I haven't been able to try out your last suggestion yet as I haven't been able to remotely connect to the client's computer. It's supposed to be fixed by this afternoon so I'm keeping my fingers crossed that I'll be able to connect.

Thanks for the MS KB link as well. I think this may be the cause of the problem.

I'll post back on the results.

Thanks again!

K

 

[Keetso]

Hi all.

Problem solved!

I created a function as follows:

Function FullMonthName(mnthnam)
FullMonthName = monthname(mnthnam)
End Funtion

Then in the text box's "Default" field entered:

=FullMonthName(datepart("m",date()))

That's all there was to it.


Thanks again, Lightning for all your help and I hope to be able to help you or others in the future.

Tek-Tips is the greatest forum bar none.

K

 

[PHV]

And what about simply Format(Date(), "mmmm") ?
No need of custom function ...

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[assets]

I have had simolar problem. My database started in access97 and I then converted it to 200 before converting it to xp. LIGHTNING first answer about a missing Reference was my main problem