Continuos form buttons (MS ACCESS 2003)

[ZOR]

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

[HansV]

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.

[ZOR]

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

[HansV]

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

[ZOR]

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

[ZOR]

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;

[HansV]

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:>.

[ZOR]

Thankyou Hans, perfect once again. Best regards

[ZOR]

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!

[HansV]

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.

[ZOR]

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