[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 400
  • Last Modified:

Method 'value' of object '_field' failed

If somebody could help me to find out what kind or error is this... can't figure it out. It blows wher I put ERROR STARTS HERE.
Is not putting the right value in the table and if I debug I will get this error message.
What do I mean when I say is not putting the right value ..
Ex. The type of payment should be CK, PO , etc but no a number and that is what is putting in the table the number not the meaning.

Dim MyDatabase As Database, Temporder As Recordset
Set MyDatabase = DBEngine.Workspaces(0).Databases(0)                  ' Get current database.
Set Temporder = MyDatabase.OpenRecordset("Orders", dbOpenTable)      ' Create Recordset.
Temporder.MoveLast
    If Temporder![Type of Payment] = "1" Then
        Temporder.Edit
        Temporder![Type of Payment] = "ck"
        Temporder.Update
    End If

    If Temporder![Type of Payment] = "2" Then
        Temporder.Edit
        Temporder![Type of Payment] = "po"
        Temporder.Update
    End If
    If Temporder![Type of Payment] = "3" Then
        Temporder.Edit
        Temporder![Type of Payment] = "rn"
        Temporder.Update
    End If
    If Temporder![Type of Payment] = "4" Then
        Temporder.Edit
        Temporder![Type of Payment] = "ca"
        Temporder.Update
    End If
      If Temporder![Type of Payment] = "5" Then
         Temporder.Edit
         Temporder![Type of Payment] = "eft"
         Temporder.Update
      End If
   

''''''here begin option box for the deposit fund numbers

    If Temporder![Deposit_funds] = "1" Then
        Temporder.Edit
        Temporder![Deposit_funds] = "128/9460"
        Temporder.Update
    End If
    If Temporder![Deposit_funds] = "2" Then
        Temporder.Edit
        Temporder![Deposit_funds] = "128/3980"
        Temporder.Update
    End If
    If Temporder![Deposit_funds] = "3" Then
        Temporder.Edit
        Temporder![Deposit_funds] = "128/3960"
        Temporder.Update
    End If
 ==========ERROR STARTS HERE=========  
    Temporder.Edit
    Temporder![Amount Due] = Forms![Order Entry]![Total]
    Temporder![FirstName] = Forms![Order Entry]![FirstName]
    Temporder![LastName] = Forms![Order Entry]![LastName]
    Temporder![OrganizationName] = Forms![Order Entry]![OrganizationName]
    Temporder![Address1-street] = Forms![Order Entry]![Address1-street]
    Temporder![Address2-PO Box] = Forms![Order Entry]![Address2-PO Box]
    Temporder![State] = Forms![Order Entry]![State]
    Temporder![City] = Forms![Order Entry]![City]
    Temporder![Zip Code] = Forms![Order Entry]![Zip Code]
    Temporder![County] = Forms![Order Entry]![County]
    Temporder.Update

Temporder.Close
If [temp] = 1 Then
    If canc() = 1 Then
    End If
End If

End Sub
0
g4b
Asked:
g4b
  • 6
  • 5
  • 5
  • +2
1 Solution
 
paaskyCommented:
Hello g4b,

I optimized your code a little...

    Dim MyDatabase As Database, Temporder As Recordset
   
    Set MyDatabase = DBEngine.Workspaces(0).Databases(0)                  ' Get current database.
    Set Temporder = MyDatabase.OpenRecordset("Orders", dbOpenTable)      ' Create Recordset.

    With Temporder

        ' check that records exists
        If .BOF And .EOF Then
            ' no records in the table
        Else
            .MoveLast
       
        ''''''here begin option box for the deposit fund numbers
           
            .Edit
           
            Select Case ![Type of Payment]
                Case "1": ![Type of Payment] = "ck"
                Case "2": ![Type of Payment] = "po"
                Case "3": ![Type of Payment] = "rn"
                Case "4": ![Type of Payment] = "ca"
                Case "5": ![Type of Payment] = "eft"
                Case Else: ' any other value?
            End Select
           
            Select Case ![Type of Payment]
                Case "1": ![Deposit_funds] = "128/9460"
                Case "2": ![Deposit_funds] = "128/3980"
                Case "3": ![Deposit_funds] = "128/3960"
                Case Else: ' any other value?
            End Select
           
            ![Amount Due] = Forms![Order Entry]![Total]
            ![FirstName] = Forms![Order Entry]![FirstName]
            ![LastName] = Forms![Order Entry]![LastName]
            ![OrganizationName] = Forms![Order Entry]![OrganizationName]
            ![Address1-street] = Forms![Order Entry]![Address1-street]
            ![Address2-PO Box] = Forms![Order Entry]![Address2-PO Box]
            ![State] = Forms![Order Entry]![State]
            ![City] = Forms![Order Entry]![City]
            ![Zip Code] = Forms![Order Entry]![Zip Code]
            ![County] = Forms![Order Entry]![County]
            .Update
            .Close
           
        End If    ' (.BOF And .EOF)
       
    End With
   
    Set Temporder = Nothing

    ' what's this [temp] - form field?
    If [temp] = 1 Then
        If canc() = 1 Then
        End If
    End If

End Sub

Regards,
Paasky
0
 
nico5038Commented:
Strange, sure that the Temporder![Type of Payment] field is a text field !
If so, test whether the contents are really "1" and not " 1" (extra space!)
If so use TRIM to get rid of space.
0
 
g4bAuthor Commented:
NO I didn't work.. I still have the same problem..
It is not putting the right value into the table.

Please need help...
0
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.

 
paaskyCommented:
hmm.. I'm glad nico5038 you commented because I noticed copy-paste error in my code. Here's fixed and trim() improved version of code:

....
            Select Case Trim(![Type of Payment])
                Case "1": ![Type of Payment] = "ck"
                Case "2": ![Type of Payment] = "po"
                Case "3": ![Type of Payment] = "rn"
                Case "4": ![Type of Payment] = "ca"
                Case "5": ![Type of Payment] = "eft"
                Case Else: ' any other value?
            End Select
           
            Select Case Trim(![Deposit_funds])
                Case "1": ![Deposit_funds] = "128/9460"
                Case "2": ![Deposit_funds] = "128/3980"
                Case "3": ![Deposit_funds] = "128/3960"
                Case Else: ' any other value?
            End Select
....

Paasky
0
 
BelieverCommented:
Gee, paasky's code looks like what I posted to g4b's other question a few moments ago... 'Great minds think alike!"

g4b: See what I posted to your other question about un-binding the option group...
0
 
g4bAuthor Commented:
NO that didn't work either.
what is happening with this....
is not putting the right value in the table.
Any other suggestions..
Please.....
0
 
BelieverCommented:
What didn't work?  (Which of the suggestions posted here or on the other Q?)
0
 
paaskyCommented:
Hi Believer, nice to see you ;-)

I checked your comment just a second ago and tested option group (option values are always numeric) with text type field. Seems that numeric value is stored okay without any extra spaces or errors.

In case the values should come from form option group fields here's a simple solution:

....
            ![Type of Payment] = Choose(Forms![Order Entry]![Type of Payment], "ck", "po", "rn", "ca", "eft")
            ![Deposit_funds] = Choose(Forms![Order Entry]![Deposit funds], "128/9460", "128/3980","128/3960")
....

(option groups names in the form are "Type Of Payment" and "Deposit Funds")

Paasky
0
 
nico5038Commented:
OK,

Just a totally different appraoch:
Define two "two field" tables for
Type of Payment and
Deposit_funds
containing:
1) key (the number) and
2) value
(Like "1" and "ck" for Type of Payment, etc.)
Connect key's of these tables with the Temporder numberfields with a relation with enforced referential integrety.
No further editing has to be done. Adding new "key-numbers" can be done without editing your code.
0
 
BelieverCommented:
nico: Fantastic idea!  (Um, yes, I thot of it and didn't follow thru with a post <grin>).  If this is done, the option group can be easily converted to a bound combo box.
0
 
wesleystewartCommented:
I'm sure you gents are well on your way to working this one out, but just for grins and giggles:

Put in a

MsgBox Temporder![Type of Payment]

After the Temporder.Movelast and see what it returns.  You might not be getting a value between 1 and 5 at all. . .

Wes
0
 
nico5038Commented:
Wesley, have no clue what you found, as for me a recordset (opened this way) works fine and gives indeed the last record.
Even the multiple .edit's and .update's work fine!
(On A2000)
0
 
g4bAuthor Commented:
Wesleystewart
I did what you told me and I select #1 which is CK but it should give me the result of number 1 but it doesn't it gives me the result of CK.
Any suggestions of what do you think I am doing wrong?
0
 
BelieverCommented:
Don't think you're doing anything wrong... under this scenario the parent table ("Orders"?) *will* have a number stored.  When you want to see the text value, link to the new table ("Type of Payment") in your query...
0
 
g4bAuthor Commented:
Nothing works so far.. I absolutely can't understand now. I had some copies that were not working and I din't do any changes to it. but now it works..
now it seems like there is something in the recordset that I am doing wrong.
or when the database closes for a large period of time then it won't work again.

if anybody has more suggestions.. please...
0
 
nico5038Commented:
Sounds like MS Access cluttering up.

Remedies I use in order:
1) Compress database
2) Export object with code to other .mdb, delete it and import again.
3) Create whole new empty .mdb and import all again. (check references!)
4) Delete and build from scratch object again.

Success.

(Did you thought about the extra tables?)
0
 
g4bAuthor Commented:
Yes, I did create a new mdb and also create another one and imported all of the objects.
I called Microsoft and they told me to download Jet3.51. To do a better compacting so.. that is what I am going to do.  If it doesnn't work then I don't know what is wrong. But they can't tell me what the Method 'collect' of object 'recordset' failed  error message means..
I get this error not all the time... because sometimes it works fine and some don't.. and there is no help for the error... it is a weird error message.

Any suggestions?????
0
 
wesleystewartCommented:
g4b:

If you'd like to email me your database (provided it isn't too big) I'll be happy to take a look at it so I can better discuss it here.  This could be a subtle issue that might be very obvious if I take a look at it on my own system.

My email address is in my profile.

Wes
0
 
wesleystewartCommented:
Well, I was wrong about this being something simple.  It would appear that some re-engineering needs to happen.  Please understand I'm not being critical of you at all, but this app needs some work.  These are my thoughts on the ConvertTo97.mdb:

First, I wasn't able to get the code to compile because it referenced some dll files I don't have.

Second, There are some changes you should make to make any further development easier.  Add the Option Explicit statement to the top of EVERY module and check the Require Variable Declaration box under Tools>>Options>>Module.  This will keep you from making spelling errors in your code.  Go through the database and change the names of all objects that have punctuation and spaces in them.  Large, puncutated names that include spaces are just problems waiting to happen.

Fix your calls to the MsgBox function from this:

Response = MsgBox(...)

to this

MsgBox "..."

Make sure you handle every possible result within your Select Case and If...Then structures.  Several places in your code these structures are incomplete.

I would suggest leaving the numeric codes in the table for Type of Payment.  This is the type of information typically handled by codes that are then referenced to another table that contains the code keys.  This way if payment types change you can just change the code keys rather than maintaining the code.

Good Luck,

Wes
0
 
BelieverCommented:
(Don't know if this is worth enough pts to "tickle", but...)
0
 
nico5038Commented:
<echo>

<echo

<ech

<ec

<e

<

0
 
paaskyCommented:
<ahem> any news here?
0
 
wesleystewartCommented:
Looks like a dead one . . .
0
 
g4bAuthor Commented:
Comment accepted as answer
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

  • 6
  • 5
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now