Error 40036 VBA Ms Access 2003

[LonelyBunny]

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!

[BradHodge]

Sounds like a corruption in the database. You should try to rebuild the form and see if that helps.

Brad.

[FishVal]

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!

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

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.

[ADezii]

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.

[LonelyBunny]

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:

Private Sub Form_Load()
' set up the connection and recordset.
Set conn = CurrentProject.Connection
 
'set client code
'MsgBox Me.ClientCode
'Me.txtClientCode = "gfkgjdflg"
'empty all other controls
Me.Title = ""
Me.FName = ""
Me.LName = ""
Me.Position = ""
Me.txtSpecify = ""
Me.Phone = ""
Me.GSM = ""
Me.Email1 = ""
Me.Email2 = ""
Me.Remarks = ""
'empty listbox
Set MyRS = New ADODB.Recordset
MyRS.Open "qryContactListbox", conn, adOpenDynamic, adLockOptimistic
Do While Not MyRS.EOF
    MyRS.Delete
Loop
MyRS.Close
Set MyRS = Nothing
'-------------------------------
'populate listbox
sSQL = "SELECT * FROM tblClientContacts WHERE " & _
"tblClientContacts.ClientCode = " & Me.ClientCode
 
Set MyRS = New ADODB.Recordset
MyRS.Open sSQL, conn, adOpenDynamic, adLockOptimistic
Set MyRS2 = New ADODB.Recordset
MyRS2.Open "qryContactListbox", conn, adOpenDynamic, adLockOptimistic
'transfer searched details to qryContactList
Do While Not MyRS.EOF
    With MyRS2
        .AddNew
        .Fields(0).Value = MyRS.Fields(0).Value 'ContactID
        .Fields(1).Value = MyRS.Fields(1).Value 'ClientCode
        .Fields(2).Value = MyRS.Fields(2).Value 'Title
        .Fields(3).Value = MyRS.Fields(3).Value 'FName
        .Fields(4).Value = MyRS.Fields(4).Value 'LName
        .Fields(5).Value = MyRS.Fields(5).Value 'Position
        .Fields(6).Value = MyRS.Fields(6).Value 'Phone
        .Fields(7).Value = MyRS.Fields(7).Value 'GSM
        .Fields(8).Value = MyRS.Fields(8).Value 'Email1
        .Fields(9).Value = MyRS.Fields(9).Value 'Email2
        .Fields(10).Value = MyRS.Fields(10).Value 'Remarks
        .Update
    End With
Loop
 
Me.lstContacts.Requery
Me.Title.SetFocus
 
MyRS.Close
Set MyRS = Nothing
MyRS2.Close
Set MyRS2 = Nothing
conn.Close
Set conn = Nothing
End Sub

Thanks

[FishVal]

Yes on Form Load I have:

Private Sub Form_Load()
' set up the connection and recordset.
Set conn = CurrentProject.Connection
 
'set client code
'MsgBox Me.ClientCode
'Me.txtClientCode = "gfkgjdflg"
'empty all other controls
Me.Title = ""
Me.FName = ""
Me.LName = ""
Me.Position = ""
Me.txtSpecify = ""
Me.Phone = ""
Me.GSM = ""
Me.Email1 = ""
Me.Email2 = ""
Me.Remarks = ""
'empty listbox
Set MyRS = New ADODB.Recordset
MyRS.Open "qryContactListbox", conn, adOpenDynamic, adLockOptimistic
Do While Not MyRS.EOF
MyRS.Delete
Loop
MyRS.Close
Set MyRS = Nothing
'-------------------------------
'populate listbox
sSQL = "SELECT * FROM tblClientContacts WHERE " & _
"tblClientContacts.ClientCode = " & Me.ClientCode
 
Set MyRS = New ADODB.Recordset
MyRS.Open sSQL, conn, adOpenDynamic, adLockOptimistic
Set MyRS2 = New ADODB.Recordset
MyRS2.Open "qryContactListbox", conn, adOpenDynamic, adLockOptimistic
'transfer searched details to qryContactList
Do While Not MyRS.EOF
With MyRS2
.AddNew
.Fields(0).Value = MyRS.Fields(0).Value 'ContactID
.Fields(1).Value = MyRS.Fields(1).Value 'ClientCode
.Fields(2).Value = MyRS.Fields(2).Value 'Title
.Fields(3).Value = MyRS.Fields(3).Value 'FName
.Fields(4).Value = MyRS.Fields(4).Value 'LName
.Fields(5).Value = MyRS.Fields(5).Value 'Position
.Fields(6).Value = MyRS.Fields(6).Value 'Phone
.Fields(7).Value = MyRS.Fields(7).Value 'GSM
.Fields(8).Value = MyRS.Fields(8).Value 'Email1
.Fields(9).Value = MyRS.Fields(9).Value 'Email2
.Fields(10).Value = MyRS.Fields(10).Value 'Remarks
.Update
End With
Loop
 
