Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Continuos form buttons (MS ACCESS 2003)

    I am trying to use a continuos form as a 2 column listbox, Name and Surname. The form has the two bound fields and works okay. However is there a way to substitute the text boxes with command buttons, having the data in their captions. Its to allow a user to pick either the full name or a surname in a queried recordset. Many thanks

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

    Re: Continuos form buttons (MS ACCESS 2003)

    No, that wouldn't work - if you change the caption of a command button in a continuous form, it changes in all displayed records, not just in the relevant record.

    You can simulate it:

    1) Set the properties of the text boxes as follows:
    - Border style: Transparent
    - Back color: -2147483633 (this is the "button face" color), or the same as the detail section background.
    - Enabled: No
    - Locked: Yes

    2) Place a label on top of each of the text boxes, with the following properties:
    - Caption: blank (nothing, empty)
    - Border style: Raised
    - Back style: Transparent
    - Use the On Click event of the label to execute the code that you want.
    Attached Images Attached Images
    • File Type: png x.png (2.2 KB, 0 views)

  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Continuos form buttons (MS ACCESS 2003)

    Many thanks Hans. I did it on my form and it looks good. However I am stuck on how I extract the data on label click from the underlying bound textboxes? One holding a name the other surname. User will only pick one of the two at a time. Regards

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

    Re: Continuos form buttons (MS ACCESS 2003)

    You should use a separate label for each text box, so you know which label belongs to which text box:

    Private Sub lblName_Click()
    ' Do something with the value of the Name text box
    MsgBox Me.txtName
    End Sub

    Private Sub lblSurname_Click()
    ' Do something with the value of the Surname text box
    MsgBox Me.txtSurname
    End Sub

  5. #5
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Continuos form buttons (MS ACCESS 2003)

    Many, Many thanks. I had not tried that as originally I was trying to do the same to get the text in a command buttons caption but failed. Have a good weekend and take a break. Best regards

  6. #6
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Continuos form buttons (MS ACCESS 2003)

    Trying to string convert this query so that the names are converted to proper case, ie Capital starts.

    Thanks

    SELECT AthleteNames.Athlete, Mid([Athlete],InStrRev([Athlete]," ")+1) AS Expr1
    FROM AthleteNames
    WHERE (((AthleteNames.Athlete) Like [FORMS]![Memos].[UU].[CAPTION] & "*"))
    ORDER BY AthleteNames.Athlete;

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

    Re: Continuos form buttons (MS ACCESS 2003)

    Try using

    SELECT StrConv([Athlete], 3), StrConv(Mid([Athlete], InStr([Athlete], " ")+1), 3)

    The StrConv function with 3 as second argument capitalizes the first letter of each word, i.e. "james brown" becomes "James Brown". It doesn't handle special cases, for example "macdonald" becomes "Macdonald" instead of "MacDonald". If you need the latter, you need custom VBA coding - see the thread starting at <post:=309,433>post 309,433</post:>.

  8. #8
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Continuos form buttons (MS ACCESS 2003)

    Thankyou Hans, perfect once again. Best regards

  9. #9
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Continuos form buttons (MS ACCESS 2003)

    I have a problem. When I tested the label click it appeared to work, however I now find it works only on the first two label/buttons in the list. Others down the list bring up the wrong data? Nothings easy hey!

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

    Re: Continuos form buttons (MS ACCESS 2003)

    I see that now. The problem appears to be that clicking on the label doesn't activate the record that contains the label. So this won't work either.

    So you'll have to do something else. How about placing a small command button next to each of the text boxes? Clicking a command button does make the record containing it the current record.

  11. #11
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Continuos form buttons (MS ACCESS 2003)

    Hi Hans. I took the easy way out and continued with two listboxes, one for the full name the other for surnames. Locking the lists with scroll bars is not an issue as the user justs types in text until the name appears in the list for selection. However there will be a need in another part of the database for your idea, and yes I will put in buttons as you suggest. So thanks for your efforts, not wasted. 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
  •