Replies

thank you for your suggestion it suits me perfectly

thank you for your suggestion it suits me perfectly

It works so nice now !! About the order details table, how can i add the composite key in the table order details through code?I can only do it
for the first key, but Access does not allow me to put the second key.Perhaps there is
a command for the composite key?

dbs.Execute “ALTER TABLE [order details] ADD CONSTRAINT PrimaryKey Primary Key(orderID);”
dbs.Execute “ALTER TABLE [order details] ADD CONSTRAINT PrimaryKey Primary Key(ProductID);”

It works so nice now !! About the order details table, how can i add the composite key in the table order details through code?I can only do it
for the first key, but Access does not allow me to put the second key.Perhaps there is
a command for the composite key?

dbs.Execute “ALTER TABLE [order details] ADD CONSTRAINT PrimaryKey Primary Key(orderID);”
dbs.Execute “ALTER TABLE [order details] ADD CONSTRAINT PrimaryKey Primary Key(ProductID);”

Thank you very much for your advice which i followed again sucessfully.
I wonder could you help me with a specific table called order details.
This table lies between the tables “orders” and the table “products”.
The two functions seem not to work together and mostly the link between products and order details is not realised, and i get only the link between orders and order details.
The table order details contains no primary key, although my colleagues say
i should have two primary keys, for orderid and for productid.Is it so?
My question is, how get i get both functions work?

Public Function LinkProductsAndOrderDetails()
Set rel = db.CreateRelation(“CustomerIDRelationship”, “products”, “order details”, dbRelationUpdateCascade + dbRelationDeleteCascade)
rel.Fields.Append rel.CreateField(“ProductID”)
rel.Fields!Productid.ForeignName = “ProductID”
‘ Append relation
db.Relations.Append rel
End Function
Public Function LinkOrdersAndOrderDetails()
Set rel = db.CreateRelation(“CustomerIDRelationship”, “orders”, “order details”, dbRelationUpdateCascade + dbRelationDeleteCascade)
rel.Fields.Append rel.CreateField(“OrderID”)
rel.Fields!Orderid.ForeignName = “OrderID”
‘ Append relation
db.Relations.Append rel
End Function

Thank you very much for your advice which i followed again sucessfully.
I wonder could you help me with a specific table called order details.
This table lies between the tables “orders” and the table “products”.
The two functions seem not to work together and mostly the link between products and order details is not realised, and i get only the link between orders and order details.
The table order details contains no primary key, although my colleagues say
i should have two primary keys, for orderid and for productid.Is it so?
My question is, how get i get both functions work?

Public Function LinkProductsAndOrderDetails()
Set rel = db.CreateRelation(“CustomerIDRelationship”, “products”, “order details”, dbRelationUpdateCascade + dbRelationDeleteCascade)
rel.Fields.Append rel.CreateField(“ProductID”)
rel.Fields!Productid.ForeignName = “ProductID”
‘ Append relation
db.Relations.Append rel
End Function
Public Function LinkOrdersAndOrderDetails()
Set rel = db.CreateRelation(“CustomerIDRelationship”, “orders”, “order details”, dbRelationUpdateCascade + dbRelationDeleteCascade)
rel.Fields.Append rel.CreateField(“OrderID”)
rel.Fields!Orderid.ForeignName = “OrderID”
‘ Append relation
db.Relations.Append rel
End Function

I am very happy and successful with my new function.Actually i use it for remote database and not for the current.
I wonder could i further simplify it. I use different functions for relating different tables.I see that i always repeat dimming the db , the password,
and i always open the database in one and the same place, named by me as Bepath.
Is it possible to refer to these as constant or as a separate function,
and then to remain only the settings of the relations?

Below is my whole function

On Error Resume Next
Dim db As DAO.Database
Dim wsp As DAO.Workspace
Dim StrPassword As String
StrPassword = “classified”
Set wsp = DAO.DBEngine.Workspaces(0)
Dim rel As DAO.Relation
Set db = wsp.OpenDatabase(BEpath, False, False, “;PWD=” & StrPassword)

Note : i repeat the above lines in all my other functions

