Hi, i have a sub form, which shows 2

[Egglar]

Hi, i have a sub form, which shows invoices for a particular customer, there is an "xselect" on the subform so the user can select what records they want to delete etc. Ive created a bit of code that sums the total amount of all of the invoices selected, and am trying to use a record set to put it into a txt box on the main form, but i cant work out why its not working. The code looks like this:

Private Sub chkXSelect_Click()
Dim Totalsql As String
Dim db As Database
Dim rst As DAO.Recordset

Totalsql = "SELECT Sum(tblPurchaseInvoice.Amount) AS SumOfAmount FROM tblPurchaseInvoice " _
& "HAVING (((tblPurchaseInvoice.Supplier)=1) AND ((tblPurchaseInvoice.XSelect)=True));"
'Debug.Print Totalsql

Set db = CurrentDb
Set rst = db.OpenRecordset(Totalsql)
txtSelectedTotal = rst.Fields("SumOfAmount&quot
rst.Close
Set rst = Nothing
End Sub

txtSelectedtotal is on the main form, but it just doesnt update, any ideas?

Thanks, Elliot.

 

[Egglar]

Hmm could have sworn i put a thread title in, oh well

 

[chickey]

Try putting the full reference for the textbox in
i.e. Forms![Your Form]![txtSelectedTotal] = rst.Fields("SumOfAmount&quot

If you are setting this from the sub form, it won't see the
textbox on the mainform unless you reference it.

Regards...

 

[Egglar]

Brilliant, i thought it was something like that! Thanks!

Instead of starting a new thread, i got another simple question, may as well add it on

Whats the code to run a query from vb, i have

Call DoCmd.OpenQuery("qryPaidUpdate", acViewNormal, acReadOnly)

This runs fine but i get messages comeing up saying you are about to append (1) rows etc, (its an update query btw).

Is there any way of suppressing those messages? Or another way to execute the query?

Thanks, Elliot.

 

[chickey]

You could try the following:

Docmd.setwarnings false
docmd.echo false (or application.echo false)

Make sure you turn them back on before your error routine
kicks in or you won't be able to see any errors though!

Regards...

 

[chickey]

Sorry, more info - should have been in my last reply:

Action queries tend to throw up these "information"
messages. The setwarnings to false tend to hide them
when running action queries from code. So the following
should work for you:

docmd.setwarnings false
docmd.OpenQuery("qryPaidUpdate", acViewNormal, acReadOnly)
docmd.setwarnings true

Set "docmd.setwarnings true" in the first line of your
error handler as well in case the docmd.openquery line
throws up an error (i.e. otherwise, the docmd.setwarnings
true above would never get executed).

Regards...

 

[Egglar]

Great stuff, thanks again! Works a treat.