Solved

Method 'value' of object '_field' failed

Posted on 2000-04-11
24
364 Views
Last Modified: 2008-02-01
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
Comment
Question by:g4b
  • 6
  • 5
  • 5
  • +2
24 Comments
 
LVL 10

Expert Comment

by:paasky
ID: 2704445
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
 
LVL 54

Expert Comment

by:nico5038
ID: 2704466
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
 

Author Comment

by:g4b
ID: 2704559
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
 
LVL 10

Accepted Solution

by:
paasky earned 15 total points
ID: 2704560
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
 
LVL 7

Expert Comment

by:Believer
ID: 2704598
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
 

Author Comment

by:g4b
ID: 2704672
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
 
LVL 7

Expert Comment

by:Believer
ID: 2704689
What didn't work?  (Which of the suggestions posted here or on the other Q?)
0
 
LVL 10

Expert Comment

by:paasky
ID: 2704727
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
 
LVL 54

Expert Comment

by:nico5038
ID: 2704860
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
 
LVL 7

Expert Comment

by:Believer
ID: 2704868
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
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2705389
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
 
LVL 54

Expert Comment

by:nico5038
ID: 2705556
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:g4b
ID: 2707728
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
 
LVL 7

Expert Comment

by:Believer
ID: 2707746
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
 

Author Comment

by:g4b
ID: 2708437
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
 
LVL 54

Expert Comment

by:nico5038
ID: 2709570
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
 

Author Comment

by:g4b
ID: 2711787
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
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2711889
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
 
LVL 4

Expert Comment

by:wesleystewart
ID: 2712440
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
 
LVL 7

Expert Comment

by:Believer
ID: 2825618
(Don't know if this is worth enough pts to "tickle", but...)
0
 
LVL 54

Expert Comment

by:nico5038
ID: 2970135
<echo>

<echo

<ech

<ec

<e

<

0
 
LVL 10

Expert Comment

by:paasky
ID: 3103810
<ahem> any news here?
0
 
LVL 4

Expert Comment

by:wesleystewart
ID: 3104638
Looks like a dead one . . .
0
 

Author Comment

by:g4b
ID: 3186390
Comment accepted as answer
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

760 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now