Why Would A Dlookup Function Work On One Computer But Not On AnotherComputer?

[R Tanner]

I have a main form with a dlookup function that looks up an account
number based on another textbox that is looking up the oldest record
in my table. This is all fine and dandy and works wonderfully on most
of my users' computers, but there are a couple people whom my dlookup
function returns a null error. I don't get it, because I know there
are records there. There IS an account number there in the table for
this record, and it works fine on every computer except two...What
could cause this?

 

[Jeff Boyce]

?Perhaps the field being DLookedUp has a null value, even though a record
exists?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[R Tanner]

?Perhaps the field being DLookedUp has a null value, even though a record
exists?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Nope. The field I am looking up is not null. The dlookup function
works fine on my computer, as well as most of the users' computers.
There are a few computers it does not work on though. This is my
code, so you know what's happening:

Public Function CheckForOverDueTickets()

Dim MyForm As Form
Set MyForm = Forms![frmSplash]

Dim ComplaintHours As Long, MyCompDate As Date, MyCompAccount As Long,
TradeHours As Long, TradeDate As Date, TradeAccount As Long



On Error GoTo NoComplaint
MyCompDate = DMin("[StartTime]", "[ComplaintTickets]",
"[ComplaintTickets].StatusID=2")

ComplaintHours = DateDiff("h", MyCompDate, Now)

Forms![frmSplash]![CompDate] = MyCompDate
Forms![frmSplash]![CompDif] = ComplaintHours & " Hours Unresolved"

MyCompAccount = DLookup("[Account]", "[ComplaintTickets]",
"ComplaintTickets.StartTime=Forms![frmSplash]![CompDate]")
Forms![frmSplash]![CompAccount] = MyCompAccount

GoTo TryTrades

NoComplaint:

MyForm![CompLabel].Visible = True
MyForm![CompDate].Visible = False
MyForm![CompAccount].Visible = False
MyForm![CompDif].Visible = False
MyForm![NoComplaints].Visible = True

TryTrades:

On Error GoTo NoTrade
TradeDate = DMin("DAT", "[Pending Tickets]", "[Pending
Tickets].Status=2")
TradeHours = DateDiff("h", TradeDate, Now)
Forms![frmSplash]![TradeDate] = TradeDate
Forms![frmSplash]![TradeDiff] = TradeHours & " Hours Unresolved"
TradeAccount = DLookup("[Account]", "[Pending Tickets]", "[Pending
Tickets].DAT=Forms![frmSplash]![TradeDate]")
Forms![frmSplash]![TradeAccount] = TradeAccount

Exit Function


NoTrade:
MyForm![TradeLabel].Visible = True
MyForm![TradeDate].Visible = False
MyForm![TradeAccount].Visible = False
MyForm![TradeDiff].Visible = False
MyForm![NoTrades].Visible = True




End Function


On my computer as well as most other computers, this works fine. On a
select few computers (2 to be exact), the dlookup function for the
account number throws a null error. I have went back into the
database and looked up the record with the date which is returned by :
MyCompDate = DMin("[StartTime]", "[ComplaintTickets]",
"[ComplaintTickets].StatusID=2") and there are no problems with it.
There is an account number there.

 

[Douglas J. Steele]

Try:

MyCompAccount = DLookup("[Account]", "[ComplaintTickets]", _
"StartTime=" & Format(Forms![frmSplash]![CompDate], "\yyyy\-mm\-dd\#"))


TradeAccount = DLookup("[Account]", "[Pending Tickets]", _
"DAT=" & Format(Forms![frmSplash]![TradeDate], "\yyyy\-mm\-dd\#"))

No idea why it would work on some machines but not on others though.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

?Perhaps the field being DLookedUp has a null value, even though a record
exists?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Nope. The field I am looking up is not null. The dlookup function
works fine on my computer, as well as most of the users' computers.
There are a few computers it does not work on though. This is my
code, so you know what's happening:

Public Function CheckForOverDueTickets()

Dim MyForm As Form
Set MyForm = Forms![frmSplash]

Dim ComplaintHours As Long, MyCompDate As Date, MyCompAccount As Long,
TradeHours As Long, TradeDate As Date, TradeAccount As Long



On Error GoTo NoComplaint
MyCompDate = DMin("[StartTime]", "[ComplaintTickets]",
"[ComplaintTickets].StatusID=2")

ComplaintHours = DateDiff("h", MyCompDate, Now)

