Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

Custom Save Query function not working in Access2002.

Hi all,

I have a Access97 database that has a custom for for creating queries.  On the form is a button for activating the code.  The code is posted below.

It references the wzmain80.wlib_stUniquedocname.  

My problem is this.  I have migrated the application to Access2002.  This particular code no longer works.  I have tried adding the file wzmain80 into the references,  but as it is an assess97 mde file Access2002 won't reference it.

The users run the front end as a MDE as well.  How to I get this to work in Access2002.

Thanking you all in advance and greatfully appreciating all help.

Regards
Mavreich

Private Sub cmdCreateQDF_Click()
On Error GoTo Error_cmdCreateQDF
Dim db As Database
Dim qdf As QueryDef
Dim strName As String
   'first get a unique name for the querydef object
   strName = Application.Run("wzmain80.wlib_stUniquedocname", "Query1", acQuery)
   strName = InputBox("Please specify a query name", "Save As", strName)
   If Not strName = vbNullString Then
      'only create the querydef if user really wants to.
      Set db = CurrentDb
      Set qdf = db.CreateQueryDef(strName, Me.txtSQL)
      qdf.Close
   Else
      'ok, so they don't want to
      'MsgBox "The save operation was cancelled." & vbCrLf & _
      '   "Please try again.", vbExclamation + vbOKOnly, "Cancelled"
   End If
   Me.lbxListSQL.Requery
Exit_cmdCreateQDF:
   On Error Resume Next
   qdf.Close
   Set qdf = Nothing
   db.QueryDefs.Refresh
   Set db = Nothing
   Exit Sub
Error_cmdCreateQDF:
   Resume Exit_cmdCreateQDF
End Sub
0
Mavreich
Asked:
Mavreich
  • 3
  • 3
1 Solution
 
shanesuebsahakarnCommented:
I think the wzmain for Access 2002 is called ACWZMAIN.MDE, and should be in the Office10 folder in your Office installation directory.
0
 
MavreichAuthor Commented:
Hi, shanesuebsahakarn

Thankyou for that pointer.  It was spot on.  However the only glitch is am having is that it will not select a unique save name for the query.

When the user saves the query it pops up a input window with a default save name.

Example:  Default safe name is Query1.  If there is a query already saved with the name then the next default save name should be Query2.

In this case it always comes up with Query1.

Below is the modified code.

Again i look forward to your responses.

Regards
Mavreich

Private Sub cmdCreateQDF_Click()
On Error GoTo Error_cmdCreateQDF
Dim db As Database
Dim qdf As QueryDef
Dim strName As String
   'first get a unique name for the querydef object
    strName = acwzmain.wlib_StUniqueDocName("Query1", acQuery)
     
   strName = InputBox("Please specify a query name", "Save As", strName)
   If Not strName = vbNullString Then
      'only create the querydef if user really wants to.
      Set db = CurrentDb
      Set qdf = db.CreateQueryDef(strName, Me.txtSQL)
      qdf.Close
   Else
      'ok, so they don't want to
      'MsgBox "The save operation was cancelled." & vbCrLf & _
      '   "Please try again.", vbExclamation + vbOKOnly, "Cancelled"
   End If
   Me.lbxListSQL.Requery
Exit_cmdCreateQDF:
   On Error Resume Next
   qdf.Close
   Set qdf = Nothing
   db.QueryDefs.Refresh
   Set db = Nothing
   Exit Sub
Error_cmdCreateQDF:
   Resume Exit_cmdCreateQDF
End Sub
0
 
shanesuebsahakarnCommented:
I haven't used this particular function before so I can't tell you what the problem is, but you could use this to get a default save name:
Dim lngMax As Long

lngMax=0
For Each qdf In CurrentDb.QueryDefs
   If Left$(qdf.Name,5)="Query" Then
      If Val(Right$(qdf.Name,6)) > lngMax Then lngMax = Val(Right$(qdf.Name,6))
   End If
Next
strName = "Query" & (lngMax+1)

Use this code in place of the StUniqueDocName line - I think it will do it for you.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
MavreichAuthor Commented:
Hi shanesuebsahakarn,


Your code keeps coming up with query1 as well.  it does not increment.

Thanks for the help though.  It is appreciated.

Regards
Mavreich
0
 
shanesuebsahakarnCommented:
Doh! The line:
If Val(Right$(qdf.Name,6)) > lngMax Then lngMax = Val(Right$(qdf.Name,6))

should read:
If Val(Mid$(qdf.Name,6)) > lngMax Then lngMax = Val(Mid$(qdf.Name,6))

Sorry about that!
0
 
MavreichAuthor Commented:
Hi shanesuebsahakarn,

Perfect.   I would like to thankyou for your speedy responses and great help.

As always Experts Exchange live up to it name.

With much apreciation
Mavreich
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now