Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form Design (A2K SR1)

    I am trying to design a form that is used to determine the amount of a deposit refund. I want to
    have a series of check boxes for the operator to check if certain charges are to be assessed.

    Amount
    Trash not removed $100.00
    Microwave not cleaned $15.00

    Total $115.00




    The Trash Amount field would have an conditional If Trash check box is checked then Amount
    = $100 else $0.

    The Microwave Amount field would have an conditional If Microwave check box is checked
    then Amount = $15 else $0.

    There will be 10-12 check boxes.

    The total assumes that both boxes were checked.

    I created a form that has check boxes from the Tool Box with Text boxes with the description
    and unbound boxes for the amounts. How do I tie the check boxes to the amounts.

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

    Re: Form Design (A2K SR1)

    Use the AfterUpdate event of each of the CheckBoxes to set the value in the related textbox.
    Charlotte

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Design (A2K SR1)

    As per your Suggestion, I used an After Update event.
    I created the following:
    Private Sub NoticeToggle_After Update()
    If [NoticeToggle] = -1 then [Notice] = 100
    If NoticeToggle] = 0 then [Notice] = 0
    End Sub

    This worked fine to populate the text box with 100 when checked and 0 when unchecked.

    Since I wanted to be able to get a sum of a number of the text boxes, I tried
    Private Sub NoticeToggle_After Update()
    Dim Notice1 as Currency
    If [NoticeToggle] = -1 then Notice1 = 100
    If NoticeToggle] = 0 then Notice1 = 0
    End Sub

    Also tried If [NoticeToggle] = -1 then Notice1 = 100 else Notice1 = 0 (All on 1 line)
    If I put an End If on the line following the If statement, I got a error message End If without
    block If

    Then in the Control Source of the Notice text box, I put Notice1. The result was a #Name?

    Any suggestions on where I went wrong?

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

    Re: Form Design (A2K SR1)

    Do you mean a sum or a *count* of the number of checkboxes? I don't understand what a "sum of the number of checkboxes" might be.

    In any event, you can't use a variable as a controlsource as you discovered. You can set the textbox directly by substituting the textbox for Notice1 in your code. You don't need the variable at all. You don't use an end if in single line statements like that. I don't like in-line IF statements because I don't think they're as clear as IF-END IF constructions If you're going to introduce an ELSE condition, don't do it in-line. It may work but it can be hard to read.
    Charlotte

  5. #5
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Design (A2K SR1)

    What I am trying to do is get a sum
    Amount Box
    Check box 1 $100
    Check box 2 $15
    Check box 3 $25
    Check box 4 $0

    Total $140

    Thus, if the first 3 boxes are checked and dollar amounts are shown and the 4th box is uncheck so it amount box is 0, I want the total to be $140.00.

    The mesages got reformatted. The Amount box was to be over the dollars Amounts. The dollars amounts are in seperated boxes from the check boxes. See original post.

    Tom

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Form Design (A2K SR1)

    Try something like this in the after update event of the toggle:

    If Toggle1 = -1 Then
    Me.TextBoxName.Value = 100
    Else: Me.TextBoxName.Value = 0
    End If

    Response was edited as follows:

    You could then add a textbox that adds all of the values by putting the equation =[TextBox1]+[TextBox2]+[TextBoxN] in the control source of the textobx.



    HTH
    Regards,

    Gary
    (It's been a while!)

  7. #7
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Design (A2K SR1)

    Many thanks for the code. It works perfectly.

    I do have one more problem:

    This frmSecurityDepositRefund, pulls the name, address, telephone number etc from
    qrySecurityDeposit. This data is automatically entered on the form. I have a check box
    AltNameCheckBox on the form. Is it possible to have these field blanked out if the
    AltNameCheckBox is checked? I don

  8. #8
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Form Design (A2K SR1)

    I would not recommend "blanking out" fields when alt name, etc are entered, when you save record the data in the "blanked out" fields will be wiped out too. I'd recommend toggling field control properties based on the alt name checkbox value. Sample code shows one way to do this:

    <pre>Private Sub chkAltName_AfterUpdate()
    On Error GoTo Err_Handler

    Dim n As Integer
    Dim lngBackColor As Long
    Dim ctl(1 To 6) As Access.Control

    Set ctl(1) = Me.txtAddress
    Set ctl(2) = Me.txtCity
    Set ctl(3) = Me.txtRegion
    Set ctl(4) = Me.txtPostalCode
    Set ctl(5) = Me.txtCountry
    Set ctl(6) = Me.txtPhone

    With Me
    If .chkAltName = True Then
    lngBackColor = 12632256 'lt grey
    Else
    lngBackColor = 16777215 'white
    End If

    For n = 1 To 6
    ctl(n).Locked = .chkAltName
    ctl(n).Enabled = Not .chkAltName
    ctl(n).BackColor = lngBackColor
    Next n
    End With

    Exit_sub:
    For n = 1 To 6
    Set ctl(n) = Nothing
    Next n
    Exit Sub

    Err_Handler:
    MsgBox "ERROR: " & Err.Number & ": " & Err.Description, _
    vbExclamation, "CHK ALT UPDATE ERROR"
    Resume Exit_sub

    End Sub</pre>

    In this example checkbox is named chkAltName. When checkbox is checked (value = true) the six textboxes are locked, un-enabled, and background changed to light grey to indicate visually that they are disabled while alt name data is entered. When checkbox unchecked (false) the textboxes toggle back to default settings (unlocked, enabled, white background). Sometimes you see the "For each ctl in Controls" approach for this type of thing, using control's tag property, but I prefer using bogus control array, the other approach can be slow if form is complex with many controls.

    Code can be modified as necessary if different property settings are desired.

    HTH

  9. #9
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Design (A2K SR1)

    I was successful in adaptng your code to my situation. Many Thanks.

    On addition question.
    When I close the frmSecurityDepositRefund, I want to post the value in NetDepositAmt to the filed in tblRental called DepositRefund. The source of the data in frmSecurityDepositRefund is qrySecurityDeposit which has tblRental as a source of date.

    I was trying to use an Onclose event on frmSecurityDeposit.

    Me.DepositRefund = Me.NetDepositAmt.Value

    It doesn't like Me.DepositRefund. The error message is Method or data member not found.

  10. #10
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Form Design (A2K SR1)

    Where you say, "I was trying to use an Onclose event on frmSecurityDeposit." did you mean frmSecurityDepositRefund?? In other words, there is just one form involved here, right?? Next question: Is the DepositRefund field included in the columns returned by the qrySecurityDeposit query? Even if tblRental is source for query, the field must be included in the query's output fields. Assuming it is, is there a control on form for this field? All fields in the form's Record Source will appear as properties of the form (as when you type "Me." in code window) but you will not be able to assign a value to the field unless there is a control on the form bound to that field. For example, if you have field named "Rent" and control on form bound to this field named "txtTent", "Me.Rent = $2000.00" will not work, but "Me.txtRent = $2000.00" will work. If field and control are both named "Rent" then "Me.Rent = $2000.00" will work. If this is a field that you don't want to be updated "manually" by user, use a hidden control.

    The error message you are getting (Method or data member not found) occurs if the field you are referencing is not included in the underlying recordset, OR field is included but there is no control of the name you are referencing.

    One final note, after you fix problem with missing field/missing control, do NOT recommend using form's Close event to try to assign a value to a control. If you do, you will get a run-time error, "You cannot assign a value to this object." Use the form's Unload event (which fires before the form's Deactivate & Close events) to update the field/control value.

    HTH

  11. #11
    4 Star Lounger
    Join Date
    May 2002
    Location
    Roanoke, Virginia, USA
    Posts
    493
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form Design (A2K SR1)

    Once again many thanks for getting me on the correct path.

    The problem was not having a control on the form. Everything is working correctly now.

    Tom

Posting Permissions

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