Replies

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

Thank you for your advices.I think i owe you an appreciation for the advices you have given.After your reply i have made a code
that really works.it is the following
Me![CmdDeleteInvoice].Caption = ” Invoice number ” & Forms![FOrderInformation]!.Value & ” has been updated.”

And there the number of the invoice can be seen.I want to that you for that.

regards

Thank you for your advices.I think i owe you an appreciation for the advices you have given.After your reply i have made a code
that really works.it is the following
Me![CmdDeleteInvoice].Caption = ” Invoice number ” & Forms![FOrderInformation]!.Value & ” has been updated.”

And there the number of the invoice can be seen.I want to that you for that.

regards

Dear Hans,

Thank you very much for your reply. I have used your code and you are right, i receive no error, but again the problem persists and the combo box customerid appears not in a new record, but with the number of the customer whose order i have opened. Once again, how i proceed. First, i choose an order from the list box,then i click the label above the list box with the following event:

Dim stLinkCriteria As String
Dim stDocName As String
stDocName = “Invoice”
stLinkCriteria = “orderid = ” & Me!.Requery

However in that case the form is not clear.In order to be clear the form i have to close it manually, or click on the control with the following code :

DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Customerid.SetFocus

But why this code is no longer valid in my first code? By the way, in the OnOpen event of the form i also have the same code:

DoCmd.GoToRecord , , acNewRec
Customerid.SetFocus

My question is, is there any way to make the combo box customerID be on a new record.Maybe the fault is with the list box called ListOrders. But again, the Command ListBox.Value = Nothing
gives no result. Maybe the form is not closed properly in the OnClose event of the report ?

Best Regards

Dear Hans,

Thank you very much for your reply. I have used your code and you are right, i receive no error, but again the problem persists and the combo box customerid appears not in a new record, but with the number of the customer whose order i have opened. Once again, how i proceed. First, i choose an order from the list box,then i click the label above the list box with the following event:

Dim stLinkCriteria As String
Dim stDocName As String
stDocName = “Invoice”
stLinkCriteria = “orderid = ” & Me!.Requery

However in that case the form is not clear.In order to be clear the form i have to close it manually, or click on the control with the following code :

DoCmd.GoToRecord acDataForm, Me.Name, acNewRec
Customerid.SetFocus

But why this code is no longer valid in my first code? By the way, in the OnOpen event of the form i also have the same code:

DoCmd.GoToRecord , , acNewRec
Customerid.SetFocus

My question is, is there any way to make the combo box customerID be on a new record.Maybe the fault is with the list box called ListOrders. But again, the Command ListBox.Value = Nothing
gives no result. Maybe the form is not closed properly in the OnClose event of the report ?

Best Regards

Dear Hans,

A few words of what i have done.today I have built a control on the form with the following code in the OnClick event:

DoCmd.GoToRecord , , acNewRec
Customerid.SetFocus

It works, the combo box CustomerID is cleared.But i want to do it automatically,on opening the report,and not by clikcing on a control.

However,if i put these lines in my function, i receive
Runtime error 2499 and the message “You can’t use the GoTorecord method in Design view

Without the 2 lines of code the form is being opened and stays with customer under question. It is not clear.
With these 2 lines, as shown below,i receive the message that i cant use the GoTo method :

Dim stLinkCriteria As String
Dim stDocName As String
stDocName = “Invoice”
stLinkCriteria = “orderid = ” & Me!
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
DoCmd.GoToRecord , , acNewRec
Customerid.SetFocus

Thank your for yor reply and concern indeed.
CustomerId is a combo box.Each time the form opens,it is opened with a new orderid.Then the next step is to choose a customer from the combo box
CustomerID.On that same form i have a list box to view the already given orders. When i choose an order from the list box, i open the report Invoice.
On closing the report invoice and opening the form again, i receive this problem. But the problem aqppear onlky when i use the list box, otherwise
when i issue a normal order, everything is ok and the form opens normally.So i think i need a way to clear the form from my choice of an order from the list box
The list box is called Listrders.

