SubForm/table filtering main form

[Lenovo123]

Hi Guys,

was wondering if anyone has a solution to this; will explain as customer and sales order example for ease.

I have created a form which displays all the customers and their details, I've put in a tab control box with information in the different pages; one of the pages is Transactions i.e. the sales orders they have placed

All the above works fine the sales orders do come through based on the record selected. however the problem is the main form is being filtered so that if a customer has no sales records then it will not be displayed i.e. only customers with sales orders linked to their customer reference are in the form and some are duplicated depending on number of transactions against their record.

if i remove the master link fields then all the orders for everyone is displayed in each customer record, this is not needed.

Any help will be greatly appreciated!

Many thanks

Bill

 

[Minty]

How is the main form populated - what is it's record source. It sounds like you have it based on a query linking the sales orders and customers...

 

[Lenovo123]

Hi Minty!

thanks for replying.

The form was created directly off the tables section no queries used - how do I check the record source?

Without the subtable (table.transactions) all the records in the customer table show up in the form.

Thanks!

Bill

 

[arnelgp]

change you record source.
create another combo box for [transaction] therefore you can filter on your recordsource against this combo:
<all>
with transaction
without transaction

 

[Lenovo123]

change you record source.
create another combo box for [transaction] therefore you can filter on your recordsource against this combo:
<all>
with transaction
without transaction

Arnelgp Thanks! - but can you explain this is in simple steps; quite new to all this!

Thank you!!!

Bill

 

[arnelgp]

assuming you have created a new combobox, cboMainForm, with the ff properties:
rowsource: "All";"With Transactions";"Without Transaction"
rowsource type: Value
bound column: 1

1. remove the recordsource of your main form.
2. on your forms load event, put this inside

private sub form_load()
me.cboMainForm ="All"
me.recordsource = "select * from yourTableName"
end sub

now on afterupdate event of our new combo:

private sub cboMainForm_Afterupdate()
select case me.cboMainForm
case "All"
me.recordsource ="Select * from yourTable;"
case "With Transactions"
me.recordsource = "Select * from yourtable Where [transactions] = the filtercondition with transaction"
case Else
me.recordsource = "Select * from yourTable Where [transaction] = the filter without transaction"
end select
end sub


you should put the correct filtercondition in place.

 

[Lenovo123]

assuming you have created a new combobox, cboMainForm, with the ff properties:
rowsource: "All";"With Transactions";"Without Transaction"
rowsource type: Value
bound column: 1

1. remove the recordsource of your main form.
2. on your forms load event, put this inside

private sub form_load()
me.cboMainForm ="All"
me.recordsource = "select * from yourTableName"
end sub

now on afterupdate event of our new combo:

private sub cboMainForm_Afterupdate()
select case me.cboMainForm
case "All"
me.recordsource ="Select * from yourTable;"
case "With Transactions"
me.recordsource = "Select * from yourtable Where [transactions] = the filtercondition with transaction"
case Else
me.recordsource = "Select * from yourTable Where [transaction] = the filter without transaction"
end select
end sub


you should put the correct filtercondition in place.

Arnelgp,

really really appreciate the above however i'm getting an error "The expression on Load you entered as the event property setting produced the following error: Ambigous name detected: form_Load

The code I've entered are;

Private Sub form_Load()

Me.Combo836 = "All"
Me.RecordSource = "Select * from Individuals new"
End Sub

and

Private Sub combo836_AfterUpdate()

Select Case Me.Combo836
Case "All"
Me.RecordSource = "select * from Individuals new;"
Case "with transactions"
Me.RecordSource = "select * from Transactions where [transactions] = the Individual Investor ID with transaction"
Case Else
Me.RecordSource = "Select * from transactions Where [Transactions] = the Individual Investor ID without transactions"
End Select

End Sub


Also just wondering is there anyway instead of a combo box i can get the information in a table with rows and headers?

Thank you sooo sooo much!!

Bill

 

[Minty]

Do you have lookup fields in your table design ?

 

[Lenovo123]

Do you have lookup fields in your table design ?

Yes - the transaction table is designed to lookup into the individuals table to allocate the sale to one of the individual records hence creating the link between the sale and the customer.

thanks!!

Bill

 

[Minty]

That will be your problem then.
Remove the Lookup from the table, they cause more problems than they are worth. You can always create a join manually and perform much more besides.

 

[Lenovo123]

That will be your problem then.
Remove the Lookup from the table, they cause more problems than they are worth. You can always create a join manually and perform much more besides.

Minty - you're absolutely fab! thank you soooo much. This has seemed to work