473,320 Members | 1,694 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Macros/VBA created in Access 2003 not working in Access 2013

12
Our Access database was recently updated from 2003 to 2013, which is quite a large jump. Several code areas no longer work and the creator of the dbase has since retired. Now in .accdb, we receive the following errors during debugging:

Set EmpInfo = QD.OpenRecordset(DB_OPEN_DYNASET)

This error occurs in the following code.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3. Dim ProjectCount As Integer
  4.  
  5. Private Sub cmdDETAIL_Click()
  6. If IsNull([prmEmpNo]) Then
  7.     MsgBox ("No employee selected.")
  8.     [prmEmpNo].SetFocus
  9.     Exit Sub
  10. End If
  11. ProjectCount = NoOfProjects
  12. If ProjectCount <> 0 Then
  13.     Forms![fPREPROJECT]![fProjectData].SetFocus
  14.     ProjectCount = NoOfProjects
  15. Else
  16.     MsgBox ("Selection has no records.")
  17.     Exit Sub
  18. End If
  19. Select Case prmProjectGroup
  20.     Case "Complaints"
  21.         If ProjectCount <> 0 Then DoCmd.OpenForm "fComplain01", acNormal, , , acFormEdit
  22.     Case "Lab"
  23.         If ProjectCount <> 0 Then DoCmd.OpenForm "fComplain01", acNormal, , , acFormEdit
  24.     Case "ASBESTOS-INSP"
  25.         If ProjectCount <> 0 Then DoCmd.OpenForm "fAsbInsp01", acNormal, , , acFormEdit
  26.     Case "Inspect-PERM"
  27.         If ProjectCount <> 0 Then DoCmd.OpenForm "fFacInsp01", acNormal, , , acFormEdit
  28.     Case "Enforcement Cases"
  29.         If ProjectCount <> 0 Then DoCmd.OpenForm "fEnforce01", acNormal, , , acFormEdit
  30.     Case "Projects"
  31.         If ProjectCount <> 0 Then DoCmd.OpenForm "fProject01", acNormal, , , acFormEdit
  32.     Case "Permits"
  33.         If ProjectCount <> 0 Then DoCmd.OpenForm "fPermRev01", acNormal, , , acFormEdit
  34.     Case "Training"
  35.         If ProjectCount <> 0 Then DoCmd.OpenForm "fTraining01", acNormal, , , acFormEdit
  36.     Case Else
  37.         MsgBox ("Type of Project selected does not have detail records.")
  38.         Exit Sub
  39. End Select
  40. End SubPrivate Sub cmdExit_Click()
  41. DoCmd.Echo False
  42. Me.Visible = False
  43. If (SysCmd(SYSCMD_GETOBJECTSTATE, A_FORM, "fAQSplashForm") = 0) Then
  44.     DoCmd.OpenForm "fAQSplashForm"
  45. End If
  46. DoCmd.Echo True
  47. End Sub
  48.  
  49. Private Sub cmdHELP_Click()
  50. Call NavigHelp
  51. End Sub
  52.  
  53.  
  54. Private Sub cmdMemo_Click()
  55.  
  56. If [Forms]![fPREPROJECT]![fProjectData].Form.CurrentRecord <> 0 Then
  57.     glbProjID = [Forms]![fPREPROJECT]![fProjectData].Form![ipProjID]
  58.     [Forms]![fPREPROJECT]![FormLink1] = [Forms]![fPREPROJECT]![fProjectData].Form![ipProjID]
  59.     DoCmd.OpenForm "fProjMemoPopup"
  60. End If
  61.  
  62.  
  63. End Sub
  64.  
  65.  
  66.  
  67. Private Sub Form_Activate()
  68. 'Me![fProjectData].SetFocus
  69. 'Me![fProjectData].Form![ipProjID].SetFocus
  70.  '           .SelStart = intWhere - 1
  71. '            .SelLength = Len(strSearch
  72. End Sub
  73.  
  74. Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
  75. If KeyCode <> vbKeyF6 Then Exit Sub
  76. KeyCode = 0
  77. If (SysCmd(SYSCMD_GETOBJECTSTATE, A_FORM, "fAsbFac01") <> 0) Then
  78.     Forms![fAsbFac01].SetFocus
  79. ElseIf (SysCmd(SYSCMD_GETOBJECTSTATE, A_FORM, "fFacDetail01") <> 0) Then
  80.     Forms![fFacDetail01].SetFocus
  81. End If
  82.  
  83. End Sub
  84.  
  85. Private Sub Form_Open(Cancel As Integer)
  86. Dim db As Database
  87. Set db = DBEngine.Workspaces(0).Databases(0)
  88. 'load the Rowsource for [prmEmpNo]
  89. 'code below performs a query and loads information
  90. 'from the EMPLOYEE table into the Rowsource
  91. Dim EmpInfo As Recordset
  92. Dim i As Integer
  93. Dim qte
  94. qte = Chr(34)
  95. Dim prm As Parameter
  96. Dim QD As QueryDef
  97.     Set QD = db.QueryDefs("qCmbEmpInfo2")
  98. For i = 0 To QD.Parameters.Count - 1
  99.     Set prm = QD.Parameters(i)
  100.     prm.Value = Eval(prm.Name)
  101. Next i
  102. Set EmpInfo = QD.OpenRecordset(DB_OPEN_DYNASET)
  103. EmpInfo.MoveFirst
  104. Do Until EmpInfo.EOF
  105.     [prmEmpNo].RowSource = [prmEmpNo].RowSource & qte & EmpInfo![EmpInfo] & qte & ";" & qte & EmpInfo![Empl_No] & qte & ";"
  106. EmpInfo.MoveNext
  107. Loop
  108. EmpInfo.Close
  109. 'this adds an additional entry at the beginning of Rowsource
  110. [prmEmpNo].RowSource = qte & "AllActive" & qte & ";" & qte & "Actv" & qte & ";" & [prmEmpNo].RowSource
  111. 'this adds additional entries to the end of Rowsource
  112. [prmEmpNo].RowSource = [prmEmpNo].RowSource & qte & "TOXICS" & qte & ";" & qte & "tox" & qte & ";"
  113. [prmEmpNo].RowSource = [prmEmpNo].RowSource & qte & "STATIONARY SOURCE" & qte & ";" & qte & "cmp" & qte & ";"
  114. [prmEmpNo].RowSource = [prmEmpNo].RowSource & qte & "AllEmployees" & qte & ";" & qte & "AllEmployees" & qte & ";"
  115. 'sets values of startup parameter fields
  116. Forms![fPREPROJECT]![prmProjectGroup] = "AllProjects"
  117. Forms![fPREPROJECT]![prmOpenClosed] = 2
  118. Forms![fPREPROJECT]![prmEmpNo] = "Actv"
  119. DoCmd.Maximize
  120. Me![fProjectData].SetFocus
  121. End Sub
  122.  
  123.  
  124.  
  125. Private Sub grpOpenClosed_Click()
  126. Call ResetParms
  127. End Sub
  128.  
  129.  
  130.  
  131. Public Function NoOfProjects()
  132. NoOfProjects = DCount("*", "qProjectData")
  133. End Function
  134.  
  135. Private Sub Opt1_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  136. Call RedoSort(1)
  137. 'ME: added September 10, 2008
  138. Call RedoSort(1)
  139.  
  140. End Sub
  141.  
  142. Private Function RedoSort(OptNo As Integer)
  143. If Me.[grpOpenClosed] = OptNo Then
  144.     Forms![fPREPROJECT]![fProjectData].Form.OrderBy = "[SortDate],  [Due Date], [Project Description]"
  145. End If
  146. 'ME:  09/03/2008:  added the following for the Plan Date sorting-this is a test
  147. If OptNo = 5 Then
  148.     Forms![fPREPROJECT]![fProjectData].Form.OrderBy = "[SortDate], [Plan Date], [Project Description]"
  149. End If
  150. End Function
  151.  
  152. Private Sub Opt2_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  153. Call RedoSort(2)
  154. 'ME: added September 10, 2008
  155. Call RedoSort(2)
  156. End Sub
  157.  
  158.  
  159.  
  160. Private Sub Opt3_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  161. Call RedoSort(3)
  162. 'ME: added September 10, 2008
  163. Call RedoSort(3)
  164. End Sub
  165.  
  166.  
  167. Private Sub Opt4_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  168. Call RedoSort(4)
  169. 'ME: added September 10, 2008
  170. Call RedoSort(4)
  171. End Sub
  172.  
  173. Public Function ResetParms()
  174. Call qProjectDataGen(Forms![fPREPROJECT], Forms![fPREPROJECT]![fProjectData].Form)
  175. [Forms]![fPREPROJECT]![fProjectData].Requery
  176. If [Forms]![fPREPROJECT]![fProjectData].[Form].[CurrentRecord] <> 0 Then
  177.     Me![fProjectData].SetFocus
  178.     Me![fProjectData].Form![ipProjID].SetFocus
  179. End If
  180. End Function
  181.  
  182. Private Sub Opt5_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
  183. Call RedoSort(5)
  184. 'ME: added September 10, 2008
  185. Call RedoSort(5)
  186. End Sub
  187.  
  188. Private Sub prmEmpNo_Click()
  189. Call ResetParms
  190.  
  191. End Sub
  192.  
  193. Private Sub prmProjectGroup_Click()
  194. Call ResetParms
  195.  
  196. End Sub
  197.  
