Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Clear the form (Access 2000)

    <P ID="edit" class=small>(Edited by HansV on 30-Aug-03 17:19. Removed unnecessary line breaks - the browser will take care of that.)</P>My problem is that after chosing an order from the list and open a report, the form stays with the number of the customer for that number and cannot be requeried.How can i clear the form? This is my problem and to be more clear i will give more details.

    I have a form called F1. On that form i have a listbox called ListOrders.When i select an order from the list, i open the report called "invoice" for that order number. My code is the following:

    Dim stLinkCriteria As String
    Dim stDocName As String
    stDocName = "Invoice"
    stLinkCriteria = "orderid = " & f![list].requery

    but again with no result. I cannot understand why after closing and opening the form again the form is not cleared from the previous results. If i close the form by hand, and open it, then everything is OK. Obviously it is my code somwehere that bothers me. Is there any magic code that will solve my problems?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clear the form (Access 2000)

    I don't understand the connection between ListOrders and the CustomerID control. Is your form F1 bound to a table (or query), and if so, is CustomerID bound to a field in the table?

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear the form (Access 2000)

    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

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clear the form (Access 2000)

    Thanks for the info. I gather that CustomerID is a list box or combo box. Is the Control Source property of this list box/combo box empty, or does it contain a field name?

  5. #5
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear the form (Access 2000)

    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

  6. #6
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear the form (Access 2000)

    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![list]
    DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria
    DoCmd.GoToRecord , , acNewRec
    Customerid.SetFocus

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clear the form (Access 2000)

    The problem is that the report has the focus when you execute DoCmd.GoToRecord , , acNewRec. But GoToRecord is not valid for a report. So you must specify that you want to apply it to the form:

    DoCmd.GoToRecord acDataForm, Me.Name, acNewRec

  8. #8
    2 Star Lounger
    Join Date
    Nov 2001
    Posts
    192
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Clear the form (Access 2000)

    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![list].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

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Clear the form (Access 2000)

    I'm sorry, I have no idea. I can't reproduce the error. Could you post a stripped-down version of the database?
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>Attach the zip file to a reply.[/list]This will enable Loungers to investigate the problem.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •