Subform not showing all data

[Guest]

I have a subform that the agent can enter comments in regarding the owner's
request. I have set it up so that there is a history of comments. The
matching links are a home-grown key that will not change for a request, the
specialist, and an option that is chosen by the operator. My problem is I
want all comments to be shown but I auto-populate the option and specialist
from the Main Form for any new comments. Ideas?

 

[John W. Vinson]

I have a subform that the agent can enter comments in regarding the owner's
request. I have set it up so that there is a history of comments. The
matching links are a home-grown key that will not change for a request, the
specialist, and an option that is chosen by the operator. My problem is I
want all comments to be shown but I auto-populate the option and specialist
from the Main Form for any new comments. Ideas?

Not without knowing more about it! What's the Recordsource of the subform? The
Master/Child Link Fields? What about posting your code for the option and
specialist information?

John W. Vinson [MVP]

 

[Guest]

John,

Record source for the subform is SELECT Comments.CommentKeytomatch,
Comments.DateofComment, Comments.Splst, Comments.OptionChosen,
Comments.Comment FROM Comments;
Record source for the main form is a table called Distinct Waitlist Desired

Link Master Fields = CommentKey;Specialist;Results
Link Child fields = CommentKeytomatch;Splst;OptionChosen

The VB code is to propagate the date/time and the specialist (signon)
Private Sub Frame18_Click()
If Me.Results > zero Then
Me.Date_Worked = VBA.Date
Else
Me.Date_Worked = Null
End If

Me.Text100.Value = fOSUserName()
End Sub

Thanks

 

[Guest]

I was thinking, and pretty sure of it by testing, if I keep the key for the
comments the same, (ie same everything but the time obviously is different),
it brings back all entries. But if I supply another option or someone else's
id, it only shows the last comment.

 

[John W. Vinson]

Record source for the subform is SELECT Comments.CommentKeytomatch,
Comments.DateofComment, Comments.Splst, Comments.OptionChosen,
Comments.Comment FROM Comments;
Record source for the main form is a table called Distinct Waitlist Desired

Link Master Fields = CommentKey;Specialist;Results
Link Child fields = CommentKeytomatch;Splst;OptionChosen

This will show only records where all three fields already exist in the
subform with matching values - or it will add those three values to newly
created records.

Is that your intent? or do you just want to fill in OptionChosen into (null??)
fields based on the other two?

John W. Vinson [MVP]

 

[John W. Vinson]

I was thinking, and pretty sure of it by testing, if I keep the key for the
comments the same, (ie same everything but the time obviously is different),
it brings back all entries. But if I supply another option or someone else's
id, it only shows the last comment.

It will show only those record which match all of the options that you select.

John W. Vinson [MVP]

 

[Guest]

John,

No, I would like to see all comments that match the commentkey regardless of
who did the entering or what option was chosen. Can this be done?

 

[John W. Vinson]

No, I would like to see all comments that match the commentkey regardless of
who did the entering or what option was chosen. Can this be done?

Sure. Leave the Option out of the Master/Child link field.

John W. Vinson [MVP]

 

[Guest]

John,

If I do that, the specialist and the option will not populate but all the
comments show. Obviously, I'm doing something wrong. I thought I had to
include the specialist and the option in the link to get them to
auto-populate. Am I in a catch-22?

 

[John W. Vinson]

John,

If I do that, the specialist and the option will not populate but all the
comments show. Obviously, I'm doing something wrong. I thought I had to
include the specialist and the option in the link to get them to
auto-populate. Am I in a catch-22?

I'll come back to my earlier question:

WHY store the specialist and the option redundantly in the child table?

If they "belong" to the parent table, they should exist only in the parent
table.

If they "belong" to the child table, use a control on the subform to populate
them.

Why are you selecting an option on the mainform in order to have it populate a
record on the child form?

John W. Vinson [MVP]

 

[Guest]

John,

