Find records

[koolkebab]

I have a form and a subform on it. There are some text boxes and combo boxes on the form. The subform's record source is a query which pulls data from a table based on the user inputs in the text boxes/combo boxes. There is a Find button which just requries the sub form source so that it can refresh the query by taking into account the new inputs given by the user.

The logic of the query is given as below:

SELECT tblInvestmentManager.*, tblInvestmentManager.OfficeLocation2, tblInvestmentManager.OfficeLocation3, tblInvestmentManager.OfficeLocation4
FROM tblInvestmentManager
WHERE (((tblInvestmentManager.ManagerName) Like ("*" & Forms!frmFindRecord!FindManagerName & "*")) And ((tblInvestmentManager.FundName) Like ("*" & Forms!frmFindRecord!FindFundName & "*")) And ((tblInvestmentManager.FirstName) Like ("*" & Forms!frmFindRecord!FindFirstName & "*")) And ((tblInvestmentManager.LastName) Like ("*" & Forms!frmFindRecord!FindLastName & "*")) And ((tblInvestmentManager.OfficeLocation1) Like ("*" & Forms!frmFindRecord!FindOfficeLocation & "*")) And ((tblInvestmentManager.StrategyMainCategory) Like ("*" & Forms!frmFindRecord!FindMainStrategy & "*")) And ((tblInvestmentManager.StrategySubCategory) Like ("*" & Forms!frmFindRecord!FindSubStrategy & "*")) And ((tblInvestmentManager.Geography) Like ("*" & Forms!frmFindRecord!FindGeography & "*")) And ((tblInvestmentManager.Likelyhood) Like ("*" & Forms!frmFindRecord!FindLikelyhood & "*")) And ((tblInvestmentManager.Status) Like ("*" & Forms!frmFindRecord!FindStatus & "*"))) Or (((tblInvestmentManager.OfficeLocation2) Like ("*" & Forms!frmFindRecord!FindOfficeLocation & "*"))) Or (((tblInvestmentManager.OfficeLocation3) Like ("*" & Forms!frmFindRecord!FindOfficeLocation & "*"))) Or (((tblInvestmentManager.OfficeLocation4) Like ("*" & Forms!frmFindRecord!FindOfficeLocation & "*")));

Thus I want my outputs to be displayed in the subform as shown in the eg below:

eg:
Database ->
------------------------------
Forename Surname Status
------------------------------
Mary Able 1
Joe Bloggs 2
Fred Candy 2
Mary Able 3
Joe Bloggs 4
Fred Candy 5
Mary Bloggs 6
------------------------------

On the form ->
3 txt boxes with Forename, Surname and Status and 1 button, Find
1) When I type Mary in Forename box and press find button-
results in the subform to be displayed:

------------------------------
Forename Surname Status
------------------------------
Mary Able 1
Mary Able 3
Mary Bloggs 6
------------------------------

2) When I type Bloggs in Surname box and press find button-
results in the subform to be displayed:

------------------------------
Forename Surname Status
------------------------------
Joe Bloggs 2
Joe Bloggs 4
Mary Bloggs 6
------------------------------

3) When I type Mary in Forename box and Bloggs in Surname box and press find button-
results in the subform to be displayed:

------------------------------
Forename Surname Status
------------------------------
Mary Bloggs 6
------------------------------


Problem:
I am not getting these results and I just dont know why.

 

[jrbarnett]

What are you getting running each of your queries above with the sample data?

John

 

[Remou]

This thread continues from thread705-1487916