Expected Procedure, Not Variable

Posted on 2006-04-21
Last Modified: 2008-03-17
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
Question by:SCS1ST
    LVL 65

    Expert Comment

    by:Jim Horn
    What exactly is a cobID, and where is the 'Dim cobID as Something' line?

    LVL 85

    Accepted Solution

    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.
    LVL 44

    Expert Comment

    by:Leigh Purvis
    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

    Author Comment

    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.

    Author Comment

    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?
    LVL 44

    Assisted Solution

    by:Leigh Purvis
    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)

    Author Comment

    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.
    LVL 44

    Assisted Solution

    by:Leigh Purvis
    No problem.

    Remember to use
    Set YourObject.Connection = CurrentProject.Connection

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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now