Problem with DLookup

[MichealShinn]

Hi. This is my first posting. Thank you for your help.

I work for a utility company. Some of our customers buy gas and electricity from us, some buy just one or the other.

I have built a form that has a button on it that people can click after they enter a customer's account number. When they click the button, Access goes to two different tables (one for Electric and one for Gas) and looks up that customers' electric usage and gas usage from the last year in two local tables. The form has separate fields for the customer's electric usage, gas usage, electric revenue, etc, using DLookups (a separate DLookup for each field). If a customer buys only electricity from us, when Access gets to the DLookup to look for that customer's gas usage, I get an error number 94 because there is no gas record.

How do I check to see if a record was found so it doesn't throw this error?



Thank you again for all your help.

[John_G]

You can use the nz function. The nz function take two arguments; the second argument specifies the value to return if the first is Null, otherwise it just returns the value of the first argument.

In your case, you could use:

[MyformField] = nz(Dlookup(...), "N/A")

Which will return N/A if there is no gas record and the Dlookup return a Null.
(That's how Dlookup works - it returns Null if no records match the criteria)

John

[MichealShinn]

John,

Thank you, that worked.

There's another issue that I hope goes away overnight. This database lives on the network. People upstairs are opening the same database I am, but the changes I've made today (like adding the Nz to the DLookup) don't show up when they open the database at their computers. I've tried re-booting, shutting down, logging on as me, logging on as them, nothing.

Have you ever heard about anything like this?

[John_G]

This sort of thing will almost always happen when the front-end (forms, queries, reports, etc) is in the same .mdb as the data, and has multiple users accessing that same file. Microsoft (and any good developer!) will tell you that when there are multiple users you should split you database into two parts - data tables in one part - the back end - , and everything else - the front end - in the other. (MS Access provides a utility to do it for you, but I don't know where it is in A2007).

Once you have done that, put a copy of the front end on each user's own machine, with only the data on the network. The front end accesses the data through linked tables. That will usually resolve the problems you have been encountering, and it also allows you to have a copy of the front-end on your own machine for development. The down side is that every time you make changes, the users have to copy the amended front end to their own PC's, but IMO that is only a minor inconvenience.

John

[MichealShinn]

John,

Thank you for your suggestion about splitting the database. Unfortunately, it didn't work.

I split the database. I now have impacts_2007.accdb and impacts_2007_be.accdb. I went to a user's office and put a shortcut to impacts_2007.accdb on his desktop. Then, I opened that database on his computer. I got the Invalid Use of Null error message (which I fixed after your first message yesterday) and a table that I deleted this morning showed up and two new tables that I created yesterday didn't show up.

Do you have any more suggestions?

Micheal

[MichealShinn]

I got it working. Thank you for all your help.