2 columns on one form from the same (finite) data source

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a list of place names related to bridges, some bridges have just a few
place names, but other bridges have more places than will fit the depth of
the exisitng column. Is there a way of extending the 'overflow' data into
another column. The data lists are finite, and could be accomodated with 2
columns. No extra data will ever be added so they will never change in terms
of length. I don't mind if the columns are continous forms or datasheets.
 
Hi

You could have 2 tables
tblBridges
Fields could be like this
BridgeID = Autonumber
BridgeName = Text
Plus other information, pictures etc.

tblBridgePlaces
BridgePlaceID = Autonumber
BridgePlaceDetails = Text (discribes the details of the bridge location)
BridgeID = this is the linking field from tblBridges

Next create 2 forms
1st form - set format to signle form
frmBridges
Insert all the details of the bridges (bring in all the fields from
tblBridges)
You could also have an unbound combobox that would find the bridge you
wanted to view.

2ndForm - set format to continous forms
Insert all the details (fields) from tblBridgePlaces in the Details section
of the form.
In the Header of the form places the linking field ( [BridgeID] )

Next open the 1st form in design view and use the toolbox to insert a
subform. Select the 2nd form from the list.

When you open the 1st form you will see all the place names shown in the
subform. There is not really a limit (within reason) of the number of place
names that you can show for each bridge.

_____________________________________________
Last - the unbound combo box on the 1st form
Open the form in design view and click the combobox and select properties
in the Event column of the properties box select AfterUpDate and click Build
Code
you will see this

Private Sub ComboName_AfterUpdate()

End Sub

You need to insert just a little bit of code to make it work so that it
should look like this when you have done

Private Sub ComboName_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[BridgetID] = " & Str(Me![ComboName])
Me.Bookmark = rs.Bookmark
End Sub

Change the ComboName shown above to the real name of the combo.

In the Data column insert this into the RowSource row

SELECT [tblBridges].[BridgeID], [tblBridges].[BridgeName] FROM [tblBridges]
ORDER BY [BridgeName];
_________________________________________________________

When you select a Bridge from the list a click it you will go to that
bridge's details (and places in the sub form)


Hope this helps
 
Back
Top