Re: Search and insert into continuous subform

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

tompk wrote:
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" wrote:

tompk wrote:
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.


.

Relevant Pages

  • RE: Open a form within a subform
    ... I don't understand the 'all sub-forms run off the same query' I was ... withing the margins of the subform. ... The current form loads all the fields from each sub-form as you move ... When the form opens, it opens as a full screen document. ...
    (microsoft.public.access.forms)
  • Re: My SubForm Woes
    ... > Right-click the edge of the subform control, ... > query, rather than just those that match some field in the main form. ... > Allen Browne - Microsoft MVP. ... >> The second is that even when it opens with the appropriate query, ...
    (microsoft.public.access.forms)
  • Re: Form/Subform
    ... Are you trying to set the subform's query string dynamically, ... form opens, becuase the form will requery the subform when it opens. ... > criteria equal to the option group value in the main form. ...
    (comp.databases.ms-access)
  • Re: Search and insert into continuous subform
    ... Thanks Mike for your response but I am not sure if I am following you.....I ... my code simply opens up the Frm_MAIN_MULTIPLES on ... open a main form instead of a subform). ... If it is an arbitrary insertion, then you ...
    (microsoft.public.access.formscoding)
  • Re: Form/Subform
    ... forms!frmMain.frame1 was not available to the subform's query criteria. ... > then the subform should have no problem loading the correct records when ... > the form opens, becuase the form will requery the subform when it opens. ... >> criteria equal to the option group value in the main form. ...
    (comp.databases.ms-access)