Sep 8 '16 #1
11 1377
Seth Schrock
2,965 Expert 2GB
Please tell us where in those almost 200 lines of code the error is and we also need to know what the error message says (exact wording) along with the error number.
Sep 8 '16 #2
zmbd
5,501 Expert Mod 4TB
>>> Make a copy of your database <<<

Seth is absolutely correct, we need the code line and the
EXACT title
EXACT number
EXACT text
of the error message you receive

Of note the "DB_OPEN_DYNASET" is an older reference, in the context shown, it shouldn't give you an error; however, if for some reason this is the root cause, the following should help:

+ Open VBE <alt><F11>

+ If the project explorer isn't already open (default) <ctrl><r>

+ Open any code page, doesn't matter if it's a form, standard, or class module

+<ctrl><h>
This will open the Replace dialog

- Find What: DB_OPEN_DYNASET
- Replace With: dbOpenDynaset
- Search: Current Project
- Direction: All
- leave all other settings blank
[Replace All]

+ Menu>File>Save
(or the icon, or <ctrl><s>)

++ Consider inserting between line 120 and 121
Expand|Select|Wrap|Line Numbers
  1. On Error Resume Next
  2. If not EmpInfo is nothing then
  3.    EmpInfo.Close
  4.    Set EmpInfo = nothing
  5. end if
  6. If not QD is nothing then
  7.    QD.Close
  8.    Set QD = nothing
  9. end if
  10. If not DB is nothing then Set QD = nothing
