VBA code bug, VERY strange, (works when step through)

A

Access_guy49

Registered User.
Local time
Today, 00:49
Joined
Sep 7, 2007
Messages
462
OK so I have a very strange bug.
I am using Access 2003. on an XP machine with SP3 installed.

I have a split database, the front end has a form for inputing new records.
The user of the form has asked for a button to duplicate some of the information on the form for people where he is creating more than one file.

E.g - our planner fills in a record for John Smith. Then he clicks the duplicate button to create Another blank record for John Smith. Our planner wanted the button to bring over the basic information that would be the same for both records and only leave blank the fields that would be different.

I did this by storing the values of the desired fields in an array. Then using the GoToRecord,,AcNewRec command, i create a new record and then populate the fields with the values in the array. Not terribly complicated.

the problem is that every time it gets to the new record line of code, I get an error. "2105 - can't go to specified record"

YET...
If i place a stop, one line before the AcNewRec, and then step through the code, everything works perfect.
Also if i just use a standard wizard "next record" or "New Record" button. they both work just fine.
I can't understand how when you step through, it works, and when you just run it outright, it doesn't.


PLEASE PLEASE Help.

Code: 
    'This button duplicates the values in many fields for a new record.
    Dim MyValues(17) As String
    
    Me.Combo117.SetFocus
    MyValues(0) = Me.Combo117
    Me.Combo119.SetFocus
    MyValues(1) = Me.Combo119
    Me.Combo121.SetFocus
    MyValues(2) = Me.Combo121

    MyValues(3) = Me.LotPlan
    MyValues(4) = Me.PlanNum
    MyValues(5) = Me.F_Name
    MyValues(6) = Me.L_Name
    MyValues(7) = Me.HomePhone
    MyValues(8) = Me.Postal
    MyValues(9) = Me.Business_Phone
    MyValues(10) = Me.Business
    MyValues(11) = Me.Munic
    MyValues(12) = Me.Lot
    MyValues(13) = Me.Conc
    MyValues(14) = Me.Street_Num
    MyValues(15) = Me.Street
    MyValues(16) = Me.Easting
    MyValues(17) = Me.Northing
    
'Tried saving record first, didn't solve problem.
'I useually uncomment this line and place my stop  hear in order to step through the code to have it work.
    'DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

'Also tried using a simple wait function just to see if that helped. didn't.    
'    WaitTime (3)
'    DoEvents
    
    
    DoCmd.GoToRecord , , acNewRec
'Tried AcNext and got the same result.
    'DoCmd.GoToRecord , , acNext
    

'This part works fine, but i can't only get to it by stepping through the above lines.
    Me.Combo117 = MyValues(0)
    Me.Combo119 = MyValues(1)
    Me.Combo121 = MyValues(2)
    Me.LotPlan = MyValues(3)
    Me.PlanNum = MyValues(4)
    Me.F_Name = MyValues(5)
    Me.L_Name = MyValues(6)
    Me.HomePhone = MyValues(7)
    Me.Postal = MyValues(8)
    Me.Business_Phone = MyValues(9)
    Me.Business = MyValues(10)
    Me.Munic = MyValues(11)
    Me.Lot = MyValues(12)
    Me.Conc = MyValues(13)
    Me.Street_Num = MyValues(14)
    Me.Street = MyValues(15)
    Me.Easting = MyValues(16)
    Me.Northing = MyValues(17)
 
Access_guy49 said:
OK so I have a very strange bug.
I am using Access 2003. on an XP machine with SP3 installed.

I have a split database, the front end has a form for inputing new records.
The user of the form has asked for a button to duplicate some of the information on the form for people where he is creating more than one file.

E.g - our planner fills in a record for John Smith. Then he clicks the duplicate button to create Another blank record for John Smith. Our planner wanted the button to bring over the basic information that would be the same for both records and only leave blank the fields that would be different.

I did this by storing the values of the desired fields in an array. Then using the GoToRecord,,AcNewRec command, i create a new record and then populate the fields with the values in the array. Not terribly complicated.

the problem is that every time it gets to the new record line of code, I get an error. "2105 - can't go to specified record"

YET...
If i place a stop, one line before the AcNewRec, and then step through the code, everything works perfect.
Also if i just use a standard wizard "next record" or "New Record" button. they both work just fine.
I can't understand how when you step through, it works, and when you just run it outright, it doesn't.


PLEASE PLEASE Help.

