Replies

Thank you very much for your replies.I have copied exactly the function but i obtain red letters on the following line:
If CLng(DLookup(“branch” & city, “Products”, strCondition)) < CLng(StrCartons) Then
which means i have some syntax problems, but i cannot find them.
I wonder why is it so, may be my Access does not include some references?
Here is how i copied the function:

Public Function FncUpdateCartons()
Dim city As Long
city = Forms![FOrderInformation]![office] – 1
Dim MySubform As Form
Set MySubform = [Forms]![FOrderInformation]![Forder details extended].[Form]
Dim StrCartons As String
StrCartons = MySubform![cartons]
StrItems = MySubform![items]
Dim strSQL As String
Dim strWhere As String, strCondition As String
strCondition = "ProductID=" & MySubform.Productid
strWhere = " WHERE " & strCondition
strSQL = "UPDATE Products SET " & _
" products.branch" & city & " = products.branch" & city & " – " & StrCartons & "strWhere "
If CLng(DLookup("branch" & city, "Products", strCondition)) < CLng(StrCartons) Then
MsgBox "There isn't enough stock to fill this order.", vbInformation + vbOKOnly
Else
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL ' substract
End Function

Could you please explain to me why do i receive line fo code coloured with red?

Very Best regards

Thank you very much for your answer.Your first suggestion saved for me an amazing amount of coding,since i have to repeat the opton in many places.
In the second part, the whole line which is

If CLng(DLookup(“branch” & city, “Products”, strWhere)) < CLng(StrCartons) Then
MsgBox "There isn't enough stock to fill this order.", vbInformation + vbOKOnly
Else

