Solved

Update field in subform from unbound control in main form

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

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

Suggested Solutions

Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
These days, all we hear about hacktivists took down so and so websites and retrieved thousands of user’s data. One of the techniques to get unauthorized access to database is by performing SQL injection. This article is quite lengthy which gives bas…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

867 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

21 Experts available now in Live!

Get 1:1 Help Now