Replies

Thank you for your reply.I do not get any error,but the tables orders and order details are not linked in one to many relationship when i check it..I am puzzled since with the other function it works and i can see the links created.But in this case no error occrus but nothing is created

Best regards

Thank you very much for your reply.Could you please check up what i have done, since i am afraid i do make some errors in my
code and i receive the message ” syntax error in query expression orderdate< #1/1/2002# "

Public Const CnstBefore As Date = #1/1/2002#
Public Function Test()
Dim SqlRemoveFromOrders
SqlRemoveFromOrders = "DELETE DISTINCTROW orderdate FROM orders WHERE orderdate < #" & Format(CnstBefore, "mm/dd/yyyy") & "#"
CurrentDb.Execute SqlRemoveFromOrders
End Function

Best regards

Thank you for your reply.I have followed your advice,but i am afraid i didnt follow it properly,since i receive this time Syntax error.
Obviously my line
< " & CnstBefore & "))"

is not right.May you help me write it correctly?

best regards
===============================

Public Const CnstBefore As Date = #6/1/2003#
Public Function DeleteAllBefore1()

Dim SqlRemoveFromOrders
SqlRemoveFromOrders = "DELETE DISTINCTROW orders.orderdate AS Expr1 " & _
" FROM orders WHERE WHERE (((orders.orderdate) < " & CnstBefore & "))"

CurrentDb.Execute SqlRemoveFromOrders
End Function

May i have a small help?

i have copied your proposal and made a function, but obviously
i have missed something, since i receive the message
for IntLoop that variable is not defined.The function is below:

Public Function Test()
Dim strDepot As String
For intLoop = 1 To 3
strDepot = Choose(intLoop, DSo, DVa, DBi)
If Dir(strDepot, vbNormal) “” Then
Call FromDepot(strDepot)
UpdateTables
Kill (strDepot)
End If
Next intLoop
End Function

Best regards

Thank you !!! it is exactly what i wanted. I see i was not very clear but you did guess what i need and it will solve my problem.

Warm regards

i want to explain further what i have done with the hope that i will be more clear and thus receive the help i need

I have tried to build the code but i received

an error in the line tdf.Fields.Name = “CustomerID” with the message
” method or data member not found.” Can you help me build the right way ? Below is my code

Dim wsp As DAO.Workspace
Dim StrPassword As String
StrPassword = “password”
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set wsp = DAO.DBEngine.Workspaces(0)
Dim dbs As DAO.Database
Set dbs = wsp.OpenDatabase(“C:BEstoreBE.mdb”, False, False,

“;PWD=” & StrPassword)
Set tdf = dbs.TableDefs(“CallsCustomers”)
Set fld = tdf.Fields(“ContactID”)
tdf.Fields.Name = “CustomerID”
tdf.Fields.Refresh
dbs.Close
Set fld = Nothing
Set tdf = Nothing
Set dbs = Nothing
Set wsp = Nothing

(Edited by charlotte on 03-May-03 06:47. remove unnecessary line breaks)

How not to open a report ?
I am looking for a way to stop the execution of a code for opening a report in case the customer has not chosen an option.However it
seems that my way of doing things is not right.My option box is called Office,and i have put the command Exit Sub in case no option is chosen, but Access seems to neglect my command.Where am i wrong?

The command i have is the following :

If Me![Office] = False Then
MsgBox ” Please select a city”
Exit Sub
Else
Dim stDocName As String
Select Case Me![FrameCalls]
Case 1
stDocName = “CallsCustomers1”
Case 2
stDocName = “CallsCustomers2”
End Select
DoCmd.OpenReport stDocName, acPreview
End If

i have made a mistake in my question:
instead of Detail section i have written order section

Thank you !!! it works great now !

Thank you for your reply.Both tables are in the same database.The table1 is corrupted,but it is a part of a relationship, and i cannot make the relationship with tables filled with data. I am trying to evade this problem,and i am sending this code
in another town,so the best way will be if i append all the data from the table2 into the blank table2. Can you help me writing the code ?

regards

Dear Hans,

Thank you so much for your reply which after i applied it, my code works excellent!!!!
Could you help with one very similar function called ProcessOrder and here again i have the same problem,
when i choose the preview the document, i print it.

Thank you so much in adcance fro your wonderful remarks !!!!!!!!

Here is my function as follows:

