Access project (with SQL server)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Have anyone seen this error message before:
-----
The data was added to the database but the data won't be displayed in the
form because it doesn't satisfy the criteria in the underlying record source.
-------
In the error box there is a 'Show help' which gives this message:
--------
This error can occur if the data you are attempting to save is saving to an
SQL database (Microsoft SQL Server 7.0) and the RecordSource for the subform
is using an SQL Select statement with fields pulled directly from the source
table, and the table is using an Identity field set as its primary key.
To avoid this problem set the RecordSource property of the Subform to a
Table or a View.
-Or-
Upgrade to a newer version of Microsoft SQL Server.
More information about this error message online.
-------
- which makes absolutely no sense since I use MS SQL Server 8.0, and it does
not come in a subform, it is in a plain form with a record source in the form
of an SELECT-statement. I cannot use a table or a view, since I have dynamic
criteria in the form which changes the recordset.

By the way, the link to 'More information about this online' gives
absolutely nothing! Here is the link:
http://watson.microsoft.com/dw/erro...=10021&Language=1033&productBuild=11.0.6352.0
 
What is your criteria? In other words, what's the WHERE clause in the SELECT
statement? Does the data you're trying to insert meet that criteria?

If, for example, you're only displaying records associated with this month,
and you're trying to insert a record for a different month, you'll get that
error.
 
Hi,
Yes I know, but it is not when I insert a record I get it. It is when I
change a record so it no longer matches the criteria. But that is the whole
point with my form: the user goes through a list of records and change a
status-field to get it off from the list. So it is very natural that the
record should disappear, but I cannot get rid of the error message.
Every now and then Access crashes at this specific point also.
 
Just a thought but you might need to;
Me.Requery
or
Me.Refresh
after the insert.

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Hi,
| Yes I know, but it is not when I insert a record I get it. It is when I
| change a record so it no longer matches the criteria. But that is the
whole
| point with my form: the user goes through a list of records and change a
| status-field to get it off from the list. So it is very natural that the
| record should disappear, but I cannot get rid of the error message.
| Every now and then Access crashes at this specific point also.
| --
| /John
 
Unfortunately folks its not that straight forward and a bit painful I've
found.

Try the following:

1, Create a stored procedure using your forms current record source and get
your parameters in order and named properly if you need to pass them (if you
can't do this, email your recordsource to me at: mr dot smith at trearc dot
com with)

2, Set the 'Unique Table' property of your form to the name of the table
that will be updated by your users inputs

3, Set the 'Resync Command' to your current record source WITHOUT its Order
by clause and replace he Where clause with "Where [your unique tables ID
field] = ??"

4, if you have any parameters that you need to pass to your recordsource
from your form enter them in the 'Input Parameters' property as '@varname
dataype=nvalue' seperated by commas.

For example:

1, I have stored proc named sp_Orders as my record source
***************************
ALTER PROCEDURE dbo.sp_Orders
(@Months int,
@Until datetime,
@OID int)
AS SELECT dbo.vw_Orders.*
FROM dbo.tbl_ContractInventory INNER JOIN
dbo.tbl_InventoryDetails ON
dbo.tbl_ContractInventory.InventoryDetailID =
dbo.tbl_InventoryDetails.InventoryDetailID INNER JOIN
dbo.tbl_Contracts ON
dbo.tbl_ContractInventory.ContractID = dbo.tbl_Contracts.ContractID INNER JOIN
dbo.vw_Orders ON dbo.tbl_InventoryDetails.AssetID =
dbo.vw_Orders.AssetID
WHERE (dbo.vw_Orders.DateOrdered BETWEEN DATEADD(month, - @Months,
@Until) AND DATEADD(day, 1, @Until)) AND
(dbo.tbl_Contracts.OfficeLocationID = @OID)
ORDER BY dbo.vw_Orders.DateOrdered DESC
********************************

2, My unique table is "tbl_Orders"

3, My Resync Command is
********************************
SELECT dbo.vw_Orders.*
FROM dbo.tbl_ContractInventory INNER JOIN
dbo.tbl_InventoryDetails ON
dbo.tbl_ContractInventory.InventoryDetailID =
dbo.tbl_InventoryDetails.InventoryDetailID INNER JOIN
dbo.tbl_Contracts ON
dbo.tbl_ContractInventory.ContractID = dbo.tbl_Contracts.ContractID INNER JOIN
dbo.vw_Orders ON dbo.tbl_InventoryDetails.AssetID =
dbo.vw_Orders.AssetID
WHERE vw_Order.OrderID = ??
**********************************

