The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
|
|
Adding records to a grid not in the db
Posted: 03 May 07 11:41 AM
|
fiogf49gjkf0d Hello all,
I am attempting to create a datagrid that contain info from a one to many plus 4-5 records at the bottom of the grid. I have started by trying to add one row.
I have a grid bound to the one to many table then am using logic like this to add the additional rows:
dim rs
Set rs = dgExpenses.Recordset
rs.addnew
rs.Fields("ICS_ExpenseDescription").Value = "Structuring Fee" 'DNL
rs.Fields("ICS_ExpenseAmountReimb").Value = "1234" 'DNL
Set dgExpenses.Recordset = rs
set rs=nothing
dgExpenses.refresh
Currently I have code firing on the form onchange event - . I am getting the new row into the grid, but in various tries am getting results like the recordset being added again or the extra line being added again when scrolling to a new record or refreshing.
I have tried creating the grid from an sql statment, compairing recordcounts and only adding the extra record if the are less than equal plus some other things that didn't work for one reason or another. My problem here was that when I would scroll to the next record, the rs.recordcount would not clear out and the data from the previous screen would show.
I feel like I have been coming close, but just can stick the dismount.. Does anyone have advice on how to accomplish this? |
|
|
|
Re: Adding records to a grid not in the db
Posted: 03 May 07 1:28 PM
|
fiogf49gjkf0d If you want to display data in a grid that doesn't exist in the database you'll need to create a new disconnected recordset, put your data there and then bind that to the grid.
You could either create teh recordset from scratch, or disconnect the one the grid is currently connected to (by setting it's ActiveConnection property to nothing).
Does that make sense? |
|
|
|
Re: Adding records to a grid not in the db
Posted: 03 May 07 1:40 PM
|
fiogf49gjkf0d Yes, makes perfect sense - similar to what I have been trying to accomplish. Are there any code samples showing this or screens anyone knows of in SLX doing this I could peek at? I am not getting something right.. I also will try setting the ActiveConnection to nothing - I have not tried that. Thanks |
|
|
|
Re: Adding records to a grid not in the db
Posted: 03 May 07 1:46 PM
|
fiogf49gjkf0d I do know there are a few places in SLX that does this, I just can't remember where and who knows how readable they are anyway. I think opp products, manage products, price programs (on the edit product screen) all work this way.
I came accross this handy code for creating a new empty recordset from an existing one: http://www.devx.com/vb2themax/Tip/19114
But, really, if you just take the recordset and set it's activeconnection to nothing that should do it I believe. |
|
|
|
Re: Adding records to a grid not in the db
Posted: 03 May 07 9:30 PM
|
fiogf49gjkf0d Ok, giving it another try.. Couldn't get the syntax of the 'set DataGrid1.activeconnection=nothing' correct - throws an error. Not sure exactly where to put in the code below either.
The code works great the first time it fires - exactly what I need. When refreshed (F5) or scroll to the next record I get "Object Required:rs" at this line: < For Each fld In rs.Fields >
Any thoughts? The sub is being fired on the OnChange event of the form. Thanks
Sub DynamicGrid(Sender)
Dim rs
Dim col
Dim fld
Dim i
Dim sICSid
sICSid = Application.MainViews.ActiveView.CurrentID
With DataGrid1
' Remove any existing columns
If (.Columns.Count > 0) Then
For i = 0 To .Columns.Count - 1
.Columns.Item(0).Delete
Next
End If
' Add new SQL and refresh so grid is bound
.SQL.Text = "select ICS_ExpenseDescription, ICS_ExpenseAmountnonreimb, ICS_ExpenseAmountReimb from ics_expenses where ICS_detailID='" & sICSid & "' "
.Refresh
' Pull the recordset from grid to determine fields in query
Set rs = .Recordset
For Each fld In rs.Fields
Set col = .Columns.Add(0)
col.FieldName = fld.Name
col.Caption = fld.Name
' If field ends in "ID" then assume that field is hidden
If Right(LCase(fld.Name), 2) = "id" Then col.Visible = False
Next
.ReadOnly = True
.RowSelect = True
.Refresh
End With
set rs=nothing
dim rs2
Set rs2 = DataGrid1.Recordset
rs2.addnew
rs2.Fields("ICS_ExpenseDescription").Value = "Structuring Fee" 'DNL
rs2.Fields("ICS_ExpenseAmountReimb").Value = "1234" 'DNL
Set DataGrid1.Recordset = rs2
set rs2=nothing
DataGrid1.refresh
End Sub
|
|
|
|
Re: Adding records to a grid not in the db
Posted: 07 May 07 3:25 PM
|
fiogf49gjkf0d *Broken record* The datagrid's .recordset property only stores CHANGES, i.e. a diff between what has changed via an Edit or Add and it's internal structure that has all of the records. The only way to "synchronize" the internal structure with .recordset is to set sortable = true (or load the entire recordset into memory).
Whenever I see something like set rs = DataGrid.Recordset, what I mentioned above immediately pops in my head. It may not cause problems now but I can pretty much guarantee that eventually you'll run into an issue where you're thinking you're working off enough data when in actuality you may only be dealing with a single record in the recordset. |
|
|
|
Re: Adding records to a grid not in the db
Posted: 07 May 07 3:45 PM
|
fiogf49gjkf0d Originally posted by Jeremy Brayton
*Broken record* The datagrid's .recordset property only stores CHANGES, i.e. a diff between what has changed via an Edit or Add and it's internal structure that has all of the records. The only way to "synchronize" the internal structure with .recordset is to set sortable = true (or load the entire recordset into memory).
Whenever I see something like set rs = DataGrid.Recordset, what I mentioned above immediately pops in my head. It may not cause problems now but I can pretty much guarantee that eventually you'll run into an issue where you're thinking you're working off enough data when in actuality you may only be dealing with a single record in the recordset. |
|
Not sure I follow what you mean Jeremy. The DataGrid's Recordset property *is* the data the grid is bound to. |
|
|
|
Re: Adding records to a grid not in the db
Posted: 07 May 07 4:23 PM
|
fiogf49gjkf0d This mainly applies to child grids. Not a grid on the Account Form ("tab"), one on the Data Form tied to the table. When you first open the data form, the recordset property is properly synchronized with the internal structure. Proceed to add, edit, and delete records from the child grid then loop through the recordset. Instead of the entire recordset, you are going to get a diff between the last change(s) and the internal structure, unless sortable = true. A quick way to verify this is to add a MsgBox Recordset.Count call during the OnAdd, OnEdit, and OnDelete events and watch the behavior.
I initially found this in a 6.1 customization that created ListView items at runtime based on a grid's recordset property. Initially the listview would be perfect but the second I made a change to the grid it would display partial results. Not only was the record I just manipulated in the recordset, a partial list of X other records were included as well, making it difficult for me to discern a pattern.
I believe that if this were an Account "tab" grid, the effects aren't quite as black and white. I suspect the problem would come up but because the Details form changes it's bindings entirely during OnChange, the grid's recordset and internal structure most likely sync during every binding operation. |
|
|
|
Re: Adding records to a grid not in the db
Posted: 11 May 07 11:54 AM
|
fiogf49gjkf0d Is it possible that you need to OPEN the new rs after you have altered it..
like:
rs2.Open
Set Datagrid1.recordset = rs2
Just a thought |
|
|
|