423,873 Members | 2,170 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,873 IT Pros & Developers. It's quick & easy.

Error 40036 VBA Ms Access 2003

LonelyBunny
P: 3
hi i'm new here. im developping on ms access 2003 for a planning db for a company's staff.

I have a contact adress book to create for each client.I need to copy a value from the ClientsAdmin form to the frmCreateContact form with the code:

DoCmd.OpenForm "frmCreateContact", acNormal
'Forms("frmCreateContact").SetFocus
Forms!frmCreateContact.Controls!txtClientCode = Forms!ClientsAdmin.Controls!ClientCode

Here I have tried use explicit reference coz im having error 40036 (Method Item of object Form failed).
Trying to set focus etc does not help...

Thanks for suggesting me the possible solution to this!
Jun 15 '07 #1
Share this Question
Share on Google+
6 Replies


BradHodge
Expert 100+
P: 166
Sounds like a corruption in the database. You should try to rebuild the form and see if that helps.

Brad.
Jun 15 '07 #2

FishVal
Expert 2.5K+
P: 2,653
hi i'm new here. im developping on ms access 2003 for a planning db for a company's staff.

I have a contact adress book to create for each client.I need to copy a value from the ClientsAdmin form to the frmCreateContact form with the code:

DoCmd.OpenForm "frmCreateContact", acNormal
'Forms("frmCreateContact").SetFocus
Forms!frmCreateContact.Controls!txtClientCode = Forms!ClientsAdmin.Controls!ClientCode

Here I have tried use explicit reference coz im having error 40036 (Method Item of object Form failed).
Trying to set focus etc does not help...

Thanks for suggesting me the possible solution to this!
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frmCreateContact", acNormal
  2. 'Forms("frmCreateContact").SetFocus
  3. Forms!frmCreateContact.Controls!txtClientCode = Forms!ClientsAdmin.Controls!ClientCode
  4.  
Hmm

This should work.
I suggest you to set breakpoint in line#3 and check in VBA Immediate window what actually fails "Forms!frmCreateContact.Controls!txtClientCode " or "Forms!ClientsAdmin.Controls!ClientCode".

Good luck.
Jun 15 '07 #3

ADezii
Expert 5K+
P: 8,591
hi i'm new here. im developping on ms access 2003 for a planning db for a company's staff.

I have a contact adress book to create for each client.I need to copy a value from the ClientsAdmin form to the frmCreateContact form with the code:

DoCmd.OpenForm "frmCreateContact", acNormal
'Forms("frmCreateContact").SetFocus
Forms!frmCreateContact.Controls!txtClientCode = Forms!ClientsAdmin.Controls!ClientCode

Here I have tried use explicit reference coz im having error 40036 (Method Item of object Form failed).
Trying to set focus etc does not help...

Thanks for suggesting me the possible solution to this!
Do you have any code in the Open, Load, or Current Event of frmCreateContact? If you do, please post it.
Jun 15 '07 #4

LonelyBunny
P: 3
Do you have any code in the Open, Load, or Current Event of frmCreateContact? If you do, please post it.
Yes on Form Load I have:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. ' set up the connection and recordset.
  3. Set conn = CurrentProject.Connection
  4.  
  5. 'set client code
  6. 'MsgBox Me.ClientCode
  7. 'Me.txtClientCode = "gfkgjdflg"
  8. 'empty all other controls
  9. Me.Title = ""
  10. Me.FName = ""
  11. Me.LName = ""
  12. Me.Position = ""
  13. Me.txtSpecify = ""
  14. Me.Phone = ""
  15. Me.GSM = ""
  16. Me.Email1 = ""
  17. Me.Email2 = ""
  18. Me.Remarks = ""
  19. 'empty listbox
  20. Set MyRS = New ADODB.Recordset
  21. MyRS.Open "qryContactListbox", conn, adOpenDynamic, adLockOptimistic
  22. Do While Not MyRS.EOF
  23.     MyRS.Delete
  24. Loop
  25. MyRS.Close
  26. Set MyRS = Nothing
  27. '-------------------------------
  28. 'populate listbox
  29. sSQL = "SELECT * FROM tblClientContacts WHERE " & _
  30. "tblClientContacts.ClientCode = " & Me.ClientCode
  31.  
  32. Set MyRS = New ADODB.Recordset
  33. MyRS.Open sSQL, conn, adOpenDynamic, adLockOptimistic
  34. Set MyRS2 = New ADODB.Recordset
  35. MyRS2.Open "qryContactListbox", conn, adOpenDynamic, adLockOptimistic
  36. 'transfer searched details to qryContactList
  37. Do While Not MyRS.EOF
  38.     With MyRS2
  39.         .AddNew
  40.         .Fields(0).Value = MyRS.Fields(0).Value 'ContactID
  41.         .Fields(1).Value = MyRS.Fields(1).Value 'ClientCode
  42.         .Fields(2).Value = MyRS.Fields(2).Value 'Title
  43.         .Fields(3).Value = MyRS.Fields(3).Value 'FName
  44.         .Fields(4).Value = MyRS.Fields(4).Value 'LName
  45.         .Fields(5).Value = MyRS.Fields(5).Value 'Position
  46.         .Fields(6).Value = MyRS.Fields(6).Value 'Phone
  47.         .Fields(7).Value = MyRS.Fields(7).Value 'GSM
  48.         .Fields(8).Value = MyRS.Fields(8).Value 'Email1
  49.         .Fields(9).Value = MyRS.Fields(9).Value 'Email2
  50.         .Fields(10).Value = MyRS.Fields(10).Value 'Remarks
  51.         .Update
  52.     End With
  53. Loop
  54.  
  55. Me.lstContacts.Requery
  56. Me.Title.SetFocus
  57.  
  58. MyRS.Close
  59. Set MyRS = Nothing
  60. MyRS2.Close
  61. Set MyRS2 = Nothing
  62. conn.Close
  63. Set conn = Nothing
  64. End Sub
Thanks
Jun 18 '07 #5

FishVal
Expert 2.5K+
P: 2,653
Yes on Form Load I have:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. ' set up the connection and recordset.
  3. Set conn = CurrentProject.Connection
  4.  
  5. 'set client code
  6. 'MsgBox Me.ClientCode
  7. 'Me.txtClientCode = "gfkgjdflg"
  8. 'empty all other controls
  9. Me.Title = ""
  10. Me.FName = ""
  11. Me.LName = ""
  12. Me.Position = ""
  13. Me.txtSpecify = ""
  14. Me.Phone = ""
  15. Me.GSM = ""
  16. Me.Email1 = ""
  17. Me.Email2 = ""
  18. Me.Remarks = ""
  19. 'empty listbox
  20. Set MyRS = New ADODB.Recordset
  21. MyRS.Open "qryContactListbox", conn, adOpenDynamic, adLockOptimistic
  22. Do While Not MyRS.EOF
  23. MyRS.Delete
  24. Loop
  25. MyRS.Close
  26. Set MyRS = Nothing
  27. '-------------------------------
  28. 'populate listbox
  29. sSQL = "SELECT * FROM tblClientContacts WHERE " & _
  30. "tblClientContacts.ClientCode = " & Me.ClientCode
  31.  
  32. Set MyRS = New ADODB.Recordset
  33. MyRS.Open sSQL, conn, adOpenDynamic, adLockOptimistic
  34. Set MyRS2 = New ADODB.Recordset
  35. MyRS2.Open "qryContactListbox", conn, adOpenDynamic, adLockOptimistic
  36. 'transfer searched details to qryContactList
  37. Do While Not MyRS.EOF
  38. With MyRS2
  39. .AddNew
  40. .Fields(0).Value = MyRS.Fields(0).Value 'ContactID
  41. .Fields(1).Value = MyRS.Fields(1).Value 'ClientCode
  42. .Fields(2).Value = MyRS.Fields(2).Value 'Title
  43. .Fields(3).Value = MyRS.Fields(3).Value 'FName
  44. .Fields(4).Value = MyRS.Fields(4).Value 'LName
  45. .Fields(5).Value = MyRS.Fields(5).Value 'Position
  46. .Fields(6).Value = MyRS.Fields(6).Value 'Phone
  47. .Fields(7).Value = MyRS.Fields(7).Value 'GSM
  48. .Fields(8).Value = MyRS.Fields(8).Value 'Email1
  49. .Fields(9).Value = MyRS.Fields(9).Value 'Email2
  50. .Fields(10).Value = MyRS.Fields(10).Value 'Remarks
  51. .Update
  52. End With
  53. Loop
  54.  
  55. Me.lstContacts.Requery
  56. Me.Title.SetFocus
  57.  
  58. MyRS.Close
  59. Set MyRS = Nothing
  60. MyRS2.Close
  61. Set MyRS2 = Nothing
  62. conn.Close
  63. Set conn = Nothing
  64. End Sub
Thanks
Hi!

Have you tried to trace the code in step-by-step mode?
If so where does it fail? If not you should definitely do this to help yourself and others to detect a reason of failing. The code behaiviour is too context dependant to detect where does it fail.

