Open form to display specific record

Hi, I use an ado recordset to populate a form in the form_load() event , all is well however, I now need to open this form from another form else where in the app after a user has identified a specific record they wish to use.

This needs to be via a click button, the sub form where they select the record to open uses the following code to display basic details from a main table;

ADOdetailRS.open "select [etc], [etc], [etc] from tblappts where tblappts.[index] = "val(me.[text2]), etc,etc,etc

This gives me core details for a selection, I now need to open the main form based on a click button using the value of txtfield within this form, I know I can create another form and open it with another recordset, but I would rather not create another form.

Thanx

LVL 2
apptsoluAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamSkanRetiredCommented:
I'm not sure that I understand your requirement, so I'll start by asking why you don't close the recordset and open it again with the new details from the textbox?
RejojohnyCommented:
What i understand from ur question is that u need to open the same form from 2 different source. if thats the case, u could probably try this ..
- copy ur existing code in form_load() to a public procedure within ur form. This procedure could accept an optional parameter which could be the primary key on which u r fetching the values using the existing code.
- The form_load could call this procedure and do not pass any parameter
- The other form on button click could call this form using the call : <form name>.<procedure name(paramter)>
NYProgrammerCommented:
The way I understand the question, declare a global variable in a module, set that variable to the value in the text box on button_click, load the second form and have it use the global variable in its SELECT statement.  (There are more elegant ways, but using a global variable is simple and makes the code obvious.)
Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

apptsoluAuthor Commented:
Thanx everyone so far, if I post my code it should give you and idea,

Clients form is one used most, thus ;
Private Sub Form_Load()
 Dim db As Connection
  Set db = New Connection
  db.CursorLocation = adUseClient
  db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\SALONMAGIC\DATA\theSALON.mdb;"

  Set adoPrimaryRS = New Recordset
  adoPrimaryRS.Open "select * from tblCLIENTS", db, adOpenStatic, adLockOptimistic

etc, etc, etc
 End Sub

What I want to do is either alter the above adoPrimaryRS.open statement or via a click button on another form filter the above recordset to only include a specific record that is selected in an event on another form.
Ideally I want to substitute the above select statement with,

ADOprimaryRS.open "select [etc], [etc], [etc] from .... where tblclients.[index] = "val(me.[text2]),

but the change can't be permanent,

I can use a physical adodc control on the client form and alter recordsources that way but i wud rather manipulate it as above

regards
NYProgrammerCommented:
Will this work?

ADOprimaryRS.movefirst
ADOprimaryRS.find "tblclients.[index] = " & val(me.[text2]), , adSearchForward

If "but the change can't be permanent" means that you want the dataset to be unaffected after the form is unloaded, send the form a copy of the dataset and do the find on the copy.

apptsoluAuthor Commented:
NYProgrammer,

Sorry If I seem a bit slow, how wud I send the form a copy of the dataset.

 adoPrimaryRS.Open "select * from tblCLIENTS", db, adOpenStatic, adLockOptimistic  ' must always remain in place as it is.

I just need to occaisonally have the form open at a specific record selected on another form.
...............
ADOprimaryRS.movefirst
ADOprimaryRS.find "tblclients.[index] = " & val(me.[text2]), , adSearchForward '

How and where would I use this - it cant go in the main form we are opening as static code because of the above.

I think I will have to use a fixed ADODC control and use Recordsource statement changes.

Thanx for your input.
NYProgrammerCommented:
Clone the recordset and .find on the clone.  The original recordset stays the same.

Use a global adodbRecordSet for the clone, then Form1 can fill it and Form2 can .find on it and display the results.
apptsoluAuthor Commented:
Thanx for everyones comments, however I solved it by using IF statements within the existing recordset on the Main forms opening load and counteracting any null values just in case """ val(me.[text2])"""" wasnt the method the user arrived at the main screen.

Im sure your responses were valid, but as Ive never used a global clones before I couldnt implement it.
However,

NYPROGRAMMMER, as I'm keen to learn, if you could send me code using above ADOrecordset as a global then I will be more than happy to award you the points for all your efforts.

Regards
NYProgrammerCommented:
assuming your original recordset is adors:

dim myClone as new adodb.recordset
myClone = adors.clone

Now you can do whatever you want to the clone:

myClone.find "tblappts.[index] = "val(me.[text2])
'display the new recordset, etc.

The original recordset, adors, is unchanged.  Just set myClone to Nothing when you're through with it.
NYProgrammerCommented:
oops, that should have been

myClone.find "tblappts.[index] = " & val(me.[text2])

(or whatever your selection criteria are)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.