Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Form problem (2000)

    I have a form that has a field called ClNum. In the ClNum After update event is the following code:


    Private Sub ClNum_AfterUpdate()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    On Error GoTo Err_Handler

    'Only do this if user is on new record
    'If Me.NewRecord Then
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset _
    ("SELECT ClientName, FAContracts FROM tblClients WHERE ClNum='" & Me!ClNum & "'")


    'Did we get a match?
    If rst.RecordCount = 0 Then
    'No, we don't
    MsgBox "There is no matching record.", vbInformation
    Exit Sub
    Else
    'Yes we do
    'Copy some fields
    ClientName = rst!ClientName
    FAContracts = rst!FAContracts
    End If


    Exit_Handler:
    'Clean Up
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    Exit Sub

    Err_Handler:
    MsgBox Err.Description, vbExclamation
    Resume Exit_Handler
    End Sub

    After updating CLNUM, it populates a field called FAContracts.
    There is another field on the form (a combo box) called "Values" which gets its values from a table. In the table each value is assigned a number value.
    In a report I take the facontracts field and multiple it by the number value assigned to whatever value they pick.

    Now, I have been asked to add 3 more fields which are offshoots of the FAContract field. Lets call the fields - HSA contracts, HRA contracts, FSA contracts.
    These fields will be populated according to the CLNUM also.
    The drop down list will have values that only apply to these new fields.

    My problem is when they select a value pertaining to the three new fields, then I don't want the FAContract field to populate
    Same thing when they choose a value pertaining to the FAcontracts fields, I don't want the 3 new fields to populate.

    Is there someway to edit the code above to do this?
    I hope this isn't too confusing.

    Instead of adding additional values to the one value list, I could create a separate table that populates another drop down list that just pertains to the new fields.
    Any help would be appreciated.

    Thanks

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

    Re: Form problem (2000)

    I'm sorry, I fail to understand your description. You have a control named ClNum that updates FAContracts in its After Update event. You have another control named Values, a combo box from which the user selects a value. Why would that update FAContracts at all?

  3. #3
    Lounger
    Join Date
    Sep 2003
    Posts
    33
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Form problem (2000)

    I am not sure what to do. I have a form now that when clnum is filled in, Fa Contracts and CLient name are automatically populated. Another field on that form called values has a drop down list of selections that are assigned a number value in the table that populates this drop down list. This works fine when I use this data to create a report where I multiple the fa contracts (which is a number representing how many contracts) by the assigned number that goes with what value they pick in the drop down list. Say the value they select from dropdown list is "Demo with President" (This is assigned a .05 number for "Demo with President" in the table).

    Now they want me to add 3 new fields that are offshoots of FAcontracts. They have their own values and related number. Now when someone enters the CLNum field, they want these new fields to be populated too (which I will add to the table that has CLNUM, Client Name, FA contracts). My problem is how do I populate these new fields if a value that pertains to the new fields is picked from the value list without populating FA contracts and vice versa - if I pick a value that pertains to FA contracts - how do I populate just Fa contracts. In the query, I would add the three new values together and then multiple them by their respective number value or if just FA contracts - I would multiple that by its respective number but I can't do both in the same record.

    I am not clear about what I can do. I am open for suggestions. Right now the form is pretty straight forward - enter a clnum (client number), populate fa contracts, client name. Pick a value from a drop down list that is assigned a number value. In a query, multiple fa contracts by the number that is assigned to the value that was selected from the drop down list. The value in the dropdown list determines if the number multipler uses fa contracts or the other 3 contract fields but not both. I can either add new values to the same dropdown list that pertain to the three new fields or I can add another dropdown list that have values (with their respective number values). I know I am not explaining this very well.

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

    Re: Form problem (2000)

    I would use two combo boxes, one for the FA Contracts related values, and one for the other contracts related values. In the After Update event of each, clear the other, so that the user cannot select a value in both. By selecting a value from one of the combo boxes, the user specifies what kind of contract it is.

    You can't use the After Update event of ClNum or either of the combo boxes to populate the FA Contracts etc. fields, for you need both a ClNum and a value to decide what to do. Instead. use the Before Update event of the form as a whole for this. First check that ClNum is not empty, and check that the user didn't leave both combo boxes empty; cancel the update if necessary. Then, if the FA contracts related combo box is not null, propulate FA Contracts, else populate the other fields.

Posting Permissions

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