I guess that code execution will never pass line #53 bcz you have an infinite loop (lines #37-#53).

Expand|Select|Wrap|Line Numbers
  1. Do While Not MyRS.EOF
  2. With MyRS2
  3. .AddNew
  4. .Fields(0).Value = MyRS.Fields(0).Value 'ContactID
  5. .Fields(1).Value = MyRS.Fields(1).Value 'ClientCode
  6. .Fields(2).Value = MyRS.Fields(2).Value 'Title
  7. .Fields(3).Value = MyRS.Fields(3).Value 'FName
  8. .Fields(4).Value = MyRS.Fields(4).Value 'LName
  9. .Fields(5).Value = MyRS.Fields(5).Value 'Position
  10. .Fields(6).Value = MyRS.Fields(6).Value 'Phone
  11. .Fields(7).Value = MyRS.Fields(7).Value 'GSM
  12. .Fields(8).Value = MyRS.Fields(8).Value 'Email1
  13. .Fields(9).Value = MyRS.Fields(9).Value 'Email2
  14. .Fields(10).Value = MyRS.Fields(10).Value 'Remarks
  15. .Update
  16. End With
  17. Loop
  18.  
So we have two options.
1. Infinite loop causes fail
2. Code fails before.

I hope this makes sence.
Jun 18 '07 #6

ADezii
Expert 5K+
P: 8,591
Yes on Form Load I have:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2. ' set up the connection and recordset.
  3. Set conn = CurrentProject.Connection
  4.  
  5. 'set client code
  6. 'MsgBox Me.ClientCode
  7. 'Me.txtClientCode = "gfkgjdflg"
  8. 'empty all other controls
  9. Me.Title = ""
  10. Me.FName = ""
  11. Me.LName = ""
  12. Me.Position = ""
  13. Me.txtSpecify = ""
  14. Me.Phone = ""
  15. Me.GSM = ""
  16. Me.Email1 = ""
  17. Me.Email2 = ""
  18. Me.Remarks = ""
  19. 'empty listbox
  20. Set MyRS = New ADODB.Recordset
  21. MyRS.Open "qryContactListbox", conn, adOpenDynamic, adLockOptimistic
  22. Do While Not MyRS.EOF
  23.     MyRS.Delete
  24. Loop
  25. MyRS.Close
  26. Set MyRS = Nothing
  27. '-------------------------------
  28. 'populate listbox
  29. sSQL = "SELECT * FROM tblClientContacts WHERE " & _
  30. "tblClientContacts.ClientCode = " & Me.ClientCode
  31.  
  32. Set MyRS = New ADODB.Recordset
  33. MyRS.Open sSQL, conn, adOpenDynamic, adLockOptimistic
  34. Set MyRS2 = New ADODB.Recordset
  35. MyRS2.Open "qryContactListbox", conn, adOpenDynamic, adLockOptimistic
  36. 'transfer searched details to qryContactList
  37. Do While Not MyRS.EOF
  38.     With MyRS2
  39.         .AddNew
  40.         .Fields(0).Value = MyRS.Fields(0).Value 'ContactID
  41.         .Fields(1).Value = MyRS.Fields(1).Value 'ClientCode
  42.         .Fields(2).Value = MyRS.Fields(2).Value 'Title
  43.         .Fields(3).Value = MyRS.Fields(3).Value 'FName
  44.         .Fields(4).Value = MyRS.Fields(4).Value 'LName
  45.         .Fields(5).Value = MyRS.Fields(5).Value 'Position
  46.         .Fields(6).Value = MyRS.Fields(6).Value 'Phone
  47.         .Fields(7).Value = MyRS.Fields(7).Value 'GSM
  48.         .Fields(8).Value = MyRS.Fields(8).Value 'Email1
  49.         .Fields(9).Value = MyRS.Fields(9).Value 'Email2
  50.         .Fields(10).Value = MyRS.Fields(10).Value 'Remarks
  51.         .Update
  52.     End With
  53. Loop
  54.  
  55. Me.lstContacts.Requery
  56. Me.Title.SetFocus
  57.  
  58. MyRS.Close
  59. Set MyRS = Nothing
  60. MyRS2.Close
  61. Set MyRS2 = Nothing
  62. conn.Close
  63. Set conn = Nothing
  64. End Sub
Thanks
Did you forget to declare the Object Variable conn as in:
Expand|Select|Wrap|Line Numbers
  1. Dim conn As ADODB.Connection
  2. Set conn = CurrentProject.Connection
Jun 19 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.