Search and use Recordset

[masadeebo]

Please help,

The scenario is i have two tables Table1 and Table2 with thier respective forms Form1 and Form2.

Table1/Form1 has 9 fields, say A,B,C,D,E,F,G,H,J and Table2/Form2 has 5, A,B,C,D,E
ThE linking field is A

I want to be able to use a search form to populate the results of my search on a list box.
Select a recordset on the list box and pass/copy it to Form1/Table1 and then fill the rest of the fields myself on the form.

Please illustrate by code where possible,

Thanking you in advance

Rahim

 

[masadeebo]

Please help urgently

 

[ssecca]

If I understand your request correctly...

You want to find a record on one form and pass some of the information from that record to a different table and pull it up to edit on a second form. Is this correct?

ssecca

 

[masadeebo]

Yes Ssecca, that is exatly what i want to do
Your help will be much appreciated

Thanx

Rahim

 

[masadeebo]

Please, please, please, someone help me

 

[ssecca]

There are several ways to acomplish this

One method not nessasarally the best is to create global variables to hold the information desired. Once it is found click a button that copies the data to the globals. On open of the second form in add mode programaticaly enter teh values from the globals into the correct fields leaving the remaining fields for you to enter.

The way I would approch using this solution is

1) create the globals
2) on the close button of the first form add VBA coding to capture the fields of interest into the globals. Then open the second form while hiding the first.
3) On the on open event of teh second form have it load teh globals into the fields.

If you need more detailed instructions please let me know.

An alternative would be upon finding the desired data in form one click a button that hides the form (visible = false) and opens form two. Again using the on open event of form two you can read directly from form 1 into form 2.

One of the keys to remember when doing either of these is to open form 2 in add only mode.

Just to complicate the issue the third option (and the one I would choose because I very seldom use bound forms. Would be to capture the data into an insert SQL statement and save it into table 2 then open form 2 with a filter to the record I was interested in.

Goos Luck
ssecca

 

[masadeebo]

Thank you Ssecca, but can you illustrate by a code, i understand you but i am very new to vb coding
thank you

 

[masadeebo]

Please help

 

[masadeebo]

Please, please, help

 

[ssecca]

TO create a global go to the module section. I normally create a module called "mod_GLOBALS".

Within that module under the Option Explicit line add...

GLOBAL gblMyVar as string

<<<<END CODE>>> (do not include this line)

You may need to add several one for each value you need to temp save.

On the close button of the first form use code simalar to the following...


gblMyVal = me!lastname

or

gblMyVal = me.lastname.value

<<<<<end code>>>>>>>>

Both of the above are the same, and there are other ways of doing this as well but lets not to to carried away.

Also add the code to open the next form in the add new status. Remember to open the next form BEFORE actually closing the first form. The procedure and VBA code are only good and usable while the form is open. That is why we made the globals, global for the application not just the form.

This line would be simalar to what you would need...

DoCmd.OpenForm &quot;frm_name&quot;, acNormal, , , acFormAdd


Last in the on open event of the second form add code simalar to the following to insert the globally saved daat into the now open form...


me!lastname = gblMyVal

or

me.lastname.value = gblMyVal


Hope this helps
ssecca

 

[masadeebo]

Anymore suggestions