Open new form with related record set?

[Guest]

Hi,
I hope someone can help me! I am having so much trouble with this, which is
frustrating, because I used to do this stuff all the time, but it has been a
few years. I've tried to use my huge Access book that I used when I first
learned, but it seems like 1,000 pages of stuff like how to make columns
wider, and useless things like that that I already know!
Anyway, here is the problem:
I have 2 tables, [Contacts] and [Tracking], in a 1-to-1 relationship. The
Tracking table contains 1 record of related dates and status information for
each client in the Contacts table. The tables are related through the
"ClientID" field. Each table has a related form where you can view and edit
the information for each client. What I want to be able to do is add an "Edit
Tracking Status" button to the bottom of the View Client form, which, when
clicked, will open the Tracking Status form with the SAME CLIENT as I was
viewing in the View Client form, so that I can add or edit information
through this form into the Tracking table.

I've been reading through the answers on this forum for several hours, and
have tried everything that I could find that seemed related. Things have
improved, as before I kept getting errors just opening the form. Now it will
open, but the ClientID field shows up blank, and then when I try to start
adding information to the form, it adds a NEW ClientID which did not exist,
so it is essentially trying to enter a new record in the Contacts table,
instead of in the Tracking table! I hope someone can help! It seems like
something that should be fairly simple, but I can't figure it out. Do I need
to use a query? A macro? I know how to use these things, but haven't been
able to get it to work no matter what I've tried.
Thanks in advance to anyone who can help!
Rose.

 

[MacDermott]

When you add the button to your form, make sure the wizard is turned on.
Choose Form Operations - Open Form.
Select your second form.
When you get to the screen about data, select the first option, to open the
form and show selected data.
On the next screen, select the fields on your first and second forms which
should match.

Access should take care of writing the code for you.

 

[Guest]

Thanks for the info. I've tried that already. It works if there is already a
record in the Tracking table for that client, but if I'm trying to add a new
Tracking record that doesn't exist yet, it doesn't add the record for the
client that shows in the Contacts form -- it tries to add a completely new
ClientID, and then gives me an error because there is not a corresponding
Client.

Also, I would like the Tracking form to automatically show both the ClientID
AND the First and Last Names -- I want to match on more than one field. How
can I do that? I don't want people entering in the names in the Tracking form
and making errors -- I want those names pulled directly from the Contacts
table to reduce data problems.

Any ideas?
Thanks!
Rose.

When you add the button to your form, make sure the wizard is turned on.
Choose Form Operations - Open Form.
Select your second form.
When you get to the screen about data, select the first option, to open the
form and show selected data.
On the next screen, select the fields on your first and second forms which
should match.

Access should take care of writing the code for you.

Hi,
I hope someone can help me! I am having so much trouble with this, which is
frustrating, because I used to do this stuff all the time, but it has been a
few years. I've tried to use my huge Access book that I used when I first
learned, but it seems like 1,000 pages of stuff like how to make columns
wider, and useless things like that that I already know!
Anyway, here is the problem:
I have 2 tables, [Contacts] and [Tracking], in a 1-to-1 relationship. The
Tracking table contains 1 record of related dates and status information for
each client in the Contacts table. The tables are related through the
"ClientID" field. Each table has a related form where you can view and edit
the information for each client. What I want to be able to do is add an "Edit
Tracking Status" button to the bottom of the View Client form, which, when
clicked, will open the Tracking Status form with the SAME CLIENT as I was
viewing in the View Client form, so that I can add or edit information
through this form into the Tracking table.

I've been reading through the answers on this forum for several hours, and
have tried everything that I could find that seemed related. Things have
improved, as before I kept getting errors just opening the form. Now it will
open, but the ClientID field shows up blank, and then when I try to start
adding information to the form, it adds a NEW ClientID which did not exist,
so it is essentially trying to enter a new record in the Contacts table,
instead of in the Tracking table! I hope someone can help! It seems like
something that should be fairly simple, but I can't figure it out. Do I need
to use a query? A macro? I know how to use these things, but haven't been
able to get it to work no matter what I've tried.
Thanks in advance to anyone who can help!
Rose.

 

[MacDermott]

Here's some code you could use in your Tracking form's OnCurrent event:
If Me.NewRecord Then ClientID=Forms!Contacts!ClientID
That should copy the correct ClientID into the Tracking form.

Does/can one ClientID represent more than one FirstName/LastName
combination?
Or do you just want to display that data on the Tracking form to remind
yourself (or your user) who they're working with?

Thanks for the info. I've tried that already. It works if there is already a
record in the Tracking table for that client, but if I'm trying to add a new
Tracking record that doesn't exist yet, it doesn't add the record for the
client that shows in the Contacts form -- it tries to add a completely new
ClientID, and then gives me an error because there is not a corresponding
Client.

