Solved

update query - trouble with recordset

Posted on 2013-02-04
7
249 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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 …

823 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