Filter form through customer selected criteria

  • Thread starter Thread starter Rosana
  • Start date Start date
R

Rosana

Hello...

I am trying to build an unbound form with two unbound text
boxes for Beginning and Ending dates and a Combo box to
select Company name.

My dream was that by entering the Beginning date, the
Ending date and the company name, all the invoice data
that meets the criteria would show in a subform.

I have tried to set the record source of the Subform to a
Query and stablishing the unbound boxes of the Form as
criteria for the corresponding Company field and Date
field (this would be Between BeginninDate and EndingDate),
but that doen't seem to be enough.

Any ideas of how could I do it?

Thank you in advance
Rosana
 
First it seems your tables are not correct. Company and InvoiceDate should be in
the main form and not the subform. Your tables should look like:

TblInvoice
InvoiceID
CompanyID
InvoiceDate
<< Other general invoice fields such as InvoiceNum)

TblInvoiceDetail
InvoiceDetailID
InvoiceID
ItemID
Quantity
Price
ExtendedPrice

Your main form would be based on a query based on TblInvoice and your subform
would be based on a query based on TblInvoiceDetail.

You then need an unbound combo box, two textboxes and a command button in the
form header. Name the combobox "FindCompany". It should have a value of
CompanyID and its dropdown list should display company names. Name the first
textbox "StartDate" and the second textbox "EndDate".

In the query for the main form, put the following expression in the criteria for
CompanyID:
Forms!NameOfYourForm!FindCompany

Put the following expression in the criteria for InviceDate:
Between Forms!NameOfYourForm!StartDate And Forms!NameOfYourForm!EndDate

Put the following code in the click event of the command button:

<< Your code to check that a company has been selected and that a
start and end date have been entered >>
Me.Requery
 
Back
Top