passing a single record

[misterhux]

hey... so I have a form with two subforms on it. in one sub form (A) I have a listing of records, what I want to have happen is that when the user dblClicks on one of the records in A, that record is add to the other subForm (B). Right now it just adds all the records from A to B, and I can't seem to figure out how to get this to work. the data in A isn't changable which is what I want, but I would like to be able to change data in the other form B. I think I can do it if I use a local table (and then dump it back to the server), but I haven't gotten that far yet. does this make any sense?

here's the code from subform A
Dim recsetClone As ADODB.recordSet
Dim varMark As Variant
'Stop
Set recsetClone = recordSet.Clone
varMark = Bookmark
recsetClone.Bookmark = varMark

Dim str As String

'str = Me.cmb_sorting.Text

Dim k, fin As Integer
str = "nothing happened"
fin = recsetClone.Fields.Count - 1
If Not (recsetClone.EOF And recsetClone.BOF) Then
'MsgBox ("recset open&quot
str = "cert_Data recordset count " & _ recsetClone.AbsolutePosition & " Data-> "
For k = 0 To fin
str = str & " " & recsetClone(k).Name & "= " &_ recsetClone(k)
Next
End If
MsgBox (str)

Set parent.Controls("B&quot.Form.recordSet = &_ recsetClone.Bookmark

thanks

 

[jsteph]

hux,
I'm having a difficult time seeing either what you want to do or even what you're trying to do.

When you say 'listing of records', do you mean sbf A has complete records, and you want a copy of that record in sbf B? Or does sbf A just have a list of the Key's or ID's of the entire record? And is the structure the same for A and B? Is it a different table, but with the same fields? Or same table, but each sbf is filtered differently? And the 'Code from subform a'. Where is that code? In a button event on the subform? In the DblClick event?

If sbf A is based on some table or joined tables, and sbf B is based on some other table or joined tables (regardless of the actual RecordSource, which may be a query), Then the easiest thing to do is just write code to copy the record from a's source to b's source.

Assuming both are based on similar tables the pseudo code would be: (In whatever event you want to trigger the copy, assuming dblclick of the From subform, A)

1. Get the Primary Key of sbf A's current record
2. Create SQL to INSERT INTO B's source table WHERE A's PK = the PK of current sbf A record.
3. Requery sbf B

If you want more detail I can give that but I can't without more clarity on what you have.
--jsteph

 

[jbento]

Can you give me more clarity?

I have a Main Form with a field called "MainID (Autonumber)" - table is called: tbl_main. I have a subform on the main form that is connected to a table with a field called "MainID (Number)" - table is called: tbl_sub.

I need to be able to copy the record on the main form and all the records in the subform to a table called "tbl_copy"

The MainID is the related field.

Please help me with the code for this.

I would really appreciate this so very much.

Jerome Benton
JERPAT Web Designs

http://www.jerpat.org

GOD Is Good All The Time!!!