Connecting two view of the same data

[bceo via AccessMonster.com]

I have a form I designed in form view from a table, then I created another
form from the same table but this time in data sheet view and placed it
inside the main form as a sub form. How do I connect the two forms so that
when I click on a record in the data sheet view it will show in the form view,
so that I can edit it?

 

[DStegon via AccessMonster.com]

When you open form b have form A set the Filter of form B to the specifc
field and value from From A that you want on Form b. You can also do a
master-child relationship so that the second form is always getting its
filter from the frist form.

 

[bceo via AccessMonster.com]

Thank you for your response DS.
I have tried to follow your suggestion but I still could not get the results
I wish. I am not sure what you mean in the first part, but I did give it a
try but it restricted the records I would see. I also tried the master-child
relationship but it too restricted the records I would see ( I have about 8
fields in the table) . The only way I could get all my records to show in
the data sheet view was not to use any links. What did I do wrong?
I should also inform you that I am using Access 2003.

 

[DStegon via AccessMonster.com]

Then you are not choosing the master and child fields correctly. When you
have a main form that has a table as the Record source and you add a subform
Access will try to setup a master-child relation that makes sense but you can
always edit it by choosing the subform and right click to get the properties.
Set the master and child to the PK of the table and when you move through the
records on the main form the subform will show the corresponding record.

I am not sure what you mean by "restricting" the records. That is the idea.
That the record you chose on the main form will "restrict" what record
appears in the subform. That is how it works. If that is not what you want
then I am really confused. Sorry.

Sorry for the confusion, I thought the first form was opening up a second
form and that is why a told you to open the form and set the forms Filter
property to the field and value you have selected on the first form.
Requires code.

Thank you for your response DS.
I have tried to follow your suggestion but I still could not get the results
I wish. I am not sure what you mean in the first part, but I did give it a
try but it restricted the records I would see. I also tried the master-child
relationship but it too restricted the records I would see ( I have about 8
fields in the table) . The only way I could get all my records to show in
the data sheet view was not to use any links. What did I do wrong?
I should also inform you that I am using Access 2003.

When you open form b have form A set the Filter of form B to the specifc
field and value from From A that you want on Form b. You can also do a

[quoted text clipped - 6 lines]

 

[AccessVandal via AccessMonster.com]

I would suggest that you readup on these links

http://www.datamodel.org/NormalizationRules.html

http://r937.com/relational.html

Right now, there's no solution to the Excel design table until your First
Normal Form is started.

 

[bceo via AccessMonster.com]

Once again thank you for your reply DS and your patience. What I wish to do
is to set a split view similar to the spit view in Access 2007, but in Access
2003. The "master" form is in form view, the"'sub form" is in data sheet
view and placed inside the main form window. Both views were build from the
same table. This table has a PK which is a auto number for the record count
(called "Counter"). If I use the master\child system with this PK field as
the link all I get is the last record appearing in the data sheet view. What
I want to happen is for all the record to appear in the data sheet view and
when my staff select a record in the data sheet view, the same record will
show in the "Form View" where my staff can edit it if required. This is what
happens in 2007 version, I do not want any edits done on the data sheet view
which is what they are doing now.
Is this possible in Access 2003? If so how?

Then you are not choosing the master and child fields correctly. When you
have a main form that has a table as the Record source and you add a subform
Access will try to setup a master-child relation that makes sense but you can
always edit it by choosing the subform and right click to get the properties.
Set the master and child to the PK of the table and when you move through the
records on the main form the subform will show the corresponding record.

I am not sure what you mean by "restricting" the records. That is the idea.
That the record you chose on the main form will "restrict" what record
appears in the subform. That is how it works. If that is not what you want
then I am really confused. Sorry.

Sorry for the confusion, I thought the first form was opening up a second
form and that is why a told you to open the form and set the forms Filter
property to the field and value you have selected on the first form.
Requires code.

Thank you for your response DS.
I have tried to follow your suggestion but I still could not get the results

[quoted text clipped - 10 lines]

 

[DStegon via AccessMonster.com]

Just make two forms. One with your DATA SHEET view. One with your Form view
in Columnar view, I am assuming for ease of reading.

on the DATASHEET view put this code

Private Sub Form_DblClick(Cancel As Integer)

DoCmd.OpenForm "Employees2", acNormal, , "EmployeeID =" & Me.EmployeeID,
acFormEdit

End Sub

Change "Employees2" to the name of the form that is the Columnar view.
Change the WHERE ("EmployeeID =" & Me.EmployeeID) to the Field name for your
PK. The form will launch into a FILTERED format only showing the one matchin
record to the record they DOUBLE clicked on. Tell your staff to edit the
information they need to double click on the record on your primary form and
it will launch to your second form with only that information showing.

