Replies

Thanks Hans.

If those forms are based queries rather than tables such as qryOrderdetail rather than Orderdetail and some of the fields on the form are based fields in that query that will not be updated by this code… Will it still work?

EXample the code would insert the copied values for….

ordernumber, productid, qty

But other items on the form such as the item description gets filled in by the query on linked table products…

Thanks

Jason

Thanks Hans.

I did as you say. I got inconsistent results. Not sure why. Sometimes it updated others is did not. I added to yours and think I have consistent updating now. Will keep testing

I used this in case you are interested….

Private Sub cntdpuntos_AfterUpdate()
Me.refresh
Me.Parent!frmAsigsubPuntoSUM.Requery
End Sub

Thanks again very much

Thanks Hans.

I have that working now as you instructed. However When I change the number of ponts on my first subform. The new second form does not update unless I put the focus on the main form and manually refresh. I have tried my hand with all manner of events like “afterupdate” to no avail. Any thoughts. Thanks

Thanks Francios.

Where in this code would I indicated the this is mysql and not SQL. or where does it reference the ODBC driver?

thanks to both.

I used John’s sample in his DB and it worked. I imported his tables and query into my DB and got the same errors as before. I deleted my Concat module. and imported his. His query worked worked in my DB with his module. (I noticed his module was named module1 in the module window. I had named mine Concat there… maybe I created myself a conflict doing that) Eventual after additional pain and suffering my query also seems to work.

Notes.
In the query I had to NOT ADD the table that is being concatenated (I seemed to create a huge record set until I figured that out)
Also the “table” being concatenated can be a query but there is nothing that says whether it is a query or table so I assume if you had a table and query by the same name (or maybe access wont permit that?) you would get a conflict.
At one point I got into an eternal loop in the de###### —strRSt was highlighted and I would hit end and just kept looping … It said I lacked some additional perameter. I had to control alt delete to make it stop. (nothing unusual for me)

These notes are only meant to help provide feedback in case it is useful. Not at all meant to complain..

Thank you both very much John and Hans. This is great. I can use ths other places too!

Very truly yours.

Jason

thanks Hans.

The error says

undefined function ‘Concat’ in expresion.

Thanks again
Jason
PS (sending a stripped down version is rather impractical with this DB… )

Thanks for the help. I think you are correct. However I added the module. I checked the DAO 3.51 in tools references in the VB editor. Inside the query builder I click build and go to functions and the function is there. I include it in my query and when I run the query it very quickly responds telling me there is an unknown function Concat

Any thoughts. ( I have added a total of maybe 3 functions…in my life so I am sure it is an issue how I added the function….

Thank you very much

Thanks

Hans.

As Always your solution works great.

Thanks

Jason

Thanks
Here is a zip of my mdb.
jason

Thanks
Here is a zip of my mdb.
jason

What is it that allows you to show “data” as opposed to numerica values resulting from aggregate funtions in “interior grid”
your crosstab query result.

I get this error
” You tried to execute a query that does not include the specified expresion ‘client’ as part of an aggregate function.”

This occurs when I change the crosstab row to EXPRESSION in the GUI query designer. If I set it to first. It gives me “-1” in all the “cells” where I wish to see the client’s name. It seems that Leeshas crosstab query (in file posted by Francois) does what I want (and a whole lot more) Maybe its simpler than I think.

I have products that I sell exclusively by city. In the USA a city has multiple zip codes but the left 2 digits roughly define a city. So I want my cross tab to have Left([zipcode],2) accross as column headings… ProductID as row headings and the cell values of Client name (or empty if nobody in that city carries that product)

Thanks for the help… Jason

What is it that allows you to show “data” as opposed to numerica values resulting from aggregate funtions in “interior grid”
your crosstab query result.

I get this error
” You tried to execute a query that does not include the specified expresion ‘client’ as part of an aggregate function.”

This occurs when I change the crosstab row to EXPRESSION in the GUI query designer. If I set it to first. It gives me “-1” in all the “cells” where I wish to see the client’s name. It seems that Leeshas crosstab query (in file posted by Francois) does what I want (and a whole lot more) Maybe its simpler than I think.

I have products that I sell exclusively by city. In the USA a city has multiple zip codes but the left 2 digits roughly define a city. So I want my cross tab to have Left([zipcode],2) accross as column headings… ProductID as row headings and the cell values of Client name (or empty if nobody in that city carries that product)

Thanks for the help… Jason

I have a similar need. I have a query that has InvoiceID and INVdetailnum I’d like to add a field that essential assings a line item number 1,2,3 etc to each Invdetail num so in the end you can see that invdetailnum 136 id item 3 of invoice 17. So. the querry should sort first on InvoiceID, then on INVdetailnum, and the Linenum fields starts at 1 and goes up by one until it sees that its hit a record with a new InvoiceID so it starts at 1 again and counts up by one…. Any hope for me.

I have a similar need. I have a query that has InvoiceID and INVdetailnum I’d like to add a field that essential assings a line item number 1,2,3 etc to each Invdetail num so in the end you can see that invdetailnum 136 id item 3 of invoice 17. So. the querry should sort first on InvoiceID, then on INVdetailnum, and the Linenum fields starts at 1 and goes up by one until it sees that its hit a record with a new InvoiceID so it starts at 1 again and counts up by one…. Any hope for me.

modified registry to exclude space. Now double clicking internet shortcut produces the following pop p form.. see attchd

Regards

Jason