Alternative to subform

  • Thread starter Thread starter Scott A
  • Start date Start date
S

Scott A

I have a subform tha currently displays data from a
related table (one to many relationship) as a continuous
form.

The data in the linked table is basically a list of names,
but there can be a lot of them to display, and I am
interested in presenting this data in a way that takes up
less space on the screen - the continuous form gives each
name a new row and the user only needs a snapshot of this
information...

I'm wondering if it would be possible to present this data
in a single textbox and concatenate all of the values to
the single control, separated by commas.

If anyone has another approach that would accomplish the
same thing, that would be great.

Thanks,

Scott
 
Single textbox is not a good idea. Too difficult to read. A listbox
could be used for this.

--
HTH

Dale Fye


I have a subform tha currently displays data from a
related table (one to many relationship) as a continuous
form.

The data in the linked table is basically a list of names,
but there can be a lot of them to display, and I am
interested in presenting this data in a way that takes up
less space on the screen - the continuous form gives each
name a new row and the user only needs a snapshot of this
information...

I'm wondering if it would be possible to present this data
in a single textbox and concatenate all of the values to
the single control, separated by commas.

If anyone has another approach that would accomplish the
same thing, that would be great.

Thanks,

Scott
 
Scott,

You could use a single unbound text box, as you said. In the form Current
event, you would need code something like this:

Dim ssql$, sTxt$, rsNames as recordset
stxt = ""
ssql = " SELECT Fullname " _
& " FROM qryNames " _
& " WHERE fKey = " & txtFkey & ";"
' in where clause, substitute your foreign key column name & text box name
' if key is Text, use WHERE fKey = '" & txtFkey & "';" (add single
quotes)
Set rsNames = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
If rsNames.RecordCount > 0 Then
With rsNames 'create string for txtNames
.MoveFirst
while not .eof
sTxt = !FullName & ", "
.MoveNext
Wend
sTxt = left(sTxt, Len(sTxt) - 2) 'trim the last "; "
End With
End if
txtNames = sTxt
set rsNames = Nothing
'add error trapping, etc

Hope this is what your looking for. There may be better ways to do this,
but this should work.

--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting Systems
www.fieldscope.com
860.242.4184
 
The listbox presents the same problem I have with the
continuous form - that all I get is one long list of names
that scrolls off the bottom of the form.

Is it possible to manipulate the listbox properties to
display the data in multiple columns?
 
How about several text boxes side by side? This should be more readable than
just a list without being so long as a sub-form.

Use code similar to that posted by Allan Thompson but put a vbCrLf after each
name, and either switch boxes when you have output however many rows will fit,
or switch after each name depending on whether you want the names in rows or
columns.

If you name the boxes in the form NameList1 etc you can use the Me("NameList" &
N) syntax to control which box to append to depending on the value of N.
 
You can make the listbox as small or as large as you want it. So the
argument that the listbox takes up more space than the textbox, with
all the names concatenated together, doesn't hold water.
Additionally, using the textbox makes it significantly harder to view
the information than in the list. Just make the listbox the size of
the textbox. If the user wants to see all the names, they can scroll
thru the list. If they don't, then they don't have to.

As I read your post again, it sounds like your form real estate has
room horizontally, but not vertically. In that case, you could create
a query that computes the alphabetical sequence of each person in the
list, then uses a second query to put them in columns that could be
displayed in a listbox.

qryNameRank
SELECT T.NameField
, Count(T1.NameField) as Rank
FROM yourTable T
LEFT JOIN yourTable T1
ON T1.NameField < T.NameField
GROUP BY T.NameField

Then do a crosstab to organize these in rows and columns. Something
like this. This will give you 3 columns, if you want more or less,
just edit the 3.

TRANSFORM First(qryNameRank.SalesName) AS FirstOfSalesName
SELECT Int([CountOfEmpID]/3) AS Row
FROM qryNameRank
GROUP BY Int([CountOfEmpID]/3)
PIVOT [CountOfEmpID] Mod 3;

--
HTH

Dale Fye


The listbox presents the same problem I have with the
continuous form - that all I get is one long list of names
that scrolls off the bottom of the form.

Is it possible to manipulate the listbox properties to
display the data in multiple columns?
 
Use a Combo Box, which will be the size of a Text Box if you aren't working
in it, and, thus, will not obscure other info, but when you work in it, has
a list similar to a List Box.

Larry Linson
Microsoft Access MVP
 
Looks like I have a number of great suggestions, so I'm
going to give each of them a try and see which one has the
best result - a good little VBA learning exercise for me
as well (wahoo)

Allan - I like your suggestion very much, as it is exactly
what I was interested in implementing.

I am, however, having a few problems with the code,
specifically on the line where it sets the value of the
recordset - I get a type mismatch error, and am not sure
which type it might be referring to. I usually get these
types of errors when forgetting that an FK field type is
text instead of a number, but that isn't the case here.

Here's what I'm working with - I modified the code a
little just to keep it consistent with the formats and
declarations I use in the rest of the application:

Private Sub Form_Current()

Dim strSql As String
Dim strTxt As String
Dim rstDistList As Recordset

