?
Solved

Update field in subform from unbound control in main form

Posted on 2013-06-08
13
Medium Priority
?
1,787 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 200 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 520 total points
ID: 39231655
Look at sample
SmartStorageV2.4.accdb
0
 
LVL 51

Assisted Solution

by:Gustav Brock
Gustav Brock earned 280 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 51

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 51

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 51

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 58
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

10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

770 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