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

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 756
  • Last Modified:

Expected Procedure, Not Variable

I have a module called modSave_Record  (code below)  I'm not sure I'm following this code.  I'm not sure what's going on when it gets to the ContactIDEBP.  Anyway, I get a complie error 'Expected Procedure, Not Variable" when it hits the "codID" line.  Could someone help me with this please?  Could you also help me understand what's going on with the ContactIDEBP?  Thanks

Public Function clickGo()
Select Case Len(gvarContactID)
    Case 0
        gvarContactID = cobID
    Case Else
End Select
    gstrLinkCriteria = "[ContactID]=" & gvarContactID
Select Case ContactIDEBP
    Case ""
        ContactIDEBP = gvarContactID
    Case Else
        ContactIDEBP = gvarContactID
End Select

    DoCmd.OpenForm "frmMain", , , gstrLinkCriteria  
    Dim tolocation As Byte
Dim objCnct As New ADODB.Connection
Dim objCnct1 As New ADODB.Connection
Dim objCnct2 As New ADODB.Connection
Dim objCmd As New ADODB.Command
Dim objRset As New ADODB.Recordset
Dim objRset2 As New ADODB.Recordset
Dim myApp As Variant
Dim strSQL As String
Const AccConn = "Provider=SQLOLEDB;Data Source=DAS2003;Initial Catalog= ClientTrack6_ContactSQL;Integrated Security=SSPI"

cobID    ***ERROR HERE***
    strSQL = "select * from dbo_Contact WHERE (ContactID ='" & ContactIDEBP & "')"
    objCnct.CursorLocation = adUseClient
    objCnct.Mode = adModeReadWrite
    objCnct.Open AccConn
    objRset.Open strSQL, objCnct, adOpenStatic, adLockPessimistic
    'MsgBox objRset.RecordCount
    Set Forms!frmMain.Recordset = objRset
    'DoCmd.Close acForm, gstrGoForm, acSaveNo
    If (Forms![frmMain].Visible = False) Then
        Forms![frmMain].Visible = True
    End If
    DoCmd.Close acForm, "_frmSearch", acSaveNo
End Function
3 Solutions
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
What exactly is a cobID, and where is the 'Dim cobID as Something' line?

Rory ArchibaldCommented:
That line is erroring because you just have cobID as a statement. If cobID is a variable that line doesn't mean anything because you are not doing anything with the variable.
Leigh PurvisDatabase DeveloperCommented:
Your compile error has been pretty clearly pointed out ;-)

You also say you're not sure what's going on when you get to ContactIDEBP?
Well wht's the intention?
It also isn't declared - is it another global variable?  Something from a form?

On a side note (not sure if you've gotten this far yet...)
Are you using linked tables too in this application?
Just your SQL string "select * from dbo_Contact ..." looks like the owner_underscore_tablename of a wizard linked table.
"select * from dbo.Contact ..."
or even just
"select * from Contact ..."
work better on the server?  Or have you actually named your tables in SQL Server with a dbo underscore? :-S
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

SCS1STAuthor Commented:
Global cobID As Variant
Everywhere that I search it's the CoborrowerID.  ie cobID = Me.CoborrowerID
In this module all the Global and Public variables are set.  It looks to me like this module is used to Globally open forms, save records, send mail, update records, etc.  Looks like this function "Public Function clickGo()" is used to open forms linking the underlying record of whatever form you're on with the new form being opened.  The particular form that I'm presently working with, "the New Contact form" only has a ContactID field on it.  That's where the ContactIDEBP is confusing me.
SCS1STAuthor Commented:
No I didn't name these tables.  Yes they are actually named the tables in SQL Server with a dbo underscore.  I don't know why.  This is obviously a hand me down program from someone else that I not get to inherit.  I would LOVE to go in and clean the whole thing up to be more....standard, however, when I get a program like this that I'm not very familiar with, I'm hestitant until I'm sure that whatever changes I make are not going to effect anything else.  I'm fairly new to this and don't want to mess up what I can't fix right away.  Is there a fast way to go into each table and see if a ContactIDEBP actually exists?  Or a fast way to search variables set on forms for the same thing, without opening every form/table to check it?  
As far as the linked tables question, yes.  This program was originally a mdb with 6 different databases linked to the one with the program.  We are in the process of converting it to SQL Server with an ADP front end.  Does that help?
Leigh PurvisDatabase DeveloperCommented:
You're using an ADP front end?
I don't see a lot of point in opening a connection object explicitly then - just use the CurrentProject.Connection

As mentioned earlier - you'll always get an error without doing anything with a variable (it's just sat there).
But an odd error message you've received for it (do you have a module named "cobID" anywhere? :-S)

You can certainly do project wide searches in your code.  (Edit> Find)
SCS1STAuthor Commented:
No - no module named "cobID" anywhere.  Just the Global Setting as variant and it's used to set the CoborrowersID throughout the program.  
DUH!!!  I'm so dense sometimes.  Project Wide Search -- I KNEW THAT!!! Thanks
Opening a connection object explicitly vs using CurrentProject.Connection - THANK YOU FOR THAT.  This is done in every program I've seen here so far.  I was thinking that there had to be a better way.  I wasn't familiar with CurrentProject.Connection; and obviously neither were my peers.  A little research on that should clear up a lot for me.  Thanks.
Leigh PurvisDatabase DeveloperCommented:
No problem.

Remember to use
Set YourObject.Connection = CurrentProject.Connection

(as opposed to
YourObject.Connection = CurrentProject.Connection
which is less efficient)

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

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