Re: Using macros to permit updating of subform

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: tina (nospam_at_address.com)
Date: 12/02/04 

Date: Thu, 02 Dec 2004 00:58:16 GMT

okay, hon, we'll give this a go. i'll make up the table, field, form and
control names - you can replace them with the correct names from your
database as you go.

i'll call your parent table: tblClients. and i'll call the child table:
tblClientActivities. tblClients should have a primary key field, which i'll
call: ClientID. that field should also exist in the child table as a
foreign key field, which i'll call ClientIDfk.

open the relationships window and link the two tables, on the
ClientID/ClientIDfk fields, and enforce referential integrity.

create a form bound to tblClients (the RecordSource property shows
tblClients). i'll call it frmClients. use the combo box wizard to create an
unbound combo box (you can put it in the form's header section) to find any
client record you want. when the wizard starts, select the third option
"find a record on my form...". i'll call this control: cboFindClient.

create another form, bound to tblClientActivities. i'll call it:
frmClientActivitiesSub. save and close the form. note: you do *not* have to
have a control on the form for the ClientIDfk field.

open frmClients in design view. add a subform control (from the Toolbox
toolbar). i'll name it ChildActivities. set the SourceObject property to
frmClientActivitiesSub. because you set the relationship in the
Relationships window, Access may set the LinkChildFields and
LinkMasterFields properties for you. if not, set them as follows:
LinkChildFields property should be ClientIDfk. LinkMasterFields property
should be ClientID. save and close the form.

now, when you open frmClients in form view, you can use the combo box to
find any client record you need. as you move from one client record to
another, the record(s) in the Activities subform change to match the current
record in the Clients mainform. you can add or update subform records for an
existing record in the mainform. you can add a new record in the mainform,
and immediately add new records in the subform as well. you never need to
enter the ClientID value in the subform record manually or
programmatically - Access will automatically add the correct ClientID value
in the ClientIDfk field in the subform record, for you.

this is an easy setup that requires no macros or code (except the code
behind the cboFindClient control, which is generated for you by the wizard).

now, to address your other question: if i understand correctly, you want to
go to any specific record in the main form, close the form, then open the
form again and automatically, and immediately, go back to that same record.
well, that is probably do-able, depending on the circumstances (same user -
different user, same session - different session, for instance). i can think
of a couple ways it might be accomplished, off the top of my head. but they
all involve using VBA rather than macros, and their reliability might depend
on , again, the circumstances.

hth

"TomC" <TomC@discussions.microsoft.com> wrote in message
news:438A0110-AF1E-4950-8C60-D093FE3D938A@microsoft.com...
> It appears that I don't know what I'm doing, but you have given me hope! I
> just want to make sure that I will now do what you are saying that I
should
> do, because I am confused on the terms main and parent. Yes, I have at the
> top a form which is unbound (RecordSource property blank) which just
contains
> only the Combo Box (Combo2). I use that to retrieve names. Below that I
have
> a parent subform linked to the Combo Box form and then I have a child
subform
> linked to the parent subform. Both these subforms has in the form
properties
> - LinkedChild is ClientID and the LinkedMaster is Combo2. So if I
understand
> you I should eliminate the top form with the Combo Box and move the Combo
Box
> to the parent form and then link the child subform to the parent. To make
the
> parent bound, do I make sure the RecordSource property contains my ID. Is
> there anything I have to do special to make the parent bound?
> I am using the procedure that is outlined in Article ID 100132 (Four Ways
to
> Move to a Record from a Combo Box Selection. I using Method 3 which it
> claims to give me some features that I need. My only problem with this is
> that I just haven't found a way to get my ClientID into a new child record
at
> the time I am adding a new parent record. But it appears that I have made
> problems for myself and I am more than willing to pursue the course you
> outlined. Please set me on the correct path and I will try to follow.
> Thanks much for replying. I do appreciate it.
>
> "tina" wrote:
>
> > so your main form is unbound (RecordSource property is blank)? your
parent
> > table is bound to a subform, and your child table is bound to another
> > subform, both on the unbound main form? if the preceding is correct, is
> > there a specific reason that you set up your mainform/subforms this way?
> >
> > the usual setup is to create a main form bound to the "parent" table,
then
> > add a subform bound to the "child" table. when they are properly linked,
you
> > can smoothly and easily add new child records to an existing parent
record,
> > or add a new parent record with or without adding new child records for
it.
> >
> > to quickly "find" a specific parent record in the above setup, you can
use
> > the combo box wizard to create a combo box on the main form that will
"find
> > a record on my form".
> >
> > hth
> >
> >
> > "TomC" <TomC@discussions.microsoft.com> wrote in message
> > news:D1411356-47BB-4F8A-82E7-F1527B496C5C@microsoft.com...
> > > I have a data entry application which uses a form with a Combo Box to
> > bring
> > > up data in my Client (one) subform and in my Activity (many) subform.
My
> > > problem is that when I enter a new Client record with a new Activity
> > record,
> > > my ClientId is not being placed into my Activity subform data - I
guess
> > > because the ClilentID is not assigned until I add the Client record to
the
> > > file. I am trying to solve this by using Macros with Command Buttons.
My
> > > first Command Button is in the Client area to execute a macro which is
> > > supposed to SAVE the record before proceeding, but that's not working.
I
> > get
> > > the error message 3022 which states that the change I requested to the
> > table
> > > were not successful because they would create duplicate values in the
> > index.
> > > How can I get this Client record into the file before I start keying
the
> > > Activity data?
> > > Another question while I'm at it, is there a Macro way of saving the
> > > ClientID using Macros so that I can retrieve the Client record back
after
> > > saving it????
> > > Sorry, for all this mess, but as you can tell, I need help.
> > >
> > > A second minor irritation is that whenever I try to start keying in a
> > Client
> > > Record, I always get the message "You can't assign a value to this
> > object.
> > > The object may be a control, etc".
> >
> >
> >

Quantcast