how do I open a new form with a cmd button using data in current f

[LiquidWrench]

I have a form called contact details in which i can input data concerning
contacts made. I have a ContactID field on this form. It is created and
filled by a query.
I have also a form called 7 Step which holds follow up information about
each contact. This form is also created and filled by a query. The query also
includes the ContactID field from the underlying Contacts table, therefore it
shows up on the 7 Step form. I want to open the 7 Step form by a command
button on the contact details form and have the 7 step information appear
concerning the current record on the Contact details form. I tried to use
the wizard but it will not show matching fields in both forms all it shows
are all of the fields in the 7 step form. How can I make this happen

 

[Carl Rapson]

I have a form called contact details in which i can input data concerning
contacts made. I have a ContactID field on this form. It is created and
filled by a query.
I have also a form called 7 Step which holds follow up information about
each contact. This form is also created and filled by a query. The query
also
includes the ContactID field from the underlying Contacts table, therefore
it
shows up on the 7 Step form. I want to open the 7 Step form by a command
button on the contact details form and have the 7 step information appear
concerning the current record on the Contact details form. I tried to use
the wizard but it will not show matching fields in both forms all it shows
are all of the fields in the 7 step form. How can I make this happen

Would it be possible instead to embed the "7 Step" form as a subform in your
main form? That way, you can use the Link Child Field and Link Master Field
properties to tie the two forms together through the ContactID, and the 7
Step subform will always and only show records for the current ContactID.

The way to do it using your method would be to pass the ContactID in the
WhereCondition parameter of the OpenForm call, to effectively filter the 7
Step form to those records for that ContactID.

Carl Rapson

 

[LiquidWrench]

will this work even if the 2 tables have a 1 to 1 relationship? If so how do
I do this? BTW I started this whole venture using the contact management
template for Access 2007

 

[LiquidWrench]

Just a little more insight that may or may not be of help. The form 7Step
was created from a select query the query includes all of the fields from
the 7Step table one of which is a field called ID this field is set to
"Indexed (No Duplicates) and is not an autonumber field because it is the
secondary key in a one to one relationship with the Contact Details table,
which contains an autonumber field "ID"
I can open the form and if I have the recordsource for the ID field on the
7Step form set to =[Forms]![frmContactDetails]![ID] it will display the value
in the ID field in frmContactDetails I can then enter data into all of the
other fields of the 7Step form but when i try to save the record it saves the
record as a new record each time. There should only be 1 7Step record for
each selected Contact. Not all of the contacts will have a seven step record
but the ones that do, should only have one record in the 7Step Table
Thank you for your help.

 

[LiquidWrench]

Okay here is how i need this to work. I have the Contact Details form with a
cmd button on it to open the 7Step form. the 7 Step form has an ID field
that has the control source set to the ID text box on the Contact Details
form. When the 7 Step form opens it will show the current name and ID of the
contact and I can edit the data on the form but it will not save because the
control source for the ID field on the 7Step form is not set to the 7Step
table. The 7Step table is currently empty of data because I want to be able
to add new records for selected contacts as needed how can I make the contact
ID save into the 7 Step table to make the relationship for each contact? If
needed I can give a field list for the tables although rather long.
--
LiquidWrench

"Of all the things I have lost in life, I miss my mind the most!!"

More than one way to skin a cat. You can always use DLOOKUP() to populate
controls but I don't know why it would be a problem to use the related table
since they are clearly related on the ID.

Bonnie

http://www.dataplus-svc.com

Just a little more insight that may or may not be of help. The form 7Step
was created from a select query the query includes all of the fields from
the 7Step table one of which is a field called ID this field is set to
"Indexed (No Duplicates) and is not an autonumber field because it is the
secondary key in a one to one relationship with the Contact Details table,
which contains an autonumber field "ID"
I can open the form and if I have the recordsource for the ID field on the
7Step form set to =[Forms]![frmContactDetails]![ID] it will display the value
in the ID field in frmContactDetails I can then enter data into all of the
other fields of the 7Step form but when i try to save the record it saves the
record as a new record each time. There should only be 1 7Step record for
each selected Contact. Not all of the contacts will have a seven step record
but the ones that do, should only have one record in the 7Step Table
Thank you for your help.