All of your code where you set or open anything should explicitly close those resources or you risk file bloat and data corruption.
>> NOTE DO NOT CLOSE THE DATABSE only set the variable back to nothing to release the memory and pending writes as I have done in line 10.
I usually have this as a cleanup routine in my error traps for all code where I create or set pointers to objects.


+ Menu>Debug>Compile
Fix any errors found
Repeat this step until Compiles without error
> I highly recommend saving after each correction

+ Close the VBE

+ Access Application
Ribbon>Database Tools>Tools>Compact and Repair Database
Sep 8 '16 #3
tallen
12
The error is in line 102.

The error reads,
"Run-time error '13':
Type mismatch
Sep 8 '16 #4
zmbd
5,501 Expert Mod 4TB
Sounds like a library reference setting
Please try the Replace method as outlined in my last post.
:)
Sep 8 '16 #5
ADezii
8,834 Expert 8TB
You will also need to change your Syntax for retrieving the State of the Form fAQSplashForm using SysCmd to:
Expand|Select|Wrap|Line Numbers
  1. If (SysCmd(acSysCmdGetObjectState, A_FORM, "fAQSplashForm") = 0) Then
  2.  DoCmd.OpenForm "fAQSplashForm"
  3. End If
Sep 8 '16 #6
tallen
12
Thank you for your help. I replaced line 102 of the code as suggested with the following:

