Replies

This is exactly so, as you have written !!! I have followed your advice and i solved the problem !

Thank you !!!!!!!!

This is exactly so, as you have written !!! I have followed your advice and i solved the problem !

Thank you !!!!!!!!

you reply looks to me interesting.Could you help me further, what exactly should i put as code in the OnOpen event of the Subform, in order to be opened only when the MainForm is opened but not as a standalone form
Thank you.
Best regards

Thank you for the reply.Yeas, i want to be opened only as a subform on the main form.What line of code can i write in the subform so that it cannot be opened as a
standalone form but only as a subform?
Best regards

Dear Charlotte, Dear Francoise,

thank you so much to you both for your kind and very profesional and considerate answers.I am writing to both of you You must know that this problem is of a great importance to me and therefore solving it will solve also a lot of my problems.
Please allow me to explain once again what i am doing. I am collecting the sales from different companies and each company sends the information under the name orders1 .I have a mirror table called orders and i want to append all the orders from the companies on this table,one by one.The order number does not repeat itslelf ,it is unique.But each office has a different range of allocated order numbers.What i want is to receive the orders1 from the different companies and append it to my orders table.I have set warning to False, since otherwise i cannot do it,as Charlotte says. Also,since the orders are sent not chronological i cannot use the DMax function as Frnacoise says.

So the following append query runs fine, and gives me the right results:
INSERT INTO orders ( OrderID )
SELECT orders1.orderid AS Expr1
FROM orders1;
However next time when i receive the table orders1, the appending is twice. For example if the first time i have sales of 30 tons, nect time i have sales of 60 tons which is impossible. A lot of trouble for me.
In a nutshell, i want to append from the table orders1, containing all the orders for a given company, only the orders that do not exist on my orders table. I think the find unmatched query will do the job but i cannot finish it. For example the following query i have built with the help of the wiard for find unmatched tables, finds exactly those orders that do not exist on my table oders and need to be appended:

SELECT orders1.orderid
FROM orders1 LEFT JOIN orders ON orders1.orderid = orders.orderid
WHERE (((orders.orderid) Is Null));

This time the query is not an append query but a special query with a different sign on the grid.My question is, how can i append now in a code the results of the above query? I have tried in vain.
If i solve it my problem is also solved.

Thank you for your reply. I am afraid my question was rather foggy and i think i should apologize for that.
I will try to explain myself in more detail.

Below is the original record source of a report:
Qbas is a query

Dim StrSales as string
strSales = ” SELECT DISTINCTROW QBas.afid, QBas.CompanyName, Sum(QBas.Liters) AS ProductSales ” & _
” FROM QBas” & _
” GROUP BY QBas.afid, QBas.CompanyName”

Me.RecordSource = StrSales
I want to base my sql not on the query QBas, but on the sql from that query.But i receive errors.

I converted the query QBas into an sql and i received the folowing:

strBas = ” SELECT DISTINCTROW Format([invoicedate],’yy-mm’) AS [Month], Products.size, Sum([Order Details].liters) AS Liters,

orders.paymentid, orders.invoicedate, customers.afid, affiliates.CompanyName ” & _
” FROM ((affiliates INNER JOIN customers ON affiliates.afid = customers.afid) INNER JOIN orders ON (customers.Customerid =

orders.customerid) AND (customers.Customerid = orders.customerid)) INNER JOIN (Products INNER JOIN [Order Details] ON

Products.Productid = [Order Details].ProductID) ON orders.orderid = [Order Details].OrderID ” & _
” GROUP BY Format([invoicedate],’yy-mm’), Products.size, orders.paymentid, orders.invoicedate, customers.afid,

affiliates.CompanyName ” & _
” HAVING (((orders.paymentid)>0) AND ((orders.invoicedate)>#1/1/2001#))”

Now i tried to replace the QBAs with the StrBas into the code like that:
strSales = “SELECT DISTINCTROW afid, CompanyName, Sum(Liters) AS ProductSales ” & _
” FROM ” & strBas & _
” GROUP BY afid, CompanyName”
But i receive the error Sytnatx error in FROM clause

I also tried the following”
strSales = “SELECT DISTINCTROW afid, CompanyName, Sum(Liters) AS ProductSales ” & _
” FROM ” & strBas & ” & GROUP BY afid, CompanyName”

And also:
strSales = “SELECT DISTINCTROW strBas.afid, strBas.CompanyName, Sum(strBas.Liters) AS ProductSales ” & _
” FROM strBas ” & _
” GROUP BY strBas.afid, strBas.CompanyName”

but again with the same result

Thank you for your reply. Yes, i think something has to do with my code.And also that the query continues running after i want to close the database.But can i avoid that?Is there any other command beside Application.Quit that can do the job? I am opening the form just to avoid the malfunction of the macro. But with no success.If i put my autoexec macro to perfrom only the the makeup function, then i receive the same results, the function has done its job,but just on exit i receive the message that the database
cannot be found.For example, if i put the function MakeUp on the OnClick event of the form, then everything is O.Ok, the function perdorms easility and the database is closed. But not if i use autoexec macro.I do not want to use the StartUp option becasuse of some reasons connected with my coding. So it follows that my problem lies with the autoexec macro and the Application.quit statement.I hope
i am making some mistakes in my arranged code or you might show me some other way to close the database through the autoexec macro.I will be very grateful to you indeed.
Best regards

Thank you very much for your prompt reply indeed. I am very impressed by this forum. Is there any function using DAO which drops table from a remote database?

Best regards

Thank you .Yeas you are right,there is no relationship,i have only one row in this table, which is the information for MY Company and not the Customers company.So there is alweays on e telephone number to show.. Actuallyt this information should always be one and the same for each invoice. But the owners of the MyCompany might change.My question is how can i avoit the command Preview and how to print these lookup fields.
regards

i have to correct myself the lookjup fields look like this

=”Telephone : ” & “” & DLookUp(“[PhoneNumber]”;”My Company Information”)

i didnt write it correctly the firs ttime, but the problem remains as i have written it

Dear Sir,

i will check up and study your suggeston but furst of all i want to thank cordially for the pains you have taken to answer
Best regards

Dear Charlotte,

Just to let you know that it works!!!!!! I have received and excellent and elegant solution.Thank you
very much!

May i wish you all the best

Dear Charlotte

Thank you so muh for your reply.Yes i eant to do my code with the on RError Resume next, however i cannot set the referene to my table. I am trying it on the following lines
Dim rs As Recordset
Set rs = CurrentDb().OpenRecordset(“TableName”)
However i receive the message “Variable not defined”,pointing to the table name.
Could you possibly help?
Would you mind suggesting me the entire code in order for me to avoid further obstacles?I hope the code will not be too time consuming for you
Best regards

Dear Charlotte,

I realy have to let you know that what you suggested about appending tables works excellent and you really helped me a lot indeed.I appreciate very much what you and your colleagues are doing

Thank you very much indeed for your kind and helpful reply.
Best regards