'Set the WHERE clause of the SQL String
strTxt = ""
strSql = " SELECT RecipientCode " _
& " FROM qlkpDistList " _
& " WHERE SOPID = " & txtSOPID & ";"
'See note***
Set rstDistList = CurrentDb.OpenRecordset(strSql,
dbOpenSnapshot)
If rstDistList.RecordCount > 0 Then
With rstDistList 'create string for txtDistList
.MoveFirst
While Not .EOF
strTxt = !FullName & ", "
.MoveNext
Wend
strTxt = Left(strTxt, Len(strTxt) -
2) 'trim the last "; "
End With
End If
txtDistList = strTxt
Set rstDistList = Nothing


End Sub

***This is the line where it hangs up and declares a type
mismatch. When I check the values returned by strSql in
the locals window, it is returning the correct number
displayed in txt SOPID.

If you have any idea as to why the code is hanging at this
point, please let me know - I am very interested in seeing
what It will look like when the names returned by the
query are concatenated to the textbox...

Thanks,

Scott A
 
I really don't have a preference for one or the other
(listbox, textbox)

You are right when you say my form has more horizontal
than vertical real estate - I currentyl have a list box
installed on the form, but if the list gets too long, the
user has to scroll. Unfortunately, my user group wants to
see all values from the related table without having to
scroll, and I am nice enough to see what will work. They
don't seem to care if it is difficult to read or not, as
long as it's all visible. The easiest thing to do is
probably redesign the form and install the listbox
somewhere near the top of the screen, but that would be
too easy!

I'm going to give the crosstab a try - I really like the
idea of organizing the information into three columns -
the layout of information will work perfectly for the form
in its existing design.

It's going to be my first crosstab, though, so please
understand if I ask a few really brainless questions.

Off to do some reading - thanks for the suggestion.

Scott A

-----Original Message-----
You can make the listbox as small or as large as you want it. So the
argument that the listbox takes up more space than the textbox, with
all the names concatenated together, doesn't hold water.
Additionally, using the textbox makes it significantly harder to view
the information than in the list. Just make the listbox the size of
the textbox. If the user wants to see all the names, they can scroll
thru the list. If they don't, then they don't have to.

As I read your post again, it sounds like your form real estate has
room horizontally, but not vertically. In that case, you could create
a query that computes the alphabetical sequence of each person in the
list, then uses a second query to put them in columns that could be
displayed in a listbox.

qryNameRank
SELECT T.NameField
, Count(T1.NameField) as Rank
FROM yourTable T
LEFT JOIN yourTable T1
ON T1.NameField < T.NameField
GROUP BY T.NameField

Then do a crosstab to organize these in rows and columns. Something
like this. This will give you 3 columns, if you want more or less,
just edit the 3.

TRANSFORM First(qryNameRank.SalesName) AS FirstOfSalesName
SELECT Int([CountOfEmpID]/3) AS Row
FROM qryNameRank
GROUP BY Int([CountOfEmpID]/3)
PIVOT [CountOfEmpID] Mod 3;

--
HTH

Dale Fye


The listbox presents the same problem I have with the
continuous form - that all I get is one long list of names
that scrolls off the bottom of the form.

Is it possible to manipulate the listbox properties to
display the data in multiple columns?
-----Original Message-----
Single textbox is not a good idea. Too difficult to read. A listbox
could be used for this.

--
HTH

Dale Fye


I have a subform tha currently displays data from a
related table (one to many relationship) as a continuous
form.

The data in the linked table is basically a list of names,
but there can be a lot of them to display, and I am
interested in presenting this data in a way that takes up
less space on the screen - the continuous form gives each
name a new row and the user only needs a snapshot of this
information...

I'm wondering if it would be possible to present this data
in a single textbox and concatenate all of the values to
the single control, separated by commas.

If anyone has another approach that would accomplish the
same thing, that would be great.

Thanks,

Scott


.
Click to expand...


.
Click to expand...
 
Scott,

Sorry I didn't check back on this earlier. I hope you have an answer, but
if not .. My guess would be that the column SOPID and the text box txtSOPID
are Text. If that is the case, you need a single quote inside the quotes
before and after & txtSOPID &. The line should read:

& " WHERE SOPID = '" & txtSOPID & "';"

Another possibility is that SOPID is Numeric but txtSOPID is being
interpreted as Text. In this case you need to convert txtSOPID as follows:

& " WHERE SOPID = " & clng(txtSOPID) & ";"


--
Allan Thompson
APT Associates/ FieldScope LLC
MS Office Automation / Measurement and Reporting Systems
www.fieldscope.com
860.242.4184
 
What gives? SOPID is a key field, and uses Autonumber - I
set the format of txtSOPID as numeric as well, but no luck.

I tried both the quotes and the conversion - but still get
a type mismatch error. Must be something else...

Not to worry, I've given up on the idea anyway and
redesigned the form to display the list vertically (sorry,
but I got too frustrated with it and decided it was best
to move on to other pieces of the application - now I get
to tell the users that scrolling is fun!!!)

Thanks for the suggestion.

Scott
 
Back
Top