Dbl_click event in Sub_form gives error : A problem occurred while Microsoft Access . . .

[Ondrej_P]

Hi I’m new to Access and VBA in Access. I have problem with the DB. I made an main form from where I can filter the records in an Sub form. The Sub form is an Query from Two tables, let’s say “original“ and “change” on one of the field is an dbl_click event start what should trigger an VBA code but instead it return an error : A problem occurred while Microsoft Access was communicating with the OLE Server or ActiveX Control.




The problem is only on the version Access 2016 in Work at Home where I use the 2019 it works fine

I tried many times to copy the DB to an New DB didn’t work for me.

Please help

[ranman256]

do you have some odd control on the form other than the subform?
an old calendar view? , an embedded word doc?

what do u dbl-click to change the source data?

[Ondrej_P]

Hi,

In the main form are only two list boxes witch I use for filtering and the Sub form nothing else.

and this is the code behind the Dbl-click (The Tabell names are inn Slovak sorry) :

Private Sub Real_Start_cinnosti_DblClick(Cancel As Integer)

Dim strSQL1 As String
Dim strSQL2 As String
Dim a, b, c, d As String
DoCmd.SetWarnings False
a = Me.Zakazka
b = Me.SS_Baugruppe
c = Date
'd = "R"
If IsNull(Me.Real_Start_cinnosti) Then 'pokial uz existuje zaznam nespravi nic

strSQL1 = "INSERT INTO T_Hlavne_vypinace_change ( Zakazka, SS_Baugruppe, Real_Start_cinnosti )" _
& "SELECT T_Hlavne_vypinace.Zakazka, T_Hlavne_vypinace.SS_Baugruppe, T_Hlavne_vypinace.Real_Start_cinnosti " _
& "FROM T_Hlavne_vypinace " _
& "WHERE (((T_Hlavne_vypinace.Zakazka)= '" & a & "')and((T_Hlavne_vypinace.SS_Baugruppe)= '" & b & "'));"

'CurrentDb.Execute strSQL1, dbFailOnError Or dbSeeChanges
DoCmd.RunSQL strSQL1

strSQL2 = "UPDATE T_Hlavne_vypinace_Change " _
& "SET Real_Start_cinnosti= '" & c & "' " _
& "WHERE Zakazka= '" & a & "' and SS_Baugruppe= '" & b & "';"

DoCmd.RunSQL strSQL2

Else

End If

DoCmd.SetWarnings True

DoCmd.Requery D_Show_Hlavne_vypinace_plan_and_status_podformular
End Sub

[ranman256]

instead of using code, (possible syntax errors), put your sql into a query.
the query uses the listbox as criteria like:

insert into table (select * from table2) where [field]=forms!myForm!lstbox

NO need for any code. Query will prevent syntax errors.
and you only run:
docmd.openquery "qaMyQuery"

[ssanfu]

Welcome to the forum.

You have a few issues in your code. By the way, you should enclose the code in code tags, (click the # button in the reply menu)

Private Sub Real_Start_cinnosti_DblClick(Cancel As Integer)
    Dim strSQL1 As String
    Dim strSQL2 As String
    Dim a, b, c, d As String   ' <<--Issue 1
    
    DoCmd.SetWarnings False
    a = Me.Zakazka
    b = Me.SS_Baugruppe
    c = Date                     ' <<-- Issue 2
    'd = "R"

    If IsNull(Me.Real_Start_cinnosti) Then 'pokial uz existuje zaznam nespravi nic

        'I changed how the query is constructed
        strSQL1 = "INSERT INTO T_Hlavne_vypinace_change ( Zakazka, SS_Baugruppe, Real_Start_cinnosti )"
        strSQL1 = strSQL1 & " SELECT T_Hlavne_vypinace.Zakazka, T_Hlavne_vypinace.SS_Baugruppe, T_Hlavne_vypinace.Real_Start_cinnosti"
        strSQL1 = strSQL1 & " FROM T_Hlavne_vypinace"
        strSQL1 = strSQL1 & " WHERE (((T_Hlavne_vypinace.Zakazka)= '" & a & "')and((T_Hlavne_vypinace.SS_Baugruppe)= '" & b & "'));"
        Debug.Print strSQL1     '<<-- I added this line for debugging. Comment out when debugging is complete.

        'CurrentDb.Execute strSQL1, dbFailOnError Or dbSeeChanges   ' <<-- Issue 3
        DoCmd.RunSQL strSQL1


          'I changed how the query is constructed
        strSQL2 = "UPDATE T_Hlavne_vypinace_Change"
        strSQL2 = strSQL1 & " SET Real_Start_cinnosti= '" & c & "'"      ' <<-- Issue 4
        strSQL2 = strSQL1 & " WHERE Zakazka= '" & a & "' and SS_Baugruppe= '" & b & "';"
        Debug.Print strSQL1     '<<-- I added this line for debugging. Comment out when debugging is complete.
        
        DoCmd.RunSQL strSQL2

    Else        '<<-- not needed - no FALSE option

    End If

    DoCmd.SetWarnings True

    DoCmd.Requery D_Show_Hlavne_vypinace_plan_and_status_podformular
End Sub

Issue 1 : If you use a statement like

Dim a, b, c, d As String

d is declared as type string
a, b. & c are declared as Variants

Variable types MUST be explicitly declared (Dim)

Dim a As String
Dim b As String
Dim c As Date
Dim d As String

================================================== ================

Issue 2:

    c = Date

Do you really want to assign a Date to a string Variable?? Maybe the statement should be

Dim c as Date

================================================== ================

Issue 3: Instead of

CurrentDb.Execute strSQL1, dbFailOnError Or dbSeeChanges

try

CurrentDb.Execute strSQL1, dbFailOnError + dbSeeChanges

================================================== ================

Issue 4:

SET Real_Start_cinnosti= '" & c & "'"  

Dates MUST be delimited with hashes (#)

SET Real_Start_cinnosti= #" & c & "#"

I ALWAYS use EXECUTE, Never RunSQL

[Ondrej_P]

Hi Ranman256 and Ssanfu,


Your advices are as gold for me but unfortunately I still get the same result : A problem occurred while Microsoft Access was communicating with the OLE Server or ActiveX Control


I tried an simple on Dbl_click event in the main form and there it works. (like on dbl_click txtfield=Date())
I tried to use the Macro builder to start the Query in sub form and it started the Query but I have problems to create an query that does that what is in the SQL.

As a told before I’m an total newbie in Access so please be patient with me.

[CarlettoFed]

Attach a sample file, free of sensitive data, to be able to directly prove what happens.

[CJ_London]

the error sounds like you might have a corruption in your code modules. This can happen if you make changes to your code whilst it is running, or the code is such it interferes with the code structure in some way

I tried many times to copy the DB to an New DB didn’t work for me.

rather than copying to, try import from

other things to try - decompile your file - see this link https://www.fmsinc.com/MicrosoftAcce.../Decompile.asp

also try the following

1. copy the whole module code to notepad
2. in the form properties, change the 'has module' property to no - this will clear the code from the form
3. save and close the form
4. compact the db
5. reopen the db
6. open the form in design view
7. select any event and go to the vba editor (can be any event, you will be removing it anyway)
8. delete any code that is now in the module (should just be Option Compare database and the event you just selected)
9. copy all the code from notepad (don't use the copy you may still have in the clipboard)
10. Paste the code from the notepad
11. if you don't already have it, ensure you have Option Explicit below Option Compare Database
12. Compile the code

job done

[Ondrej_P]

Ok, and how do it ?

[Ondrej_P]

@CarlettoFed, here is the DB
@Ajax, i tried that still the same

[Welshgasman]

Can't even open it?
Can't find the object 'Databases' ?

[Ondrej_P]

the main form is "D_Show_Hlavne_vypinace_plan_and_status"

[CarlettoFed]

The problem is probably due to the system with which the masks were created. By re-creating them from scratch everything works smoothly.
NewDB_New.zip

[Ondrej_P]

Hi,
it worked, when i made the form from scratch the event fired.
Thanks to all for your time and great help.