Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Whick Combo Box Did I Click? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I have Form with several Combo Box Controls and a Command Button

    I want to be able to click one of the Combo Box Controls on the Form then click the Command Button and in the Command Button On Click Event in VBA determine which one of the Combo Boxs I clicked.

    Thanks, John

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

    Re: Whick Combo Box Did I Click? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    The easiest way is to look at Screen.PreviousControl. Alternativelt, you could set a variable (defined at the top of the form's module) to the name of the combo box in the On Click event of each combo box, and inspect the value of the variable in the On Click event of the command button.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Whick Combo Box Did I Click? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Great, I

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

    Re: Whick Combo Box Did I Click? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    You can look at the ControlSource property of ctlPrevious. If it's blank, the control is unbound; if it starts with "=", it is a calculated control and otherwise, it is a bound control. You should have error handling to avoid problems if ctlPrevious is of a type that doesn't have a control source (such as a command button).

  5. #5
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Whick Combo Box Did I Click? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    So far so good, the following Sub works OK, however when the query is opened the field width is only about 12 characters wide.

    What can I change so the query width of the field is say 60 characters wide?

    Thanks, John

    <pre>Private Sub FixField_Click()

    On Error GoTo ErrorHandler

    Dim ctlPrevious As Control
    Set ctlPrevious = Screen.PreviousControl

    If ctlPrevious.ControlSource = "" Or ctlPrevious.ControlSource = "=" Then
    MsgBox "You must Click on a valid Field to view its contents before using " & _
    "this Button!"
    Exit Sub
    End If

    Set db = CurrentDb()

    If QueryExistsLoop("qry_Fix_RMS_tbl_RMS_Inventory_Aut o") = False Then
    Set qd = db.CreateQueryDef("qry_Fix_RMS_tbl_RMS_Inventory_A uto")
    End If

    qd.sql = "SELECT " & ctlPrevious.Name & " " _
    & "FROM tbl_RMS_Inventory " _
    & "WHERE (((" & ctlPrevious.Name & ") Is Not Null) " _
    & "AND ((strProfileCode)=GetPref('Profile Code'))) " _
    & "ORDER BY " & ctlPrevious.Name

    DoCmd.OpenQuery "qry_Fix_RMS_tbl_RMS_Inventory_Auto", acNormal, acEdit

    Set db = CurrentDb()
    If QueryExistsLoop("qry_Fix_RMS_tbl_RMS_Inventory_Aut o") = True Then
    db.QueryDefs.Delete ("qry_Fix_RMS_tbl_RMS_Inventory_Auto")
    End If

    ExitHandler:
    Exit Sub

    ErrorHandler:

    If Err.Number = 2483 Then 'run-time error, You can'tmove to a previous control
    'when only one control has had the focus
    MsgBox "You must Click on a valid Field to view its contents before using " & _
    "this Button! - RTE 2483"
    Exit Sub
    Else
    MsgBox str(Err.Number) & Err.Description
    Resume Next
    End If

    End Sub
    </pre>


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

    Re: Whick Combo Box Did I Click? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    I would never let an end user open a query directly; instead, I would create a (continuous) form, and set its Record Source to the SQL statement you generate.

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Whick Combo Box Did I Click? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Your right

    I replace the query with:

    <pre>gloSetValue "FieldName", ctlPrevious.Name
    DoCmd.OpenForm "frm_RMS_Inventory_FixField", acFormDS, , , , , Me.Name
    </pre>


    frm_RMS_Inventory_FixField (data sheet) looks like this:

    <pre>Private Sub Form_Open(Cancel As Integer)

    Me.Field1.ControlSource = gloGetValue("FieldName")

    End Sub
    </pre>


    It works OK

    When I

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

    Re: Whick Combo Box Did I Click? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    The easiest solution is to use the field name as caption for the label.

  9. #9
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Whick Combo Box Did I Click? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    That

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Whick Combo Box Did I Click? (a2k (9.0.6926) S

    When I want to associate a field name with a label I put the field name in the Tag property of the label.
    Regards
    John



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

    Re: Whick Combo Box Did I Click? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    You could use a Select Case statement with hard-coded captions, or DLookup on a table with field names and captions, or loop through the controls...

  12. #12
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Whick Combo Box Did I Click? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Hi John,
    Could you please post the code for the functions gloSetValue, gloGetValue and QueryExistsLoop you mentioned in the thread?

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

    Re: Whick Combo Box Did I Click? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    John posted the code for gloGetValue and gloSetValue in <post:=198,890>post 198,890</post:>.
    QueryExistsLoop is a renamed version of the first function from <post:=314,387>post 314,387</post:>.

  14. #14
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Whick Combo Box Did I Click? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Thank you Hans

  15. #15
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Whick Combo Box Did I Click? (a2k (9.0.6926) SP-3 Jet 4.0 SP-8)

    Hans

    I went the Dlookup route using the Dump Tables and Queries I already Posted <post#=366999>post 366999</post#> Re: Document tables (Access 2002 SP-2) modified to include the Caption property

    I have another appliciation that would work better by looping through the controls.

    Going back to <post#=588300>post 588300</post#> where and what code would I use to loop through the selected control to retrieve the caption?

    Thanks. John

Page 1 of 2 12 LastLast

Posting Permissions

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