SubForm Problem

[iamnu]

My SubForm has a Default View of DataSheet.
I am entering a Directory Name, and a Department Name.
The Department Name has a criteria as follows:

SELECT zDepartments.DeptID, zDepartments.DeptName,
zDepartments.DirectoryID
FROM zDepartments
WHERE (((zDepartments.DirectoryID)=[Forms]![PhoneDirectory]!
[PDSubForm]![cboDirectory]))
ORDER BY zDepartments.DeptName;

and I also perform the following:

Private Sub cboDirectory_AfterUpdate()
Me!cboDeptName.Requery
End Sub

This works fine until I enter a Directory in a different Row of the
DataSheet, then the Department Name Displays as blank for all previous
Rows. The correct data is correctly stored in the table, it just
doesn't display on the form.

Can someone explain how to fix this?

 

[Pat Hartman]

This happens because Access only maintains a single set of properties for
the form even though you are viewing multiple instances in the DataSheet or
Continuous Form views. You cannot solve this problem in DataSheet view.
You will need to switch the form to Continuous view.
1. Change the subform' default view from datasheet to continuous
2. Replace the subform's RecordSource with a query that joins to the lookup
table. You need to select the text value from the lookup table as well as
the numeric value from the main table.
3. Add a textbox control to the form and align it exactly over the combo.
4. On the format menu, send the new control to the back.
5. Change the combo's BackStyle to Transparent.
6. In the GotFocus event of the text box, move the focus to the combo. You
don't want the text box to ever get the focus.

In summary what we are doing is placing a control on the form that always
shows the text value that was selected from the combo. We are putting that
behind the combo control so that when the combo's RowSource is changed,
there is still a value to show even though the desired value is no longer in
the current RowSource.

 

[iamnu]

This happens because Access only maintains a single set of properties for
the form even though you are viewing multiple instances in the DataSheet or
Continuous Form views. You cannot solve this problem in DataSheet view.
You will need to switch the form to Continuous view.
1. Change the subform' default view from datasheet to continuous
2. Replace the subform's RecordSource with a query that joins to the lookup
table. You need to select the text value from the lookup table as well as
the numeric value from the main table.
3. Add a textbox control to the form and align it exactly over the combo.
4. On the format menu, send the new control to the back.
5. Change the combo's BackStyle to Transparent.
6. In the GotFocus event of the text box, move the focus to the combo. You
don't want the text box to ever get the focus.

In summary what we are doing is placing a control on the form that always
shows the text value that was selected from the combo. We are putting that
behind the combo control so that when the combo's RowSource is changed,
there is still a value to show even though the desired value is no longer in
the current RowSource.

My SubForm has a Default View of DataSheet.
I am entering a Directory Name, and a Department Name.
The Department Name has a criteria as follows:

SELECT zDepartments.DeptID, zDepartments.DeptName,
zDepartments.DirectoryID
FROM zDepartments
WHERE (((zDepartments.DirectoryID)=[Forms]![PhoneDirectory]!
[PDSubForm]![cboDirectory]))
ORDER BY zDepartments.DeptName;

and I also perform the following:

Private Sub cboDirectory_AfterUpdate()
Me!cboDeptName.Requery
End Sub

This works fine until I enter a Directory in a different Row of the
DataSheet, then the Department Name Displays as blank for all previous
Rows. The correct data is correctly stored in the table, it just
doesn't display on the form.

Can someone explain how to fix this?

Pat, thanks for the fine explanation and instructions.
I don't have time to fully understand why a DataSheet view will not
work in this case, but I do think I understand your solution for
making a Continuous Form work.

I'll give it a try later, and then respond again with my results.
Again, thanks for your help.
Bernie

 

[Pat Hartman]

The solution doesn't work for DataSheet view because you don't control how
the form looks, Access does and it won't overlay one control with another
which is the heart of this solution.

This happens because Access only maintains a single set of properties for
the form even though you are viewing multiple instances in the DataSheet
or
Continuous Form views. You cannot solve this problem in DataSheet view.
You will need to switch the form to Continuous view.
1. Change the subform' default view from datasheet to continuous
2. Replace the subform's RecordSource with a query that joins to the
lookup
table. You need to select the text value from the lookup table as well
as
the numeric value from the main table.
3. Add a textbox control to the form and align it exactly over the
combo.
4. On the format menu, send the new control to the back.
5. Change the combo's BackStyle to Transparent.
6. In the GotFocus event of the text box, move the focus to the combo.
You
don't want the text box to ever get the focus.

In summary what we are doing is placing a control on the form that always
shows the text value that was selected from the combo. We are putting
that
behind the combo control so that when the combo's RowSource is changed,
there is still a value to show even though the desired value is no longer
in
the current RowSource.

My SubForm has a Default View of DataSheet.
I am entering a Directory Name, and a Department Name.
The Department Name has a criteria as follows:

SELECT zDepartments.DeptID, zDepartments.DeptName,
zDepartments.DirectoryID
FROM zDepartments
WHERE (((zDepartments.DirectoryID)=[Forms]![PhoneDirectory]!
[PDSubForm]![cboDirectory]))
ORDER BY zDepartments.DeptName;

and I also perform the following:

Private Sub cboDirectory_AfterUpdate()
Me!cboDeptName.Requery
End Sub

This works fine until I enter a Directory in a different Row of the
DataSheet, then the Department Name Displays as blank for all previous
Rows. The correct data is correctly stored in the table, it just
doesn't display on the form.

Can someone explain how to fix this?

Pat, thanks for the fine explanation and instructions.
I don't have time to fully understand why a DataSheet view will not
work in this case, but I do think I understand your solution for
making a Continuous Form work.

I'll give it a try later, and then respond again with my results.
Again, thanks for your help.
Bernie

 

[iamnu]

The solution doesn't work for DataSheet view because you don't control how
the form looks, Access does and it won't overlay one control with another
which is the heart of this solution.

This happens because Access only maintains a single set of properties for
the form even though you are viewing multiple instances in the DataSheet
or
Continuous Form views. You cannot solve this problem in DataSheet view.
You will need to switch the form to Continuous view.
1. Change the subform' default view from datasheet to continuous
2. Replace the subform's RecordSource with a query that joins to the
lookup
table. You need to select the text value from the lookup table as well
as
the numeric value from the main table.
3. Add a textbox control to the form and align it exactly over the
combo.
4. On the format menu, send the new control to the back.
5. Change the combo's BackStyle to Transparent.
6. In the GotFocus event of the text box, move the focus to the combo.
You
don't want the text box to ever get the focus.
In summary what we are doing is placing a control on the form that always
shows the text value that was selected from the combo. We are putting
that
behind the combo control so that when the combo's RowSource is changed,
there is still a value to show even though the desired value is no longer
in
the current RowSource.

My SubForm has a Default View of DataSheet.
I am entering a Directory Name, and a Department Name.
The Department Name has a criteria as follows:
SELECT zDepartments.DeptID, zDepartments.DeptName,
zDepartments.DirectoryID
FROM zDepartments
WHERE (((zDepartments.DirectoryID)=[Forms]![PhoneDirectory]!
[PDSubForm]![cboDirectory]))
ORDER BY zDepartments.DeptName;
and I also perform the following:
Private Sub cboDirectory_AfterUpdate()
Me!cboDeptName.Requery
End Sub
This works fine until I enter a Directory in a different Row of the
DataSheet, then the Department Name Displays as blank for all previous
Rows. The correct data is correctly stored in the table, it just
doesn't display on the form.
Can someone explain how to fix this?

Pat, thanks for the fine explanation and instructions.
I don't have time to fully understand why a DataSheet view will not
work in this case, but I do think I understand your solution for
making a Continuous Form work.

I'll give it a try later, and then respond again with my results.
Again, thanks for your help.
Bernie

Okay, I have modified everything as you suggested, and everything
seems to be configured as you instructed. However, EXACTLY the same
thing happens with the Continuous Form as was happening with the
DataSheet Form. As soon as I select a "Directory" value in a new row,
the Department value goes blank (not visible).

By the way, an additional thing I did was to make the DeptName Text
Field tab stop = No. I don't know what else to try at this point.

I'm sure this is not a problem with the form, but something else
regarding the Directory and Department Tables. So in case that is the
problem, I'm showing you the table design of each.

zDepartments: Table
DeptID.........AutoNumber
DeptName...Text
DirectoryID...Number...Row Source for Lookup: SELECT
zDirectories.DirectoryID, zDirectories.Directory FROM zDirectories
ORDER BY zDirectories.Directory;

zDirectories: Table
DirectoryID...AutoNumber
Directory.....Text

I hope this helps you, or someone else to find the problem.

Thanks again...
Bernie

 

[Pat Hartman]

Try changing the z order of the two controls. I may have that backwards. I
don't have a database where I used this method handy. To prove to yourself
that it will work, drag the new control down to a separate line. The new
control should show the valid value regardless of the state of the combo's
RowSource.

The solution doesn't work for DataSheet view because you don't control
how
the form looks, Access does and it won't overlay one control with another
which is the heart of this solution.

