Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Aug 2002
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    For each.... (Access 2000)

    For each...


    Can somebody help me with the for each code? On leaving the form, i want to set all the values for all the controls in the form to be value = 0.
    The purpose is to clear the from from the previous filters anc choices and make it ready for a new choice

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

    Re: For each.... (Access 2000)

    I assume that you mean controls such as text boxes and combo boxes; I doubt that you would want to set the caption of labels to 0.

    You can try something like this:

    Dim ctl As Control
    For Each ctl In Me.Controls
    Select Case ctl.ControlType
    Case acTextBox, acComboBox
    ctl.Value = 0
    Case Else
    ' Do nothing
    End Select
    Next ctl
    Set ctl = Nothing

    You can replace ct.Value = 0 by ctl.Value = Null if you want to clear the controls. You can add Case statements to handle other control type, for example check boxes (acCheckBox) and option groups (acOptionGroup).

  3. #3
    Lounger
    Join Date
    Aug 2002
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: For each.... (Access 2000)

    Thank you for your answer.I have an additional question regarding reusability.
    I wanted to alter your suggestion for clearing the form to make a general code
    calling it with ClearForm(Me) however i receive the message Type mismatch.
    May i know wjere is my error ?



    Public Function ClearForm(f As Form)
    ' Whenever i lleave the form ,to call this function as Clearform (Me)
    Dim f As Form
    Dim ctl As Control
    For Each ctl In f.Controls
    Select Case ctl.ControlType
    Case acTextBox, acComboBox
    ctl.Value = 0
    Case Else
    ' Do nothing
    End Select
    Next ctl
    Set ctl = Nothing
    End Function

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

    Re: For each.... (Access 2000)

    Since you already specify "f As Form" in the definition of the function, you shouldn't have "Dim f As Form" in the body of the function. Please remove that line.

    But you say you are calling this when you "leave" the form. Which event do you mean by this?

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: For each.... (Access 2000)

    Why don't you just clear it when it opens? That way it will always be ready for new choices. I don't see much point in clearing a form when it closes, especially if the form is unbound. In fact, you shouldn't *have* to clear unbound controls unless you have code that is saving form changes.
    Charlotte

  6. #6
    Lounger
    Join Date
    Aug 2002
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: For each.... (Access 2000)

    Thank you very much for your reply.I must have a funbdamental misate in my forms since i receive always the errors.

    In the OnClick event i again receive the message "Type mismatch":
    Public Function ClearForms(f As Form)
    ' in the OnClick event i write ClearForms(me)
    Dim ctl As Control
    For Each ctl In f.Controls
    Select Case ctl.ControlType
    Case acTextBox
    ctl.Value = Null
    Case acComboBox
    ctl.Value = Null
    Case (acOptionGroup)
    ctl.Value = Null
    Case Else
    ' Do nothing
    End Select
    Next ctl
    Set ctl = Nothing
    End Function


    In the OnClick event, when i do not use funcyion, i have the message
    "you cant assign a value to this subject.

    Private Sub Command573_Click()
    Dim ctl As Control
    For Each ctl In Me.Controls
    Select Case ctl.ControlType
    Case acTextBox
    ctl.Value = 0
    Case acComboBox
    ctl.Value = 0
    Case (acOptionGroup)
    ctl.Value = 0
    Case Else
    ' Do nothing
    End Select
    Next ctl
    Set ctl = Nothing

    End Sub

    Best regards

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

    Re: For each.... (Access 2000)

    It does *not* help to keep on repeating the same code. We have seen that already. It *would* help if you told us on which line you receive the error, and the name and type of the control that cause the error. You can find these by clicking "Debug" when the error occurs. A line in the code will be highlighted in yellow. Activate the Immediate window (Ctrl+G). Type
    ? ctl.Name
    and press Enter. You will see the name of the control. Next, type
    ? ctl.ControlType
    and press Enter.

    You haven't answered Charlotte's questions yet.

  8. #8
    Lounger
    Join Date
    Aug 2002
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: For each.... (Access 2000)

    Thank you for your advice that i do not need the code if i close the form.I wanted now to use the code in the OnClick event.
    I could not do it and now i will try to follow the advices of Hans and find out my error through debugging
    best regards

Posting Permissions

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