Making Finding Aids for the WWW

..or Adventures in extracting HTML from Microsoft Access

4.2.2 Item listings

Now we're going to loop through rstSeries again, but this time we're going to extract all the item entries for each series into another recordset and use this to create an item listing. We'll reuse some of the variables we defined above.

	rstSeries.MoveFirst
	Do until rstSeries.EOF
		SQLItems = "SELECT * FROM Items \
			WHERE [series number] = '" & \
			rstSeries![series number] \
			& "' ORDER BY [item number]"
		Set rstItems = dbs.OpenRecordset(SQLItems)
(So for each series we create a recordset that contains all the items in that series)

		startList = "<DL>"
		endList = "</DL>"
(These will be used to create an HTML "Definition List")

The value of several variables will depend on the current series, so we redefine these inside the rstSeries loop.

		headTitle = "<TITLE>Item listing - Series " \
			& rstSeries![series number] & \
			" - " & rstSeries![series title] & \
			"</TITLE>"
		docTitle = "<H1>Series " & \
			rstSeries![series number] & " - " \
			& rstSeries![series title] & \
			"</H1>"
		fileName = "c:\finding_aid\series_" & \
			rstSeries![series number] & ".htm"
		fileNumber = FreeFile
		Open fileName For Output As #fileNumber
Start writing to file as before.

		Print #fileNumber, headOpen
		Print #fileNumber, headTitle
		Print #fileNumber, headClose
		Print #fileNumber, bodyOpen
		Print #fileNumber, docTitle
		Print #fileNumber, startList
Now we want to create a listing of each item in the series. So we just loop through rstItems, writing variables to the file as we go.

		rstItems.MoveFirst
		Do Until rstItems.EOF
			itemTitle = "<DT><B>" \
				& rstItems![item number] & \
				" - " & rstItems![item title] \
				& "</B>"
			itemDescription = "<DD>" & \
				rstItems![item description] & \
				"<P>"
			Print #fileNumber, itemTitle
			Print #fileNumber, itemDescription
			rstItems.MoveNext
		Loop
Finish off as before.

		Print #fileNumber, endList
		Print #fileNumber, bodyClose
		Close #fileNumber
	Loop
That's it!

Next Section - 4.2.3 The results

1. Introduction
2. Why use databases?
3. Exporting your files to databases
4. Producing HTML from databases
4.1 Export to rtf method
4.2 The module method
4.2.1 Contents page
4.2.2 Item listings
4.2.3 The results

View code:

Created by Tim Sherratt (Tim.Sherratt@asap.unimelb.edu.au)
Last modified: 16 March 1998