Once again thank you for your reply DS and your patience. What I wish to do
is to set a split view similar to the spit view in Access 2007, but in Access
2003. The "master" form is in form view, the"'sub form" is in data sheet
view and placed inside the main form window. Both views were build from the
same table. This table has a PK which is a auto number for the record count
(called "Counter"). If I use the master\child system with this PK field as
the link all I get is the last record appearing in the data sheet view. What
I want to happen is for all the record to appear in the data sheet view and
when my staff select a record in the data sheet view, the same record will
show in the "Form View" where my staff can edit it if required. This is what
happens in 2007 version, I do not want any edits done on the data sheet view
which is what they are doing now.
Is this possible in Access 2003? If so how?

Then you are not choosing the master and child fields correctly. When you
have a main form that has a table as the Record source and you add a subform

[quoted text clipped - 18 lines]

 

[bceo via AccessMonster.com]

Thanks for the help and the script, I will give it a try and get back to you.

Just make two forms. One with your DATA SHEET view. One with your Form view
in Columnar view, I am assuming for ease of reading.

on the DATASHEET view put this code

Private Sub Form_DblClick(Cancel As Integer)

DoCmd.OpenForm "Employees2", acNormal, , "EmployeeID =" & Me.EmployeeID,
acFormEdit

End Sub

Change "Employees2" to the name of the form that is the Columnar view.
Change the WHERE ("EmployeeID =" & Me.EmployeeID) to the Field name for your
PK. The form will launch into a FILTERED format only showing the one matchin
record to the record they DOUBLE clicked on. Tell your staff to edit the
information they need to double click on the record on your primary form and
it will launch to your second form with only that information showing.

Once again thank you for your reply DS and your patience. What I wish to do
is to set a split view similar to the spit view in Access 2007, but in Access

[quoted text clipped - 15 lines]

 

[bceo via AccessMonster.com]

I tried your script DS, but every time I try to use it I get an error at the .
Counter portion, just before the acFormEdit part of the line. My script is
as follows:

DoCmd.OpenForm "Daily Log", acNormal, , "Date =" & Me.Date, acFormEdit

I made sure the entire code was on a single line.
I tried it with my PK (Counter) and got the same error.
I did notice that I have no filter in the properties of the Data Sheet View,
but I do have an Order By, [Daily Log],[Date].
I did notice on the main form I do have a filter Counter =(a number follows),
I assume this is the counter (PK) of the record that the form is pointing to.
The Main Form also has the same Order By line.

What have I done wrong?

Just make two forms. One with your DATA SHEET view. One with your Form view
in Columnar view, I am assuming for ease of reading.

on the DATASHEET view put this code

Private Sub Form_DblClick(Cancel As Integer)

DoCmd.OpenForm "Employees2", acNormal, , "EmployeeID =" & Me.EmployeeID,
acFormEdit

End Sub

Change "Employees2" to the name of the form that is the Columnar view.
Change the WHERE ("EmployeeID =" & Me.EmployeeID) to the Field name for your
PK. The form will launch into a FILTERED format only showing the one matchin
record to the record they DOUBLE clicked on. Tell your staff to edit the
information they need to double click on the record on your primary form and
it will launch to your second form with only that information showing.

Once again thank you for your reply DS and your patience. What I wish to do
is to set a split view similar to the spit view in Access 2007, but in Access

[quoted text clipped - 15 lines]

 

[AccessVandal via AccessMonster.com]

Split Form does not work in A2003. Even if it works, you may have problems
using it with newer version of Access.

If you still persist, I have a solution for you.

I tried your script DS, but every time I try to use it I get an error at the .
Counter portion, just before the acFormEdit part of the line. My script is
as follows:

DoCmd.OpenForm "Daily Log", acNormal, , "Date =" & Me.Date, acFormEdit

I made sure the entire code was on a single line.
I tried it with my PK (Counter) and got the same error.
I did notice that I have no filter in the properties of the Data Sheet View,
but I do have an Order By, [Daily Log],[Date].
I did notice on the main form I do have a filter Counter =(a number follows),
I assume this is the counter (PK) of the record that the form is pointing to.
The Main Form also has the same Order By line.

What have I done wrong?

 

[DStegon via AccessMonster.com]

Date is a reserved word in VB it is the way to pull the system date. I
wouldnt think your code would even compile. You never want to name a field
or control or variable a reserved word "Date" or "Name" etc.

I tried your script DS, but every time I try to use it I get an error at the .
Counter portion, just before the acFormEdit part of the line. My script is
as follows:

DoCmd.OpenForm "Daily Log", acNormal, , "Date =" & Me.Date, acFormEdit

I made sure the entire code was on a single line.
I tried it with my PK (Counter) and got the same error.
I did notice that I have no filter in the properties of the Data Sheet View,
but I do have an Order By, [Daily Log],[Date].
I did notice on the main form I do have a filter Counter =(a number follows),
I assume this is the counter (PK) of the record that the form is pointing to.
The Main Form also has the same Order By line.

What have I done wrong?

Just make two forms. One with your DATA SHEET view. One with your Form view
in Columnar view, I am assuming for ease of reading.

[quoted text clipped - 20 lines]