Set a form record from another form

[bundy75]

I have two forms A and B both of which are open. Form A is the details of a client and form B is a datasheet of all the clients. What I was hoping to do is select a client on form B and then by clicking a button(or double clicking the client if its possible) have form A's record set to the matching client. For reasons I won't go into I don't want to open/reopen form A with the corresponding record but to just set the recod.



I've tried

' Find the record that matches the control.
Dim rs As Object
Set rs = Forms!Main_form!Client_Subform.Recordset.Clone
'Just hardcode a value to see if working
rs.FindFirst "[Client_Id] = 21"
If Not rs.EOF Then Forms!Main_form!Client_Subform.Bookmark = rs.Bookmark

and get the error "Object doesn't support this property or method".

How should I go about achieving this??????????

[NoellaG]

Hi,

I don't think Access 2003 already has the "split form" view, which does exactly what you need. You can try simulating this by placing the forms as subforms in a main form.
As the datasource of the single form you set "Select * from tblX where 1 = 0", so it doesn't open with the first record.
In the OnCurrent event of the continous subform you can write a code that changes the data source of the single form in something like "select * from tblX where ID = " & nz(me.txtID,0).

gr
NG

[bundy75]

By datasource do you mean record source??

This give me the error "The microsoft database engine cannot find the input table or query"

Is there another way??

[NoellaG]

Hi bundy,

of course you have to substitute the table name and filedname in my example by the table /field/control names of the actual table/field/control in your database..

grNG

[bundy75]

Just an update

Using your idea NoellaG I set the record source to the main_form to "Select * from tblX where 1 = 0" and then linked my single from to it. Now when the button is clicked on the datasheet form I set the main forms recordsource to "Select * from tblX where Id = 21" (hardcoded 21 to test)
and its working!!!!!

Just need to find code to get the Id from the form the button is on but problem is fixed - I believe

Thanks again

[bundy75]

Sorry NoellaG just seen your response, I was substituting the correct table names. I think the error was the way I obtain my Id because I am using and mysql server and ODBC driver to access it. But all good now as your idea still works just needed to take it out to main form.

[bundy75]

Sorry but I thought this was an easy one but do you know how in VB code to get the value of the id from a the selected row?

Go it!!!!!!