We help IT Professionals succeed at work.

Access can't see data

Murray Brown
Murray Brown used Ask the Experts™
on
Hi
I have an Access form with the following record source. My data is in a  linked table called t_Arms but nothing is pulling through. If I add new data to t_Arms it shows, but for some reason all the older data that I can see in that table doesn't

SELECT t_Arms.ArmPK, t_Arms.ArmType, t_Arms.ActionType, t_Arms.Manufacturer, t_Arms.SerialNo, t_Arms.Model, t_Arms.Calibre1, t_Arms.Status, t_Arms.LicenceNumberFK, t_Arms.Country, t_Arms.Source, t_Arms.SerialNo2, t_Arms.SerialNo3, t_Arms.Calibre2
FROM t_Arms
WHERE (((t_Arms.ArmType) Like "*" & [forms]![f_MainScreen]![sArmType].[Text] & "*") AND ((t_Arms.ActionType) Like "*" & [forms]![f_MainScreen]![sActionType].[Text] & "*") AND ((t_Arms.Manufacturer) Like "*" & [forms]![f_MainScreen]![sManufacturer].[Text] & "*") AND ((t_Arms.SerialNo) Like "*" & [forms]![f_MainScreen]![sSerialNo].[Text] & "*") AND ((t_Arms.Model) Like "*" & [forms]![f_MainScreen]![sModel].[Text] & "*") AND ((t_Arms.Calibre1) Like "*" & [forms]![f_MainScreen]![sCalibre].[Text] & "*") AND ((t_Arms.Country) Like "*" & [forms]![f_MainScreen]![sCountry].[Text] & "*") AND ((t_Arms.SerialNo2) Like "*" & [forms]![f_MainScreen]![sSerialNo2].[Text] & "*") AND ((t_Arms.SerialNo3) Like "*" & [forms]![f_MainScreen]![sSerialNo3].[Text] & "*") AND ((t_Arms.Calibre2) Like "*" & [forms]![f_MainScreen]![sCalibre2].[Text] & "*"));
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Software & Systems Engineer
Commented:
Are you sure that there isn't  a filter "blocking" your view...how about removing everything and add them one at a time.
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
Remove the filters one by one until you have some records returned.
Then adjust your Where clause to return the desired records.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
You have a lot of values being pulled from text controls in your filter.  Since you are using this as a filter, why not use cascading combo boxes, so that you know you are entering valid values in those boxes to define your filter?  it is far to easy to misspell a value in a textbox and then you get no results and don't know why.
Distinguished Expert 2017
Commented:
Don't use the .text property.  In most versions of Access this property is only available when the control has the focus and multiple controls certainly cannot have the focus.  I'm not sure it ever works in queries either.   Just drop the property entirely and that will use the default property which for Access forms is .Value.  Queries are not parsed by VBA so if this is a querydef, you may get an error if you reference a control property at all in the query.

Some VB users are confused by this because the VB default was .Text

Here's the error you get when you use the .text property incorrectly in code.
DotText.JPG
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
good catch, Pat.  Funny how we all see different things when we looked at this query.

Generally, I don't create queries like that which reference form controls.  Instead, I generally build the WHERE clause dynamically and only include those fields in the WHERE clause where there is a value entered in the associated textbox or combo.
Distinguished Expert 2017

Commented:
I learned VB before I learned VBA so I made the .text error early on in my Access career.  I always wondered why VBA ended up different from VB in areas where they overlap and this is just one of them.

In this case, VBA would have raised an error which I showed a picture of so I assumed that there was a querydef involved and you get different or even no error is some situations.  Murray didn't mention an error, just that he got unexpected results.
Murray BrownASP.net/VBA/VSTO Developer

Author

Commented:
thanks for all the options
Distinguished Expert 2017

Commented:
We all couldn't have given THE answer.  Since you just awarded participation points to everyone, no one who finds this thread later is likely to find it useful.