Criteria for opening form from a different subform

Hi Scott,

I see you got rid of the default values for numeric foreign
keys smile

In my opinion, it is best not to use Captions in the table
design -- the only people who should open tables directly
are administrators and they need to see the real fieldnames,
not captions

Do be sure to fill out the field Descriptions in your table
designs -- these are used for StatusBarText when you created
forms.

you still have forms based on multiple tables -- if you plan
to edit data, your should not do this

in frmViewDonor, do not pull Position information into the
RecordSource -- this can be shown using a combobox.

Because qryViewDonor gets all fields from both Contributors
and PositionList, you are only going to see records where
PositionID is filled out. Here is what you see for your
current sql:

SELECT tblContributors.*
, ([DonorFirstName] & " " & [DonorLastName]) AS DonorName
, ([City] & ", " & [StateorProvince] & " " & [PostalCode])
AS CSZ
, tblContributors.DonorLastName
, tblPositionList.*
FROM tblPositionList
INNER JOIN tblContributors
ON tblPositionList.Position_ID = tblContributors.PositionID
ORDER BY tblContributors.DonorLastName;

Donor First Name
Diane
Scott
Karen
Mary
Luci
Claire
June
Amy
Linda

by changing the Join Type to show all records from
Contributors even if a matching record is not found in
PositionList, the FROM clause of the sql now is:

FROM tblPositionList
RIGHT JOIN tblContributors
ON tblPositionList.Position_ID = tblContributors.PositionID

and we now see 20 records:

Donor First Name
Diane
Scott
Jackson
Karen
William
Mary
James
Joyce Ann
Mary Ellen
Gaston
Willy
Luci
Claire
June
Bill
Sara
James
Sam
Amy
Linda

.... but, as I said, you should not be pulling the
tblPositionList table into the RecordSource for this form anyway

It doesn't even look like you are using any fields from
PositionList on your form. The combobox for PositionID can
use PositionList as a RowSource withOUT it being in the form
RecordSource



Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com

free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access