Best regards

Thank your for yor reply and concern indeed.
CustomerId is a combo box.Each time the form opens,it is opened with a new orderid.Then the next step is to choose a customer from the combo box
CustomerID.On that same form i have a list box to view the already given orders. When i choose an order from the list box, i open the report Invoice.
On closing the report invoice and opening the form again, i receive this problem. But the problem aqppear onlky when i use the list box, otherwise
when i issue a normal order, everything is ok and the form opens normally.So i think i need a way to clear the form from my choice of an order from the list box
The list box is called Listrders.

Best regards

Thank you for your reply.

The Control Source of the form is :

SELECT DISTINCTROW Orders.orderid, Orders.customerid,

Orders.orderdate, Orders.[required date], Orders.freigth,

Customers.CompanyName, Customers.employee,

Customers.address, Customers.city, Customers.taxid, Orders.PaymentMethodID, Orders.bankid, Bank.bankname, Bank.account, Bank.affiliate, Bank.bankaddress, Bank.bankcity, Customers.kindid, Orders.AuftragNr, Customers.afid, Orders.paymentid, Orders.invoicedate FROM Customers INNER JOIN (Bank INNER JOIN Orders ON Bank.bankid = Orders.bankid) ON Customers.Customerid = Orders.customerid;

The Row Source of the CustomerID is :
SELECT customers.* FROM customers WHERE (((customers.afid)=1)) ORDER BY customers.CompanyName;

Best regards

Thank you for your reply.

The Control Source of the form is :

SELECT DISTINCTROW Orders.orderid, Orders.customerid,

Orders.orderdate, Orders.[required date], Orders.freigth,

Customers.CompanyName, Customers.employee,

Customers.address, Customers.city, Customers.taxid, Orders.PaymentMethodID, Orders.bankid, Bank.bankname, Bank.account, Bank.affiliate, Bank.bankaddress, Bank.bankcity, Customers.kindid, Orders.AuftragNr, Customers.afid, Orders.paymentid, Orders.invoicedate FROM Customers INNER JOIN (Bank INNER JOIN Orders ON Bank.bankid = Orders.bankid) ON Customers.Customerid = Orders.customerid;

The Row Source of the CustomerID is :
SELECT customers.* FROM customers WHERE (((customers.afid)=1)) ORDER BY customers.CompanyName;

Best regards

Thank you very much for your reply.I receive the following message

invalid use of ‘.’,’!, or ‘(), in query expression

‘T.[customerid]=customers1].[customerid]’.

I cal the function in the following way :

AppendToTable “customers1”, “customers”, “customerID”

May be the wau i call the function is not right ?

Thank you in advance for your reply

Best regards

Thank you so much indeed !!!!!!!!!!!!!!!!

Thank you so much for your kind reply.It is true that the field CallId is not updated.I The two tables are absolutely identical in a sense that
the source table and the target table contain the same fields.Having this in mind, that the Callid is not updated, and that the two
tables contain the same fields, can you recommend a formula for updating the fields, or may be this is not possible ?

Best regards

I feel obliged to let you know that the code suggested by you works now!! I did not cooy your suggestion in the right way
but i have copied again and it is excellent !!!!!!!!!!!

Best regards

Thank you very much indeed for your reply.I have tried your suggestion but i must have missed something since i get the Run Time error 91 namely ” “Object Variable or With block variable not set “. Could you check up my function again to find where my error is ?
Here is the function:
Function DeleteAll()
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim i As Integer
Set dbs = CurrentDb

‘ Loop backwards through relations
For i = dbs.Relations.Count – 1 To 0 Step -1
dbs.Relations.Delete dbs.Relations(i).Name
Next i

‘ Loop backwards through tabledefs
For i = dbs.TableDefs.Count – 1 To 0 Step -1
If Left(tdf.Name, 4) “MSys” Then
dbs.TableDefs.Delete dbs.TableDefs(i).Name
End If
Next i

Set dbs = Nothing
End Function

Best Regards