Forum Discussion
VBA issue
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
- 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.
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.
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 Suband 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
