MS Access Question

skearon

I have a very simple database:

One table with these fields:
First name, last name, address line 1, town, county, Paid Sub (Yes/No)

What I want to do is present a form showing a list of records

At the top of the form I want to have a couple of drop down boxes which displays the the entries in the town and county fields (each unique entry just once) and would like when either is selected that the list changes to just those entries.

Ditto with a Tick Box for the Yes/No field.

I am not a programmer and new to databases :-)


Thanks in advance

Seth Brundle

see http://www.techonthenet.com/access/comboboxes/linked_combos.php

skearon

kbannon wrote:

see http://www.techonthenet.com/access/comboboxes/linked_combos.php

Thanks, but not sure what to look for :-)

Will upload an example shortly

Seth Brundle

upload your example DB and I'll help you out

skearon

Quick example posted,

in demo form when I select the "show by town" drop down it doesn't show a unique list of towns, e.g. bray is shown twice, I just want each unique town found the the table shown once?

Seth Brundle

you would need to change the SQL of the drop down
right click on the drop down and choose properties.
Then select the data tab
Currently the SQL is SELECT [demo table].ID, [demo table].Town FROM [demo table];
Change it to
SELECT DISTINCT [demo table].ID, [demo table].Town FROM [demo table] ORDER BY [demo table].Town ASC;
The DISTINCT keyword ensures each value is unique. The results are also ordered by town.

skearon

Thanks a million for the quick reply

Have made the change (see attached), however doesn't work, Bray still displays twice?

Seth Brundle

You need to remove the [demo table].ID, bit from the SQL.
However, as the combo uses two columns you need to reset that. Go to the Format tab. Change Column Count to 1 and under column widths remove the 0cm; bit.
You also appear to have a procedure running when the combo updates. Is this necessary? What are you trying to do?

skearon

kbannon wrote:

You need to remove the [demo table].ID, bit from the SQL.
However, as the combo uses two columns you need to reset that. Go to the Format tab. Change Column Count to 1 and under column widths remove the 0cm; bit.
You also appear to have a procedure running when the combo updates. Is this necessary? What are you trying to do?

It was created by the combo wizard, what I want to do is show a unique list of all the towns in the db, and when one is select for the bottom part of the form to show entries from this town only

Made the changes and the drop down list now only shows unique towns - thanks!, however then get a runtime error after I select one of them.

This is the code on Event, After Update:

Private Sub Combo12_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Nz(Me![Combo12], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

As ID is gone I presume this now should say TOWN, but what should I change & Str(Nz(Me![Combo12], 0)) to?

Any again many thanks for your help to date

Seth Brundle

Right click on your combo. Go to event and then click the ellipsis (...) for "On Change".
The visual Basic editor will open. add

Private Sub Combo12_Change()
    If Not IsNull(Me.Combo12) Then
        Filter = "Town='" & Me.Combo12 & "'"
        FilterOn = True
    Else
        FilterOn = False
    End If
End Sub

delete the rest except "Option Compare Database"

skearon

Thank you!!

It works :-)

Seth Brundle

of course it does


That tech on the net site I linked to in my first post is a great site.