will this work even if the 2 tables have a 1 to 1 relationship? If so how do
I do this? BTW I started this whole venture using the contact management

[quoted text clipped - 24 lines]

Carl Rapson

 

[LiquidWrench]

The data source is a select query. Instead of trying to explain here is the
SQL Code from this query:

SELECT Contacts.ID, [7 Step].TrackStartDate, [7 Step].Paslet1StartDate, [7
Step].Paslet1EndDate, [7 Step].Paslet1ActualDate, [7 Step].WCardStartDate, [7
Step].WCardEndDate, [7 Step].WCardActualDate, [7 Step].WCallStartDate, [7
Step].WCallEndDate, [7 Step].WCallActualDate, [7 Step].DOZStartDate, [7
Step].DOZEndDate, [7 Step].DOZActualDate, [7 Step].TransStartDate, [7
Step].TransEndDate, [7 Step].TransActualDate, [7 Step].HBSStartDate, [7
Step].HBSEndDate, [7 Step].HBSActualDate, [7 Step].Paslet2StartDate, [7
Step].Paslet2EndDate, [7 Step].Paslet2ActualDate, [7 Step].TrackCompleteDate
FROM Contacts LEFT JOIN [7 Step] ON Contacts.ID = [7 Step].ID;

--
LiquidWrench

"Of all the things I have lost in life, I miss my mind the most!!"

What is the data source for the Control Source on the 7Step form?

Bonnie

http://www.dataplus-svc.com

Okay here is how i need this to work. I have the Contact Details form with a
cmd button on it to open the 7Step form. the 7 Step form has an ID field
that has the control source set to the ID text box on the Contact Details
form. When the 7 Step form opens it will show the current name and ID of the
contact and I can edit the data on the form but it will not save because the
control source for the ID field on the 7Step form is not set to the 7Step
table. The 7Step table is currently empty of data because I want to be able
to add new records for selected contacts as needed how can I make the contact
ID save into the 7 Step table to make the relationship for each contact? If
needed I can give a field list for the tables although rather long.

More than one way to skin a cat. You can always use DLOOKUP() to populate
controls but I don't know why it would be a problem to use the related table

[quoted text clipped - 23 lines]

Carl Rapson

 

[LiquidWrench]

Yes that is correct. After editing the 7Step record I want to be able save
the information into the tables. I tried to add a page onto the existing
Contact Details form (because it has tabs) but all of the text boxes and such
bleed through the first page. I'm not sure if this is what you call a
subform or not but that is how I originally wanted it instead of 2 separate
forms.
--
LiquidWrench

"Of all the things I have lost in life, I miss my mind the most!!"

Let me make sure I understand,

You have two different tables with a related field (can you make a query that
includes both tables and see all the related records without a problem?).
You have two forms based on these two tables. You want to popup the second
form filled in with the ID from the first form. Is this correct?


I would first check your data source is correct and put a filter (for example:
=forms!form1.ID) in the data tab of the forms properties. Then I would
include an OnCurrent event on the form that sets the value of the ID control
to the value of the ID on form one. You can either do this with a macro or
code in the OnCurrent event. Do you know how to make an event procedure?


Bonnie

http://www.dataplus-svc.com

The data source is a select query. Instead of trying to explain here is the
SQL Code from this query:

SELECT Contacts.ID, [7 Step].TrackStartDate, [7 Step].Paslet1StartDate, [7
Step].Paslet1EndDate, [7 Step].Paslet1ActualDate, [7 Step].WCardStartDate, [7
Step].WCardEndDate, [7 Step].WCardActualDate, [7 Step].WCallStartDate, [7
Step].WCallEndDate, [7 Step].WCallActualDate, [7 Step].DOZStartDate, [7
Step].DOZEndDate, [7 Step].DOZActualDate, [7 Step].TransStartDate, [7
Step].TransEndDate, [7 Step].TransActualDate, [7 Step].HBSStartDate, [7
Step].HBSEndDate, [7 Step].HBSActualDate, [7 Step].Paslet2StartDate, [7
Step].Paslet2EndDate, [7 Step].Paslet2ActualDate, [7 Step].TrackCompleteDate
FROM Contacts LEFT JOIN [7 Step] ON Contacts.ID = [7 Step].ID;

What is the data source for the Control Source on the 7Step form?

[quoted text clipped - 17 lines]

Carl Rapson

 

[LiquidWrench]

Also to let you know I created a query that included all of the Contacts
table and all of the 7 Step table and it returned no data. The reason that
this happened is that the data in the 7 Step table is not there at this point
and the whole purpose of this whole ordeal is to enter the data as needed
--
LiquidWrench

"Of all the things I have lost in life, I miss my mind the most!!"

Yes that is correct. After editing the 7Step record I want to be able save
the information into the tables. I tried to add a page onto the existing
Contact Details form (because it has tabs) but all of the text boxes and such
bleed through the first page. I'm not sure if this is what you call a
subform or not but that is how I originally wanted it instead of 2 separate
forms.
--
LiquidWrench

"Of all the things I have lost in life, I miss my mind the most!!"

Let me make sure I understand,

You have two different tables with a related field (can you make a query that
includes both tables and see all the related records without a problem?).
You have two forms based on these two tables. You want to popup the second
form filled in with the ID from the first form. Is this correct?


I would first check your data source is correct and put a filter (for example:
=forms!form1.ID) in the data tab of the forms properties. Then I would
include an OnCurrent event on the form that sets the value of the ID control
to the value of the ID on form one. You can either do this with a macro or
code in the OnCurrent event. Do you know how to make an event procedure?


Bonnie

http://www.dataplus-svc.com

The data source is a select query. Instead of trying to explain here is the
SQL Code from this query:

SELECT Contacts.ID, [7 Step].TrackStartDate, [7 Step].Paslet1StartDate, [7
Step].Paslet1EndDate, [7 Step].Paslet1ActualDate, [7 Step].WCardStartDate, [7
Step].WCardEndDate, [7 Step].WCardActualDate, [7 Step].WCallStartDate, [7
Step].WCallEndDate, [7 Step].WCallActualDate, [7 Step].DOZStartDate, [7
Step].DOZEndDate, [7 Step].DOZActualDate, [7 Step].TransStartDate, [7
Step].TransEndDate, [7 Step].TransActualDate, [7 Step].HBSStartDate, [7
Step].HBSEndDate, [7 Step].HBSActualDate, [7 Step].Paslet2StartDate, [7
Step].Paslet2EndDate, [7 Step].Paslet2ActualDate, [7 Step].TrackCompleteDate
FROM Contacts LEFT JOIN [7 Step] ON Contacts.ID = [7 Step].ID;

What is the data source for the Control Source on the 7Step form?

[quoted text clipped - 17 lines]

Carl Rapson

 

[LiquidWrench]

after entering data into the 7 step table i ran the query it returned the
data for the contact who's contact ID number I used in the 7 step ID field
which of course the primary key (ContactID) and secondary key(7Step).
--
LiquidWrench

"Of all the things I have lost in life, I miss my mind the most!!"

Try manually added some data and then check the query. Just to make sure the
query is returning what you expect.

Bonnie

http://www.dataplus-svc.com

Also to let you know I created a query that included all of the Contacts
table and all of the 7 Step table and it returned no data. The reason that
this happened is that the data in the 7 Step table is not there at this point
and the whole purpose of this whole ordeal is to enter the data as needed

Yes that is correct. After editing the 7Step record I want to be able save
the information into the tables. I tried to add a page onto the existing

[quoted text clipped - 38 lines]

Carl Rapson