Crashing (1 Viewer)

Gaztech

Member
Local time
Today, 06:08
Joined
Jan 5, 2021
Messages
39
Hi guys,

An easy problem was solved yesterday using the forums but I now have another that is more serious...

Everything within my database and forms has been working just fine but this morning I launched the application to do some more work and I find that pressing any of my search buttons on my lookup form causes Access to crash. Pressing any search button on the form causes a rotating circle to appear for about 5 seconds then the application crashes.

My query yesterday involved the use of DLookUp. I had the following code in one field of the form:

=DLookUp("[DESC]","[SUBASSY]","[SUBASSY_REF] = ' " & [SUB_ASSY_REF] & " ' ")

This retrieves a value (DESC) from table SUBASSY and places the result equal to that in the SUB_ASSY_REF field already on the form.

The DLookUp code works fine and retrieves and displays the correct values - but only after just saving the form and in that current session. Exiting the session and re-launching it causes the form to crash Access.

If I remove the code, save the form, put it back and re-save the form, the form then works just fine. If I then exit Access and re-open it, the form no longer works and crashes.

How can it work when saved in the same session and then not work when you start a new session? Is there something I need to initialise or set up on launch to stop this from happening?

I can post the database application if necessary but I would have thought that someone would know why this might be happening. Is it something obvious that I'm missing? Please bear with me as I'm a complete newbie with Access although I do have experience with procedural code.

Any thoughts?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:08
Joined
May 7, 2009
Messages
19,169
what is the RecordSource of your form?
use Query instead of Table:

Select T1.*, T2.[Desc] As [Description] From yourTable As T1 Left Join SubAssy As T2 On T1.Sub_Assy_Ref = T2.SubAssy_Ref;

therefore you can Remove the (=Dlookup(...)) from the textbox and make it bound to [Description] Column.
Make the textbox Locked Property to Yes so it will not be modified.
 

Gaztech

Member
Local time
Today, 06:08
Joined
Jan 5, 2021
Messages
39
Thanks for your reply!

However, I think I've located the main issue... I'll report back if it still plays up though...

I performed an Office Update. I noticed that updates were turned off. I did an Update and now it's not doing it. Everything seems to be working fine again. Very odd.

I guess this might be a "red herring" but it now seems to work ok. I'm going to put it down to that until it does it again. If I get lucky, maybe it won't!

I will try the query instead as it's certainly worth looking at but I'm curious as to why you tell me to use that.

Is DLookUp known to cause issues like this?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 05:08
Joined
Feb 19, 2013
Messages
16,553
note that both 'desc' and 'description' are reserved words (the first is used to specify the direction of a sort the second is a property used by various objects). Using them as field names can cause unexpected errors, surrounding with square brackets usually solves the problem, but not always
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:08
Joined
May 7, 2009
Messages
19,169
query is faster than calling DLookup().
you need to Requery the textbox (the one using Dlookup())
every once and a while to update that textbox.
 

Gaztech

Member
Local time
Today, 06:08
Joined
Jan 5, 2021
Messages
39
Ah... Reserved words... Didn't realise that "desc" and "description" were reserved.

That makes a lot of sense. Will change them as I don't want issues in the future.

The query also works too!

Many thanks to contributors!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:08
Joined
Feb 28, 2001
Messages
27,001
As to the crash,... I have seen stuff go bonkers when you have incomplete or partial updates. It would occur when Windows library file A is updated but Access library file B is not, yet the two are intertwined in some way. So if you allowed updates and the problem went away, that "mixed update" status might have done it to you. And Windows does not like it when you turn off updates.

I know my antivirus package complains if I have updates off and it even locks some things when it needs a reboot and I haven't allowed one yet. Updates are a really strange beast sometimes.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:08
Joined
May 7, 2009
Messages
19,169
for info "description" is Not a Reserved word.
"desc" is as it is command used in query to sort
in "descending order.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 13:08
Joined
May 7, 2009
Messages
19,169
access will notify you if you use a Reserved word as Fieldname.
that is for sure.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 00:08
Joined
Feb 28, 2001
Messages
27,001
Actually, ArnelGP, that notification appears to depend on the version of Access, because I accidentally used a rather obscure reserved word and it let me do it.
 

Users who are viewing this thread

Top Bottom