Using macros to permit updating of subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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".
 
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
 
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.
 
One more thing I should have mentioned. Everything was working just like I
wanted it to work except that my child records were not displaying in the
sequence that I wanted them in (Date). I then created a query to sort the
child records and I am using that as my child input instead of the unsorted
activity record. After that, my ClientID was not going into my new activity
records whenever I added a new parent record. It works if the parent record
already exists when I add the child.
A different question. Using Macros, is there any way to store or save an ID
from the displayed record, close it and then retrieve it back using the
ID????
 
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
 
Tina,

I thank you 1000 times for these instructions. I really am impressed with
all this effort you have put into this. Tomorrow, I will endeavor to do what
you have told me to do. Thanks to you, I am relieved that there is a way of
doing this after all.
"tc"
 
no problem. i hope it works for you. if you run into problems or have more
questions, post back. even if i can't help you, there a plenty of better
brains here in the newsgroups who'll be able to! :)
 
tina,
Everything works just like you said it would. I followed your instructions
line by line and the only minor thing was that I didn't get an opportunity to
name the subform control "ChildActivities" until I saved the whole subform as
frmClientActivitiesSub. I feel like I've found the mother load! Now don't
leave me now because. I still have a couple of problems with the way this is
set up. I want my combox to present the names in LastName, FirstName
sequence. When I used the combo box before (outside the frmClients), it gave
me a sequencing option which I used. This procedure did not give me that
option. Trying the "Order By" didn't work on the frmClients. How can I fix
this??? Again my most humble thanks for getting me this far. I should ask
you another macro question since I'm in your Macro group. The macros have a
close, but before when I used that, it always said the record was already in
the file. At what point is the record updated from a form??
tc
 
open the main form in design view. click on cboFindClient to select it. in
the Properties box, scroll down until you find the RowSource property. click
in the RowSource line, and you'll see a small button at the right with an
ellipsis (...) on it. click the button. this opens a window that looks like
a query design grid. set up your "LastName, FirstName" sequence there, then
close the window and click Yes in the message box that comes up. note: if
you *add* any additional column(s) in the design grid, make sure you
increase the number in the ColumnCount property to match.

re saving a record in a form. in most situations, you don't have to do
anything programmatically to save a record. when you enter a record in a
form, or make changes to an existing record, the record saves automatically
when you 1) move to another record, including if you go to a new, blank
record, or 2) close the form, or 3) move from a main form into a subform, or
4) move from a subform back into a main form. you can also save a record
programmatically *without* leaving the record: with the macro Action
RunCommand, Command SaveRecord, or with VBA code, as

DoCmd.RunCommand acCmdSaveRecord

hth
 
You are the greatest. Don't go away, I'll probably need more help! Thanks
again for helping me with all this. I don't care what Access question I
have, I'll try coming back to the Macro Community to get my answers. I'm now
going to try the sequence thing on my child file also.
Thanks, Thanks, Thanks,
tc
 
you're welcome, Tom. :)

one note: it's a good idea to post questions in the appropriate group for
the subject - macro questions in microsoft.public.access.macros, query
questions in microsoft.public.access.queries, etc. you're more likely to get
the help you need in a group that focuses on the area your question is
related to. there are MVPs available to answer questions in all the groups;
many of them answer questions in several different groups, as do quite a
number of us lesser folk.

also, btw: usually you can get all the help you need by posting your
question to just one appropriate newsgroup. if you ever feel the need to
post the same question to more than one group, please cross-post. do not
multi-post!

hth
 
Back
Top