Solved

Blank Currency Fields in Access

Posted on 2013-01-21
8
181 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
  • 5
  • 2
8 Comments
 
LVL 77

Expert Comment

by:peter57r
Comment Utility
" 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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:LJKMartin
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:LJKMartin
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now