I am beginning to understand (I think!). You are sayings that I should move
the Option Group to the subform instead of the Main form. Then, If I do
that, I would place my VB code on the Option Group to find the signon and the
option then place them both on the Comments table. Is that about it? Then I
would not need to link to anything but the key that ties the two forms
together, Right?

One more question (I'm certain I've worn out my welcome by now.) Is there a
good book you could recommend that would go into DETAIL regarding all this
stuff including VB coding?

Again thank you (& everyone) for your kind help and awesome knowledge.

 

[John W. Vinson]

John,

I am beginning to understand (I think!). You are sayings that I should move
the Option Group to the subform instead of the Main form. Then, If I do
that, I would place my VB code on the Option Group to find the signon and the
option then place them both on the Comments table. Is that about it? Then I
would not need to link to anything but the key that ties the two forms
together, Right?

Sounds reasonable to me: put the controls that populate a table onto the form
which populates that table!

One more question (I'm certain I've worn out my welcome by now.) Is there a
good book you could recommend that would go into DETAIL regarding all this
stuff including VB coding?

The gold standard is the Access <version> Developer's Handbook by Getz, Litwin
et al., Sybex Press. 2400 pages plus, pretty expensive... and you'll save ten
times its price in your time within the first week if you're a serious
database developer.

Again thank you (& everyone) for your kind help and awesome knowledge.

You're welcome - glad to be able to help!

John W. Vinson [MVP]

 

[Guest]

John,

I put my option group inside the Subform but when I chg from Design to Form
View it only is showing the value of the option group and I do not see any of
the options that should be available for clicking. Also it is showing
everything in horizonal listing not the vertical that was designed. Ideas?

 

[John W. Vinson]

John,

I put my option group inside the Subform but when I chg from Design to Form
View it only is showing the value of the option group and I do not see any of
the options that should be available for clicking. Also it is showing
everything in horizonal listing not the vertical that was designed. Ideas?

I'm sorry, I have NO idea what you're talking about.

"the options available for clicking"? Do you mean the Labels in the option
group control?

How did you "put the option group inside the subform"?

Perhaps you could simply delete the control that you don't want and recreate a
new one using the option group tool on the toolbar.

John W. Vinson [MVP]

 

[Guest]

I'm sorry; I keep thinking you have seen the form/subform.

I created a totally new subform complete with the Option Group. The Option
Group has 11 options with radio click buttons. There is no default and the
result is stored in OptionResult. I even changed the background color to
make it 'pretty' (blue).

When I leave the design mode in just the subform, it looks exactly the way I
want it. The option group is showing and the color is blue. When I put the
Main Form in design view, it does not show the color or the proper placement
of the elements. When I go to Form View, it shows only a window that shows
the data elements I want but in a horizonal line with none of the options
just the OptionResult. Obviously, I did something wrong but I have no idea
what. I recreated everything 2-3 times and it always is the same.

 

[Pieter Wijnen]

This may be far fetched, but have you tried setting Windows Theme to Windows
Classic?

Pieter

 

[Guest]

I had not thought of that but it did not work. I keep thinking there must be
a setting somewhere that says vertical / horizonal that pertains only to
subforms. Thanks for the try!

 

[John W. Vinson]

When I leave the design mode in just the subform, it looks exactly the way I
want it. The option group is showing and the color is blue. When I put the
Main Form in design view, it does not show the color or the proper placement
of the elements. When I go to Form View, it shows only a window that shows
the data elements I want but in a horizonal line with none of the options
just the OptionResult. Obviously, I did something wrong but I have no idea
what. I recreated everything 2-3 times and it always is the same.

It sounds like the Subform is in Datasheet view. Open the subform in design
view; view its Properties; and change the default view from Datasheet to
Continuous. You may need to move the controls around to make them look better.

John W. Vinson [MVP]

 

[Guest]

YOU ARE A GENIUS!!!!

Many Thanks

It sounds like the Subform is in Datasheet view. Open the subform in design
view; view its Properties; and change the default view from Datasheet to
Continuous. You may need to move the controls around to make them look better.

John W. Vinson [MVP]