Solved

Blank Currency Fields in Access

Posted on 2013-01-21
8
223 Views
Last Modified: 2013-06-21
Hi,

My database is doing something I can't workout - it's displaying blanks where there should be a currency value over £0.  (yes it shows £0, but nothing else).

This is true if I am looking at a table or a form, and different ones.

Normally the database runs an autoexec macro that opens a form ("fil") that asks for the users name, the user clicks Continue and some further code runs that opens the main form and runs a make table query.

If I bypass the Autoexec I get two possible situations:

1) If I replicate the Autoexec by opening "fil" manually, the values still won't show
2) If I go straight to a table the values do show, and then if I replicate the Autoexec the values continue to show as they should.

Originally, this was an .mdb, but the .accdb does the same.

I'd be grateful for any advice to track down the problem.

Cheers

L
0
Comment
Question by:LJKMartin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
8 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 38802177
" that opens the main form and runs a make table query."

Can we assume that the main form is NOT based on the table you are creating in the query?
0
 

Author Comment

by:LJKMartin
ID: 38802225
Yes.

The main form is just a set of command buttons that open other forms.

It did have a sub-form, but I have removed that and the problem still persists.
The code also opens (and hides) another form, which seems to be the problem.

This form "Permissions" has no code attached to it.  Is there a property I should be looking for?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 38802298
OK ,so you are saying that this form has never worked in either the mdb nor the accdb - is that correct?

How do you open the problem form (what command is used)?

What is the recordsource for the problem form?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:LJKMartin
ID: 38802807
This form has been working for about 6 years in the mdb.

Up until now it was opened by a macro.  Because of this new problem I converted to VBA (but it made no difference.  Here is the code that opens it and then hides it.

    DoCmd.OpenForm "Permission", acNormal, "", "", , acNormal
    Forms!Permission.Visible = False

The recordsource is a query

SELECT tbl6Users.f6aUsername, tbl6Users.f6bPassword, tbl6Users.f6cGroup
FROM tbl6Users
WHERE (((tbl6Users.f6bPassword)=[Forms]![fil]![Password]));
0
 

Author Comment

by:LJKMartin
ID: 38803020
It gets wierder.

If the Permission query returns no records, then values are displayed.  But it should return one record and so the values are blank.

I have tried to create a new form with the same name, and got the same result.

I think I'm going to have to chop this thing from the database.

Cheers
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 500 total points
ID: 38856611
FWIW,

I never insert empty stings for unused arguments, I simply leave them blank.
...and also check your syntax, your last augment should be:  acWindowNormal


So try:

DoCmd.OpenForm "Permission", acNormal, , , , acWindowNormal
    Forms!Permission.Visible = False
0
 

Author Comment

by:LJKMartin
ID: 38856740
Thanks,

That's good advice.

The problem is not down to that form, however.

I have bypassed that form completely, and the problem still occurs.

We have a work around that we are using at the moment:  You have to open the main (Orders) form first, which shows the values.  Then the whole database works fine.  But if you start with the wrong form then none of it does.  You have to close the database and use the workaround.

I think it's something to do with legacy macros used behind the command buttons.

I will convert them all to VBA as soon as I can.

Cheers

LJM
0
 

Author Comment

by:LJKMartin
ID: 38904644
I have narrowed this down to the DSN that links this database via ODBC to the SQL Server.

I am based in the UK, so when creating the DSN I ticked to use Regional Settings.

If I don't tick that box, the problem is cured.

Now...how to get the dates right....
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question