This happens because Access only maintains a single set of properties
for
the form even though you are viewing multiple instances in the
DataSheet
or
Continuous Form views. You cannot solve this problem in DataSheet
view.
You will need to switch the form to Continuous view.
1. Change the subform' default view from datasheet to continuous
2. Replace the subform's RecordSource with a query that joins to the
lookup
table. You need to select the text value from the lookup table as
well
as
the numeric value from the main table.
3. Add a textbox control to the form and align it exactly over the
combo.
4. On the format menu, send the new control to the back.
5. Change the combo's BackStyle to Transparent.
6. In the GotFocus event of the text box, move the focus to the combo.
You
don't want the text box to ever get the focus.

In summary what we are doing is placing a control on the form that
always
shows the text value that was selected from the combo. We are putting
that
behind the combo control so that when the combo's RowSource is
changed,
there is still a value to show even though the desired value is no
longer
in
the current RowSource.

My SubForm has a Default View of DataSheet.
I am entering a Directory Name, and a Department Name.
The Department Name has a criteria as follows:

SELECT zDepartments.DeptID, zDepartments.DeptName,
zDepartments.DirectoryID
FROM zDepartments
WHERE (((zDepartments.DirectoryID)=[Forms]![PhoneDirectory]!
[PDSubForm]![cboDirectory]))
ORDER BY zDepartments.DeptName;

and I also perform the following:

Private Sub cboDirectory_AfterUpdate()
Me!cboDeptName.Requery
End Sub

This works fine until I enter a Directory in a different Row of the
DataSheet, then the Department Name Displays as blank for all
previous
Rows. The correct data is correctly stored in the table, it just
doesn't display on the form.

Can someone explain how to fix this?

Pat, thanks for the fine explanation and instructions.
I don't have time to fully understand why a DataSheet view will not
work in this case, but I do think I understand your solution for
making a Continuous Form work.

I'll give it a try later, and then respond again with my results.
Again, thanks for your help.
Bernie

Okay, I have modified everything as you suggested, and everything
seems to be configured as you instructed. However, EXACTLY the same
thing happens with the Continuous Form as was happening with the
DataSheet Form. As soon as I select a "Directory" value in a new row,
the Department value goes blank (not visible).

By the way, an additional thing I did was to make the DeptName Text
Field tab stop = No. I don't know what else to try at this point.

I'm sure this is not a problem with the form, but something else
regarding the Directory and Department Tables. So in case that is the
problem, I'm showing you the table design of each.

zDepartments: Table
DeptID.........AutoNumber
DeptName...Text
DirectoryID...Number...Row Source for Lookup: SELECT
zDirectories.DirectoryID, zDirectories.Directory FROM zDirectories
ORDER BY zDirectories.Directory;

zDirectories: Table
DirectoryID...AutoNumber
Directory.....Text

I hope this helps you, or someone else to find the problem.

Thanks again...
Bernie

 

[iamnu]

Try changing the z order of the two controls. I may have that backwards. I
don't have a database where I used this method handy. To prove to yourself
that it will work, drag the new control down to a separate line. The new
control should show the valid value regardless of the state of the combo's
RowSource.

The solution doesn't work for DataSheet view because you don't control
how
the form looks, Access does and it won't overlay one control with another
which is the heart of this solution.

This happens because Access only maintains a single set of properties
for
the form even though you are viewing multiple instances in the
DataSheet
or
Continuous Form views. You cannot solve this problem in DataSheet
view.
You will need to switch the form to Continuous view.
1. Change the subform' default view from datasheet to continuous
2. Replace the subform's RecordSource with a query that joins to the
lookup
table. You need to select the text value from the lookup table as
well
as
the numeric value from the main table.
3. Add a textbox control to the form and align it exactly over the
combo.
4. On the format menu, send the new control to the back.
5. Change the combo's BackStyle to Transparent.
6. In the GotFocus event of the text box, move the focus to the combo.
You
don't want the text box to ever get the focus.
In summary what we are doing is placing a control on the form that
always
shows the text value that was selected from the combo. We are putting
that
behind the combo control so that when the combo's RowSource is
changed,
there is still a value to show even though the desired value is no
longer
in
the current RowSource.

