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
    objCnct.Close
    'DoCmd.Close acForm, gstrGoForm, acSaveNo
    If (Forms![frmMain].Visible = False) Then
        Forms![frmMain].Visible = True
    End If
    DoCmd.Close acForm, "_frmSearch", acSaveNo
End Function
SCS1STAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
What exactly is a cobID, and where is the 'Dim cobID as Something' line?

0
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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
Would
"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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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.
0
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?
0
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)
0
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.
0
Leigh PurvisDatabase DeveloperCommented:
No problem.

Remember to use
Set YourObject.Connection = CurrentProject.Connection

(as opposed to
YourObject.Connection = CurrentProject.Connection
which is less efficient)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.