Solved

Update field in subform from unbound control in main form

Posted on 2013-06-08
13
1,736 Views
Last Modified: 2013-06-08
HI,
Attached a sample file,
I need to perform update field=CurrencyValue in a subform=subQuery from a button control in main form=form1 where CurrencyValue=

UPDATE currencyvalue field in subquery.form from a value of unbound textbox call txtNewValue.value in main form where  currencyvalue fied in subquery = value of the unbound control textbox=txtCurrencyValue
SmartStorageV2.4.accdb
0
Comment
Question by:drtopserv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
13 Comments
 

Assisted Solution

by:baderms1959
baderms1959 earned 50 total points
ID: 39231638
Private Sub Command15_Click()

    Dim strSQL As String
   
    strSQL = "UPDATE tblCell SET CurrencyValue = " & txtNewValue & " WHERE CurrencyValue=" & txtcurrencyvalue
   
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL
    DoCmd.SetWarnings True
    subQuery.Requery

End Sub
0
 
LVL 40

Accepted Solution

by:
als315 earned 130 total points
ID: 39231655
Look at sample
SmartStorageV2.4.accdb
0
 
LVL 50

Assisted Solution

by:Gustav Brock
Gustav Brock earned 70 total points
ID: 39231690
It is much faster to use DAO for this, and your datasheet will update instantly without a requery:
Private Sub Command15_Click()

    Dim rst     As DAO.Recordset
    Set rst = Me!subQuery.Form.RecordsetClone
    
    rst.MoveFirst
    While Not rst.EOF
        If rst!CurrencyValue.Value = CCur(Nz(Me!txtcurrencyvalue.Value, 0)) Then
            rst.Edit
                rst!CurrencyValue.Value = CCur(Nz(Me.txtNewValue.Value, 0))
            rst.Update
        End If
        rst.MoveNext
    Wend
    rst.Close
    
    Set rst = Nothing

End Sub

Open in new window

/gustav
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

Author Comment

by:drtopserv
ID: 39231741
thnx ALL,
but is there away to write a line like the one written by baderms1959 :

 strSQL = "UPDATE tblCell SET CurrencyValue = " & txtNewValue & " WHERE CurrencyValue=" & txtcurrencyvalue

and change the tblcell to -> Query.Query1
0
 

Author Comment

by:drtopserv
ID: 39231798
i`ll post another Q also with 500 points :} , i think it needs more to think :}
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 39231809
No it doesn't.
There is no reason to fool around with slow SQL for this task.

Just copy and paste my code into your button click event.

/gustav
0
 

Author Comment

by:drtopserv
ID: 39231824
cactus_data, as i have read from experts they said :
"Don`t use recordset looping and updating to simply update a group of records in a table. it`s much more efficient to build on update query with the same selection criteria to modify the records as a group."
(from the book wrox access 2010 programmer~)
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 39231851
Well, other Experts tell another story.

Why don't you just try? One minute and you are done. It is tested and works at an amazing speed, and you will have learnt something new.

/gustav
0
 

Author Comment

by:drtopserv
ID: 39231856
yea thnx alot i know the recordset update but i thought  it`s agood way to do update through sql.
but anyway thanx alot pals..
i`ll give the point to the 3.

i`ll open a new post in a mins..
i hope someone can the ability to solve it(it`s a continuation Q)
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 39231869
Strange. Leaving the solution on the floor and walk away ...

/gustav
0
 

Author Comment

by:drtopserv
ID: 39231870
lollllllllll
0
 

Author Closing Comment

by:drtopserv
ID: 39231872
thnx Alotttttttttttttttttttt
0
 
LVL 57
ID: 39231904
<<
"Don`t use recordset looping and updating to simply update a group of records in a table. it`s much more efficient to build on update query with the same selection criteria to modify the records as a group."
(from the book wrox access 2010 programmer~)
>>

  The thing here is that they are talking about doing this in general in code and not in a form.   When in a form and using the forms recordset, the recordset is already built.  Plus you avoid the requery/refresh of the data.  I would not use SQL statements in a form unless it was un-bound.

  I'm also left wondering if they were not using ADO instead of DAO.  DAO is faster then ADO when dealing with recordsets in a JET based DB.

Jim.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Suggested Solutions

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

726 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