Limiting list box by another field

[lukebowes]

Hello!

I'm trying to limit the funders under previous applications to ones with the same name so then when you click on one it loads that other funding application details...Could anyone tell me where I'm going wrong please?

SELECT [Sheet1].[ID], [Sheet1].[Funder] FROM Sheet1 WHERE [Sheet1.Funder] Like [Sheet1].[Funder] ORDER BY funder;

[Sheet1.Funder] Like '" & [Sheet1].[Funder] & "*' ORDER BY funder;

SELECT [1].[Funder] FROM 1 WHERE [Sheet1.Funder] Like [1].[Funder] ORDER BY funder;

SELECT [Sheet1].[Funder] FROM 1 WHERE [1].[Funder] Like [Sheet1].[Funder] ORDER BY funder;

SELECT [Sheet1].
[List89] FROM [Sheet1].Funder WHERE [1.Funder] Like [Sheet1].[Funder] ORDER BY funder;

[June7]

Is this what you want to do http://datapigtechnologies.com/flash...combobox2.html

If you don't use LIKE operator with wildcard (*) might as well just use = operator.

[lukebowes]

Hello, thanks for your reply

not quite

There are some entries with identical Funder names, when I am on an entry which has more than one with the same name I would like the others to appear in the previous application box so when I click on one of them it goes to that entry.




Uploaded with ImageShack.us

Thanks

[June7]

You want the listbox (unbound) dependent on the value in the Funder combobox? Then you want the form to filter based on value selected in the listbox? This is one way to do the filtering http://datapigtechnologies.com/flash...tomfilter.html

[lukebowes]

I tried the tutorial, but it didn't seem right. The Funder is just a field which displays whatever the Funder is for that particular entry. What I want the listbox (unbound) to do is show all entries with the same funder name in there so when I click on a 'previous application' it changes to that one.

[June7]

Do you want to provide project for analysis? Follow instructions at bottom of my post.

[lukebowes]

databasecopy.zip

(None of the data is confidential)

Thanks

[June7]

If I understand the setup you have attempted: You want the listbox restricted to records for the Funder shown in textbox and at the same time you want the form filtered to record selected in the listbox. This is essentially circular referencing and won't work. I should have recognized much sooner that was what you were attempting.

The entities of this data appear to be Funders and Projects. The relationship appears to be many to many. Each funder can have many projects and each project can have many funders. You need 3 tables: Funders, Projects, FundersProjects. Then use form/subform arrangement for data entry/edit. Review http://office.microsoft.com/en-us/ac...101872705.aspx

[lukebowes]

Hello I wondered if you could advise me on my progress at all - When someone creates a new application I want it automatically to link to the Funder they're on by having the same funder name under previous applications. I also want the previous applications to be in a drop down box, e.g. all the entries in the project table are displayed if they have the same name as the current field in the funders table/form. They when you click on one it changes to that entry?

Thanks

Database4.zip

[lukebowes]

Also rather than going to the next project when clicking through the records it repeats the same funder without changing the project?

[lukebowes]

Database5.zip

Sorry... Newer neater version attached

[June7]

You have the autonumber field designated as primary key but are saving the funder name as the fk value in projects table. That will work but makes a very long pk. If you want it this way then need to change Funder field to the pk.

However, if the relationship is many to many this is not correct setup. Many to many requires a third junction table to associate the two entities.

With the proper relationship setting and form/subform arrangement, listbox is not needed.

[lukebowes]

Hi, its excatly how I want it except I want when someone creates a new application I want it automatically to link to the Funder they're on by having the same funder name under previous applications.

I also want the previous applications to be in a drop down box, e.g. all the entries in the project table are displayed if they have the same name as the current field in the funders table/form. They when you click on one it changes to that entry?

Adn I'm quite confused how I would do this

[June7]

Maybe this is what you want. Have an unbound combobox named like cbxApplications in Projects form header section with RowSource:
SELECT Projects.Project FROM Projects WHERE (((Projects.Funder)=[Forms]![Funders]![Funders_Funder]));
This combobox value can be used as criteria to filter Projects form with code in the AfterUpdate event of the combobox. Consider this approach:
Private Sub cbxApplications_AfterUpdate()
Me.FilterOn = False
Me.Filter = "Project='" & Me.cbxApplications & "'"
Me.FilterOn = True
End Sub

And behind the Funders form:
Private Sub Form_Current()
Me.Projects.Form.cbxApplications.Requery
Me.Projects.Form.FilterOn = False
End Sub

Suggest you also move the control buttons on the Projects form to its form header section.

[lukebowes]

Apologies for the late reply and thank you so much for help so far!

I've done as suggested however it still throws an error?Database6.zip