Expand|Select|Wrap|Line Numbers
  1. Set EmpInfo = QD.OpenRecordset(dbOpenDynaset)
The error '13' is still occurring.
Sep 9 '16 #7
tallen
12
I should clarify that the Form now flashes up, but then quickly disappears on the screen so I know we are getting closer to a solution.
Sep 9 '16 #8
tallen
12
Could the OpenDynaset code still be our issue. This error continues to pop up when attempting to open various forms.
Sep 9 '16 #9
ADezii
8,834 Expert 8TB
  1. Change the following in the Form's Open() Event
    Expand|Select|Wrap|Line Numbers
    1. Dim db As Database
    2. Dim QD As QueryDef
    3. Dim EmpInfo As Recordset
    4. Set db = DBEngine.Workspaces(0).Databases(0)
    5.  
  2. to
    Expand|Select|Wrap|Line Numbers
    1. Dim db As DAO.Database
    2. Dim QD As DAO.QueryDef
    3. Dim EmpInfo As DAO.Recordset
    4. Set db = CurrentDb()
    5.  
Sep 9 '16 #10
tallen
12
ADezii and zmbd,

The combination of your suggested code updates worked! We have our database up and running again thanks to your expertise. Thank you with much appreciation for sharing your skill.
Sep 12 '16 #11
zmbd
5,501 Expert Mod 4TB
Related question moved to:
Part 2: Macros/VBA created in Access 2003 not working in Access 2013
Sep 16 '16 #12

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Susan Bricker | last post by:
I just purchased a new PC that came with MS Office 2003 (Professional Ed.). I opened MS Access 2003 and created a new database but it opened with a "note in the window header bar" that says...
3
by: Dave | last post by:
Hi, I wrote a VBA program to read and process data from an ODBC source and put it into an Access database. I want to make the program into an MDE file and run it on systems with Access 2003. ...
5
by: DeanL | last post by:
I know you can convert a database from Access 2003 to 97 easily but is there anything that I should avoid doing in Access 2003 that might make my database incompatible with Access 97? Many...
2
by: carmela_wong | last post by:
Hi all, I have converted a database from Access 2003 to Access 97. Everything gets neatly imported into the new database except for one form which continues to show a fatal error when I try to...
0
by: jayohare | last post by:
Hello, I have code within my DB application to process credit cards through authorize.net. Ive been using the same code for several years without a problem. I have an order entry computer and...
18
by: Dennis | last post by:
Greetings. I want to run the two versions of Access on the same machine (2003 & 2007). I still need 2003 for most of my work, yet I need to start learning 2007. In my previous attempts at this,...
2
by: curran.george | last post by:
'add one textbox to form1 with Control Source property = ID 'copy/paste the form_load code below: 'Then open the form and then attempt to sort the datasheet 'crashes 2003, error 3450 Access 2007 -...
12
by: pballou | last post by:
The is a simple example of code that worked in access 2003 but does not in access 2007. I have checked it with the debugger and the reason is the value of formField1 does not change in ver. 2007...
5
by: SUSAN MALAN | last post by:
I have created a Database in Access 2003 and have now downloaded 2007. I have tried converting it in several ways. I have opened my previous Database and saved it as a 2007 verion -No success, I have...
6
by: msilva100 | last post by:
Hello, I need to have remote access to a MS Access 2013 Database running on a Windows 7 Server. Can someone kindly recommend a remote access tool? Thank you.
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.