MSFLEXGRID and Dynamically Created Option Buttons 1

[mamartin]

I have a form that pops up when the user clicks on the Add button that displays which address this record should be associated with (current and any potential past addresses). I am using an MSFLEXGRID form to display the address information. I would like to add an option button as the first column for each row in the grid. The user would then click the button that corresponds to the correct address.

Can anyone help out with the code to dynamically create the option buttons based upon the number of addresses found for a given customer and then would be linked to the correct row the address? The following is a snippet of the code in the grid form:

Private Sub Form_Load()

'Or Error Got errhand:

blAddrFlag = False

Dim conn As New ADODB.Connection
Dim recset As New ADODB.Recordset

Dim Row_Number As Integer

'Set up format for headers and column attributes.
With msflxgrd_addr
.RowHeight(0) = 500
.WordWrap = True
.ColWidth(0) = 0
.ColWidth(1) = 1000
.ColWidth(2) = 1100
.ColWidth(3) = 1100
.ColWidth(4) = 5000
.ColAlignment(1) = 1
.ColAlignment(2) = 1
.ColAlignment(3) = 1
.ColAlignment(4) = 1
.TextMatrix(0, 1) = "Select Address"
.TextMatrix(0, 2) = "ASGN Date"
.TextMatrix(0, 3) = "TERM Date"
.TextMatrix(0, 4) = "Street Address"
End With

'Very lengthy SQL statement goes here to populate the record set.

recset.Open strSQL, conn, adOpenStatic, adLockReadOnly

If recset.RecordCount <= 0 Then
strMsg = "No Address Information Found"
MsgBox strMsg, vbExclamation
Else
msflxgrd_addr.Rows = recset.RecordCount + 1

recset.MoveFirst

Row_Number = 1

Do Until recset.EOF
'This is the bit that doesn't appear
to work.
With frm_flgrd_address
Load .opt_addr(Row_Number)
.opt_addr(Row_Number).Top =
.opt_addr(Row_Number - 1).Top + 500
.opt_addr(Row_Number).Visible = True
End With

If IsNull(recset.Fields("APT_NBR")) Then
strAddr = Trim(recset.Fields("ST_NBR")) & " " & _
Trim(recset.Fields("ST_NAME"))
Else
strAddr = Trim(recset.Fields("ST_NBR")) & " " & _
Trim(recset.Fields("ST_NAME")) & " " & _
Trim(recset.Fields("APT_NBR"))
End If

If Not IsNull(recset.Fields("CITY_NAME")) Then
strAddr = strAddr & ", " & Trim(recset.Fields("CITY_NAME"))
End If

If Not IsNull(recset.Fields("ZIP_CODE")) Then
strAddr = strAddr & ", " & Trim(recset.Fields("ZIP_CODE"))
End If

With msflxgrd_addr
.TextMatrix(Row_Number, 2) = Format(recset.Fields("ASGN_DATE"), "dd-mmm-yyyy")
.TextMatrix(Row_Number, 3) = Format(recset.Fields("TERM_DATE"), "dd-mmm-yyyy")
.TextMatrix(Row_Number, 4) = strAddr
End With

Row_Number = Row_Number + 1

recset.MoveNext
Loop

opt_addr(1).Value = True

End If

recset.Close
conn.Close

Set recset = Nothing
Set conn = Nothing


End Sub

Hope someone can show me the error of my ways.

Thanks,
Michael A. Martin

 

[zemp]

You can use floating controls, see faq222-3262.

Another option might be to simply use Yes/No text toggled with the click event. A third is to change the .cellpicture property and toggle two images with the click event.

zemp

 

[mamartin]

Yes, I found that document earlier. But I am not "tracking" when you mention "floating controls". Can you expand a bit on that, please?

Michael A. Martin

 

[zemp]

Basically you place a control on top of the grid, size it and set it's properties so that a user believes that the control is part of the grid. Usually you place it on a specific cell.

The purpose is to make a non-editable grid seem editable to the end user.

zemp

 

[mamartin]

I thought that was what I did. I created the grid form, created an option button (opt_addr), placed it on the form, sized so that it fits snugly in the first row/column cell, set the index property to 0 (creates a control array, correct?), then wrote the code behind it all. When I run my form, only the first option box shows up even if I return multiple addresses. I walked through the debugger and the code is being executed. It's just not creating/displaying the option button on the form. Do you have some code examples of option buttons integrated with a flex grid? There is something wrong with my code, just can't see what it is.

Thanks again,
Michael A. Martin

 

[zemp]

Post your relevant code.

zemp

 

[zemp]

Sorry, you did.

zemp

 

[zemp]

I think that your options buttons are being placed behind the grid. Try this,

           With frm_flgrd_address
                Load .opt_addr(Row_Number)
                .opt_addr(Row_Number).Top = 
                .opt_addr(Row_Number - 1).Top + 500
                [COLOR=red].opt_addr.ZOrder 0[/color]
                .opt_addr(Row_Number).Visible = True
            End With

The ZOrder should move the control to in front of the grid.


zemp

 

[mamartin]

Zemp,

the ZOrder did the trick.

Actual code is .opt_addr(Row_Number).ZOrder 0

Thank you very much,
Michael A. Martin