asked on

Sub form showing data is being saved but cannot be displayed..

Hi Experts,

This is a form is an ADP project linked to SQL Server.

Users suddenly started to get the error attached while entering new records.

If I change the recordsed of the sub form from a Select statement to a table then it works fine.

FYI- The error msg is really misleading, as next time the user opens the screen the record is there, and its definitely not as it states (data is not satisfying criteria..) In fact it was working perfectly fine till now, not sure what exactly caused it, I even when back to previous releases to check if they have this error, and I see that they do have now, therefore I'm convinced that is rather a DB issue, perhaps due to certain QTY of records..

Wondering if someone came across this issue, and whats the solution?

Thanks in advance.
Untitled.png

HainKurt

looks like there is sql here and you update/add a record which is not under scope of the query...

for example if sql is

select * from myTable where cityid=12

and then you update a record with cityid=5, then that data will not selected by the same query...

bfuchs

ASKER

@Huseyin,

Right, this is what the msg states, however as mentioned this is definitely not the case here.. (See above under FYI)

Thanks,
Ben

HainKurt

what is query here? are you getting this after every update or some?

if some, can you see any pattern?

also, how do you update your data?

do you get same/similar message on add, or just after update?

bfuchs

ASKER

This only happens after adding, when updating its fine.

Thanks,
Ben

HainKurt

maybe you have an identity column which is when you insert
and you have a PK=that column which is used to retrieve the data...

is this the case?

bfuchs

ASKER

Not sure what you mean, I have one column which is used as PK and its also identity column (auto number).

In either case, when I bound the form to the table directly or to to a view then this doesn't happen, so basically I do have a way out here, was just trying to avoid creating unnecessary objects in the database..

Thanks,
Ben

HainKurt

what is your query here?

maybe you should check your query and give unique aliases to column names selected...

bfuchs

ASKER

Select id, columnA, columnB, ColumnC..from MyTable where CurrentYN = 1

And I have a check box bound to CurrentYN with default value set to 1.

Thanks,
Ben

HainKurt

are you using any identity check in your code
is data coming from any external db like sql?

HainKurt

so it is simple select from one table with one filter...

what happens if you remove that filter?

bfuchs

ASKER

what happens if you remove that filter?

Same thing, also get the error msg.

Thanks,
Ben

HainKurt

is this a linked table to sql somehow?
do you have any code that deals with identity?
what happens if you connect to table and add a filter to datasource instead of using query

bfuchs

ASKER

Hi,

Its quite late for me, will have to continue tom..good night!

Thanks,
Ben

John Tsioumpris

Probably CurrentYN is not getting the value needed...you need to check its value when this fires up..
Try to see if you can trap this error on the Error event of the form to see the value of your fields...

Bitsqueezer

Hi Ben,

in ADP you have an additional property "ServerFilter" which you should check for contents. Sometimes Access saves a value here together with the form in design mode and it will be applied to the form at any time you open it.
Moreover you said this is a subform so it is likely that you used the link criteria in the subform control which is also an additional filter.

You can also use the "BeforeInsert" event to check the contents of all fields of your SELECT command before the INSERT happens (before the error message should appear) and this is also the right place to check all possible kind of filters of the form. You should also check if there are any triggers or default values involved on the table which sets a value you didn't expect.

Cheers,

Christian

Neil Fleming

As Bitsqueezer says, the smoking gun may be your sub-form, which is probably slaved to its parent form via the "id" field.

If you then create new data in the subform and run an INSERT query, you are creating a new line in your table with a new auto-numbered id.

The parent form has no knowledge of this id until you close the form and re-open it, unless you add code to read the value of the newly created id and modify the parent filter.

bfuchs

ASKER

Hi Experts,

Just checked while running the before insert event, there are no filters and no server filters and of course the value of CurrentYN is null at this point..

Just to recap as several suggestions were bought up here..

This is a case that only happens when sub form record source is a Select statement (as mentioned, in that case this happens even if no filter included in select statement, simple like select columnA, ColumnB.. from MyTable), while if I use a view instead of a SQL string then this doesn't happen.

So if we are looking for an solution, we need to look for something that will explain the difference of using those two methods..

Thanks,
Ben

Bitsqueezer

Hi Ben,

and why don't you use a view for that if that works? This would be the best way.
It's hard to say more without the possibility to test that.

Cheers,

Christian

bfuchs

ASKER

Hi Bit,

