Need Help

[NickFox]

Dear Experts,

I am using a combo box to search records in a subform. I want to add a method so that to clear the selection and see all records again. The code i am using is as follows

Private Sub Combo2_AfterUpdate()
Dim itemsel As String

itemsel = "Select * from Items where ([ItemID] = " & Me.Combo2 & ")"
Me.Items_subform.Form.RecordSource = itemsel
Me.Items_subform.Form.Requery
End If

End Sub

[andy49]

How about a clear filter button which
Me.items_subform.form.recordsource = ""


Sent from my iPhone using Tapatalk

[ssanfu]

I prefer to use the form filter property, instead of constantly changing the form record source. (simpler IMO)

Private Sub Combo2_AfterUpdate()
  'if a number - not text
  Me.Filter = "[ItemID] = " & Me.Combo2
  Me.FilterOn = TRUE

End Sub

Add a button to remove the filter and show all records:

Private Sub btnClrFilter_Click()

  Me.Filter = vbNullString
  Me.FilterOn = FALSE

End Sub

Button is named "btnClrFilter"

You should take the time to give short, meaningful names to controls. What is "Combo2"? Having meaningful names makes reading code/maintenance easier...

[NickFox]

Thanks mate,

I am very new to the access world. Infact i stumbled upon access by a random video on youtube. so i thought to give it a shot as it seems to work better than my basic excel files.

[NickFox]

How about a clear filter button which
Me.items_subform.form.recordsource = ""


Sent from my iPhone using Tapatalk

Thanks andy. Nice and easy. works like a charm.

[Micron]

it seems to work better than my basic excel files.
Word of advice - don't apply what you know about Excel when designing database tables. They might look the same, but are two very different animals. If they turn out not to be, you are headed for trouble. If you want suggestions on recommended reading (there is LOTS of it) we have suggestions for beginners.
EDIT
Agree with the filter on/off suggestion. Frequent resetting of a form's recordsource and subsequent requerying is not optimal performance wise, especially if the data is coming across a network. In some cases, you might cause data over-write when you don't intend to. I think we're all assuming the combo box is not bound to any table field too.

[NickFox]

it seems to work better than my basic excel files.
Word of advice - don't apply what you know about Excel when designing database tables. They might look the same, but are two very different animals. If they turn out not to be, you are headed for trouble. If you want suggestions on recommended reading (there is LOTS of it) we have suggestions for beginners.
EDIT
Agree with the filter on/off suggestion. Frequent resetting of a form's recordsource and subsequent requerying is not optimal performance wise, especially if the data is coming across a network. In some cases, you might cause data over-write when you don't intend to. I think we're all assuming the combo box is not bound to any table field too.

Thanks for your reply Micron. I would highly appreciate any recommendation to start with. I manage a small business and for my own personal ease i wanted to set up a table that stores daily fluctuating raw material prices. Excel was doing the job but then i came across with Access. All i needed a table that can store daily prices with an ease of searching according to Item, Date. I found this video on youtube that in my mind could do the trick.

https://www.youtube.com/watch?v=8N1hRBdsI1s

My Table has Following fields.

Vendor,Date, ItemType,ItemDescription,Size,Thickness,Rate

I needed a set of filters to get to the set of records for a particular item which when sorted according to Ascending Date would give me the price variation. Excel was doing the job but an adventure with Access has landed me in an unknown world.

I would be grateful if you could please give me any advice regarding this situation. Thank you.

[ssanfu]

Be aware that "Date", "Size" and "Rate" are reserved words and shouldn't be use as object names.
See http://allenbrowne.com/AppIssueBadWord.html

I would use:
tblItems
-------------
ItemVendor - Text
ItemDate - Date/Time
ItemType - Text(??)
ItemDescription - Text
ItemSize - Text or number type
ItemThickness - Text or number type
ItemRate - Number -double (Price?)

Create a query based on the table, sorted by date.
Create a form based on a the above query
Set the form view to Continuous Form view
In the form header, add an unbound combo box to be able to select a particular item (ItemType?) and a button to set the filter.
Add another filter to be able to clear the filter.


Would need to see some raw data and expected results to be more precise......

[Micron]

Agree with the reserved word advice, but what to replace it with?
http://www.access-programmers.co.uk/...d.php?t=225837

WHERE YOU SHOULD START
Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.ca/...on-part-i.html

If after normalizing you end up with but 2 tables, you might not need this, but I'd bookmark it for future reference.
Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.ca/...ng-part-i.html

When to split a database and why, and other info
How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.ca/2009/05/how-do-i-create-application-in.html

What autonumbers are really for: http://www.utteraccess.com/wiki/index.php/Autonumbers
Design tips (lots of related subject matter in left nav pane on this page) http://www.fmsinc.com/free/newtips/primarykey.asp

My own personal advice:
- don't use lookup fields in tables (unless maybe such data entry will be rare and preferably only by you)
- don't store calculations (unless not doing so would alter historical data such as invoice pricing)
- DO NOT use multi value fields in tables
- do create working queries before you build forms or reports. If the data cannot be retrieved or edited as required, there is something wrong with the query. The exception is that queries like UNION, CROSS TAB or ones that use aggregate functions are not updatable by design. A form built on a query that doesn't work can result in a huge waste of time. The inability to add or edit records once the form is built will tell you the problem is with the form, thereby eliminating the query as the possibility.
That's all I've got for now.

EDIT: based on your original post, I think I see a need for 3 tables, not one. Let us know what you end up deciding after reviewing normalization.