Code: 
    'This button duplicates the values in many fields for a new record.
    Dim MyValues(17) As String
    
    Me.Combo117.SetFocus
    MyValues(0) = Me.Combo117
    Me.Combo119.SetFocus
    MyValues(1) = Me.Combo119
    Me.Combo121.SetFocus
    MyValues(2) = Me.Combo121

    MyValues(3) = Me.LotPlan
    MyValues(4) = Me.PlanNum
    MyValues(5) = Me.F_Name
    MyValues(6) = Me.L_Name
    MyValues(7) = Me.HomePhone
    MyValues(8) = Me.Postal
    MyValues(9) = Me.Business_Phone
    MyValues(10) = Me.Business
    MyValues(11) = Me.Munic
    MyValues(12) = Me.Lot
    MyValues(13) = Me.Conc
    MyValues(14) = Me.Street_Num
    MyValues(15) = Me.Street
    MyValues(16) = Me.Easting
    MyValues(17) = Me.Northing
    
'Tried saving record first, didn't solve problem.
'I useually uncomment this line and place my stop  hear in order to step through the code to have it work.
    'DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

'Also tried using a simple wait function just to see if that helped. didn't.    
'    WaitTime (3)
'    DoEvents
    
    
    DoCmd.GoToRecord , , acNewRec
'Tried AcNext and got the same result.
    'DoCmd.GoToRecord , , acNext
    

'This part works fine, but i can't only get to it by stepping through the above lines.
    Me.Combo117 = MyValues(0)
    Me.Combo119 = MyValues(1)
    Me.Combo121 = MyValues(2)
    Me.LotPlan = MyValues(3)
    Me.PlanNum = MyValues(4)
    Me.F_Name = MyValues(5)
    Me.L_Name = MyValues(6)
    Me.HomePhone = MyValues(7)
    Me.Postal = MyValues(8)
    Me.Business_Phone = MyValues(9)
    Me.Business = MyValues(10)
    Me.Munic = MyValues(11)
    Me.Lot = MyValues(12)
    Me.Conc = MyValues(13)
    Me.Street_Num = MyValues(14)
    Me.Street = MyValues(15)
    Me.Easting = MyValues(16)
    Me.Northing = MyValues(17)
Click to expand...

See this post, especially the explanation by Roy Vidar dated 1 Apr 04 15:39, and see if it helps.
http://www.tek-tips.com/viewthread.cfm?qid=813047
 
Learn to understand the reasons and uses for DoEvents.

I can't imagine why you need to duplicate a record. It seems to break rules of normalization and could be why you're having this problem.
 
I tried the do events method simply because the code worked properly when it was being steped through. this lead me to believe that perhaps another code thread was holding onto something on the record at runtime causing the error.

Also, the new record is not an exact duplicate. Sometimes files have different information on them, but SOME of the information is the same. the button is for that case.

I did solve the problem, it had to do with code that was to execute on a lost focus event of one of my combo boxes. I removed them from my code and it worked perfectly.

Thanks to both responders for the assistance, it's much appreciated to have people take their own time and try to come up with solutions or advice. :)
 
georgedwilkinson said:
Learn to understand the reasons and uses for DoEvents.

I can't imagine why you need to duplicate a record. It seems to break rules of normalization and could be why you're having this problem.
Click to expand...

This does not break normalization rules as he isn't duplicating keys, only specific information. I used to do this a lot in a school, as there were multiple students with the same address and phone number and other information. When entering many students from the same house, it is useful to have a Duplicate button. I called it "Clone".

Here is the code I used in a command button in Access 97. I have no idea if it will work in A2003. This copies ALL fields but the key field. My key field is autoincremented.
Code: 
' This seems to work. It clones the record, then goes to the new record.
On Error GoTo Err_cmdClone4_Click
Dim tstr As String, procname as string

procname = "cmdClone"

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend

'DoCmd.GoToControl "txtDate" ' Go to first text control.

tstr = "You are viewing the new record now."

MsgBox tstr

Exit_cmdClone4_Click:
Exit Sub

Err_cmdClone4_Click:
Call DispError(procname)
Resume Exit_cmdClone4_Click
 
thanks bulrush, That looks good but I don't know if i would be any better using it because i don't even copy the entire record minus the key. I copy maybe 1/3 of the fields. With planning records, similar to your situation, some information stays the same, but there is ALOT of information that doesn't stay the same at all. I think making the user delete all of that data would be just as long.

I do appreciate the code though, I think i'll keep it in my back pocket for the future because it looks WAY more simple.
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom