Solved

update query - trouble with recordset

Posted on 2013-02-04
7
250 Views
Last Modified: 2013-02-04
In the query below I am getting the error "method or data member not found"  for rs.NegociatedRate.

What am I doing wrong?

    Dim db As DAO.Database
    Dim RcdSet As DAO.Recordset
    Dim sqlFP As String
    Set db = CurrentDb()
    sqlFP = "Select * From tblTranslationContractPurchases where trad_commandesID=" & Me![trad_commandesID]
    Set RcdSet = db.OpenRecordset(sqlFP, dbOpenDynaset, dbSeeChanges)
    
    Dim rs As DAO.Recordset
    Dim sqlOption As String
    sqlOption = "Select * From tbltrad_commandes_FPdetails where trad_commandesID=" & Me![trad_commandesID] & _
    "AND OptionSelected=1"
    Set rs = db.OpenRecordset(sqlOption, dbOpenDynaset, dbSeeChanges)
    
    
        If RcdSet.RecordCount = 0 Then
        'can't find proposal. need to do an append
        
        ElseIf RcdSet.RecordCount = 1 Then
            If rs.RecordCount = 0 Then
            MsgBox "You need to choose the number of words."
            ElseIf rs.RecordCount = 1 Then
            rs.MoveFirst
            DoCmd.SetWarnings False
            Dim strSQL As String
            strSQL = "Update tblTranslationContractPurchases set OrderDate = Now()," & _
here ====>>>            "NegociatedRate = " & rs.NegociatedRate & _
            ",ContractPrice = " & rs.ContractPrice & _
            ",ContractQuantity = " & rs.ContractQuantity & _
            ",FPEstimateStatus = 2 " & _
            "where tblTranslationContractPurchases.trad_commandesID = " & Me.trad_commandesID
            DoCmd.RunSQL strSQL
            DoCmd.SetWarnings True
            Else
            MsgBox "You need to choose the number of words. Only one option at a time."
            End If
        End If
    
    RcdSet.Close
    Set RcdSet = Nothing
    

Open in new window

0
Comment
Question by:Shawn
  • 3
  • 3
7 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 250 total points
ID: 38853102
Check your spelling and use ! Instead of .  For fieldnames.
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 250 total points
ID: 38853111
rs!NegociatedRate

and the same for the other rs fields.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38853113
Same applies for all of your fieldnames...

Rs!fieldname
0
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
LVL 1

Author Comment

by:Shawn
ID: 38853136
ok, got it.

I'm a little confused as to when to use . and when to use !
0
 
LVL 1

Author Comment

by:Shawn
ID: 38853148
both answers are right. First right answer or split?
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38853169
Use . for methods such as Update, MoveNext, etc and properties such as .NoMatch, etc.

Use ! for fieldnames, or the alternate syntax rs("FieldName")


>>> First right answer or split?

Our answers were almost at the same time.  A split is good with me.
0
 
LVL 1

Author Comment

by:Shawn
ID: 38853184
great, thanks for clarification.

If you want to tackle another question, I've been stuck on this one for a while
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_28017619.html

Shawn
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

820 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