Forms![frmSplash]![CompDate] = MyCompDate
Forms![frmSplash]![CompDif] = ComplaintHours & " Hours Unresolved"

MyCompAccount = DLookup("[Account]", "[ComplaintTickets]",
"ComplaintTickets.StartTime=Forms![frmSplash]![CompDate]")
Forms![frmSplash]![CompAccount] = MyCompAccount

GoTo TryTrades

NoComplaint:

MyForm![CompLabel].Visible = True
MyForm![CompDate].Visible = False
MyForm![CompAccount].Visible = False
MyForm![CompDif].Visible = False
MyForm![NoComplaints].Visible = True

TryTrades:

On Error GoTo NoTrade
TradeDate = DMin("DAT", "[Pending Tickets]", "[Pending
Tickets].Status=2")
TradeHours = DateDiff("h", TradeDate, Now)
Forms![frmSplash]![TradeDate] = TradeDate
Forms![frmSplash]![TradeDiff] = TradeHours & " Hours Unresolved"
TradeAccount = DLookup("[Account]", "[Pending Tickets]", "[Pending
Tickets].DAT=Forms![frmSplash]![TradeDate]")
Forms![frmSplash]![TradeAccount] = TradeAccount

Exit Function


NoTrade:
MyForm![TradeLabel].Visible = True
MyForm![TradeDate].Visible = False
MyForm![TradeAccount].Visible = False
MyForm![TradeDiff].Visible = False
MyForm![NoTrades].Visible = True




End Function


On my computer as well as most other computers, this works fine. On a
select few computers (2 to be exact), the dlookup function for the
account number throws a null error. I have went back into the
database and looked up the record with the date which is returned by :
MyCompDate = DMin("[StartTime]", "[ComplaintTickets]",
"[ComplaintTickets].StatusID=2") and there are no problems with it.
There is an account number there.

 

[R Tanner]

Try:

MyCompAccount = DLookup("[Account]", "[ComplaintTickets]", _
  "StartTime=" & Format(Forms![frmSplash]![CompDate], "\yyyy\-mm\-dd\#"))

TradeAccount = DLookup("[Account]", "[Pending Tickets]", _
  "DAT=" & Format(Forms![frmSplash]![TradeDate], "\yyyy\-mm\-dd\#"))

No idea why it would work on some machines but not on others though.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

Nope.  The field I am looking up is not null.  The dlookup function
works fine on my computer, as well as most of the users' computers.
There are a few computers it does not work on though.  This is my
code, so you know what's happening:

Public Function CheckForOverDueTickets()

Dim MyForm As Form
Set MyForm = Forms![frmSplash]

Dim ComplaintHours As Long, MyCompDate As Date, MyCompAccount As Long,
TradeHours As Long, TradeDate As Date, TradeAccount As Long

On Error GoTo NoComplaint
MyCompDate = DMin("[StartTime]", "[ComplaintTickets]",
"[ComplaintTickets].StatusID=2")

ComplaintHours = DateDiff("h", MyCompDate, Now)

Forms![frmSplash]![CompDate] = MyCompDate
Forms![frmSplash]![CompDif] = ComplaintHours & " Hours Unresolved"

MyCompAccount = DLookup("[Account]", "[ComplaintTickets]",
"ComplaintTickets.StartTime=Forms![frmSplash]![CompDate]")
Forms![frmSplash]![CompAccount] = MyCompAccount

GoTo TryTrades

NoComplaint:

MyForm![CompLabel].Visible = True
MyForm![CompDate].Visible = False
MyForm![CompAccount].Visible = False
MyForm![CompDif].Visible = False
MyForm![NoComplaints].Visible = True

TryTrades:

On Error GoTo NoTrade
TradeDate = DMin("DAT", "[Pending Tickets]", "[Pending
Tickets].Status=2")
TradeHours = DateDiff("h", TradeDate, Now)
Forms![frmSplash]![TradeDate] = TradeDate
Forms![frmSplash]![TradeDiff] = TradeHours & " Hours Unresolved"
TradeAccount = DLookup("[Account]", "[Pending Tickets]", "[Pending
Tickets].DAT=Forms![frmSplash]![TradeDate]")
Forms![frmSplash]![TradeAccount] = TradeAccount

Exit Function

NoTrade:
MyForm![TradeLabel].Visible = True
MyForm![TradeDate].Visible = False
MyForm![TradeAccount].Visible = False
MyForm![TradeDiff].Visible = False
MyForm![NoTrades].Visible = True

End Function

On my computer as well as most other computers, this works fine.  On a
select few computers (2 to be exact), the dlookup function for the
account number throws a null error.  I have went back into the
database and looked up the record with the date which is returned by :
MyCompDate = DMin("[StartTime]", "[ComplaintTickets]",
"[ComplaintTickets].StatusID=2") and there are no problems with it.
There is an account number there.- Hide quoted text -

- Show quoted text -

I am getting the following error for this for ONLY 2 of the
application users:

Error Number: 94
Error Description: Invalid Use Of Null
Error Source: Trade Tickets

I am clueless...It doesn't work for the two people it really matters
for...Any insight would really be appreciated...I'm not sure what I
can do differently with this function...I hate to rewrite it since it
works perfectly...It has to be something small...

 

[R Tanner]

Is this all a local database, run on each user's local machine?

Is it located on a shared drive? If so, the user's in question have rights
to the folder?

Is it a SQL backend? If so, the user's in question have SELECT rights?

Just a couple of thoughts. I've learned to not always take an Access error
at it's word, and look beyond what it seems to obviously be saying when I
find weird things like this.

[quoted text clipped - 99 lines]

- Show quoted text -

I am getting the following error for this for ONLY 2 of the
application users:

Error Number: 94
Error Description: Invalid Use Of Null
Error Source: Trade Tickets

I am clueless...It doesn't work for the two people it really matters
for...Any insight would really be appreciated...I'm not sure what I
can do differently with this function...I hate to rewrite it since it
works perfectly...It has to be something small...

Yes, it is a local access database on a shared network drive. Yes,
the users have rights to the folder. It is weird because the one user
who is having problems is the senior manager and the other is the
senior trade specialist. This [seems to be] the only problem these
two users are having with the application.

 

[Douglas J. Steele]

If it isn't a FE/BE, it should be. Users should never share the same
application database.

Once each user has his/her own copy of the front-end, ideally on his/her
hard drive, check to ensure that the References collection is okay for each
of the users having a problem.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Is it FE/BE with table links? If so, you're sure the users in question
have
the most current link? (If you had added a field referenced in the
criteria
of the DLookup, and they don't have current links ...)

Are they power-users? Have they been dinking around in the design where
they
shouldn't have been? You've tried a fresh, new copy of the database on
their
machine that is an EXACT copy of the one that works on yours?

Just throwing more stuff out there ...

Is this all a local database, run on each user's local machine?

[quoted text clipped - 29 lines]
Yes, it is a local access database on a shared network drive. Yes,
the users have rights to the folder. It is weird because the one user
who is having problems is the senior manager and the other is the
senior trade specialist. This [seems to be] the only problem these
two users are having with the application.

 

[R Tanner]

If it isn't a FE/BE, it should be. Users should never share the same
application database.

Once each user has his/her own copy of the front-end, ideally on his/her
hard drive, check to ensure that the References collection is okay for each
of the users having a problem.

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)

Is it FE/BE with table links? If so, you're sure the users in question
have
the most current link? (If you had added a field referenced in the
criteria
of the DLookup, and they don't have current links ...)

Are they power-users? Have they been dinking around in the design where
they
shouldn't have been? You've tried a fresh, new copy of the database on
their
machine that is an EXACT copy of the one that works on yours?

Just throwing more stuff out there ...

On Dec 31, 1:32 pm, "tkelley via AccessMonster.com" <u47368@uwe>
wrote:
Is this all a local database, run on each user's local machine?
[quoted text clipped - 29 lines]
--
Message posted via
AccessMonster.comhttp://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200812/1
Yes, it is a local access database on a shared network drive. Yes,
the users have rights to the folder. It is weird because the one user
who is having problems is the senior manager and the other is the
senior trade specialist. This [seems to be] the only problem these
two users are having with the application.

I am using a split database, and these guys are not power users. They
don't know the first thing about access. I have made sure they have
the exact same copy of the front end I have. I have not verified they
have the correct references enabled in their access client. I will
check that. It seems weird that would be the problem though because I
am using a win32 api call that doesn't have any problems on their
machines. Not to mention the fact that I have references to the
outlook client object library model. I will check and post back on
Friday though.

If you have any other ideas, please let me know. The other users that
are using this application have much less rights and they don't have
any problems.