DataGrid: Copying a current record as a New record

Here's a tough one. (At least I think so.)

I am using the DataGrid control with ADO. I have used the form wizard to create a form that has all those nifty buttons at the botton of the form (Add, Update, Delete, Cancel.etc). This is what I would like to accomplish. When the user clicks the 'Add' button on the form with the Datagrid, I display a message asking whether or not they would like to copy an existing record (as a template). If they select Yes, I display a form that has a list box where they can choose which record to copy from.

My question is this: How do I copy the current record that I am at from one form as the new record on the other form(the one with the DataGrid)? One other thing, I have a text box next to the list where they can give the new name of the record(so the key will be unique). How do I pass this with the copied record?

I know this is alot to digest and I'm sure I have left something out. Any suggestions?

I do something similar to this in one of my programs. when the user selects the rows to use as a template, since it's on another form and usually my recordset is closed by then, i just go to the database tables and get that row again to populate the second form. Its plenty fast for me anyway. Then once they have entered the needed information, i insert that row straight to the table (after validating the info, of course) and the either refresh the recordset on the first form or manually add a row to the grid via code.

Let me know if you would like some code samples, i dont have them right here, but can get them in a jiffy, should you need them.

Hope this helps,
John

John Pirkey
MCSD
www.ShallowWaterSystems.com

That sounds like what I am looking for. If you could get me the samples, that would be great. I really appreciate it.

Sure, let me warn you though, this code was taken from a program that uses a listview instead of a grid, so...


'in the main form...
private Sub cmdAdd_Click()
If MsgBox("Use an existing row as template?", vbYesNo + vbQuestion,"Add new") = vbYes then
frmTemplateRow.Show
End If
End Sub

'in the second form there is a combo box with the unique keys listed called cboKey
'there is a global ado connection object called gCN that's open.
private Sub cboKey_Click()
dim sql as string
dim rs as adodb.recordset

sql = "SELECT * FROM Customers (NOLOCK) "
sql = sql & "WHERE CustName = '" & cboKey.Text & "'"

set rs = new adodb.recordset

set rs = gCN.execute(sql)

if not rs.eof then
'populate the form with the values from the recordset
.......
End if

rs.close
set rs = nothing

End Sub

'once the user is done entering info and that info has passed all edit checks, we insert that row
private Sub cmdSave_Click()
dim sql as string
dim itm as ListItem

sql = "INSERT INTO Customers (CustName, Contact, Phone, Email, URL) VALUES ("
sql = sql & "'" & txtCustName.Text & "', '" & txtContact.Text & "', '" & txtPhone.Text & "', "
sql = sql & "'" & txtEmail.Text & "', '" & txtURL.Text & "')"

gCN.execute sql

'now that row is in the table on the backend -we need to refresh the grid's recordset
'frmMain.datRecords.Refresh

'or we could just add a row to the control manually
With frmMain.lvwMain
set itm = .listitems.add , , txtCustName.text
itm.subitems(1) = txtContact.Text
itm.subitems(2) = txtPhone.text
itm.subitems(3) = txtEmail.text
itm.subitems(4) = txtURL.text
End With

set itm = nothing

End Sub




I hope this kind of gets you started. I realize that this code isn't exactly what you were looking for, but I think it should point you in the right direction.

hope this helps,
John

John Pirkey
MCSD
www.ShallowWaterSystems.com

You could try a "Collection" object to pass back & forth. ( Forth -> because next time when the same "template selection" form is shown, you have a choise to show it start from "previous" selection point!, or a currently active record value )

Add the key value from the text box to the collection, and add necessary info from selected list item to the collection.
and if i were you, i will add all the fields data. That way i can write one generic add routine on the frmDataGrid.

Then add a Public member fn to the Selection form, which returns the collection, after form unload also. ( Simple)

If you want to make life complicated, you can write a "OnSelect" event in the selection form, and pass the Collection as the parameter for/of that event, and handle that event in the frmDataGrid, and read off the values. (Medium)
If you want to make life even more complicated, you can design an interface for the two forms to talk thru.. and have a callback implementation (advanced)


Then in the form, add a new record, add the data from the collection ( having all field data makes life easy here . You might need 2 bookmarks otherwise. I dont know if Bookmarks are available on ADO, but! )


RK

Ok, I have gotten to the INSERT INTO part of the code which brings up another question. My database that I am using has 144 fields. Is there a really clean way of generating a SQL statement that includes all of these fields? I don't want to use all of the fields but almost all of them (Otherwise I'd use *).

unfortunately, if you're not inserting values in to every field in the table, you will have to list every field you are supplying values to. And the list has to be in the exact order of the values yuo're giving. eg

INSERT INTO Table1 (Field1, Field2, Field3) VALUES ('Value1', 'Value2', 'Value3')



and so on.

If you get the values out of order with the field list, then your data will be wrong if it works, or you'll get an datatype conversion error.

Sorry,
John

John Pirkey
MCSD
www.ShallowWaterSystems.com