Solved

Update field in subform from unbound control in main form

Posted on 2013-06-08
13
1,698 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
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 39

Accepted Solution

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

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

 

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 49

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 49

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 49

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

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.

Question has a verified solution.

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

Suggested Solutions

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

813 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now