We help IT Professionals succeed at work.

break in vba isn't actually stopping execution

SCS1ST
SCS1ST asked
on
Medium Priority
314 Views
Last Modified: 2006-11-18
I'm having a problem trying to follow my code.  I've already checked that my "Break at all errors" is checked and it is.  In the code below I can't get the program to execute past the marked area.  I have put a break and a STOP before it and it will bring up my code window but I can see that something is still running.  I have to wait a while before it actually comes back to where the break was set and then it's too late.  I want to see where the execution is taking it after the bolded area and why it won't execute the rest of this code.  
Basically what's suppose to happen is if the tempbid# isn't in the recordset it's suppose to add a new record blanking out all fields with the exception of those that I'm trying to populate after it creates the new record.  What's happening is I get a blank record, nothing gets populated and the record isn't updateable.  


    Dim sMsg, sBid As String
    Dim FileLen As Long
    Dim i As Integer
    Dim m As String
'
    If IsNull(Me![TempBid#]) Then
        DoCmd.CancelEvent
        Me![TempBid#].SetFocus
        GoTo EndSub

    Else
        sBid = Me![TempBid#]

        If Len(sBid) < 8 Then GoTo OpenList
        If Me![Branch] <> Left$(sBid, 2) Then GoTo OpenList
        '  CHECK IF BID IS NOT ON THE LIST
        sMsg = "Bid: '" + sBid + "' is not currently on the List, do you want to add it ?"
        ' Return reference to current database.
        Const AccConn = "Provider=SQLOLEDB;Data Source=DAS2003;Initial Catalog= ahbeck2000;Integrated Security=SSPI"
        strSQL = "SELECT * FROM BidListT ORDER BY Bid"
        objCnct4.CursorLocation = adUseClient
        objCnct4.Mode = adModeReadWrite
        objCnct4.Open AccConn
        objRset4.Open strSQL, objCnct4, adOpenStatic, adLockPessimistic
        ' Construct SQL string.
        objRset4.MoveLast
        FileLen = objRset4.RecordCount
        objRset4.MoveFirst
        For i = 0 To FileLen - 1
            If objRset4!Bid = sBid Then
                GoTo Is_Not_New
            Else
                objRset4.MoveNext
                GoTo Next_Loop
            End If
Next_Loop:
            Next
'
''       BID IS NOT ON THE LIST, ASK IF USER WANTS TO ADD IT
'
        If MsgBox(sMsg, vbYesNo, "Add new Bid Estimate") = vbYes Then
       
'            DoCmd.Hourglass True
'       Then add a new Bid record
            objRset4.AddNew
            objRset4!Bid = Me![TempBid#]           'CODE GETS THIS FAR AND THEN GOES SOMEWHERE ELSE.  ANYTHING BELOW THIS LINE DOESN'T EVER EXECUTE.  
            objRset4!BidType = 1
            objRset4!OverrunPercent = 0.25
            objRset4!DeductPercent = 0.75
            objRset4!UnitSystem = 0
            objRset4!RigType = 1
            objRset4.Update
'
' '      Select Branch Code
            Select Case Me.Branch
                Case Is = "HO"
                    objRset4!CompCode = 4
                    objRset4!BranchCode = 2
                Case Is = "SA"
                    objRset4!CompCode = 1
                    objRset4!BranchCode = 1
                Case Is = "DL"
                    objRset4!CompCode = 1
                    objRset4!BranchCode = 1
                Case Is = "PR"
                    objRset4!CompCode = 2
                    objRset4!BranchCode = 15
                Case Is = "SC"
                    objRset4!CompCode = 1
                    objRset4!BranchCode = 10
                Case Is = "NC"
                    objRset4!CompCode = 1
                    objRset4!BranchCode = 9

            End Select
            objRset4.Update
            objRset4.Close
Comment
Watch Question

[ fanpages ]Consultant (Development Team Lead, Senior Support Engineer, and Technical Test Leader)
CERTIFIED EXPERT

Commented:
Check the data type & length of the "Bid" column in your recordset can store the value held in the "TempBid" field of 'Me'.

BFN,

fp.
[ http://www.justgiving.com/100RoundsOfSparring ]

Commented:
You´ll need a new ID, thus when it´s an autoID skip the field fill, else use the DMAX() like:

objRset4!Bid = DMax("[TempBid#]","your table name") + 1

Nic;o)          

Author

Commented:
fanpages,
Bid is a nvarchar, 10.  tempbid# pulls from the bid field in the same table.  To add a new record they just change the alpha charachter on the end.  
[ fanpages ]Consultant (Development Team Lead, Senior Support Engineer, and Technical Test Leader)
CERTIFIED EXPERT

Commented:
Did you consider Nic's suggestion of incrementing the "Bid" column?

Author

Commented:
nico,
There is already an id field that is an auto id.  I'm not sure I understand the rest of what you had to say.  I thought that I didn't need to add the id field to the code since it would generate automatically.  The bid# is not the auto id.  Does this make sense, or am I not understanding you at all?
[ fanpages ]Consultant (Development Team Lead, Senior Support Engineer, and Technical Test Leader)
CERTIFIED EXPERT

Commented:
"To add a new record they just change the alpha charachter on the end."

It it feasible that a record already exists with the same key?

Author

Commented:
fanpages,
I can't increment the bid column.  I didn't originally write the program.  I'm just converting it to ADP with SQL as a back end and the client wouldn't go for that.  
Yes, that's how they add a new estimate to this program.  And no, it's not feasible that the record already exists.  If it did, it would just bring up that record.  
Any other ideas?

Commented:
Are you sure that Me![TempBid#] holds an valid value for the BidListT table ?

Nic;o)

Author

Commented:
Yes, I'm sure.  I found another problem with this database today.  It was that the bit fields in the new sql db were allowing nulls and on the access side they were being handled as yes/no fields.  As soon as I corrected all code in Access from true false to 1/0 and fixed all bit fields in the db to not allow nulls, it worked.  This corrected several problems.  I didn't know that the differences in sql vs access mdb's would create such problems.  Thanks to everyone for your help anyway.  I still don't understand exactally how this fix worked, but it Did.  
So do I need to send this question to customer support to close it out?  I'm not quite sure how this site works just yet.  When I did that before, it seemed as if I was offending someone.  I don't want to do that.  I appreciate ALL HELP I get from this site.  Don't know where I'd be without it.

Commented:
No problem for me :-)

Nic;o)
[ fanpages ]Consultant (Development Team Lead, Senior Support Engineer, and Technical Test Leader)
CERTIFIED EXPERT

Commented:
Hi,

Place a (free point) question in the "Community Support" Topic Area [ http://www.experts-exchange.com/Community_Support/ ], quoting this question (Q_<number>) reference, and state that you solved the problem yourself.

Both Nic & I are in agreement that your points should be refunded & the question be deleted.

BFN,

fp.
[ fanpages ]Consultant (Development Team Lead, Senior Support Engineer, and Technical Test Leader)
CERTIFIED EXPERT

Commented:
No objections at all.

Thanks for your time, AnnieMod.
Commented:
Closed, 500 points refunded.
GranMod
The Experts Exchange
Community Support Moderator of all Ages

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.