Also, I would like the Tracking form to automatically show both the ClientID
AND the First and Last Names -- I want to match on more than one field. How
can I do that? I don't want people entering in the names in the Tracking form
and making errors -- I want those names pulled directly from the Contacts
table to reduce data problems.

Any ideas?
Thanks!
Rose.

When you add the button to your form, make sure the wizard is turned on.
Choose Form Operations - Open Form.
Select your second form.
When you get to the screen about data, select the first option, to open the
form and show selected data.
On the next screen, select the fields on your first and second forms which
should match.

Access should take care of writing the code for you.

Hi,
I hope someone can help me! I am having so much trouble with this,

which
is

frustrating, because I used to do this stuff all the time, but it has

been
a

few years. I've tried to use my huge Access book that I used when I first
learned, but it seems like 1,000 pages of stuff like how to make columns
wider, and useless things like that that I already know!
Anyway, here is the problem:
I have 2 tables, [Contacts] and [Tracking], in a 1-to-1 relationship. The
Tracking table contains 1 record of related dates and status

information
for

each client in the Contacts table. The tables are related through the
"ClientID" field. Each table has a related form where you can view and edit
the information for each client. What I want to be able to do is add

an
"Edit

Tracking Status" button to the bottom of the View Client form, which, when
clicked, will open the Tracking Status form with the SAME CLIENT as I was
viewing in the View Client form, so that I can add or edit information
through this form into the Tracking table.

I've been reading through the answers on this forum for several hours, and
have tried everything that I could find that seemed related. Things have
improved, as before I kept getting errors just opening the form. Now

it
will

open, but the ClientID field shows up blank, and then when I try to start
adding information to the form, it adds a NEW ClientID which did not exist,
so it is essentially trying to enter a new record in the Contacts table,
instead of in the Tracking table! I hope someone can help! It seems like
something that should be fairly simple, but I can't figure it out. Do

I
need

to use a query? A macro? I know how to use these things, but haven't been
able to get it to work no matter what I've tried.
Thanks in advance to anyone who can help!
Rose.

 

[Guest]