Me.lstContacts.Requery
Me.Title.SetFocus
 
MyRS.Close
Set MyRS = Nothing
MyRS2.Close
Set MyRS2 = Nothing
conn.Close
Set conn = Nothing
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).

Do While Not MyRS.EOF
With MyRS2
.AddNew
.Fields(0).Value = MyRS.Fields(0).Value 'ContactID
.Fields(1).Value = MyRS.Fields(1).Value 'ClientCode
.Fields(2).Value = MyRS.Fields(2).Value 'Title
.Fields(3).Value = MyRS.Fields(3).Value 'FName
.Fields(4).Value = MyRS.Fields(4).Value 'LName
.Fields(5).Value = MyRS.Fields(5).Value 'Position
.Fields(6).Value = MyRS.Fields(6).Value 'Phone
.Fields(7).Value = MyRS.Fields(7).Value 'GSM
.Fields(8).Value = MyRS.Fields(8).Value 'Email1
.Fields(9).Value = MyRS.Fields(9).Value 'Email2
.Fields(10).Value = MyRS.Fields(10).Value 'Remarks
.Update
End With
Loop
 

So we have two options.
1. Infinite loop causes fail
2. Code fails before.

I hope this makes sence.

[ADezii]

Yes on Form Load I have:

Private Sub Form_Load()
' set up the connection and recordset.
Set conn = CurrentProject.Connection
 
'set client code
'MsgBox Me.ClientCode
'Me.txtClientCode = "gfkgjdflg"
'empty all other controls
Me.Title = ""
Me.FName = ""
Me.LName = ""
Me.Position = ""
Me.txtSpecify = ""
Me.Phone = ""
Me.GSM = ""
Me.Email1 = ""
Me.Email2 = ""
Me.Remarks = ""
'empty listbox
Set MyRS = New ADODB.Recordset
MyRS.Open "qryContactListbox", conn, adOpenDynamic, adLockOptimistic
Do While Not MyRS.EOF
    MyRS.Delete
Loop
MyRS.Close
Set MyRS = Nothing
'-------------------------------
'populate listbox
sSQL = "SELECT * FROM tblClientContacts WHERE " & _
"tblClientContacts.ClientCode = " & Me.ClientCode
 
Set MyRS = New ADODB.Recordset
MyRS.Open sSQL, conn, adOpenDynamic, adLockOptimistic
Set MyRS2 = New ADODB.Recordset
MyRS2.Open "qryContactListbox", conn, adOpenDynamic, adLockOptimistic
'transfer searched details to qryContactList
Do While Not MyRS.EOF
    With MyRS2
        .AddNew
        .Fields(0).Value = MyRS.Fields(0).Value 'ContactID
        .Fields(1).Value = MyRS.Fields(1).Value 'ClientCode
        .Fields(2).Value = MyRS.Fields(2).Value 'Title
        .Fields(3).Value = MyRS.Fields(3).Value 'FName
        .Fields(4).Value = MyRS.Fields(4).Value 'LName
        .Fields(5).Value = MyRS.Fields(5).Value 'Position
        .Fields(6).Value = MyRS.Fields(6).Value 'Phone
        .Fields(7).Value = MyRS.Fields(7).Value 'GSM
        .Fields(8).Value = MyRS.Fields(8).Value 'Email1
        .Fields(9).Value = MyRS.Fields(9).Value 'Email2
        .Fields(10).Value = MyRS.Fields(10).Value 'Remarks
        .Update
    End With
Loop
 
Me.lstContacts.Requery
Me.Title.SetFocus
 
MyRS.Close
Set MyRS = Nothing
MyRS2.Close
Set MyRS2 = Nothing
conn.Close
Set conn = Nothing
End Sub

Thanks

Did you forget to declare the Object Variable conn as in:

Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection