Why is this complicated code not working - CHALLENGE HERE!

slay0r

Board Regular
Joined
Jul 8, 2011
Messages
231
Hi Guys,

One of the bosses here at my work has gone away and left some databases in my care, unfortunately during an update one of them has fallen over and it happens to be the one I know absolutely nothing about!

Below is the code that's fallen over, I don't know if you'll be able to do anything with it but I'd greatly appreciate it if someone could take a look!


Function autoexec()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
'On Error GoTo autoexec_Err<o:p></o:p>
<o:p> </o:p>
DoCmd.SetWarnings False<o:p></o:p>
Const strSharedPath = "S:\ACCOUNTS\Common Files\"<o:p></o:p>
Dim fs, f, fc, fd(1), f1(1) As Object, fn As String, xyz, ARs, fa, intX As Integer, srtARName As String<o:p></o:p>
Set fs = CreateObject("Scripting.FileSystemObject")<o:p></o:p>
'Set f = fs.GetFolder("S:\ACCOUNTS\Common Files")<o:p></o:p>
'Set fc = f.Files<o:p></o:p>
'Set f1 = fc("Current AR Report Alianca.XLS")<o:p></o:p>
ARs = Array("Current AR Report Alianca.XLS", "Current AR Report HSUK.XLS")<o:p></o:p>
'Set fa = Array(fc(ARs(0)), fc(ARs(1)))<o:p></o:p>
For intX = 0 To 1<o:p></o:p>
srtARName = ARs(intX)<o:p></o:p>
Set f1(intX) = fs.getfile(strSharedPath & ARs(intX))<o:p></o:p>
fd(intX) = f1(intX).Datelastmodified<o:p></o:p>
<o:p> </o:p>
Next intX<o:p></o:p>
xyz = DMax("[date]", "[dateupdated]")<o:p></o:p>
' MsgBox "Ali " & fd(0) & " *** HSUK " & fd(1) & " *** " & xyz<o:p></o:p>
If fd(0) > xyz Or fd(1) > xyz Then<o:p></o:p>
<o:p></o:p>
Call updatedb<o:p></o:p>
Call massupdate<o:p></o:p>
End If<o:p></o:p>
<o:p> </o:p>
DoCmd.OpenForm "frontend", acNormal, "", "", , acNormal<o:p></o:p>
<o:p></o:p>
<o:p> </o:p>
autoexec_Exit:<o:p></o:p>
Exit Function<o:p></o:p>
<o:p> </o:p>
autoexec_Err:<o:p></o:p>
MsgBox Error$<o:p></o:p>
Resume Next<o:p></o:p>
<o:p> </o:p>
End Function


The bit in bold is the bit that's fallen over. Does anyone have any suggestions?!
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
I've tried compressing and repairing the database and I've tried rolling it back to yesterday at 11am - We keep 24 hour backups. This hasn't corrected the error so I'm quite stumped from my side!
 
Upvote 0
Code:
xyz = DMax("[date]", "[dateupdated]")

Why do you believe this line is the problem?

I'd be more inclined to suspect something in these procedures:
Call updatedb
Call massupdate


I would suggest commenting out error handling to hopefully pinpoint where the error really occurs.
(and I would also make sure your backups are safe as you may need to restore again).
 
Last edited:
Upvote 0
That was just the line in yellow, thought that meant that's where it fell over!

We've got an external company that backs up all our files every 30 mins and stores them on their site so I'm requesting them to restore it back to it's state on monday when it definitely worked! Hopefully that will resolve it because the mass update etc was written well before I joined and I have no idea how it all works! I would pass on the database for people to have a look at but it's confidential!
 
Upvote 0
What's in table [DateUpdated]? Is it working okay when you just query for the max of date? Can you use the formula in a normal form (such as testing it in a textbox?).

Date is btw a bad name for a field. Wierd problems happen when you use reserved words as identifiers. Maybe it's too late.

I would also step through the code (use a copy - if you can separate the DB and all its linked tables in a test environment) to try to understand it better, and when you do this you can test some of the conditions in the database to find out more about what's happening.
 
Upvote 0
Thanks Xenou! Very much appreciated.

I'll keep that date thing in mind, didn't realise that could cause issues.

I'll test the other things this afternoon, would be interesting to see the date of max to see if it's there it's wrong!

Thanks very much for the reply, will update on progress later!
 
Upvote 0

Forum statistics

Threads
1,224,502
Messages
6,179,126
Members
452,890
Latest member
Nikhil Ramesh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top