This is what I stated above.

In either case, when I bound the form to the table directly or to to a view then this doesn't happen, so basically I do have a way out here, was just trying to avoid creating unnecessary objects in the database..

FYI- This is happening to Access 2003/2010, SQL 2005 and 2008.

Thanks,
Ben

HainKurt

I guess I mentioned before...

try to add aliases to the columns you select...

for example, if you have same columns , ID, in table1 and table2

select table1.ID, table2.colA,.. from table1 inner join table2 where...

>>>

select table1.ID as t1ID, table2.colA,.. from table1 inner join table2 where...

bfuchs

ASKER

Hi Huseyin,

Dont see you mentioning that earlier..however my SQL only selecting data from one table, do you think adding aliases will make a diff?

Thanks,
Ben

HainKurt

if it is only one table involved (but you mentioned some master/client relationship somewhere I guess), you may try to add alias to columns which is found in other tables... it will not hurt to try...

bfuchs

ASKER

No, that didnt help..

Thanks,
Ben

Neil Fleming

Ben: Sorry that we're struggling here..

This may be a stupid question, but If there is only one table involved, why is there a sub-form at all?

bfuchs

ASKER

@Neil,

I'm referring to the sub form, dont think this error has anything to do with the main form.

Thanks,
Ben

HainKurt

if there is a relation somewhere, it may be related to main form as well...
do you have any code that runs before/after add/update?

bfuchs

ASKER

Nothing to do with the code, I just tried w/o running any code and same happened.

BTW, I know sometimes a resync command can help with this, however this is something I never fully understood, therefore would not know what to put there..

Thanks,
Ben

Bitsqueezer

Hi Ben,

using a view is the usual way to get the data for a form, this is never an unnecessary object. A view has better performance as ist is a compiled query on the server and filters only the needed columns and rows of a query. Moreover the ADP can use it directly from the dropdown list of RecordSources of the form.

Access does not simply send your SQL command to the server, it uses own stored procedures for the most things so if you want to understand what happens, use the SQL Server profiler which shows exactly which commands are sent to the server.

Cheers,

Christian

bfuchs

ASKER

Hi Bit,

using a view is the usual way to get the data for a form, this is never an unnecessary object.

I know you may disagree with this, (if I remember well, you mentioned once having 500+ views in your app, lucky you..) however I find it very difficult to search for objects when there is a list of hundreds of them..so if every SQL statement in my app will be converted to a view it would mean a disaster for me..

use the SQL Server profiler which shows exactly which commands are sent to the server.

This is what I saw when the problem occurred (using select statement)

EXEC sp_MShelpcolumns '.EmployeesLicenses' , NULL, 'id', 1

exec sp_executesql N'INSERT INTO "PlacementNP".."EmployeesLicenses" ("CurrentYN","EmployeeID","LicenseNumber") VALUES (@P1,@P2,@P3)',N'@P1 bit,@P2 int,@P3 varchar(1)',1,176364,'t'

exec sp_executesql N'SELECT * FROM (select id, CurrentYN,EmployeeID,LicenseNumber, licensetype, licenseverdate,licensechecksanctionsDate, licensechecksanctionsres, state, licenseverres,LicenseNumberSignedYN,LicenseExpires,LicenseNumberTwo,LicenseExpiresTwo,LicenseTypeTwo from EmployeesLicenses where CurrentYN = 1) AS DRVD_TBL WHERE "id" = @P1',N'@P1 int',0

Open in new window

And this is when it didn't occur (using the view)

exec sp_executesql N'SET NOCOUNT OFF; INSERT INTO "PlacementNP"."dbo"."EmployeesLicenses" ("EmployeeID","LicenseNumber","CurrentYN") VALUES (@P1,@P2,@P3); SELECT SCOPE_IDENTITY() AS SCOPE_ID_COLUMN',N'@P1 int,@P2 varchar(1),@P3 bit',176364,'f',1

exec sp_executesql N'SELECT * FROM (SELECT  *  FROM "dbo"."View_EmployeesLicensesCurr") AS DRVD_TBL WHERE "ID" = @P1',N'@P1 int',176397

Open in new window

Let me know if you can determine from this what needs to be done.

Thanks,
Ben

ASKER CERTIFIED SOLUTION

Bitsqueezer

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bfuchs

ASKER

Since nobody came up with a way to fix it, guess will have to follow Bit's advice..
Thanks to all participants!