Datasheet View subForm and Form View subForm on same Master Form

  • Thread starter Edward Jones (Eddie)
  • Start date
E

Edward Jones (Eddie)

I have a main form that i want to show all records of tblItem in Datasheet
View and when a row is selected populates fields to be edited in Form View on
the same form. I have created two subForms (frmItemInventorySummary &
frmItemInventoryDetails). frmItemInventorySummary is a datasheet view of the
tblItems table, frmItemInventoryDetails is a form view of the tblItems table.
I added both the forms as subforms on a new form frmMain. I have the
following questions:

How do i only allow a user to select the entire row (i.e. if a cell is
clicked the entire row is selected)?

How do i get the value of the [ID] field of the selected record in a subform
in datasheet view?

When a user changes records in frmItemInventoryDetails how do i select the
corresponding row in frmItemInventorySummary?

Thank you for your help and any suggestions to better handle this issue.
Thanks.
 
S

Stuart McCall

Replies inline:

Edward Jones (Eddie) said:
I have a main form that i want to show all records of tblItem in Datasheet
View and when a row is selected populates fields to be edited in Form View on
the same form. I have created two subForms (frmItemInventorySummary &
frmItemInventoryDetails). frmItemInventorySummary is a datasheet view of
the

You don't need a second form for editing in form view. All you need to do
is,
when the user wishes to edit a record, do a DoCmd.OpenForm on the same
form that is hosted in your subform control. You will find that it is
ready-populated
(because its the same form containing the same record, not a new instance of
the
form, as is probably expected). Moreover (and perhaps obviously) the record
in the datasheet subform is automatically updated with the user's changes.
tblItems table, frmItemInventoryDetails is a form view of the tblItems table.
I added both the forms as subforms on a new form frmMain. I have the
following questions:

How do i only allow a user to select the entire row (i.e. if a cell is
clicked the entire row is selected)?

Not possible in datasheet view by clicking on a cell. The user can however
click the record selector on the LHS of the datasheet.
How do i get the value of the [ID] field of the selected record in a subform
in datasheet view?

variable = Me!SubformControlName.Form!ID
When a user changes records in frmItemInventoryDetails how do i select the
corresponding row in frmItemInventorySummary?

If the user clicked the record selector prior to opening the form view, the
record will still appear selected (remember no requery is needed, so the
datasheet is still displaying the same record).
 
E

Edward Jones (Eddie)

I apologize if i miss understood your response. If i did a DoCmd.OpenForm
wouldn't that just open a new instance of the form? Would it be possible to
post some sample code snippets to explain? I would like the final result of
the frmMain to show all records of tblItem on the top section of the form
and the fields in form view of the selected row on the bottom section for
editing. I have this behavior when the form opens by filtering the form view
based on the value of ID of datasheet view however when a record is changed
it only appplies to one of the views.

frmMain:
____________________
| |
| datasheet view |
| |
| form view |
|____________________|



Stuart McCall said:
Replies inline:

Edward Jones (Eddie) said:
I have a main form that i want to show all records of tblItem in Datasheet
View and when a row is selected populates fields to be edited in Form View on
the same form. I have created two subForms (frmItemInventorySummary &
frmItemInventoryDetails). frmItemInventorySummary is a datasheet view of
the

You don't need a second form for editing in form view. All you need to do
is,
when the user wishes to edit a record, do a DoCmd.OpenForm on the same
form that is hosted in your subform control. You will find that it is
ready-populated
(because its the same form containing the same record, not a new instance of
the
form, as is probably expected). Moreover (and perhaps obviously) the record
in the datasheet subform is automatically updated with the user's changes.
tblItems table, frmItemInventoryDetails is a form view of the tblItems table.
I added both the forms as subforms on a new form frmMain. I have the
following questions:

How do i only allow a user to select the entire row (i.e. if a cell is
clicked the entire row is selected)?

Not possible in datasheet view by clicking on a cell. The user can however
click the record selector on the LHS of the datasheet.
How do i get the value of the [ID] field of the selected record in a subform
in datasheet view?

variable = Me!SubformControlName.Form!ID
When a user changes records in frmItemInventoryDetails how do i select the
corresponding row in frmItemInventorySummary?

If the user clicked the record selector prior to opening the form view, the
record will still appear selected (remember no requery is needed, so the
datasheet is still displaying the same record).
Thank you for your help and any suggestions to better handle this issue.
Thanks.
 
S

Stuart McCall

Edward Jones (Eddie) said:
I apologize if i miss understood your response. If i did a DoCmd.OpenForm
wouldn't that just open a new instance of the form?

No.