*
(: have an awesome day
*


Scott_Brasted via AccessMonster.com wrote:
Good morning Crystal,

Did you get this to work in your copy of the db? I followed your instructions
to the letter and I still get a blank form except for first record. Here is
what I did.
First changed all occurances of Name and Position to DonorName and Positn,
then:
1. moved idContributorID to subform header
2. renamed idContributorID control to ContributorID
3. changed background to black and text to white
4. selected ContributorID from Control Source dropdown list in property sheet
for text box control that is now in header of subform
5. saved subform
6 decided needed fresh start & deleted cbo & replaced w/Campaign Name field
temporarily in main form. will revisit soon.
7. saved main form.
8. double checked doubleclick event vb code on field "Donor_Name" for names,
etc. & changed idContributorID to ContributorID. Here is current code:
Private Sub Donor_Name_DblClick(Cancel As Integer)
DoCmd.OpenForm "frmViewDonor", , , "ContributorID=" & Me.ContributorID
End Sub
9. saved code
10. restarted Access

No joy. Still blank records.
Scott


Crystal (strive4peace) wrote:
Hi Scott,

"Wow is the only word that comes to mind."

gee thanks! you're welcome

"And it,of course is the one thing I really need to
understand. "

I am referring to the Property sheet for the control. Read
the Properties and Methods section in Access Basics.

~~~
go to the design view of frmCamPledgeListSub

Select the idContributorID control ... but since it is under
[Name], you cannot see it

To select it you can drop down the combobox at the top of
the Property sheet and choose -- idContributorID
(I don't know why you did not name the control the same as
the Controlsource -- but at least it is specific)

Once you do get the control selected, move it to the header
section where you can SEE it in Design View and set the
following properties:
Visible -- No (Format tab of Property sheet)
ForeColor -- White (Font Color icon)
BackColor -- Black (Paint Bucket icon)

On the DATA tab, you will see a property called --
ControlSource
click in it
when you do this, a drop-down arrow will appear to the right
-- this is the list of fields in your RecordSource. As you
can see, what you have it set to is not in the list...

these link controls are very important. It is one thing to
hide them from users when they view the form, but you need
to be able to see them right away when you look at the
design -- that is why I had you set it to be white on black.

"but that screws up the combo box and the vb for the cbo
gives me a run time error that says you cannot assign a
value to this object. "

if you still get an error, try to explain this better ...

"you did yoeman's work"

don't quite understand what you mean, but I will take it as
a compliment

Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com

free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access

*
(: have an awesome day
*

Crystal,

[quoted text clipped - 23 lines]
Best,
Scott

Evening Crystal,

One last thing hopefully. I took tblPostions out of the query and everything
works fine. I created a cbo for the position field and and it has the right
data in it, but the cbo is empty when I change records and stays empty. How
do I go about getting it to display the related data for the record. The data
list is there when I click the down arrow. Do I need code?

Best,
Scott

Crystal (strive4peace) wrote:
Hi Scott,

I see you got rid of the default values for numeric foreign
keys smile

In my opinion, it is best not to use Captions in the table
design -- the only people who should open tables directly
are administrators and they need to see the real fieldnames,
not captions

Do be sure to fill out the field Descriptions in your table
designs -- these are used for StatusBarText when you created
forms.

you still have forms based on multiple tables -- if you plan
to edit data, your should not do this

in frmViewDonor, do not pull Position information into the
RecordSource -- this can be shown using a combobox.

Because qryViewDonor gets all fields from both Contributors
and PositionList, you are only going to see records where
PositionID is filled out. Here is what you see for your
current sql:

SELECT tblContributors.*
, ([DonorFirstName] & " " & [DonorLastName]) AS DonorName
, ([City] & ", " & [StateorProvince] & " " & [PostalCode])
AS CSZ
, tblContributors.DonorLastName
, tblPositionList.*
FROM tblPositionList
INNER JOIN tblContributors
ON tblPositionList.Position_ID = tblContributors.PositionID
ORDER BY tblContributors.DonorLastName;

Donor First Name
Diane
Scott
Karen
Mary
Luci
Claire
June
Amy
Linda

by changing the Join Type to show all records from
Contributors even if a matching record is not found in
PositionList, the FROM clause of the sql now is:

FROM tblPositionList
RIGHT JOIN tblContributors
ON tblPositionList.Position_ID = tblContributors.PositionID

and we now see 20 records:

Donor First Name
Diane
Scott
Jackson
Karen
William
Mary
James
Joyce Ann
Mary Ellen
Gaston
Willy
Luci
Claire
June
Bill
Sara
James
Sam
Amy
Linda

... but, as I said, you should not be pulling the
tblPositionList table into the RecordSource for this form anyway

It doesn't even look like you are using any fields from
PositionList on your form. The combobox for PositionID can
use PositionList as a RowSource withOUT it being in the form
RecordSource

Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com

free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access

*
(: have an awesome day
*

Good morning Crystal,

[quoted text clipped - 97 lines]
Best,
Scott

--
Message posted via http://www.accessmonster.com

Hi Scott,

"everything works fine"

great!

"Do I need code?"

I don't think so.

"but the cbo is empty when I change records and stays empty."

.... it sounds like perhaps you don't have ControlSource
specified...

Please tell me the following properties for your combobox:

ControlSource
RowSource
ColumnCount
ColumnWidths
Listwidth


Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com

free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access

*
(: have an awesome day
*


Scott_Brasted via AccessMonster.com wrote:
Evening Crystal,

One last thing hopefully. I took tblPostions out of the query and everything
works fine. I created a cbo for the position field and and it has the right
data in it, but the cbo is empty when I change records and stays empty. How
do I go about getting it to display the related data for the record. The data
list is there when I click the down arrow.

Best,
Scott

Never mind, I figured it out. Thank you VERY much for teaching all this. It
has been a real eye opener. I am now reading your site. Learn , learn , learn.


All my best,
Scott

p.s. Took ALL captions out! Working on multi-table forms now. Lots of cbos

Crystal (strive4peace) wrote:
Hi Scott,

I see you got rid of the default values for numeric foreign
keys smile

In my opinion, it is best not to use Captions in the table
design -- the only people who should open tables directly
are administrators and they need to see the real fieldnames,
not captions

Do be sure to fill out the field Descriptions in your table
designs -- these are used for StatusBarText when you created
forms.

you still have forms based on multiple tables -- if you plan
to edit data, your should not do this

in frmViewDonor, do not pull Position information into the
RecordSource -- this can be shown using a combobox.

Because qryViewDonor gets all fields from both Contributors
and PositionList, you are only going to see records where
PositionID is filled out. Here is what you see for your
current sql:

SELECT tblContributors.*
, ([DonorFirstName] & " " & [DonorLastName]) AS DonorName
, ([City] & ", " & [StateorProvince] & " " & [PostalCode])
AS CSZ
, tblContributors.DonorLastName
, tblPositionList.*
FROM tblPositionList
INNER JOIN tblContributors
ON tblPositionList.Position_ID = tblContributors.PositionID
ORDER BY tblContributors.DonorLastName;

Donor First Name
Diane
Scott
Karen
Mary
Luci
Claire
June
Amy
Linda

by changing the Join Type to show all records from
Contributors even if a matching record is not found in
PositionList, the FROM clause of the sql now is:

FROM tblPositionList
RIGHT JOIN tblContributors
ON tblPositionList.Position_ID = tblContributors.PositionID

and we now see 20 records:

Donor First Name
Diane
Scott
Jackson
Karen
William
Mary
James
Joyce Ann
Mary Ellen
Gaston
Willy
Luci
Claire
June
Bill
Sara
James
Sam
Amy
Linda

... but, as I said, you should not be pulling the
tblPositionList table into the RecordSource for this form anyway

It doesn't even look like you are using any fields from
PositionList on your form. The combobox for PositionID can
use PositionList as a RowSource withOUT it being in the form
RecordSource

Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com

free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access

*
(: have an awesome day
*

Good morning Crystal,

[quoted text clipped - 97 lines]
Best,
Scott

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200912/1

you're welcome, Scott happy to help

"I am now reading your site. Learn , learn , learn ... Took
ALL captions out! Working on multi-table forms now. Lots of
cbos"

great and great! I am currently adding a Whistles and Bells
section to my site... so keep checking back till items
listed are done. The index lists stuff I plan to add pages for.

Whistles and Bells
http://www.accessmvp.com/Strive4Peace/Whistle

I have an example finished with code called Sort123, which
enables the user to easily sort by any column in a
Continuous form by clicking on the column header.

Now I am working on a page called BoldMe to demonstrate how
you can boldface a label if the checkbox is true, or
boldface the label of the selected option in a frame.

Once the items listed are done, I will probably add more ...
or maybe I'll finish another video -- who knows. Depends
which way the wind blows

Warm Regards,
Crystal
remote programming and training
http://MSAccessGurus.com

free video tutorials
http://www.YouTube.com/user/LearnAccessByCrystal

Access Basics
http://www.AccessMVP.com/strive4peace
free 100-page book that covers essentials in Access

*
(: have an awesome day
*


Scott_Brasted via AccessMonster.com wrote:
Never mind, I figured it out. Thank you VERY much for teaching all this. It
has been a real eye opener. I am now reading your site. Learn , learn , learn.


All my best,
Scott

p.s. Took ALL captions out! Working on multi-table forms now. Lots of cbos