4, And my input parameters are:

@months int=Forms!frmStartup!subfrmMain.form!cboMonths,@Until
datetime=Forms!frmStartup!subfrmMain.form!txtDateUntil, @OID
int=CurrLocation()

If your parameter are fixed (IE you don't need to control them with inputs
via the form) the just hardcode them within the stored proc. Of course you
won't need any entry in the Input Parameters field. Still follow steps 2 & 3
as I described though.

Lemme know if you have any issues.


Mr. Smith



Dave Patrick said:
Just a thought but you might need to;
Me.Requery
or
Me.Refresh
after the insert.

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Hi,
| Yes I know, but it is not when I insert a record I get it. It is when I
| change a record so it no longer matches the criteria. But that is the
whole
| point with my form: the user goes through a list of records and change a
| status-field to get it off from the list. So it is very natural that the
| record should disappear, but I cannot get rid of the error message.
| Every now and then Access crashes at this specific point also.
| --
| /John
Click to expand...
 
Thanks mr. Smith,
I will try this. I never thought of using an SP with parameters. I think
this wil help me a lot. In plain Access it is very easy and simple to work
with forms using a 'SELECT'-statement as recordsource, but with SQL it is
much more tricky.
--
/John


Mr. Smith said:
Unfortunately folks its not that straight forward and a bit painful I've
found.

Try the following:

1, Create a stored procedure using your forms current record source and get
your parameters in order and named properly if you need to pass them (if you
can't do this, email your recordsource to me at: mr dot smith at trearc dot
com with)

2, Set the 'Unique Table' property of your form to the name of the table
that will be updated by your users inputs

3, Set the 'Resync Command' to your current record source WITHOUT its Order
by clause and replace he Where clause with "Where [your unique tables ID
field] = ??"

4, if you have any parameters that you need to pass to your recordsource
from your form enter them in the 'Input Parameters' property as '@varname
dataype=nvalue' seperated by commas.

For example:

1, I have stored proc named sp_Orders as my record source
***************************
ALTER PROCEDURE dbo.sp_Orders
(@Months int,
@Until datetime,
@OID int)
AS SELECT dbo.vw_Orders.*
FROM dbo.tbl_ContractInventory INNER JOIN
dbo.tbl_InventoryDetails ON
dbo.tbl_ContractInventory.InventoryDetailID =
dbo.tbl_InventoryDetails.InventoryDetailID INNER JOIN
dbo.tbl_Contracts ON
dbo.tbl_ContractInventory.ContractID = dbo.tbl_Contracts.ContractID INNER JOIN
dbo.vw_Orders ON dbo.tbl_InventoryDetails.AssetID =
dbo.vw_Orders.AssetID
WHERE (dbo.vw_Orders.DateOrdered BETWEEN DATEADD(month, - @Months,
@Until) AND DATEADD(day, 1, @Until)) AND
(dbo.tbl_Contracts.OfficeLocationID = @OID)
ORDER BY dbo.vw_Orders.DateOrdered DESC
********************************

2, My unique table is "tbl_Orders"

3, My Resync Command is
********************************
SELECT dbo.vw_Orders.*
FROM dbo.tbl_ContractInventory INNER JOIN
dbo.tbl_InventoryDetails ON
dbo.tbl_ContractInventory.InventoryDetailID =
dbo.tbl_InventoryDetails.InventoryDetailID INNER JOIN
dbo.tbl_Contracts ON
dbo.tbl_ContractInventory.ContractID = dbo.tbl_Contracts.ContractID INNER JOIN
dbo.vw_Orders ON dbo.tbl_InventoryDetails.AssetID =
dbo.vw_Orders.AssetID
WHERE vw_Order.OrderID = ??
**********************************

4, And my input parameters are:

@months int=Forms!frmStartup!subfrmMain.form!cboMonths,@Until
datetime=Forms!frmStartup!subfrmMain.form!txtDateUntil, @OID
int=CurrLocation()

If your parameter are fixed (IE you don't need to control them with inputs
via the form) the just hardcode them within the stored proc. Of course you
won't need any entry in the Input Parameters field. Still follow steps 2 & 3
as I described though.

Lemme know if you have any issues.


Mr. Smith



Dave Patrick said:
Just a thought but you might need to;
Me.Requery
or
Me.Refresh
after the insert.

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Hi,
| Yes I know, but it is not when I insert a record I get it. It is when I
| change a record so it no longer matches the criteria. But that is the
whole
| point with my form: the user goes through a list of records and change a
| status-field to get it off from the list. So it is very natural that the
| record should disappear, but I cannot get rid of the error message.
| Every now and then Access crashes at this specific point also.
| --
| /John
Click to expand...
Click to expand...
 