My SubForm has a Default View of DataSheet.
I am entering a Directory Name, and a Department Name.
The Department Name has a criteria as follows:
SELECT zDepartments.DeptID, zDepartments.DeptName,
zDepartments.DirectoryID
FROM zDepartments
WHERE (((zDepartments.DirectoryID)=[Forms]![PhoneDirectory]!
[PDSubForm]![cboDirectory]))
ORDER BY zDepartments.DeptName;
and I also perform the following:
Private Sub cboDirectory_AfterUpdate()
Me!cboDeptName.Requery
End Sub
This works fine until I enter a Directory in a different Row of the
DataSheet, then the Department Name Displays as blank for all
previous
Rows. The correct data is correctly stored in the table, it just
doesn't display on the form.
Can someone explain how to fix this?
Pat, thanks for the fine explanation and instructions.
I don't have time to fully understand why a DataSheet view will not
work in this case, but I do think I understand your solution for
making a Continuous Form work.
I'll give it a try later, and then respond again with my results.
Again, thanks for your help.
Bernie

Okay, I have modified everything as you suggested, and everything
seems to be configured as you instructed. However, EXACTLY the same
thing happens with the Continuous Form as was happening with the
DataSheet Form. As soon as I select a "Directory" value in a new row,
the Department value goes blank (not visible).

By the way, an additional thing I did was to make the DeptName Text
Field tab stop = No. I don't know what else to try at this point.

I'm sure this is not a problem with the form, but something else
regarding the Directory and Department Tables. So in case that is the
problem, I'm showing you the table design of each.

zDepartments: Table
DeptID.........AutoNumber
DeptName...Text
DirectoryID...Number...Row Source for Lookup: SELECT
zDirectories.DirectoryID, zDirectories.Directory FROM zDirectories
ORDER BY zDirectories.Directory;

zDirectories: Table
DirectoryID...AutoNumber
Directory.....Text

I hope this helps you, or someone else to find the problem.

Thanks again...
Bernie

I found the problem! Hooray!
It was just a simple matter of setting the BackStyle to Transparent.
I promise, I did this several times but apparently did not save the
form properly.
In any case, it now works.
I appreciate your help Pat. Thanks for staying with me on this.
Bernie

 

[Pat Hartman]

Glad to help. I'm hoping that one day, Access will handle this natively

Try changing the z order of the two controls. I may have that backwards.
I
don't have a database where I used this method handy. To prove to
yourself
that it will work, drag the new control down to a separate line. The new
control should show the valid value regardless of the state of the
combo's
RowSource.

The solution doesn't work for DataSheet view because you don't control
how
the form looks, Access does and it won't overlay one control with
another
which is the heart of this solution.

This happens because Access only maintains a single set of
properties
for
the form even though you are viewing multiple instances in the
DataSheet
or
Continuous Form views. You cannot solve this problem in DataSheet
view.
You will need to switch the form to Continuous view.
1. Change the subform' default view from datasheet to continuous
2. Replace the subform's RecordSource with a query that joins to
the
lookup
table. You need to select the text value from the lookup table as
well
as
the numeric value from the main table.
3. Add a textbox control to the form and align it exactly over the
combo.
4. On the format menu, send the new control to the back.
5. Change the combo's BackStyle to Transparent.
6. In the GotFocus event of the text box, move the focus to the
combo.
You
don't want the text box to ever get the focus.

In summary what we are doing is placing a control on the form that
always
shows the text value that was selected from the combo. We are
putting
that
behind the combo control so that when the combo's RowSource is
changed,
there is still a value to show even though the desired value is no
longer
in
the current RowSource.

My SubForm has a Default View of DataSheet.
I am entering a Directory Name, and a Department Name.
The Department Name has a criteria as follows:

SELECT zDepartments.DeptID, zDepartments.DeptName,
zDepartments.DirectoryID
FROM zDepartments
WHERE (((zDepartments.DirectoryID)=[Forms]![PhoneDirectory]!
[PDSubForm]![cboDirectory]))
ORDER BY zDepartments.DeptName;

and I also perform the following:

Private Sub cboDirectory_AfterUpdate()
Me!cboDeptName.Requery
End Sub

This works fine until I enter a Directory in a different Row of
the
DataSheet, then the Department Name Displays as blank for all
previous
Rows. The correct data is correctly stored in the table, it just
doesn't display on the form.

Can someone explain how to fix this?

Pat, thanks for the fine explanation and instructions.
I don't have time to fully understand why a DataSheet view will not
work in this case, but I do think I understand your solution for
making a Continuous Form work.

I'll give it a try later, and then respond again with my results.
Again, thanks for your help.
Bernie

Okay, I have modified everything as you suggested, and everything
seems to be configured as you instructed. However, EXACTLY the same
thing happens with the Continuous Form as was happening with the
DataSheet Form. As soon as I select a "Directory" value in a new row,
the Department value goes blank (not visible).

By the way, an additional thing I did was to make the DeptName Text
Field tab stop = No. I don't know what else to try at this point.