Thanks! I tried that, but it kept giving me a code error saying that the
"field cannot be updated." I FINALLY just now got the correct ClientID values
to show up in the Tracking form, by using a macro (though I had tried that
before, and it didn't work). So I'm making progress! However, now when I try
to save the record in the Tracking form, it gives me an error that says "The
changes you requested to the table were not successful because they would
create duplicate values in the primary key...." So it is STILL trying to
save the records in the Contacts table, instead of in the Tracking table!
Here is the Tracking form source that was created when I created the form:
SELECT Tracking.ClientID, Contacts.First, Contacts.Last, Tracking.Source,
Tracking.[Response Date], Tracking.[Contacted Date], Tracking.[Opening
Interview Date], Tracking.[Fact Finding Date], Tracking.[Illustrations Date],
Tracking.[Personalized Solution Date], Tracking.[Closing Date],
Tracking.[Referrals Given], Tracking.[Meds Date], Tracking.[Issued Date],
Tracking.[Policy Delivery Date], Tracking.Type, Tracking.Amount,
Tracking.[Next Review Date], Tracking.Notes FROM Contacts INNER JOIN Tracking
ON Contacts.ClientID=Tracking.ClientID;

Is it causing a problem because I pulled the names from the Contacts table?

Yes, the ClientID is unique values for each name. I just want the name to
show up so that the user will remember what client they are working on.

Any other ideas?
Thanks!
Rose.

Here's some code you could use in your Tracking form's OnCurrent event:
If Me.NewRecord Then ClientID=Forms!Contacts!ClientID
That should copy the correct ClientID into the Tracking form.

Does/can one ClientID represent more than one FirstName/LastName
combination?
Or do you just want to display that data on the Tracking form to remind
yourself (or your user) who they're working with?

Thanks for the info. I've tried that already. It works if there is already a
record in the Tracking table for that client, but if I'm trying to add a new
Tracking record that doesn't exist yet, it doesn't add the record for the
client that shows in the Contacts form -- it tries to add a completely new
ClientID, and then gives me an error because there is not a corresponding
Client.

Also, I would like the Tracking form to automatically show both the ClientID
AND the First and Last Names -- I want to match on more than one field. How
can I do that? I don't want people entering in the names in the Tracking form
and making errors -- I want those names pulled directly from the Contacts
table to reduce data problems.

Any ideas?
Thanks!
Rose.

When you add the button to your form, make sure the wizard is turned on.
Choose Form Operations - Open Form.
Select your second form.
When you get to the screen about data, select the first option, to open the
form and show selected data.
On the next screen, select the fields on your first and second forms which
should match.

Access should take care of writing the code for you.

Hi,
I hope someone can help me! I am having so much trouble with this, which
is
frustrating, because I used to do this stuff all the time, but it has been
a
few years. I've tried to use my huge Access book that I used when I first
learned, but it seems like 1,000 pages of stuff like how to make columns
wider, and useless things like that that I already know!
Anyway, here is the problem:
I have 2 tables, [Contacts] and [Tracking], in a 1-to-1 relationship. The
Tracking table contains 1 record of related dates and status information
for
each client in the Contacts table. The tables are related through the
"ClientID" field. Each table has a related form where you can view and
edit
the information for each client. What I want to be able to do is add an
"Edit
Tracking Status" button to the bottom of the View Client form, which, when
clicked, will open the Tracking Status form with the SAME CLIENT as I was
viewing in the View Client form, so that I can add or edit information
through this form into the Tracking table.

I've been reading through the answers on this forum for several hours, and
have tried everything that I could find that seemed related. Things have
improved, as before I kept getting errors just opening the form. Now it
will
open, but the ClientID field shows up blank, and then when I try to start
adding information to the form, it adds a NEW ClientID which did not
exist,
so it is essentially trying to enter a new record in the Contacts table,
instead of in the Tracking table! I hope someone can help! It seems like
something that should be fairly simple, but I can't figure it out. Do I
need
to use a query? A macro? I know how to use these things, but haven't been
able to get it to work no matter what I've tried.
Thanks in advance to anyone who can help!
Rose.

 

[Guest]

Okay, scratch that reply I just sent! The reason it was telling me there were
duplicate records is that it had already created one in the Tracking table
before when I was testing. Once I deleted out all the test records, now it
can add new ones, and it is pulling up with the correct information in the
clientID and name fields (I added SetValue actions to the macro for the names
as well, and that seems to be working). However, here is the new problem! I
can set the openform value in the macro to either Add, or Edit. If I set it
to "Add," I am able to add a new tracking record for each client, but I can't
view and edit the ones that already exist. If I set it to "Edit" I have the
opposite problem -- it pulls up the correct existing record, if there is one,
but it doesn't let me add a new one if there isn't. How do I get around this?

Thanks for all your help!
Rose.

Here's some code you could use in your Tracking form's OnCurrent event:
If Me.NewRecord Then ClientID=Forms!Contacts!ClientID
That should copy the correct ClientID into the Tracking form.

Does/can one ClientID represent more than one FirstName/LastName
combination?
Or do you just want to display that data on the Tracking form to remind
yourself (or your user) who they're working with?

Thanks for the info. I've tried that already. It works if there is already a
record in the Tracking table for that client, but if I'm trying to add a new
Tracking record that doesn't exist yet, it doesn't add the record for the
client that shows in the Contacts form -- it tries to add a completely new
ClientID, and then gives me an error because there is not a corresponding
Client.

Also, I would like the Tracking form to automatically show both the ClientID
AND the First and Last Names -- I want to match on more than one field. How
can I do that? I don't want people entering in the names in the Tracking form
and making errors -- I want those names pulled directly from the Contacts
table to reduce data problems.

Any ideas?
Thanks!
Rose.

When you add the button to your form, make sure the wizard is turned on.
Choose Form Operations - Open Form.
Select your second form.
When you get to the screen about data, select the first option, to open the
form and show selected data.
On the next screen, select the fields on your first and second forms which
should match.

Access should take care of writing the code for you.

Hi,
I hope someone can help me! I am having so much trouble with this, which
is
frustrating, because I used to do this stuff all the time, but it has been
a
few years. I've tried to use my huge Access book that I used when I first
learned, but it seems like 1,000 pages of stuff like how to make columns
wider, and useless things like that that I already know!
Anyway, here is the problem:
I have 2 tables, [Contacts] and [Tracking], in a 1-to-1 relationship. The
Tracking table contains 1 record of related dates and status information
for
each client in the Contacts table. The tables are related through the
"ClientID" field. Each table has a related form where you can view and
edit
the information for each client. What I want to be able to do is add an
"Edit
Tracking Status" button to the bottom of the View Client form, which, when
clicked, will open the Tracking Status form with the SAME CLIENT as I was
viewing in the View Client form, so that I can add or edit information
through this form into the Tracking table.

I've been reading through the answers on this forum for several hours, and
have tried everything that I could find that seemed related. Things have
improved, as before I kept getting errors just opening the form. Now it
will
open, but the ClientID field shows up blank, and then when I try to start
adding information to the form, it adds a NEW ClientID which did not
exist,
so it is essentially trying to enter a new record in the Contacts table,
instead of in the Tracking table! I hope someone can help! It seems like
something that should be fairly simple, but I can't figure it out. Do I
need
to use a query? A macro? I know how to use these things, but haven't been
able to get it to work no matter what I've tried.
Thanks in advance to anyone who can help!
Rose.