Public Function ProcessOrder()
‘ the function is placed in the control CmdOrders being the output for the ListOrders
Dim f As Form
Set f = Forms![FOrderInformation]
If IsNull(f!
Dim intPrint As Integer
Dim intAnswer As Integer
intAnswer = MsgBox(” Delete? “, vbQuestion + vbYesNo)
If intAnswer = vbYes Then
Application.Echo False
CancelOrder
Else
Dim intTrst As String
intTrst = MsgBox(” Transform? “, vbQuestion + vbYesNo)
If intTrst = vbYes Then
ftransform
Else
intPrint = MsgBox(” Print? “, vbQuestion + vbYesNo)
If intPrint = vbYes Then
direct ‘ this function prints 4 copies
Else
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
VisibleOrder
End If
End If
End If
End If
End Function

Thank you for your reply.Yes, i will use a parameter later on, but my problem is substracting from 2 queries.For say customer Nr 118, i have 2 queries
which are differennt.I want to substract the liters from the 2 queries.I can do it with three queries,but i woulld like to use VBA in order to make things more flexible,use parameters,etc.Can you help me further?If you look at my thrid query,you will see it is based on the previous 2 queries.i want to aoid the use of three queries,if possble.Actually the thrid query only substracts the quantity from the 1 st query and the 2nd query.So,to make my question more clear please forget the customer,and take it to be a constant figure. If i solve the case for this customer,as you have already noted i can solve it for ther other customners too.

Best regards

Your suggestion did update ! Thank you.I feel obliged to expplain why i am doing this.The reason is that one ofmy fields will be obsolete in the future the other is brand new and i want to copy the availabe information
best regards

The fields stock ,items,branch0 and item0 are fields in the table products.You could see that from my query where i write products.stock, which means this is a field stock from the table products.Also i do not receive any error, but the fields are not updated.Also,as i mentioned ,the query i have shown is working, but i cannot explain myself why the function is not working, which means that the function is not updating.

Thank you so much for your kind and so clever answer. I really think it makes wonders with our programme.I have applied it and it works
excellent.
May i ask you fir a little further help? When i tried to apply your suggestion to a code very similar to this, i somehow could not
manage to get it right becasue i obtain the line of code called “bas” highlighted with red.I am wrong with my commas and brackets.Could you please have a look?
AT first my new code made on the basis of your suggestions, and below the old function that i want to modify

Public Function MainProductStrings()
Dim city As Long
city = Forms![FOrderInformation]![office] – 1
Dim bas As String
bas = ” SELECT products.Productid, products.grade, products.code,products.size,products.pack, ” & _
” products.branch” & city ,& “, products.items” & city ” & _
” FROM Products – here i receive red highlighted letters from Access

Dim strDocName As String
strDocName = “FOrderinformation”
If IsOpen(strDocName) = True Then
bas = bas & ” WHERE(((products.branch” & Forms![FOrderInformation]![office] & “) > 0))ORDER BY products.grade ASC”
End If
MainProductStrings = bas
End Function

Public Function OldMainProductStrings()
Dim strOffice As String
Dim strVaM As String
Dim StrBuM As String
Dim strBlM As String
Dim strHaM As String
Dim strPlM As String
Dim strTaM As String
Dim strTaM As String
Dim strRsM As String

StrBuM = ” SELECT products.Productid, products.grade, products.size, products.branch0, products.item0 FROM products WHERE (((products.branch0) > 0))

ORDER BY products.grade;”
strVaM = ” SELECT products.Productid, products.grade, products.size, products.branch1,products.items1 FROM products WHERE (((products.branch1) > 0))

ORDER BY products.grade;”
strBlM = ” SELECT products.Productid, products.grade, products.size, products.branch2,products.items2 FROM products WHERE (((products.branch2) > 0))

ORDER BY products.grade ;”
strHaM = ” SELECT products.Productid, products.grade, products.size, products.branch3,products.items3 FROM products WHERE (((products.branch3) > 0))

ORDER BY products.grade ;”
strPlM = ” SELECT products.Productid, products.grade, products.size, products.branch4,products.items4 FROM products WHERE (((products.branch4) > 0))

ORDER BY products.grade ;”
strTaM = ” SELECT products.Productid, products.grade, products.size, products.branch5,products.items5 FROM products WHERE (((products.branch5) > 0))

ORDER BY products.grade ;”
strTarM = ” SELECT products.Productid, products.grade, products.size, products.branch6,products.items6 FROM products WHERE (((products.branch6) > 0))

ORDER BY products.grade ;”
strRsM = ” SELECT products.Productid, products.grade, products.size, products.branch6,products.items6 FROM products WHERE (((products.branch7) > 0))

ORDER BY products.grade ;”

Dim strDocName As String
strDocName = “FOrderinformation”
If IsOpen(strDocName) = True Then
Select Case Forms![FOrderInformation]![office]
Case 1
strOffice = StrBuM
Case 2
strOffice = strVaM
Case 3
strOffice = strBlaM
Case 4
strOffice = strHaM
Case 5
strOffice = strPl
Case 6
strOffice = strTrM
Case 7
strOffice = strTaM
Case 8
strOffice = strRsM

End Select
MainProductStrings = strOffice
End If
End Function

P.S. if i am right i will not need to hard code each office separately, and i may add on aditional offices without caring to modify my code.
Therefore i treasure so much the suggestions you have made

Very best regards