‘ Create one relation with attibutes added together
Set rel = db.CreateRelation(“ClientIDRelationship”, “TblClients”, “CallsClients”, dbRelationUpdateCascade + dbRelationDeleteCascade)
‘Set join fields
rel.Fields.Append rel.CreateField(“ClientID”)
rel.Fields!Clientid.ForeignName = “ClientID”
‘ Append relation
db.Relations.Append rel
‘ Clean up
Set rel = Nothing
Set db = Nothing
End Function

I am very happy and successful with my new function.Actually i use it for remote database and not for the current.
I wonder could i further simplify it. I use different functions for relating different tables.I see that i always repeat dimming the db , the password,
and i always open the database in one and the same place, named by me as Bepath.
Is it possible to refer to these as constant or as a separate function,
and then to remain only the settings of the relations?

Below is my whole function

On Error Resume Next
Dim db As DAO.Database
Dim wsp As DAO.Workspace
Dim StrPassword As String
StrPassword = “classified”
Set wsp = DAO.DBEngine.Workspaces(0)
Dim rel As DAO.Relation
Set db = wsp.OpenDatabase(BEpath, False, False, “;PWD=” & StrPassword)

Note : i repeat the above lines in all my other functions

‘ Create one relation with attibutes added together
Set rel = db.CreateRelation(“ClientIDRelationship”, “TblClients”, “CallsClients”, dbRelationUpdateCascade + dbRelationDeleteCascade)
‘Set join fields
rel.Fields.Append rel.CreateField(“ClientID”)
rel.Fields!Clientid.ForeignName = “ClientID”
‘ Append relation
db.Relations.Append rel
‘ Clean up
Set rel = Nothing
Set db = Nothing
End Function

Many thanks. Just to let you know that my db works perfectly now ! Vey grateful about that.

Many thanks. Just to let you know that my db works perfectly now ! Vey grateful about that.

i beg to be apologized.Actually i wanted to open the form F2 only through the form main, and not to allow the form F2 to be opened
by itself. Is there any simple method to do it ?Maybe to put something in the record source ?

Best regards

i beg to be apologized.Actually i wanted to open the form F2 only through the form main, and not to allow the form F2 to be opened
by itself. Is there any simple method to do it ?Maybe to put something in the record source ?

Best regards

Thank you so much for your detailed reply. I know that the fault is mine.I didnt explain in full what i need.
I still cannot make my report working.In the Sorting and Grouping window
i have put:
SumOfLiters – descending
ProductID – ascending

The report so obtained give me a nice view for the totals of all products. However, it does not list the products according to the the total quantity
sold. This quantity is not SumOfLiters,but the sum of it.to be found in the ProductID footer and is :
=Sum([SumOfliters])

Therefore, what i want is to arrange my products in descending order
not according their SumOfLiters, but according to their =Sum([SumOfliters])
which is in the report footer and giving summarized quantity of the each product.
Is it possible to put that =Sum([SumOfliters]) in the Sorting and Grouping window?.
I have tried, but it says that i have an extra bracket which is not true

Thank you so much for your detailed reply. I know that the fault is mine.I didnt explain in full what i need.
I still cannot make my report working.In the Sorting and Grouping window
i have put:
SumOfLiters – descending
ProductID – ascending

The report so obtained give me a nice view for the totals of all products. However, it does not list the products according to the the total quantity
sold. This quantity is not SumOfLiters,but the sum of it.to be found in the ProductID footer and is :
=Sum([SumOfliters])

Therefore, what i want is to arrange my products in descending order
not according their SumOfLiters, but according to their =Sum([SumOfliters])
which is in the report footer and giving summarized quantity of the each product.
Is it possible to put that =Sum([SumOfliters]) in the Sorting and Grouping window?.
I have tried, but it says that i have an extra bracket which is not true

I do not know, you may be right,but it works with updating and also in all other cases. I cannot explain myself why my command does not delete.Also,my command does not work or delete with a linked table even in the current database. It works only when the table is imported and not linked. Why is it so, and how can i remedy it?

Best Regards