John,
I am a real amateur when it comes to working with Access and SQL Server, but
I had a very similar problem. I have a view that pulls all of my information
from different tables and for some reason the data in this view wasn't
correct/complete. I reevaluated my relationships and feel that those are
fine and then I recreated my view piecemeal to try and pinpoint which table
was wrong. As I each table and ran the view I kept getting the right
information. So I went back to my original view, removed all of the tables
and then added all of them back and voila it worked. I now could update the
information on my form without the annoying message. I am sure Mr. Smith's
solution would work better, but I am not as SQL saavy as most. Hope this
helps.

Mark

johnbertell said:
Thanks mr. Smith,
I will try this. I never thought of using an SP with parameters. I think
this wil help me a lot. In plain Access it is very easy and simple to work
with forms using a 'SELECT'-statement as recordsource, but with SQL it is
much more tricky.
--
/John


Mr. Smith said:
Unfortunately folks its not that straight forward and a bit painful I've
found.

Try the following:

1, Create a stored procedure using your forms current record source and get
your parameters in order and named properly if you need to pass them (if you
can't do this, email your recordsource to me at: mr dot smith at trearc dot
com with)

2, Set the 'Unique Table' property of your form to the name of the table
that will be updated by your users inputs

3, Set the 'Resync Command' to your current record source WITHOUT its Order
by clause and replace he Where clause with "Where [your unique tables ID
field] = ??"

4, if you have any parameters that you need to pass to your recordsource
from your form enter them in the 'Input Parameters' property as '@varname
dataype=nvalue' seperated by commas.

For example:

1, I have stored proc named sp_Orders as my record source
***************************
ALTER PROCEDURE dbo.sp_Orders
(@Months int,
@Until datetime,
@OID int)
AS SELECT dbo.vw_Orders.*
FROM dbo.tbl_ContractInventory INNER JOIN
dbo.tbl_InventoryDetails ON
dbo.tbl_ContractInventory.InventoryDetailID =
dbo.tbl_InventoryDetails.InventoryDetailID INNER JOIN
dbo.tbl_Contracts ON
dbo.tbl_ContractInventory.ContractID = dbo.tbl_Contracts.ContractID INNER JOIN
dbo.vw_Orders ON dbo.tbl_InventoryDetails.AssetID =
dbo.vw_Orders.AssetID
WHERE (dbo.vw_Orders.DateOrdered BETWEEN DATEADD(month, - @Months,
@Until) AND DATEADD(day, 1, @Until)) AND
(dbo.tbl_Contracts.OfficeLocationID = @OID)
ORDER BY dbo.vw_Orders.DateOrdered DESC
********************************

2, My unique table is "tbl_Orders"

3, My Resync Command is
********************************
SELECT dbo.vw_Orders.*
FROM dbo.tbl_ContractInventory INNER JOIN
dbo.tbl_InventoryDetails ON
dbo.tbl_ContractInventory.InventoryDetailID =
dbo.tbl_InventoryDetails.InventoryDetailID INNER JOIN
dbo.tbl_Contracts ON
dbo.tbl_ContractInventory.ContractID = dbo.tbl_Contracts.ContractID INNER JOIN
dbo.vw_Orders ON dbo.tbl_InventoryDetails.AssetID =
dbo.vw_Orders.AssetID
WHERE vw_Order.OrderID = ??
**********************************

4, And my input parameters are:

@months int=Forms!frmStartup!subfrmMain.form!cboMonths,@Until
datetime=Forms!frmStartup!subfrmMain.form!txtDateUntil, @OID
int=CurrLocation()

If your parameter are fixed (IE you don't need to control them with inputs
via the form) the just hardcode them within the stored proc. Of course you
won't need any entry in the Input Parameters field. Still follow steps 2 & 3
as I described though.

Lemme know if you have any issues.


Mr. Smith



Dave Patrick said:
Just a thought but you might need to;
Me.Requery
or
Me.Refresh
after the insert.

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Hi,
| Yes I know, but it is not when I insert a record I get it. It is when I
| change a record so it no longer matches the criteria. But that is the
whole
| point with my form: the user goes through a list of records and change a
| status-field to get it off from the list. So it is very natural that the
| record should disappear, but I cannot get rid of the error message.
| Every now and then Access crashes at this specific point also.
| --
| /John
Click to expand...
Click to expand...
Click to expand...
 
Back
Top