Update Form Data

[Tara]

I'm sure this has probably been asked here before, and I'm sure there's a
simple solution, but I can't seem to find it. Also, sorry this is so long,
but I want to be as clear as possible.

I have a table that holds all the client contact data from all of my
agency's different programs. This table is then linked to our various
databases. All of the programs collect and generate different data, and one
person can be in several of our programs at once, so we felt this was the
most efficient way of storing contact data. Now, onto the issue. I have a
combo box on the main form of each database that contains the first and last
name of every client we serve, regardless of what program they're involved
in. However, the other fields on the main form of each individual program's
database lists only those clients that are actually involved in that program.
If program A wants to add Jane Doe to their program, they first look to the
combo box to see if she's already in the database due to involvement in some
other program. If her name is there, the user selects it in the combo box,
which triggers an update form to open that lists all of that client's info as
well as checkboxes to indicate the program(s) she's already involved with.
The user then checks the box for their own program. What I need to happen is
for the client to then automatically show up in the main form as soon as the
user closes the other form. I'm sure it must be something like a Requery or
Refresh Command, but I can't seem to get it to work!

Any help is appreciated!

 

[NetworkTrade]

It is my experience that a ComboBox will not have real time updates - and
that one must close the Form and re-open it to refresh the ComboBox.

I'll be very interested if an MVP advises otherwise.

Instructing the user of this is one method....but I have also hidden
close/reopen macros at points that the users frequently do - so this it
occurs without them knowing it.....

 

[Tara]

kI thought of that. Works great, but it's a visual thing for me. The pop-up
form is small and the main form is still visible behind it. If the main form
closes and re-opens, the users will notice it. No big deal, I know, but I
don't like it. Thanks though!

 

[Jeff Boyce]

Tara

I'm not sure I understand the tables' relationships and locations. Are they
located in different .mdb files? Or in some other data stores?

I don't understand how a person can be in a program, but not in your "all
the client contact data" table. It seems like you are describing a
one-to-many relationship, where one person (contact) can be associated with
many programs. Are you using a single "programs" table for each program to
store this association (not a good idea)? ...or do you have a "Programs"
table (that only lists Programs) and a "ProgramParticipant" table that shows
valid combinations of person and program? This latter approach is what
you'll find recommended most often here in the groups. But I don't have a
sense of your physical constraints in terms of where this is all happening.

How your table structure is set up will affect how you could do what you are
trying to do.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Undrline]

If I understand the question, there are two ways to do this, that I know of:
to refresh the entire form (which is part of the wizard when creating a
button; choose Form Operations>Refresh), or, if you just want the data
field/object updated, and know how to edit the VBA:

Field_Name.Requery

-Jndrline

 

[Tara]

Thanks for you input Jeff. I think I need to claify a few things.
TblClientContact (the table that holds all agency clients) is in one database
(a mailing list database actually) and is linked to each individual program's
database. A person who is in a program IS in tblClientContact , but a person
who is in tblClientContact only shows up in a program's database if they are
a part of that program. In other words, the main form in Program A filters
out those who are not in Program A, the main form in Program B filters out
those not in B, etc. To accomplish this, in tblClientContact I have yes/no
fields that indicate which programs an individual is in. So the table looks
like this:
ClientLast, ClientFirst, Address, ......., Program A (yes/no), Program B
(yes/No), etc.

Is that a bad way to do this? I had actually thought about splitting the
table anyway. Is that essentially what you're suggesting?

 

[Tara]

Thanks for the feedback. The problem with the first option is that it
requires the user to click a command button on the first form. I really want
it to be more *automated* than that. As for the second option, I tried
requerying the ClientLast field in the On Got Focus event of the main form,
but it didn't work.

 

[Jeff Boyce]

Tara

The term "splitting" has a special meaning in Access, so I'll use another
term.

If your tblClientContact has multiple yes/no fields to indicate
participation in specific programs, you have ... a spreadsheet! A
well-designed and well-normalized relational database would use one table
for ClientContact-only info (FirstName, LastName, DOB, Phone, ...), one
table for Programs (in which each program would have a separate record, not
a separate column), and one table to resolve the many-to-many relationship.

Relational databases are all about relations. From your description, one
person could participate in many programs, and one program could have many
persons participating in it. This is a many-to-many relationship and Access
(and other relational databases) can only handle this by creating a
"junction" table to resolve the many-to-many situation.

Add the third table (e.g., ProgramParticipants) and put two fields in it (to
start). Field1 is PersonID, which you get from the tblClientContact table.
Field2 is the ProgramID, which comes from the tblProgram table. This way,
if you have a person who starts in on a second program, you need to add a
row to the ProgramParticpant table.

Your way, with multiple "repeating fields", should you ever add a new
program, you'll have to change your table design, any forms that are based
on the table, any reports based on the table, any queries based on the
table, any macros based on the table, and any code based on the table.
Unless you have a strange thing for "maintenance", you really don't want to
have to work this hard.

Regards

Jeff Boyce
Microsoft Office/Access MVP