?
Solved

Update field in subform from unbound control in main form

Posted on 2013-06-08
13
Medium Priority
?
1,814 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 52

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 

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 52

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 52

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 52

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
What we learned in Webroot's webinar on multi-vector protection.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

650 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