Help needed on creating a subform

[Craig]

I am trying to create a form that shows quote info, but also has a
subform that lists all keywords and allows you to select multiple
keywords to associate with that quote. I have never done a subform
before and am struggling a little with it as there is an element of
indirection involved.

Any pointers would be greatly appreciated.

A summary of my DB so far is:

Keywords:
- KeywordID
- Keyword

Quote:
- QuoteID
- Quote

KeyworkLink:
- KeywordFK
- QuoteFK

 

[Rod Plastow]

Hi Craig,

Let me attempt to describe what you want to achieve before saying how. You
need a main form bound in some way to your Quote table. This will be a
single form. On this form you need a subform showing all the keywords chosen
for this quote and allowing the user to add further keywords or delete
existing keywords. This subform is bound to your KeywordLink table.

If you haven't done so, design your single form bound to the Quote table.

Next design a continuous form bound to your KeywordLink table. You don't
need to display QuoteFK on your continuous form, it just has to be in the
recordset. Make the single field displayed on your continuous form a combo
box. Through the wizard bind this combo box to your Keyword table or enter
something like

SELECT KeywordId, Keyword FROM Keyword ORDER BY Keyword

in the row source property. Column 1 is the one you want to store; column 2
is the one you want to display. To do the latter set the column widths to
0;something other than 0. You probably also want to switch on 'Limit to
list.' Check also the column count is 2.

Next you need to add the continuous form to your single form. If you're new
to this I recommend using the wizard. If you are asked about link fields
answer QuoteId for LinkMasterField and QuoteFK for LinkChildField.

It should now all work. It's a bit clunky as you will get Access/Jet error
messages if you try to add a duplicate keyword. There is a way of
restricting the combo box list to those keywords not already assigned.
However my advice is get the basics working first.

You could also implement this requirement using selectable list boxes but it
would involve more programming.

Don't hesitate to ask if you don't understand or if you need more info.

Rod

 

[mckbill]

I am trying to create a form that shows quote info, but also has a
subform that lists all keywords and allows you to select multiple
keywords to associate with that quote. I have never done a subform
before and am struggling a little with it as there is an element of
indirection involved.

Any pointers would be greatly appreciated.

A summary of my DB so far is:

Keywords:
- KeywordID
- Keyword

Quote:
- QuoteID
- Quote

KeyworkLink:
- KeywordFK
- QuoteFK

It might be best to practice with something simple to get the idea of
how FORM / SUBFORM works and behaves during use, and then elaborate
with your QUOTE / KEYWORD details.

First make two tables, and then make a form for each table.

Pick a table to represent the top of the hierarchy, this will be your
main table. The other table will be your sub.

Now open the form that points to the main table and make some extra
room to allow space to place your sub form.

Now, with this main form in design view look for the subform icon on
the toolbar and click it. Now use the mouse to drag a rectangle to
represent the space for your sub form. After you do this the access
subform tool wizard should ask you to select a form that will go in
this place, and then you will be asked to indicate what variables will
link the two. After you indicate one variable (aka, field) you will
see an option to enter up to two more variables for the linkage (just
use one for starters as you need to see how the form/subform works).

Once that's done you may need to tweak the size of the main and sub
forms by going in and out of design view and form view. Once you are
satisfied with the look of things you can play around by entering some
test data. Then close your form and look in the tables to see where
the test data have been written.

As you do this you will probably begin to see how to elaborate the
simple structure to fit your design objective of making a main/QUOTE
table/form with subform/KEYWORD details.

I hope this information is helpful.

-- Bill

 

[Craig]

Thanks for both your responses.

I am able to create a main form for my quote with a subform of
keywords. The keywords are a continuous form of comb boxes that you
can choose and add to. I have not played around yet to get it really
nice (e.g. removing some of the form controls I don't need seen,
making it easy to delete a keyword etc), but the basics are there.

What I was hoping to have was a selectable listbox of all Keywords
that the user could then select multiple instances of. To me that
would be a much more user friendly way of doing it, but YMMV.

Rod, you mentioned that this was possible with some programming. I'm
not adverse to a bit of programming, so if it is not too convoluted
I'd like to give it a try. What do I need to do?

Thanks again.

Craig

 

[Rod Plastow]

Craig,

I'll get back to you today. I need to test out some ideas on how to get the
list box to show keywords that already exist. Programming for new keywords
is easy.

Rod

 

[Rod Plastow]

Hi Craig,

It isn't quite as simple as I thought - it never is, is it? This solution
is a little involved and I would not recommend it if there are hundreds of
keywords, probably not otherwise you would need keywords for keywords.

