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. - Option Compare Database
-
Option Explicit
-
Dim ProjectCount As Integer
-
-
Private Sub cmdDETAIL_Click()
-
If IsNull([prmEmpNo]) Then
-
MsgBox ("No employee selected.")
-
[prmEmpNo].SetFocus
-
Exit Sub
-
End If
-
ProjectCount = NoOfProjects
-
If ProjectCount <> 0 Then
-
Forms![fPREPROJECT]![fProjectData].SetFocus
-
ProjectCount = NoOfProjects
-
Else
-
MsgBox ("Selection has no records.")
-
Exit Sub
-
End If
-
Select Case prmProjectGroup
-
Case "Complaints"
-
If ProjectCount <> 0 Then DoCmd.OpenForm "fComplain01", acNormal, , , acFormEdit
-
Case "Lab"
-
If ProjectCount <> 0 Then DoCmd.OpenForm "fComplain01", acNormal, , , acFormEdit
-
Case "ASBESTOS-INSP"
-
If ProjectCount <> 0 Then DoCmd.OpenForm "fAsbInsp01", acNormal, , , acFormEdit
-
Case "Inspect-PERM"
-
If ProjectCount <> 0 Then DoCmd.OpenForm "fFacInsp01", acNormal, , , acFormEdit
-
Case "Enforcement Cases"
-
If ProjectCount <> 0 Then DoCmd.OpenForm "fEnforce01", acNormal, , , acFormEdit
-
Case "Projects"
-
If ProjectCount <> 0 Then DoCmd.OpenForm "fProject01", acNormal, , , acFormEdit
-
Case "Permits"
-
If ProjectCount <> 0 Then DoCmd.OpenForm "fPermRev01", acNormal, , , acFormEdit
-
Case "Training"
-
If ProjectCount <> 0 Then DoCmd.OpenForm "fTraining01", acNormal, , , acFormEdit
-
Case Else
-
MsgBox ("Type of Project selected does not have detail records.")
-
Exit Sub
-
End Select
-
End SubPrivate Sub cmdExit_Click()
-
DoCmd.Echo False
-
Me.Visible = False
-
If (SysCmd(SYSCMD_GETOBJECTSTATE, A_FORM, "fAQSplashForm") = 0) Then
-
DoCmd.OpenForm "fAQSplashForm"
-
End If
-
DoCmd.Echo True
-
End Sub
-
-
Private Sub cmdHELP_Click()
-
Call NavigHelp
-
End Sub
-
-
-
Private Sub cmdMemo_Click()
-
-
If [Forms]![fPREPROJECT]![fProjectData].Form.CurrentRecord <> 0 Then
-
glbProjID = [Forms]![fPREPROJECT]![fProjectData].Form![ipProjID]
-
[Forms]![fPREPROJECT]![FormLink1] = [Forms]![fPREPROJECT]![fProjectData].Form![ipProjID]
-
DoCmd.OpenForm "fProjMemoPopup"
-
End If
-
-
-
End Sub
-
-
-
-
Private Sub Form_Activate()
-
'Me![fProjectData].SetFocus
-
'Me![fProjectData].Form![ipProjID].SetFocus
-
' .SelStart = intWhere - 1
-
' .SelLength = Len(strSearch
-
End Sub
-
-
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
-
If KeyCode <> vbKeyF6 Then Exit Sub
-
KeyCode = 0
-
If (SysCmd(SYSCMD_GETOBJECTSTATE, A_FORM, "fAsbFac01") <> 0) Then
-
Forms![fAsbFac01].SetFocus
-
ElseIf (SysCmd(SYSCMD_GETOBJECTSTATE, A_FORM, "fFacDetail01") <> 0) Then
-
Forms![fFacDetail01].SetFocus
-
End If
-
-
End Sub
-
-
Private Sub Form_Open(Cancel As Integer)
-
Dim db As Database
-
Set db = DBEngine.Workspaces(0).Databases(0)
-
'load the Rowsource for [prmEmpNo]
-
'code below performs a query and loads information
-
'from the EMPLOYEE table into the Rowsource
-
Dim EmpInfo As Recordset
-
Dim i As Integer
-
Dim qte
-
qte = Chr(34)
-
Dim prm As Parameter
-
Dim QD As QueryDef
-
Set QD = db.QueryDefs("qCmbEmpInfo2")
-
For i = 0 To QD.Parameters.Count - 1
-
Set prm = QD.Parameters(i)
-
prm.Value = Eval(prm.Name)
-
Next i
-
Set EmpInfo = QD.OpenRecordset(DB_OPEN_DYNASET)
-
EmpInfo.MoveFirst
-
Do Until EmpInfo.EOF
-
[prmEmpNo].RowSource = [prmEmpNo].RowSource & qte & EmpInfo![EmpInfo] & qte & ";" & qte & EmpInfo![Empl_No] & qte & ";"
-
EmpInfo.MoveNext
-
Loop
-
EmpInfo.Close
-
'this adds an additional entry at the beginning of Rowsource
-
[prmEmpNo].RowSource = qte & "AllActive" & qte & ";" & qte & "Actv" & qte & ";" & [prmEmpNo].RowSource
-
'this adds additional entries to the end of Rowsource
-
[prmEmpNo].RowSource = [prmEmpNo].RowSource & qte & "TOXICS" & qte & ";" & qte & "tox" & qte & ";"
-
[prmEmpNo].RowSource = [prmEmpNo].RowSource & qte & "STATIONARY SOURCE" & qte & ";" & qte & "cmp" & qte & ";"
-
[prmEmpNo].RowSource = [prmEmpNo].RowSource & qte & "AllEmployees" & qte & ";" & qte & "AllEmployees" & qte & ";"
-
'sets values of startup parameter fields
-
Forms![fPREPROJECT]![prmProjectGroup] = "AllProjects"
-
Forms![fPREPROJECT]![prmOpenClosed] = 2
-
Forms![fPREPROJECT]![prmEmpNo] = "Actv"
-
DoCmd.Maximize
-
Me![fProjectData].SetFocus
-
End Sub
-
-
-
-
Private Sub grpOpenClosed_Click()
-
Call ResetParms
-
End Sub
-
-
-
-
Public Function NoOfProjects()
-
NoOfProjects = DCount("*", "qProjectData")
-
End Function
-
-
Private Sub Opt1_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
-
Call RedoSort(1)
-
'ME: added September 10, 2008
-
Call RedoSort(1)
-
-
End Sub
-
-
Private Function RedoSort(OptNo As Integer)
-
If Me.[grpOpenClosed] = OptNo Then
-
Forms![fPREPROJECT]![fProjectData].Form.OrderBy = "[SortDate], [Due Date], [Project Description]"
-
End If
-
'ME: 09/03/2008: added the following for the Plan Date sorting-this is a test
-
If OptNo = 5 Then
-
Forms![fPREPROJECT]![fProjectData].Form.OrderBy = "[SortDate], [Plan Date], [Project Description]"
-
End If
-
End Function
-
-
Private Sub Opt2_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
-
Call RedoSort(2)
-
'ME: added September 10, 2008
-
Call RedoSort(2)
-
End Sub
-
-
-
-
Private Sub Opt3_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
-
Call RedoSort(3)
-
'ME: added September 10, 2008
-
Call RedoSort(3)
-
End Sub
-
-
-
Private Sub Opt4_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
-
Call RedoSort(4)
-
'ME: added September 10, 2008
-
Call RedoSort(4)
-
End Sub
-
-
Public Function ResetParms()
-
Call qProjectDataGen(Forms![fPREPROJECT], Forms![fPREPROJECT]![fProjectData].Form)
-
[Forms]![fPREPROJECT]![fProjectData].Requery
-
If [Forms]![fPREPROJECT]![fProjectData].[Form].[CurrentRecord] <> 0 Then
-
Me![fProjectData].SetFocus
-
Me![fProjectData].Form![ipProjID].SetFocus
-
End If
-
End Function
-
-
Private Sub Opt5_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
-
Call RedoSort(5)
-
'ME: added September 10, 2008
-
Call RedoSort(5)
-
End Sub
-
-
Private Sub prmEmpNo_Click()
-
Call ResetParms
-
-
End Sub
-
-
Private Sub prmProjectGroup_Click()
-
Call ResetParms
-
-
End Sub
-
11 1377
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.
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 - On Error Resume Next
-
If not EmpInfo is nothing then
-
EmpInfo.Close
-
Set EmpInfo = nothing
-
end if
-
If not QD is nothing then
-
QD.Close
-
Set QD = nothing
-
end if
-
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
The error is in line 102.
The error reads,
"Run-time error '13':
Type mismatch
zmbd 5,501
Expert Mod 4TB
Sounds like a library reference setting
Please try the Replace method as outlined in my last post.
:)
You will also need to change your Syntax for retrieving the State of the Form fAQSplashForm using SysCmd to: - If (SysCmd(acSysCmdGetObjectState, A_FORM, "fAQSplashForm") = 0) Then
-
DoCmd.OpenForm "fAQSplashForm"
-
End If
Thank you for your help. I replaced line 102 of the code as suggested with the following: - Set EmpInfo = QD.OpenRecordset(dbOpenDynaset)
The error '13' is still occurring.
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.
Could the OpenDynaset code still be our issue. This error continues to pop up when attempting to open various forms.
- Change the following in the Form's Open() Event
- Dim db As Database
-
Dim QD As QueryDef
-
Dim EmpInfo As Recordset
-
Set db = DBEngine.Workspaces(0).Databases(0)
-
- to
- Dim db As DAO.Database
-
Dim QD As DAO.QueryDef
-
Dim EmpInfo As DAO.Recordset
-
Set db = CurrentDb()
-
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.
zmbd 5,501
Expert Mod 4TB Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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. ...
|
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...
|
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...
|
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...
|
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,...
|
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 -...
|
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...
|
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...
|
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.
|
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...
|
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...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
| |