How to use the output from a Dynamic search on multiple fields in another form

[Minky1]

Hi there,

I am new to this forum and this is my first post - thank you in advance for any help offered.
I have been asked by my friend to set up a rentals database for her new video rental shop.

I have got so far but I am currently stuck at a customer and film search function I am trying to set up.

First a little bit about the database:


I am currently using Access 2003. The database has three tables - STOCK, CUSTOMERS and CUSTOMERRENTAL.
The CUSTOMERRENTAL table is linked to the STOCK table via the STOCKID and the CUSTOMERS table via MEMNO.

I am now in the process of trying to create a form to populate the CUSTOMERRENTAL table with checked out film records.

I have used a template on this forum to design a search form for the MEMNO (customer membership number) and a separate form for the STOCKID. The template for these forms is here: http://www.access-programmers.co.uk/...d.php?t=188663

This form (FRM_SearchMulti) allows me to type in the first name or surname of the customer, their Membership number or one of the names of the authorised renters to locate the account.

I have set up a second form (FRM_SearchMulti2) based on the same template to search by title, format etc. to locate the stock ID of the film.

The problem I now have is utilising the output of these forms in a third form that will have the following fields and will link and populate the CUSTOMERRENTAL table.
MEMNO - Customer membership number (field properties number-single) want to get this from FRM_SearchMulti.SearchResults
STOCKID - Film stock ID number (field properties number-long integer) want to get from FRM_SearchMulti2.SearchResults2
RENTDATE - The date the film was checked out (field properties date-short) Defaults to todays date
NIGHTS - Nights the film was rented for (field properties number-single) has a drop down list for number of nights
DUEDATE - Calculated field - RENTDATE + NIGHTS does not need to be stored to table

I have experimented with the open form macro in the on click event which I works if I am only using one form but falls down when using two forms.


Ideally what I am trying to get to is having the two search forms FRM_SearchMulti.SearchResults and FRM_SearchMulti2.SearchResults2 embedded in a third form that populating the MEMNO and STOCKID fields where I can also manually populate RENTDATE and NIGHTS and then have those 4 fields populate CUSTOMERRENT with a new record when I save. I have hit a wall and am basically progressing through trial and error so any guidance would be greatly appreciated.

Thanks again.

Jody

[Minky1]

Ok, I worked out what I needed to do.
Managed to find the SetValue function in Macros and added the Macro to the OnClick event of my form.

[pbaldy]

Thanks for posting your solution and welcome to the site!