Seems not to give some syntx error.Actually the error is the following:
Run time error 2075.
Syntax error(missing operator in query expression Where Productid = 76
Just to clarify. In my expreiment i wanted to update a product with id number 76.

Here again is mny whole function:

Public Function FncUpdateCartons()
Dim city As Long
city = Forms![FOrderInformation]![office] – 1
Dim MySubform As Form
Set MySubform = [Forms]![FOrderInformation]![Forder details extended].[Form]
Dim StrCartons As String
StrCartons = [Forms]![FOrderInformation]![Forder details extended].[Form].[cartons]
StrItems = [Forms]![FOrderInformation]![Forder details extended].[Form].[items]
Dim strSQL As String
Dim strWhere As String
strWhere = " WHERE ProductID=" & MySubform.Productid
strSQL = "UPDATE Products SET " & _
" products.branch" & city & " = products.branch" & city & " – " & StrCartons & strWhere
If CLng(DLookup("branch" & city, "Products", strWhere)) < CLng(StrCartons) Then
MsgBox "There isn't enough stock to fill this order.", vbInformation + vbOKOnly
Else
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL ' substract
End Function

I will be grateful if you point to me my syntax mistake,and also i must express my appreciation for your wonderful replies.

Best regards

Dear Charlotte,

I habe followed the wonderful advices and have made anew the codes.I have now update statements where only the number of the branch is different, for example branch0 = Paris, branch1 = London etc.
If i have a function pointing to the number of the branch, then my update statement works, as in:

Public Function FncUpdateCartons()
Dim city As Long
city = 1
strSQL = “UPDATE Products SET ” & _
” products.branch” & city & ” = products.branch” & city & ” – ” & StrCartons & strWhere

etcetc

end function

Now i wanted to improve the function with the introduction of the enum as follows:

Public Enum GarudaCity
gcParis = 0
gcLondon = 1
gcRome = 2
End Enum

And then i rearranged the function to read:

Public Function FncUpdateCartons( Optional city As GarudaCity)
strSQL = “UPDATE Products SET ” & _
” products.branch” & city & ” = products.branch” & city & ” – ” & StrCartons & strWhere
etc etc
End function
And then on the AfterUpdate event of the form i put :

‘ call FncUpdateCartons (gcParis, Or gcLondon Or gcRome)

To my regret i receive no error, but no updating occurs.Coul you tell me why my Enum statement does not work?

I think you are right and my assumption that these two points are separate is wrong. By the way the answers i have received for the the invisible forms are excellent. But my question about the screen refers not only for forms but for all objects in the database.Actually, even though i have two different application the thread should be he same.please accept my inadverent mix up if the questions.
Also i must express my gratitude and appreciation for this forum since obviously i receive a good answer for everything i ask about Access.

I owe you an apology. I didnt read your answer and just sent my second question, and aactualy you have answerdet that.I see i may be confusing with other threads.
So why do i need the code.The reason is i want to have a better comand on the actions.For example i want to change the messages, or to make the database close .
Best regards

Further to my question of today i have expreimented again and have tried to revise the code as follows
StrOrderDetails = ” SELECT[order details].ProductID.[order details].cartons,[orderdetails].quantity ” & _
” INTO [TempOrderDetails] FROM [order details] INNER JOIN orders ON [order details].OrderID = [orders].orderid WHERE [orders].orderid = ” & Me.OrderID

Now i receive the error “Too few parameters.Expected2.Why is it so?
Please help

Thank you very much for your kind attention! Please help me further please.It is so important to me.
I have followd your advices as far as i can.I receive now the message ” Join expression not supported”
Here is the code that i have now, after i have made the order, in the OnClick event of the form:

Dim StrOrders As String
Dim StrOrderDetails As String

StrOrders = “SELECT orders.orderid, orders.customerid, orders.orderdate, orders.[required date], orders.paymentid, orders.PaymentMethodID, orders.bankid,

orders.invoicedate, orders.AuftragNr ” & _
” INTO Temp FROM orders WHERE orderid = ” & Me.OrderID

StrOrderDetails = ” SELECT[order details].ProductID.[order details].cartons,[orderdetails].quantity ” & _
” INTO [TempOrderDetails] FROM [order details] INNER JOIN orders ON [order details].OrderID = [order details].orderid WHERE orderid = ” & Me.OrderID

CurrentDb.Execute StrOrders
CurrentDb.Execute StrOrderDetails

The execution of StrOrders is perfect.I receive a temporary table called Temp containing only the order i have issued and it does the job.
But the execution of StrOrderDetails fails.Then i receive the message “Join expression not supported”.
I will be so grateful as always for the help.

P.S. In order to help me please note that the ProductID comes from the related table Products, where the ProductId is conected with the table
Order Details in one to many relationships. Therefore the productid is availabe in the subform and not in the mainform.These are only mine considerations but

since i am not a proffessional programmer i am surely on the wrong path.All i want is to have a temporry table containing all the details for a given order,as the

number of the product, cartons and quantity, the same as i have already a table containig only this given order.I cannot explain myself why in the first case i

receive the table i want but not in the second case.

Thank you for your reply. If you disregard the end use of my cod because i will bother you with my lenfgthy explanation,, could you show me what is wrong with my code? When i debug it it shows “Type mismatch”. It think i am committing some grave mistake somewhere.
it is as follows

Dim StrPassword As String
StrPassword = “secret”
Dim db As DAO.Database
Set db = DBEngine.Workspaces(0).OpenDatabase(CurrentDb, dbDriverComplete, False, “; PWD=” & StrPassword)

Bes Regards !

A perfect answer. I feel obliged to express my gratitude since this was so important to me.
Thank you very much !

thank you for your reply.I am trying to get data out of the protected on into the unprotected database.I agrre with what you write about the protection it is just a special case where i need it and if you are interested i may send the explanation to you,however i am afraid i may be boring.How shall i proceed in my case to get data out of the protected database?

Kind regards

Can i have a help with an Update query? I want to update the fields paymentid,bankid,invoicedate and Paymentmethodid
from the table orders to be equal to the same fields in the table orders1.In other words, i want to insert the values from the table orders1 into the table orders.The values in the table orders are in fact old values and the values in the table orders1 are the new values.
I am confused about the expressions Insert Into and Update and i cannot handle the wizard.
Below are the two select clauses,just for orientation.

SELECT orders.paymentid, orders.bankid, orders.invoicedate, orders.PaymentMethodID
FROM orders;

SELECT orders1.paymentid, orders1.bankid, orders1.invoicedate, orders1.PaymentMethodID
FROM orders1;

I suppose the code shoud read
Update orders ( or orders1? i am not sure) .paymentid SET orders.paymentid = orders1.paymentid, orders1.bankid = orders1.bankid

but what i write did not make sense to me.Shall i write InsertInto instead of Update?

Thank you very much for your reply. Could you help me further? I receive an error “missing operator in query expression “when
i use the concatenated strBerlin.I cannot find out the origin of my error.I do have a control named site in my form, also in my report.
Without the strBerlin the function works.
Bedlow is my function :

Public Function FncSites()

‘ Defines the StrCriteria on opening the report.
Dim f As Form
Set f = Forms!Orders
f.Visible = False
Dim strDocName As String
Dim strCriteria As String
Dim OrderDate As Control
‘set the months
Dim jan As String
‘set the months
Dim month As Control
‘naming of variables
Set OrderDate = Forms!Orders![OrderDate]
Set month = Forms!Orders![month]
Set month = Forms!Orders![month]
jan = “Month(Orderdate)= 1 and year(Orderdate) = ” & CnstYear
Select Case month
Case 1
strCriteria = jan
End Select
strDocName = ” rptSalespersite”

Dim strBerlin As String
strBerlin = “(” & Forms!Orders![site] & ” 8000)”

DoCmd.OpenReport strDocName, acPreview, , strCriteria & ” AND ” & strBerlin

Forms![Orders]![month].Value = 0
End Function

I will appreciate very much your help

Excellent. Thats what i meant and your suggestions are very useful

Thank you so much for your wish to help.I think you will be able to help me if I send to you my abridged database.In short, the autoexec macro runs a code that opens a form and in the OnOpen event of the form is my function.I have simplified it so that the function, for more clarity, contains only the command DoCmd.Beep. So what I want is that aftewr performing the function, in this case DoCmd.Beep, to quit the application and close the database.But at the end I receive the message

i feel obliged to thank you for your nice and elegant answer
Regards