I'm sure this is not a problem with the form, but something else
regarding the Directory and Department Tables. So in case that is the
problem, I'm showing you the table design of each.

zDepartments: Table
DeptID.........AutoNumber
DeptName...Text
DirectoryID...Number...Row Source for Lookup: SELECT
zDirectories.DirectoryID, zDirectories.Directory FROM zDirectories
ORDER BY zDirectories.Directory;

zDirectories: Table
DirectoryID...AutoNumber
Directory.....Text

I hope this helps you, or someone else to find the problem.

Thanks again...
Bernie

I found the problem! Hooray!
It was just a simple matter of setting the BackStyle to Transparent.
I promise, I did this several times but apparently did not save the
form properly.
In any case, it now works.
I appreciate your help Pat. Thanks for staying with me on this.
Bernie

 

[Jokeascool]

Hello Pat and Others.

I am having the exact same problem, but I feel I am a little denser then
iamnu.

Could you please break down the solution a little further? I get all of the
points about the form controls. I just do not understand where the text box
will get the data from. Any aditional help would be very appreciated!

Joe

Try changing the z order of the two controls. I may have that backwards. I
don't have a database where I used this method handy. To prove to yourself
that it will work, drag the new control down to a separate line. The new
control should show the valid value regardless of the state of the combo's
RowSource.

The solution doesn't work for DataSheet view because you don't control
how
the form looks, Access does and it won't overlay one control with another
which is the heart of this solution.

This happens because Access only maintains a single set of properties
for
the form even though you are viewing multiple instances in the
DataSheet
or
Continuous Form views. You cannot solve this problem in DataSheet
view.
You will need to switch the form to Continuous view.
1. Change the subform' default view from datasheet to continuous
2. Replace the subform's RecordSource with a query that joins to the
lookup
table. You need to select the text value from the lookup table as
well
as
the numeric value from the main table.
3. Add a textbox control to the form and align it exactly over the
combo.
4. On the format menu, send the new control to the back.
5. Change the combo's BackStyle to Transparent.
6. In the GotFocus event of the text box, move the focus to the combo.
You
don't want the text box to ever get the focus.

In summary what we are doing is placing a control on the form that
always
shows the text value that was selected from the combo. We are putting
that
behind the combo control so that when the combo's RowSource is
changed,
there is still a value to show even though the desired value is no
longer
in
the current RowSource.

My SubForm has a Default View of DataSheet.
I am entering a Directory Name, and a Department Name.
The Department Name has a criteria as follows:

SELECT zDepartments.DeptID, zDepartments.DeptName,
zDepartments.DirectoryID
FROM zDepartments
WHERE (((zDepartments.DirectoryID)=[Forms]![PhoneDirectory]!
[PDSubForm]![cboDirectory]))
ORDER BY zDepartments.DeptName;

and I also perform the following:

Private Sub cboDirectory_AfterUpdate()
Me!cboDeptName.Requery
End Sub

This works fine until I enter a Directory in a different Row of the
DataSheet, then the Department Name Displays as blank for all
previous
Rows. The correct data is correctly stored in the table, it just
doesn't display on the form.

Can someone explain how to fix this?

Pat, thanks for the fine explanation and instructions.
I don't have time to fully understand why a DataSheet view will not
work in this case, but I do think I understand your solution for
making a Continuous Form work.

I'll give it a try later, and then respond again with my results.
Again, thanks for your help.
Bernie

Okay, I have modified everything as you suggested, and everything
seems to be configured as you instructed. However, EXACTLY the same
thing happens with the Continuous Form as was happening with the
DataSheet Form. As soon as I select a "Directory" value in a new row,
the Department value goes blank (not visible).

By the way, an additional thing I did was to make the DeptName Text
Field tab stop = No. I don't know what else to try at this point.

I'm sure this is not a problem with the form, but something else
regarding the Directory and Department Tables. So in case that is the
problem, I'm showing you the table design of each.

zDepartments: Table
DeptID.........AutoNumber
DeptName...Text
DirectoryID...Number...Row Source for Lookup: SELECT
zDirectories.DirectoryID, zDirectories.Directory FROM zDirectories
ORDER BY zDirectories.Directory;

zDirectories: Table
DirectoryID...AutoNumber
Directory.....Text

I hope this helps you, or someone else to find the problem.

Thanks again...
Bernie

I found the problem! Hooray!
It was just a simple matter of setting the BackStyle to Transparent.
I promise, I did this several times but apparently did not save the
form properly.
In any case, it now works.
I appreciate your help Pat. Thanks for staying with me on this.
Bernie