Search and insert into continuous subform

[tompk]

I have a form (Frm_RTP_MAIN) with a continuous subform (Frm_MAIN_MULTIPLES)
that has a search button on it. It opens a search form that allows the user
to search the main table records and highlight the record they want to add.
I have the code pasted below that I have used in other search forms (which
are not continuous and open a main form instead of a subform).

When I use this code, it opens the form but I actually want it to insert the
record into the row in the continous subform inside the main form. And then
continue searching and adding records as many times as they need to. There
is an Add button to for when their search comes up null.

I am terrible at code and have tried a few different options such as
[Frm_RTP_MAIN]![Frm_MAIN_MULTIPLES] but can't seem to get it to work. I was
thinking maybe it's actually the OpenForm that is wrong.

Private Sub List_Results_DblClick(Cancel As Integer)
'Open MAIN_Form based on the ID from List_Results listbox
DoCmd.OpenForm "Frm_MAIN_MULTIPLES", , , "[Record_ID] = " &
Me.List_Results, , acDialog
DoCmd.Close acForm, Me.Name
End Sub

Any help is greatly appreciated!

 

[Mike Painter]

I have a form (Frm_RTP_MAIN) with a continuous subform
(Frm_MAIN_MULTIPLES) that has a search button on it. It opens a
search form that allows the user to search the main table records and
highlight the record they want to add. I have the code pasted below
that I have used in other search forms (which are not continuous and
open a main form instead of a subform).

When I use this code, it opens the form but I actually want it to
insert the record into the row in the continous subform inside the
main form. And then continue searching and adding records as many
times as they need to. There is an Add button to for when their
search comes up null.

Access adds all records to the end of the file and uses indexes to show any
other order so you can't just insert the row into the subform.
You could pop up a form, fill it in , then have it "inserted" in some way by
a requery.
If it's inserted in an alph or numerical sequence and will "fit" there is
not problem.
If it is an arbitrary insertion, (John goes between Fred and Adam.) then you
have a prolem.

I can think of only one way to do this and it would be awkward.

 

[tompk]

Thanks Mike for your response but I am not sure if I am following you.....I
don't require any type of sort or anything like that but if there has to be
an order that would be fine. Basically the User fills in their RTP_MAIN data
and that RTP record may apply to one or many records that are already
included in the Tbl_MAIN (essentially locations).

They need to be able to search for the Tbl_MAIN record that applies and
attach it to the RTP_MAIN record. I have the search working, but when they
double click to add it, my code simply opens up the Frm_MAIN_MULTIPLES on
it's own. I thought there would be an easy way to change it to have the
Tbl_MAIN record link to the RTP_MAIN record.

If I had this set up as a drop down of Record_ID's from Tbl_MAIN, I thought
that would technically work however, we have thousands of records which is
why they need to perform a search.

Hopefully that explains it better. And hopefully this is possible!!!

 

[Mike Painter]

Once you have found the record you want to link you have the key to that
record available in code and can run an update query against that single
record using Forms!YourForm!YourKey to update the foreign key.
You could also open (a recordset of) the table, use findfirst to move to
that record and update it.
The query will be a neater solution

In general a query is always going to be the better solution if it can be
done - and it usually can.

I'm very rusty after retiring a few years ago so can't be a lot of help.
Now I work about two hours on Monday, five on T