That's what I was attempting to make clear in my last. It opens the same
instance
of the form, but in the form's form view, and on the same record as your
datasheet
is showing.

DoCmd.OpenForm "MyFormName", acNormal

Make a few changes, close the form and you will find that your datasheet row
is updated.
That's because its the _same form_ showing the _same record_.
Then filter your main form as in form_open.

Just give it a try - you'll soon see what I mean.

Would it be possible to
post some sample code snippets to explain? I would like the final result of
the frmMain to show all records of tblItem on the top section of the form
and the fields in form view of the selected row on the bottom section for
editing. I have this behavior when the form opens by filtering the form view
based on the value of ID of datasheet view however when a record is changed
it only appplies to one of the views.

frmMain:
____________________
| |
| datasheet view |
| |
| form view |
|____________________|



Stuart McCall said:
Replies inline:

message news:[email protected]...
I have a main form that i want to show all records of tblItem in Datasheet
View and when a row is selected populates fields to be edited in Form
View
on
the same form. I have created two subForms (frmItemInventorySummary &
frmItemInventoryDetails). frmItemInventorySummary is a datasheet view
of
the

You don't need a second form for editing in form view. All you need to do
is,
when the user wishes to edit a record, do a DoCmd.OpenForm on the same
form that is hosted in your subform control. You will find that it is
ready-populated
(because its the same form containing the same record, not a new instance of
the
form, as is probably expected). Moreover (and perhaps obviously) the record
in the datasheet subform is automatically updated with the user's changes.
tblItems table, frmItemInventoryDetails is a form view of the tblItems table.
I added both the forms as subforms on a new form frmMain. I have the
following questions:

How do i only allow a user to select the entire row (i.e. if a cell is
clicked the entire row is selected)?

Not possible in datasheet view by clicking on a cell. The user can however
click the record selector on the LHS of the datasheet.
How do i get the value of the [ID] field of the selected record in a subform
in datasheet view?

variable = Me!SubformControlName.Form!ID
When a user changes records in frmItemInventoryDetails how do i select the
corresponding row in frmItemInventorySummary?

If the user clicked the record selector prior to opening the form view, the
record will still appear selected (remember no requery is needed, so the
datasheet is still displaying the same record).
Thank you for your help and any suggestions to better handle this issue.
Thanks.
 
S

Stuart McCall

Sorry, the line:

DoCmd.OpenForm "MyFormName", acNormal

is incomplete. You need to supply the WhereCondition parameter. ie:

DoCmd.OpenForm "MyFormName", acNormal, , "id=" & Me.id

(assuming that the key field is a numeric called id)
Make a few changes, close the form and you will find that your datasheet row
is updated.
That's because its the _same form_ showing the _same record_.
Then filter your main form as in form_open.

Just give it a try - you'll soon see what I mean.

Would it be possible to
post some sample code snippets to explain? I would like the final result of
the frmMain to show all records of tblItem on the top section of the form
and the fields in form view of the selected row on the bottom section for
editing. I have this behavior when the form opens by filtering the form view
based on the value of ID of datasheet view however when a record is changed
it only appplies to one of the views.

frmMain:
____________________
| |
| datasheet view |
| |
| form view |
|____________________|
Form
view
of
the

You don't need a second form for editing in form view. All you need to do
is,
when the user wishes to edit a record, do a DoCmd.OpenForm on the same
form that is hosted in your subform control. You will find that it is
ready-populated
(because its the same form containing the same record, not a new instance of
the
form, as is probably expected). Moreover (and perhaps obviously) the record
in the datasheet subform is automatically updated with the user's changes.

tblItems table, frmItemInventoryDetails is a form view of the tblItems
table.
I added both the forms as subforms on a new form frmMain. I have the
following questions:

How do i only allow a user to select the entire row (i.e. if a cell is
clicked the entire row is selected)?

Not possible in datasheet view by clicking on a cell. The user can however
click the record selector on the LHS of the datasheet.


How do i get the value of the [ID] field of the selected record in a
subform
in datasheet view?

variable = Me!SubformControlName.Form!ID


When a user changes records in frmItemInventoryDetails how do i
select
view,
 
D

Duane Hookom

Apparently you have two subforms based off the same record source. One
subform is datasheet and the other is single form. You want the record with
the focus in the datasheet subform to be the only record displayed in the
single record subform. If this is the case, can you replace the datasheet
with a list box and then use the Link Master Child properties to synchronize
the two?

If you choose to not use a list box, add a text box to the main form named
"txtSynch". Add code in the On Current of the datasheet subform like:
Me.Parent.txtSynch = Me.PrimaryKeyFieldName
Then set the Link Master Child properties of the single record subform to
txtSynch and the primary key field.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top