Solved

Blank Currency Fields in Access

Posted on 2013-01-21
8
235 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 

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

Technology Partners: 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!

Question has a verified solution.

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

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

726 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