VBA issue

[cmancas]

Hi there!

I've been redirected here by the Office Customer Support, where I got the following session case number for today: 1008115744.

Unfortunately, as I live in Romania, Europe, my Office 365 license bought and renewed twice already is in Romanian, while my Windows 10 is in English.

I just created a small Access db with 4 tables and their forms. There are only ASCII characters in both the tables and the forms. In one of them I wrote a small vba method for the Form_BeforeUpdate event. I cannot test it because I got the message saying that my method cannot be run because there is a communication error with the OLE server or an ActiveX control.

I tried to attach the corresponding db file, but it was rejected.

Thank you in advance for your help,

Christian Mancas

[#2]

Can you post your code?
Does your code compile without errors?
Have you tried an Office Repair?
What Access build no are you currently running? Have you tried updating to see if it is an issue with that specific build?

[#3]

Thank you for your message @Daniel_Pineault!

1. Yes, code is compiling w/o errors; here it is:

Private Sub Form_BeforeUpdate(Cancel As Integer)
'***********************************************
Dim EmbTimeZone, DestTimeZone As Variant
Dim ETZ, DTZ, FlightMins, FlightH, FlightM As Integer

On Error GoTo err_point
Stop
If Not Me.NewRecord And Me!Embarkment = Me!Embarkment.OldValue And Me!Destination = Me!Destination.OldValue And Me!TakeOffLocDateTime = Me!TakeOffLocDateTime.OldValue And Me!LandingLocDateTime = Me!LandingLocDateTime.OldValue Then GoTo exit_point
EmbTimeZone = DLookup("TimeZone", "CITIES", "x=" & DLookup("City", "AIRPORTS", "Embarkment=" & Me!Embarkment))
ETZ = CInt(EmbTimeZone)
DestTimeZone = DLookup("TimeZone", "CITIES", "x=" & DLookup("City", "AIRPORTS", "Destination=" & Me!Destination))
DTZ = CInt(DestTimeZone)
FlightH = IIf(ETZ * DTZ < 0, Abs(DTZ) + Abs(ETZ), Abs(DTZ - ETZ))
FlightMins = Abs(CInt(DateDiff("n", Me!LandingLocDateTime, Me!TakeOffLocDateTime))) + FlightH * 60
If FlightMins <= 0 Then
Cancel = True
Beep
MsgBox " ... and, instead, it is " & FlightMins & " minutes!", vbCritical, "Wrong data rejected: flight time should be strictly positive..."
Else
FlightH = FlightMins / 60
FlightM = FlightMins Mod 60
Me![FlightTime] = FlightH & ":" & FlightM
End If
exit_point:
Exit Sub
err_point:
Cancel = True
MsgBox Err.Source & " -> " & Err.Description, vbCritical, "Error in method Form_BeforeUpdate of module OPTIONS..."
End Sub

2. No I did not repair Office: I do not think it needs, all other applications are working fine. Moreover, very complex Access applications that I developed in years under previous US Access versions are working fine even under this Romanian one as well.

3. Being a subscription, I always have the latest version that is updated automatically immediately after such updates are available. Currently, it is compilation 130001.20384 of the 2006 version.

Moreover, before contacting Microsoft, I tried one of the "solutions" circulating on the net, namely to change the international settings of Windows to Romania (instead of US), but in vain, the VBA code is not running either and Access is issuing the same error message.

Thank you once more for your time and kindness,

Christian  

[#4]

p.s. Stop is obviously inserted only for debugging purposes, but it is not reached.

[#5]

p.p.s. I also tried in vain to delete and recreate the form, as well as to compact and repair this tiny db, both under US and Romanian Windows settings.

[#6]

@cmancas Probably something in your form design is causing this...open your form in design view and check if you have any "unusual" controls...also this is a database created from scratch or something you copy-paste and tweaked it...maybe some leftovers are causing the issue.

[#7]

@tsgiannis 

it's a db created from scratch by my, with the Access form wizard, in datasheet form, over the following table, without making then any other modification than adding it that Form_BeforeUpdate method:

CREATE TABLE OPTIONS (
x COUNTER PRIMARY KEY,
FlightNo VARCHAR(8) NOT NULL UNIQUE,
Airline LONG NOT NULL,
CONSTRAINT fkAirline FOREIGN KEY (Airline) REFERENCES AIRLINES,
Embarkment LONG NOT NULL,
CONSTRAINT fkEmbarkment FOREIGN KEY (Embarkment) REFERENCES AIRPORTS,
Destination LONG NOT NULL,
CONSTRAINT fKDestination FOREIGN KEY (Destination) REFERENCES AIRPORTS,
TakeOffLocDateTime DATETIME NOT NULL,
LandingLocDateTime DATETIME NOT NULL,
FlightTime VARCHAR(5),
CONSTRAINT Koptions UNIQUE (Airline, Embarkment, Destination, TakeOffLocDateTime, LandingLocDateTime) )

Moreover, on the smallest table in this db (

CREATE TABLE AIRLINES (
x COUNTER PRIMARY KEY,
Airline VARCHAR(8) NOT NULL UNIQUE )

) I've created a similar form, attached to its class this super simple VBA method:

Private Sub Form_Open(Cancel As Integer)
MsgBox "Hello world!"
End Sub

and the result is the same: I got same error on impossible communication with the OLE server or an ActiveX control.

I'm quite sure that this not a programming issue, but a Romanian version of Access 2016.

I immensely regret that, here, we are offered only this Romanian version instead of the US one...

Best regards and thanks once more,

Christian Mancas

[#8]

p.s. please excuse my typo errors - this is an example for my next book scheduled to be published this fall in the US and I'm in a hurry: of course that the db was created by me, not by my 🙂

[#9]

p.p.s. there is also a missing right parenthesis in the definition of the kOptions key 🙂