?
Solved

break in vba isn't actually stopping execution

Posted on 2006-03-21
14
Medium Priority
?
294 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
0
Comment
Question by:SCS1ST
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 16246560
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 ]
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16246574
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)          
0
 

Author Comment

by:SCS1ST
ID: 16247407
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.  
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 16247419
Did you consider Nic's suggestion of incrementing the "Bid" column?
0
 

Author Comment

by:SCS1ST
ID: 16247472
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?
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 16247539
"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?

0
 

Author Comment

by:SCS1ST
ID: 16247743
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?
0
 
LVL 54

Expert Comment

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

Nic;o)
0
 

Author Comment

by:SCS1ST
ID: 16253192
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.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16260632
No problem for me :-)

Nic;o)
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 16263546
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.
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 16276485
No objections at all.

Thanks for your time, AnnieMod.
0
 

Accepted Solution

by:
GranMod earned 0 total points
ID: 16305178
Closed, 500 points refunded.
GranMod
The Experts Exchange
Community Support Moderator of all Ages
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

807 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