• 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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