First the concept. Dispense with a subform for maintaining keywords as this
is a little 'clunky' from the aspects of look and feel. Instead place a list
box on your main form. This list box is 'selectable,' that is the user can
select zero or more rows from the list at one time. Access offers two
options for selection: simple and extended. I prefer 'simple' for this
application as you select a row with a click of the mouse (or spacebar) and
deselect the same way. Then I thought wouldn't it be nice to sort all the
selected rows to the top of the list rather than have them scattered
throughout the list and possibly not all visible due to scrolling.

So that's the concept. I know of no automated way that Access will do this
for you so you must program all the features and functionality yourself. The
rest of this post describes how to do this. As far as possible I have used
names that will match yours but you must review the code and substitute your
own names where applicable.

The first task is to implement a list box on your main form. My list box is
called List7, yours will have a different name. In the properties box for
the list box select the 'Other' tab and change the 'Selectable' entry from
none to simple. Select the 'Format' tab and specify 3 columns with column
widths of 0; 1 in;0 (this hides the first and third columns). If you don't
think 1 in is wide enough for your keywords by all means make it wider.
Check under the 'Data' tab that the bound column is 1 and the Row Source Type
is Table/Query. You can alter the other properties to suit your design style.

Next you need to add some sub functions to the VBA code behind your form.
The most convenient way to open the VBA coding window is to select your main
form in design view, select the 'Event' tab in the properties box, double
click in the 'On Current' row to display [Event Procedure] and thenclick on
the elipsis button (...) at the end of the row. This creates an empty On
Current procedure for the form.

Don't enter anything in this procedure yet but scroll down below the End Sub
statement and enter the following. Don't worry this is by far the worst of
them all. Watch out as this board has wrapped some of the lines.

Private Sub PopulateListBox()

Dim strSQL As String

strSQL = "SELECT tblKeyword.Id, tblKeyword.Keyword,
tblKeywordLink.QuoteId " & _
"FROM tblKeyword LEFT JOIN tblKeywordLink ON tblKeyword.Id =
tblKeywordLink.KeywordId " & _
"WHERE tblKeywordLink.QuoteId = " & Me.Id & " " & _
"UNION " & _
"SELECT tblKeyword.Id, tblKeyword.Keyword, 0 " & _
"FROM tblKeyword " & _
"WHERE tblKeyword.Id Not In (SELECT tblKeyword.Id " & _
"FROM tblKeyword LEFT JOIN tblKeywordLink ON tblKeyword.Id =
tblKeywordLink.KeywordId " & _
"WHERE tblKeywordLink.QuoteId = " & Me.Id & " )" & _
"ORDER BY QuoteId DESC, Keyword"

Me.List7.RowSource = strSQL

End Sub

What this procedure does is to construct a SQL string that is a UNION query
based on the current value of the quote id. You can see that '& Me.Id &'
occurs twice, once in each WHERE clause. I have called the field that
contains the quote id, 'Id' for brevity. 'Me' is VBA shorthand for the
current VBA object which, when the code executes is the main form. So this
SQL string needs to be reconstructed every time you move to a new quote
record; thus it will eventually be invoked from the On Current event of the
form.

If you are not familiar with the '& _' symbols they are simply the string
concatenation symbol (&) and the new line continuation symbol (_). It makes
it much easier to read if you break up long strings across several coding
lines.

A UNION query concatenates the results of two separate queries. In the
above the first query (before the UNION keyword) retrieves existing keyword
links for the current quote. It selects, in this sequence, the keyword id
and the keyword text. I have also added the quote id to the selection purely
for ordering the list box entries. The second query goes to the keyword
table and retrieves all the keywords that are not in the result set of the
first query. As there is no quote id in these cases the SQL includes the
number zero in the equivalent position. The ORDER BY clause first sorts the
active links to the top of the list - the assumption is that quote id is
greater than zero - and then sorts alphanumerically within these divisions.

[A note here for any other posters: I played long and hard trying to get
this to work in the Row Source property but Access failed to recognise Me.Id
in whatever guise I tried. I also tried to avoid a UNION query but failed.
Any suggestions?]

Finally there is a statement in the procedure that assigns the SQL to the
row source of the list box. This asisignment will trigger a requery of the
list.

Now you need to enter another procedure.

Private Sub SynchroniseListBox()

Dim i As Long

For i = 0 To Me.List7.ListCount - 1
If Me.List7.Column(2, i) > 0 Then
Me.List7.Selected(i) = True
Else
Me.List7.Selected(i) = False
End If
Next

End Sub

A couple of explanations here: columns and rows are referenced relative to
zero thus it is necessary to subtract 1 from the list count as the
terminating condition for the loop. Column(2,i) is the value of quote id
(from the first query) or zero (from the second query). If this value is
greater than zero indicating an active keyword link then we must switch on
the list box's selected property for that row.

