Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 254
  • Last Modified:

Blank Currency Fields in Access

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
LJKMartin
Asked:
LJKMartin
  • 5
  • 2
1 Solution
 
peter57rCommented:
" 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
 
LJKMartinAuthor Commented:
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
 
peter57rCommented:
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
LJKMartinAuthor Commented:
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
 
LJKMartinAuthor Commented:
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
 
Jeffrey CoachmanMIS LiasonCommented:
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
 
LJKMartinAuthor Commented:
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
 
LJKMartinAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now