Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-16-2013, 10:30 AM   #1
Tango
DB/Application Dev Newbie
 
Join Date: Jun 2011
Location: Sumter, South Carolina
Posts: 141
Thanks: 27
Thanked 1 Time in 1 Post
Tango is on a distinguished road
Run-time Error (Can't nail it down)

Evening guys,
I have a user getting a runtime error on the below code (near the bottom "RUN LINKDB()" is where the debugger highlights. It works great on my computer but for some reason not on his. I checked all his reference libraries and they match mine. Any thoughts?

The on load code for my main form:
Code:
Private Sub Form_Load()
Dim suser As String
    Dim sSQL As String
    Dim CurUser As String
    
    suser = Environ("username") 'Name of real person
        'MsgBox "sUser = " & sUser
    'MsgBox "curUser = " & CurrentUser()
    Forms![enable macro page]![Text210] = suser
    
    
    'If suser = "clinton.pilgrim" Then
    'MsgBox ("..... ..... ..... ....."), vbOKOnly
    'DoCmd.OpenForm "Misc"
    'Exit Sub
    'End If
'-----------------------------
curtbl = CurrentDb.TableDefs("help table").Connect

  On Error GoTo con_error
  
  Dim cnn As ADODB.Connection
   Set cnn = New ADODB.Connection
   'Set the provider property to the OLE DB Provider for ODBC.
   cnn.Provider = "MSDASQL"
   ' Open a connection using an ODBC DSN.
   cnn.ConnectionString = "driver={SQL Server};" & _
      curtbl
   cnn.Open
   ' Find out if the attempt to connect worked.
   If cnn.State = adStateOpen Then
      
   Else
      MsgBox "Sorry. The server is not responding."
   End If
   ' Close the connection.
   cnn.Close
 
backswap.backgroundswap
    
Dim intStore As Integer
'Count of uncomplete jobs that are past the Expected Completion Date
intStore = DCount("[JobNumber]", "[tblJobs]", "[ExpectedCompletionDate] <=Now() AND [Complete] =0")
'If count of uncomplete jobs is zero display switchboard
'Else display message box detailing amount of jobs
'and give the user the option as to whether to view these or not.
    If intStore = 0 Then
    DoCmd.OpenForm "Start Page", acNormal
    
               Exit Sub
                Else
                    If MsgBox("There are " & intStore & " uncompleted jobs" & _
                    vbCrLf & vbCrLf & "Would you like to see these now?", _
                    vbYesNo, "You Have Uncomplete Jobs...") = vbYes Then
                    DoCmd.Minimize
                    DoCmd.OpenForm "frmReminders", acNormal
                Else
                    DoCmd.OpenForm "Start Page", acNormal
            Exit Sub
        End If
    End If
    
con_Exit:
    Exit Sub
    
con_error:
  Dim resp
  Dim resp2
  resp = MsgBox("Server Connection Failed!" & vbCrLf & vbCrLf & "Would you like to change the name of your base server?", vbYesNo)
If resp = vbYes Then
    On Error Resume Next
        Run linkDB()
        Else
        resp2 = MsgBox("Server Connection Failed!" & vbCrLf & vbCrLf & "Would you like to retry the connection?", vbYesNo)
            If resp2 = vbNo Then
                Application.Quit
            Else
            DoCmd.Close acForm, "enable macro page"
            DoCmd.OpenForm "enable macro page", acNormal
            End If
            
End If

End Sub
the Run linkDB line calls the below module:
Code:
Option Compare Database
Public Function linkDB()
On Error GoTo err_update
Dim dbs As DAO.Database
  Dim tdf As DAO.TableDef
  Dim Newserver As String
    
Newserver = InputBox("Enter the network name of the server you wish to use:" & vbCrLf & vbCrLf & "Example: SSC-CS-SQL02", _
                           "Bind To New Server")
    
  'If the user clicks cancel or gives a null length responce the server change will abort
  
If Newserver <> "" Then
MsgBox "Server Selected: " & Newserver & vbCrLf & vbCrLf & "Please be patient as the server is located. This process can take up to a minute."
  Set dbs = CurrentDb()
  ' Loop through TableDefs collection, only processing
  ' the table if it already has a Connection property.
  ' (all other tables are local ... not linked)
  For Each tdf In dbs.TableDefs
    If tdf.Connect <> "" Then
      tdf.Connect = "ODBC;DRIVER={SQL Server};SERVER=" & Newserver & ";DATABASE=QP3;Trusted_Connection=Yes"
      tdf.RefreshLink
    End If
  Next
Else
MsgBox "Server change request canceled or invalid response given."
Application.Quit

End If
exit_fun:
Exit Function
err_update:
MsgBox "The update experienced errors and will be canceled. This is likely because the server requested is not available."
Application.Quit
End Function

__________________
---------------
If I helped you, please click thanks or the balance scales on my post.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Tango is offline   Reply With Quote
Old 01-16-2013, 11:06 AM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,377
Thanks: 13
Thanked 4,121 Times in 4,053 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Run-time Error (Can't nail it down)

Delete the word "Run" and try it.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 01-16-2013, 11:11 AM   #3
Tango
DB/Application Dev Newbie
 
Join Date: Jun 2011
Location: Sumter, South Carolina
Posts: 141
Thanks: 27
Thanked 1 Time in 1 Post
Tango is on a distinguished road
Re: Run-time Error (Can't nail it down)

It gives an "expected =" syntax error if I remove the word "Run".

__________________
---------------
If I helped you, please click thanks or the balance scales on my post.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Tango is offline   Reply With Quote
Old 01-16-2013, 11:13 AM   #4
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,067 Times in 2,014 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Run-time Error (Can't nail it down)

Is it not Call instead of Run?
__________________
Regards,
Paul Eugin

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------



pr2-eugin is offline   Reply With Quote
Old 01-16-2013, 11:16 AM   #5
Tango
DB/Application Dev Newbie
 
Join Date: Jun 2011
Location: Sumter, South Carolina
Posts: 141
Thanks: 27
Thanked 1 Time in 1 Post
Tango is on a distinguished road
Re: Run-time Error (Can't nail it down)

Just tried Call and it compiles. now to see if it works.
__________________
---------------
If I helped you, please click thanks or the balance scales on my post.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Tango is offline   Reply With Quote
Old 01-16-2013, 11:16 AM   #6
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,377
Thanks: 13
Thanked 4,121 Times in 4,053 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Run-time Error (Can't nail it down)

Call is optional, I typically don't use it. I would just have:

linkDB
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 01-16-2013, 11:18 AM   #7
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,377
Thanks: 13
Thanked 4,121 Times in 4,053 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Run-time Error (Can't nail it down)

To clarify regarding Call, from VBA help:

You are not required to use the Call keyword when calling a procedure. However, if you use the Call keyword to call a procedure that requires arguments, argumentlist must be enclosed in parentheses. If you omit the Call keyword, you also must omit the parentheses around argumentlist.

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 01-16-2013, 11:18 AM   #8
Tango
DB/Application Dev Newbie
 
Join Date: Jun 2011
Location: Sumter, South Carolina
Posts: 141
Thanks: 27
Thanked 1 Time in 1 Post
Tango is on a distinguished road
Re: Run-time Error (Can't nail it down)

Quote:
Originally Posted by pbaldy View Post
Call is optional, I typically don't use it. I would just have:

linkDB

but when I did that it gave me a syntax error "expected=" and wouldn't compile. I think it was looking at it like I was setting a variables value.
__________________
---------------
If I helped you, please click thanks or the balance scales on my post.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Tango is offline   Reply With Quote
Old 01-17-2013, 03:17 AM   #9
pr2-eugin
Super Moderator
 
pr2-eugin's Avatar
 
Join Date: Nov 2011
Location: Bournemouth, UK
Posts: 8,498
Thanks: 68
Thanked 2,067 Times in 2,014 Posts
pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about pr2-eugin has a spectacular aura about
Re: Run-time Error (Can't nail it down)

Quote:
Originally Posted by pbaldy View Post
To clarify regarding Call, from VBA help:

You are not required to use the Call keyword when calling a procedure. However, if you use the Call keyword to call a procedure that requires arguments, argumentlist must be enclosed in parentheses. If you omit the Call keyword, you also must omit the parentheses around argumentlist.
Paul, please forgive me if I am wrong in this issue.. I agree with you, but that is the case for Procedures (Sub), however the user has declared the LinkDB as a Function, which might expect a return value, though there is no LOC that does that (i.e) something like
Code:
LinkDB = Null
My general assumption in this case was, Functions has to be Called for unlike Sub; just as the Help states..

Since the OP is not returning any values, if he/she changes it to a Sub just using LinkDB will suffice (I think )
__________________
Regards,
Paul Eugin

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
!
!
Windows 7 Professional, MS Access 2010
---------------------------------------------------------------------------------------------------------------------
If the above post has helped you, please click the scales
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
or click the 'Thumbs up'. Cheers.

---------------------------------------------------------------------------------------------------------------------



pr2-eugin is offline   Reply With Quote
Old 01-17-2013, 04:32 AM   #10
Tango
DB/Application Dev Newbie
 
Join Date: Jun 2011
Location: Sumter, South Carolina
Posts: 141
Thanks: 27
Thanked 1 Time in 1 Post
Tango is on a distinguished road
Re: Run-time Error (Can't nail it down)

That matches what I am seeing from the compiler. Makes sense too.
__________________
---------------
If I helped you, please click thanks or the balance scales on my post.
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Tango is offline   Reply With Quote
Old 01-17-2013, 09:53 AM   #11
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,377
Thanks: 13
Thanked 4,121 Times in 4,053 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Run-time Error (Can't nail it down)

Quote:
Originally Posted by pr2-eugin View Post
My general assumption in this case was, Functions has to be Called for unlike Sub; just as the Help states..

Since the OP is not returning any values, if he/she changes it to a Sub just using LinkDB will suffice (I think )
All I can say is that hasn't been my experience. Here's the first line of a function in a production db I happen to have open:

Private Function AddCharge()

and in other places on the form I fire it off with simply:

AddCharge

And another one in a standard module:

Public Function AgingDates(AsOfDate As Date, WhichOne As Integer) As Date

used to build SQL:

& AgingDates(Me.txtFromDate, 1) & "', '" _
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 01-17-2013, 06:54 PM   #12
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,710
Thanks: 105
Thanked 1,511 Times in 1,424 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Run-time Error (Can't nail it down)

Quote:
Originally Posted by pbaldy View Post
All I can say is that hasn't been my experience. Here's the first line of a function in a production db I happen to have open:

Private Function AddCharge()

and in other places on the form I fire it off with simply:

AddCharge
In this case the function has no arguments. Is is automatically being run as a Sub. If it had a single argument you would see the line rearrange itself with a space between the name and the parameter as it is with a Sub.

Give it two arguments and it will insist on being run as a Function and hence the syntax would require the parameters be enclosed in parentheses.

Note that it you put the single parameter in parentheses, these parentheses are not actually surrounding the parameters as such but indicating the parameter is to be passed ByVal regardless of the argument declaration in the function. The function will still run as a Sub. You can see this because, unlike a function there will be a space between the function name and the opening parenthesis.

When it is forced to run as a function it must either be part of an equation or be Called.

The Call keyword actually forces a function to run as a Sub.

By coincidence I posted some on this at UtterAccess yesterday.
http://www.utteraccess.com/forum/Cal...st=20&start=20

Quote:
And another one in a standard module:

Public Function AgingDates(AsOfDate As Date, WhichOne As Integer) As Date

used to build SQL:

& AgingDates(Me.txtFromDate, 1) & "', '" _
The function is being used in an equation so that is just fine.

Galaxiom is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Error "Run-time error '1004': General ODBC Error D4WNO General 1 07-19-2012 07:42 AM
Run Time Error 9 , trying to save Excel file, on 2nd time through loop heron_rider Modules & VBA 2 05-31-2011 10:20 AM
A question about the error message "Run-time error '3061' Too few parameters, Expecte dariyoosh Modules & VBA 7 03-12-2010 06:26 AM
Run time error 3075 - syntax error (missing operator) in query expression puskardas Modules & VBA 8 06-30-2008 07:35 PM




All times are GMT -8. The time now is 08:19 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World