Need 2nd & 3rd combo box for date & status filter

[Rogue]

Hi All,

I have tried different samples of combo box and still unable to finalize my form:

First let me tell you what I got and what I need:

On an unbound form I have created a subform [frmMaster_sub] from MASTERLIST QUERY. This QUERY contains
SNAME, LPODate, QTY, UNIT PRICE, AMOUNT and STATUS.

On the FORM (frmStatus) I Have created an unbound Combo boxe:
COMBO – Created created from value list

The idea is from [COMBO] I can select a particular SUPPLIER and it will list all the the purchases for the specific supplier, then I would like to add another 2 combos for date and status. wherein if I select a particular Date and status from a combo box then it will show all purchase with the same Date and status for the supplier.

So far I'm having the following vb code:

Private Sub Command2_Click()
If (Eval("[Forms]![frmStatus]![Combo] Is Null")) Then
MsgBox "Please select value first", vbOKOnly, "No Selection"
ElseIf (Eval("[Forms]![frmStatus]![Combo] Is Not Null")) Then
strSQL = "Select * from MASTERLIST where"
strSQL = strSQL & " SNAME=" & "Forms!frmStatus!Combo"


Me!frmMaster_sub.Form.RecordSource = strSQL
End If
End Sub


Private Sub Command3_Click()
strSQL = "Select * from MASTERLIST"
Me!frmMaster_sub.Form.RecordSource = strSQL

End Sub


This works perfectly, where I am stuck now is how do I do the second & third filter. Any guidance would be greatly appreciated.


Thanks,

Rogue

[June7]

Common issue.

Review https://www.accessforums.net/forms/r...orm-36909.html

[Rogue]

Dear June,

Thanks for the samples you've provided, however it's really hard for me to tailorfit the codes in the samples to my needs. As I'm just a clerk who is trying hard to create a presentable and user friendly database. I would greatly appreciate your further assistance on this.

If its not that much to ask, could you please review my code/dbase once more and maybe you could give me additional code that will fix my current problem.


Thanks,

Rogue

[June7]

Don't have your database. If you want to provide it, follow instructions at bottom of my post.

Do you want this form to serve for data entry/edit? Why don't you use a form/subform arrangement? Supplier combobox on main form to filter the supplier and the subform will display only that supplier purchases. Can have the subform in Continuous view and put other comboboxes on it to further filter the purchases.

[Rogue]

Sorry for that sir, anyways here is my dbase and yes your right, would love it to serve for data entry/edit...REally sorry to disturb you that much...I really hope you could help me with this.


Again many thanks,

Rogue

[June7]

One method to filter form records http://datapigtechnologies.com/flash...tomfilter.html

Another uses VBA and is discussed in the link referenced in my earlier post.

If you want to do data entry to both Suppliers and Orders, probably simplest way is to use a form/subform arrangement http://office.microsoft.com/en-us/ac...010098674.aspx

Try combobox properties:
RowSource: SELECT SUPPLIER.ID, SUPPLIER.SUPPLIER FROM SUPPLIER ORDER BY SUPPLIER.SUPPLIER;
RowSourceType: Table/Query
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0";2"

[Rogue]

Dear June,

As much as I dont want to be that big disturb with you, everything is still really not so clear with me...I have tried your prior advise, which is to put combobox in my subform so I did that to filter the status column but I am having below error as shown in the attachment.


Runtime error 2465
Microsoft office accss cant find the field referred to in your expression

Appreciate if you could further extend your assistance on this.


Thanks,

Rogue

[June7]

Don't put variables within quotes. Reference to combobox is a variable. If Status is a text field, need apostrophe delimiters.


strSQL = strSQL & " STATUS='" & Forms!frmStatus!Combo2 & "'"


It is not necessary to modify the form RecordSource, can just set Filter and FilterOn properties.


You want to filter the subform but is the main form bound? If not then why use subform? Just make the subform a stand-alone form in Continuous view and put the search combobox and button in form header.

Private Sub Command30_Click()
Me.Filter = "[Status]='" & Me.Combo2 & "'"
Me.FilterOn = True
End Sub

[Rogue]

Finally, that really helps...Thanks alot June and sorry for disturbing you a lot...

Rogue