Now back to that so far empty On Current procedure.

Private Sub Form_Current()

PopulateListBox
SynchroniseListBox

End Sub

Half the job is now done and I would strongly recommend that you test what
you have done. You need to constuct some keyword links by other methods. My
next post will continue with how to keep your tables up-to-date.

Regards,

Rod

 

[Rod Plastow]

I continue the saga, this time describing how to keep your KeywordLink table
up-to-date.

You could force an update every time the user clicked on a keyword in the
list. My solution however only updates the table when the user exits the
list box. So the user could make several changes before the table update
occurs.

You need to write three new procedures, one to scan the list, one to add new
links and one to delete existing links.

I haven't mentioned this yet but by writing several small single purpose
procedures it is easy to reuse them. You might in the future want to put a
command button on the form that resynchronises and requeries the list box.
Thus if the user has selected scattered keywords thay can force them all to
be resorted to the top of the list - a visual check - without leaving the
current record. All easy to do.

Here's a procedure for adding new keyword links. This uses ADO (for DAO
please ask and give me a couple of days to relearn what I have forgotten).

Private Sub AddKeywordLink(r_QuoteId As Long, r_KeywordId As Long)

Dim rstLink As ADODB.Recordset

Set rstLink = New ADODB.Recordset
With rstLink
.ActiveConnection = CurrentProject.Connection
.LockType = adLockOptimistic
.Open "tblKeywordLink"
.AddNew
!QuoteId = r_QuoteId
!KeywordId = r_KeywordId
.Update
.Close
End With

End Sub

And here's the one for deleting links

Private Sub DeleteKeywordLink(r_QuoteId As Long, r_KeywordId As Long)

Dim rstLink As ADODB.Recordset

Set rstLink = New ADODB.Recordset
With rstLink
.ActiveConnection = CurrentProject.Connection
.LockType = adLockOptimistic
.Open "DELETE * FROM tblKeywordLink WHERE QuoteId = " & r_QuoteId &
" AND KeywordId = " & r_KeywordId
End With

End Sub

Here's the procedure for scanning the list and updating the table.

Private Sub UpdateFromListBox()

Dim i As Long

For i = 0 To Me.List7.ListCount - 1
Select Case Me.List7.Selected(i)
Case True
If Me.List7.Column(2, i) = 0 Then AddKeywordLink Me.Id,
Me.List7.Column(0, i)
Case False
If Me.List7.Column(2, i) > 0 Then DeleteKeywordLink Me.Id,
Me.List7.Column(0, i)
End Select
Next

End Sub

Finally you need to trigger this update. I used the Exit event for the list
box.

Private Sub List7_Exit(Cancel As Integer)

UpdateFromListBox

End Sub

Have fun. Get back to me if you have problems. A2007 highlights the
selected rows with a nasty black background. In the back of my mind is a way
of formatting the rows in a list box but I cannot for the life of me remember
how.

Regards,

Rod

 

[Craig]

Wow, you sure went to a lot of trouble!! Thanks for doing that. I'll
try it out as soon as I can and let you know.

Thanks again for all of your help, Rod. I appreciate it!

Craig

 

[Rod Plastow]

Craig,

No problem; writing the code and testing out ideas is the quicker fun part;
it's explaining the code that takes the time.

Don't hestate to get back to me if there are problems. Also if you want the
sample accdb (Access 2007) then email me at (e-mail address removed)

Season's Greetings,

Rod

 

[Craig]

Rod,

I got it all working - fantastic!! Thanks again for your assistance.
What's more, I can read the code and understand what is going on! Not
if only *I* could write the code I'll be happy!

Because this is a new db there isn't much data yet, so often when I am
entering data I find I need to add a new keyword to the list, so I add
one in a keyword (continuous) form I have for keywords, then move back
to the quotes form to use the new keyword. But, of course, I need to
refresh the list, so I go to the previous quote then back again.
Instead of doing this could I add an "Update Keywords" button which
calls Form_Current() to refresh the list?

Further to that, I want to eventually to have the quote form as the
main form with a button to jump to a form to add a keyword (needing
the update keywords button, or even have this happen automatically
somehow), and another to add a person and then update the person
dropdown list (each quote is associate with a person (NameID, Name)).
So to do that is there a way I can easily update the data in a
standard droplist associated with another table?

Then I just need to create some reports for tagged quotes (each quote
has a boolean Tagged field), quotes with a certain keyword(s), person
maintenance form, keyword maintenance form. Then add in a switchboard.
Fun!!

Thanks again for your help - it has really jump-started the project!

Craig