RecordsetNot Updateable

M

Mark A. Sam

Hello,

I have a query as a Recordsource for a form. It originally contained two
tables, [Order Entry Header] and [Customers] with an inner join. It was
updateable.

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM [Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701))
ORDER BY [Order Entry Header].ordJob;



I added another table [CustomerAccess] which purpose is to limit the records
by Customer and UserID. This table simply shows which users can access
which customers. The function getUserID tells which User who may have
access to several customers.


SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID()))
ORDER BY [Order Entry Header].ordJob;


Suddenly the Recordset is not updateable. It doesn't make sense to me.
These are linked SQL Server tables, so I thought maybe something in the
field properties or indexes may be the problem, so I imported the
[CustomerAccess] table to see if a local table would fix it, but it didn't.

Any siggestions on how to make this recorset updateable is apprecated.

God Bless,

Mark A. Sam
 
K

Ken Snell

Multitable queries often are not updateable.

Try this query:

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID())
AND ((Customers.Custid) IN
(SELECT CustomerAccessCustid FROM
CustomerAccess)))
ORDER BY [Order Entry Header].ordJob;
 
K

Ken Snell

Forgot a . in my original reply to you, Mark. Here is corrected query:

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID())
AND ((Customers.Custid) IN
(SELECT CustomerAccess.Custid FROM
CustomerAccess)))
ORDER BY [Order Entry Header].ordJob;
 
M

Mark A. Sam

Ken,

Thank you. I have never seen syntax like that, but it worked. :)

God Bless,

Mark


Ken Snell said:
Forgot a . in my original reply to you, Mark. Here is corrected query:

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID())
AND ((Customers.Custid) IN
(SELECT CustomerAccess.Custid FROM
CustomerAccess)))
ORDER BY [Order Entry Header].ordJob;

--

Ken Snell
http://www.accessmvp.com/KDSnell/



Mark A. Sam said:
Hello,

I have a query as a Recordsource for a form. It originally contained two
tables, [Order Entry Header] and [Customers] with an inner join. It was
updateable.

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM [Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701))
ORDER BY [Order Entry Header].ordJob;



I added another table [CustomerAccess] which purpose is to limit the
records by Customer and UserID. This table simply shows which users can
access which customers. The function getUserID tells which User who may
have access to several customers.


SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID()))
ORDER BY [Order Entry Header].ordJob;


Suddenly the Recordset is not updateable. It doesn't make sense to me.
These are linked SQL Server tables, so I thought maybe something in the
field properties or indexes may be the problem, so I imported the
[CustomerAccess] table to see if a local table would fix it, but it
didn't.

Any siggestions on how to make this recorset updateable is apprecated.

God Bless,

Mark A. Sam
Click to expand...
Click to expand...
 
K

Ken Snell

Using the IN operator in the WHERE clause in this case acts the same as an
INNER JOIN, as the ID fields must exist in the table inside the IN part in
order for the main table's records to be selected.

Using a NOT IN would be how you could implement a LEFT JOIN from the main
table to the child table. However, it's much less efficient for running time
than a LEFT JOIN.
--

Ken Snell
http://www.accessmvp.com/KDSnell/



Mark A. Sam said:
Ken,

Thank you. I have never seen syntax like that, but it worked. :)

God Bless,

Mark


Ken Snell said:
Forgot a . in my original reply to you, Mark. Here is corrected query:

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID())
AND ((Customers.Custid) IN
(SELECT CustomerAccess.Custid FROM
CustomerAccess)))
ORDER BY [Order Entry Header].ordJob;

--

Ken Snell
http://www.accessmvp.com/KDSnell/



Mark A. Sam said:
Hello,

I have a query as a Recordsource for a form. It originally contained two
tables, [Order Entry Header] and [Customers] with an inner join. It was
updateable.

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM [Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701))
ORDER BY [Order Entry Header].ordJob;



I added another table [CustomerAccess] which purpose is to limit the
records by Customer and UserID. This table simply shows which users can
access which customers. The function getUserID tells which User who may
have access to several customers.


SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID()))
ORDER BY [Order Entry Header].ordJob;


Suddenly the Recordset is not updateable. It doesn't make sense to me.
These are linked SQL Server tables, so I thought maybe something in the
field properties or indexes may be the problem, so I imported the
[CustomerAccess] table to see if a local table would fix it, but it
didn't.

Any siggestions on how to make this recorset updateable is apprecated.

God Bless,

Mark A. Sam
Click to expand...
Click to expand...
Click to expand...
 
M

Mark A. Sam

I'll have to think on that. I'm not too keen on SQL and not familiar with
using the In operator. I usually usually get things done using the QBE
grid. I can't see the In phrase expressed graphically, which is
problematice for me.



Ken Snell said:
Using the IN operator in the WHERE clause in this case acts the same as an
INNER JOIN, as the ID fields must exist in the table inside the IN part in
order for the main table's records to be selected.

Using a NOT IN would be how you could implement a LEFT JOIN from the main
table to the child table. However, it's much less efficient for running
time than a LEFT JOIN.
--

Ken Snell
http://www.accessmvp.com/KDSnell/



Mark A. Sam said:
Ken,

Thank you. I have never seen syntax like that, but it worked. :)

God Bless,

Mark


Ken Snell said:
Forgot a . in my original reply to you, Mark. Here is corrected query:

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID())
AND ((Customers.Custid) IN
(SELECT CustomerAccess.Custid FROM
CustomerAccess)))
ORDER BY [Order Entry Header].ordJob;

--

Ken Snell
http://www.accessmvp.com/KDSnell/



Hello,

I have a query as a Recordsource for a form. It originally contained
two tables, [Order Entry Header] and [Customers] with an inner join.
It was updateable.

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM [Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701))
ORDER BY [Order Entry Header].ordJob;



I added another table [CustomerAccess] which purpose is to limit the
records by Customer and UserID. This table simply shows which users
can access which customers. The function getUserID tells which User
who may have access to several customers.


SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID()))
ORDER BY [Order Entry Header].ordJob;


Suddenly the Recordset is not updateable. It doesn't make sense to me.
These are linked SQL Server tables, so I thought maybe something in the
field properties or indexes may be the problem, so I imported the
[CustomerAccess] table to see if a local table would fix it, but it
didn't.

Any siggestions on how to make this recorset updateable is apprecated.

God Bless,

Mark A. Sam
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell

The IN stuff would appear in the Where box under the ID field in the grid.

Also, I need to clarify my statement about LEFT JOIN method.

Using a NOT IN would be how you could implement a LEFT JOIN from the main
table to the child table where you only want to see the records from the
main table that are not in the child table (as if you'd done a LEFT JOIN
with a WHERE criterion of IS NULL for the joined field in the child table).
However, it's much less efficient for running time than a LEFT JOIN.

--

Ken Snell
http://www.accessmvp.com/KDSnell/



Mark A. Sam said:
I'll have to think on that. I'm not too keen on SQL and not familiar with
using the In operator. I usually usually get things done using the QBE
grid. I can't see the In phrase expressed graphically, which is
problematice for me.



Ken Snell said:
Using the IN operator in the WHERE clause in this case acts the same as
an INNER JOIN, as the ID fields must exist in the table inside the IN
part in order for the main table's records to be selected.

Using a NOT IN would be how you could implement a LEFT JOIN from the
main table to the child table. However, it's much less efficient for
running time than a LEFT JOIN.
--

Ken Snell
http://www.accessmvp.com/KDSnell/



Mark A. Sam said:
Ken,

Thank you. I have never seen syntax like that, but it worked. :)

God Bless,

Mark


Forgot a . in my original reply to you, Mark. Here is corrected query:

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID())
AND ((Customers.Custid) IN
(SELECT CustomerAccess.Custid FROM
CustomerAccess)))
ORDER BY [Order Entry Header].ordJob;

--

Ken Snell
http://www.accessmvp.com/KDSnell/



Hello,

I have a query as a Recordsource for a form. It originally contained
two tables, [Order Entry Header] and [Customers] with an inner join.
It was updateable.

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM [Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701))
ORDER BY [Order Entry Header].ordJob;



I added another table [CustomerAccess] which purpose is to limit the
records by Customer and UserID. This table simply shows which users
can access which customers. The function getUserID tells which User
who may have access to several customers.


SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID()))
ORDER BY [Order Entry Header].ordJob;


Suddenly the Recordset is not updateable. It doesn't make sense to
me. These are linked SQL Server tables, so I thought maybe something
in the field properties or indexes may be the problem, so I imported
the [CustomerAccess] table to see if a local table would fix it, but
it didn't.

Any siggestions on how to make this recorset updateable is apprecated.

God Bless,

Mark A. Sam
Click to expand...
Click to expand...
Click to expand...
 
M

Mark A. Sam

Thanks. I wouldn't use an outer join unless I had to force records that
didn't match the main recordset.


Ken Snell said:
The IN stuff would appear in the Where box under the ID field in the grid.

Also, I need to clarify my statement about LEFT JOIN method.

Using a NOT IN would be how you could implement a LEFT JOIN from the main
table to the child table where you only want to see the records from the
main table that are not in the child table (as if you'd done a LEFT JOIN
with a WHERE criterion of IS NULL for the joined field in the child
table). However, it's much less efficient for running time than a LEFT
JOIN.

--

Ken Snell
http://www.accessmvp.com/KDSnell/



Mark A. Sam said:
I'll have to think on that. I'm not too keen on SQL and not familiar with
using the In operator. I usually usually get things done using the QBE
grid. I can't see the In phrase expressed graphically, which is
problematice for me.



Ken Snell said:
Using the IN operator in the WHERE clause in this case acts the same as
an INNER JOIN, as the ID fields must exist in the table inside the IN
part in order for the main table's records to be selected.

Using a NOT IN would be how you could implement a LEFT JOIN from the
main table to the child table. However, it's much less efficient for
running time than a LEFT JOIN.
--

Ken Snell
http://www.accessmvp.com/KDSnell/



Ken,

Thank you. I have never seen syntax like that, but it worked. :)

God Bless,

Mark


Forgot a . in my original reply to you, Mark. Here is corrected query:

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID())
AND ((Customers.Custid) IN
(SELECT CustomerAccess.Custid FROM
CustomerAccess)))
ORDER BY [Order Entry Header].ordJob;

--

Ken Snell
http://www.accessmvp.com/KDSnell/



Hello,

I have a query as a Recordsource for a form. It originally contained
two tables, [Order Entry Header] and [Customers] with an inner join.
It was updateable.

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM [Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701))
ORDER BY [Order Entry Header].ordJob;



I added another table [CustomerAccess] which purpose is to limit the
records by Customer and UserID. This table simply shows which users
can access which customers. The function getUserID tells which User
who may have access to several customers.


SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID()))
ORDER BY [Order Entry Header].ordJob;


Suddenly the Recordset is not updateable. It doesn't make sense to
me. These are linked SQL Server tables, so I thought maybe something
in the field properties or indexes may be the problem, so I imported
the [CustomerAccess] table to see if a local table would fix it, but
it didn't.

Any siggestions on how to make this recorset updateable is
apprecated.

God Bless,

Mark A. Sam
Click to expand...
Click to expand...
Click to expand...
 
M

Mark A. Sam

Ken,

This is bizarre. Suddenly the recordset (with the revised query) is not
updateable. It has to be something beside the query design. I'm pretty
sure the original query should be been updateable. But this is among a
string of bizarre behaviors I am encountering.

God Bless,

MarkAs


Ken Snell said:
Forgot a . in my original reply to you, Mark. Here is corrected query:

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID())
AND ((Customers.Custid) IN
(SELECT CustomerAccess.Custid FROM
CustomerAccess)))
ORDER BY [Order Entry Header].ordJob;

--

Ken Snell
http://www.accessmvp.com/KDSnell/



Mark A. Sam said:
Hello,

I have a query as a Recordsource for a form. It originally contained two
tables, [Order Entry Header] and [Customers] with an inner join. It was
updateable.

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM [Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701))
ORDER BY [Order Entry Header].ordJob;



I added another table [CustomerAccess] which purpose is to limit the
records by Customer and UserID. This table simply shows which users can
access which customers. The function getUserID tells which User who may
have access to several customers.


SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID()))
ORDER BY [Order Entry Header].ordJob;


Suddenly the Recordset is not updateable. It doesn't make sense to me.
These are linked SQL Server tables, so I thought maybe something in the
field properties or indexes may be the problem, so I imported the
[CustomerAccess] table to see if a local table would fix it, but it
didn't.

Any siggestions on how to make this recorset updateable is apprecated.

God Bless,

Mark A. Sam
Click to expand...
Click to expand...
 
K

Ken Snell

I am not familiar with using ACCESS as front-end to SQL Server, so I'm
guessing here. Perhaps something in your connection to the SQL Server
database has changed and this has made your recordsets to that database
nonupdateable?
--

Ken Snell
http://www.accessmvp.com/KDSnell/


Mark A. Sam said:
Ken,

This is bizarre. Suddenly the recordset (with the revised query) is not
updateable. It has to be something beside the query design. I'm pretty
sure the original query should be been updateable. But this is among a
string of bizarre behaviors I am encountering.

God Bless,

MarkAs


Ken Snell said:
Forgot a . in my original reply to you, Mark. Here is corrected query:

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID())
AND ((Customers.Custid) IN
(SELECT CustomerAccess.Custid FROM
CustomerAccess)))
ORDER BY [Order Entry Header].ordJob;

--

Ken Snell
http://www.accessmvp.com/KDSnell/



Mark A. Sam said:
Hello,

I have a query as a Recordsource for a form. It originally contained two
tables, [Order Entry Header] and [Customers] with an inner join. It was
updateable.

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM [Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701))
ORDER BY [Order Entry Header].ordJob;



I added another table [CustomerAccess] which purpose is to limit the
records by Customer and UserID. This table simply shows which users can
access which customers. The function getUserID tells which User who may
have access to several customers.


SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID()))
ORDER BY [Order Entry Header].ordJob;


Suddenly the Recordset is not updateable. It doesn't make sense to me.
These are linked SQL Server tables, so I thought maybe something in the
field properties or indexes may be the problem, so I imported the
[CustomerAccess] table to see if a local table would fix it, but it
didn't.

Any siggestions on how to make this recorset updateable is apprecated.

God Bless,

Mark A. Sam
Click to expand...
Click to expand...
Click to expand...
 
M

Mark A. Sam

Ken,

I agree and think it has something to do with the connection due to other
issues. Tomorrow, I'm going to import the tables and see what happens.

God Bless,

Mark

Ken Snell said:
I am not familiar with using ACCESS as front-end to SQL Server, so I'm
guessing here. Perhaps something in your connection to the SQL Server
database has changed and this has made your recordsets to that database
nonupdateable?
--

Ken Snell
http://www.accessmvp.com/KDSnell/


Mark A. Sam said:
Ken,

This is bizarre. Suddenly the recordset (with the revised query) is not
updateable. It has to be something beside the query design. I'm pretty
sure the original query should be been updateable. But this is among a
string of bizarre behaviors I am encountering.

God Bless,

MarkAs


Ken Snell said:
Forgot a . in my original reply to you, Mark. Here is corrected query:

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID())
AND ((Customers.Custid) IN
(SELECT CustomerAccess.Custid FROM
CustomerAccess)))
ORDER BY [Order Entry Header].ordJob;

--

Ken Snell
http://www.accessmvp.com/KDSnell/



Hello,

I have a query as a Recordsource for a form. It originally contained
two tables, [Order Entry Header] and [Customers] with an inner join.
It was updateable.

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM [Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701))
ORDER BY [Order Entry Header].ordJob;



I added another table [CustomerAccess] which purpose is to limit the
records by Customer and UserID. This table simply shows which users
can access which customers. The function getUserID tells which User
who may have access to several customers.


SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID()))
ORDER BY [Order Entry Header].ordJob;


Suddenly the Recordset is not updateable. It doesn't make sense to me.
These are linked SQL Server tables, so I thought maybe something in the
field properties or indexes may be the problem, so I imported the
[CustomerAccess] table to see if a local table would fix it, but it
didn't.

Any siggestions on how to make this recorset updateable is apprecated.

God Bless,

Mark A. Sam
Click to expand...
Click to expand...
Click to expand...
 
M

Mark A. Sam

Ken,

I imported the tables to the front end set the primary keys and appropriate
indexes and it didn't help. So the rules out an SQL Server connection
issue. Here is the recordsource when the form is open: Does it look
correct?

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=2) AND ((Customers.Custid) In (SELECT
CustomerAccess.Custid FROM CustomerAccess)))
ORDER BY [Order Entry Header].ordJob;

I tested this in a query, which returned a not updateable recordset.

Thank you for your input and God Bless,

Mark

Ken Snell said:
I am not familiar with using ACCESS as front-end to SQL Server, so I'm
guessing here. Perhaps something in your connection to the SQL Server
database has changed and this has made your recordsets to that database
nonupdateable?
--

Ken Snell
http://www.accessmvp.com/KDSnell/


Mark A. Sam said:
Ken,

This is bizarre. Suddenly the recordset (with the revised query) is not
updateable. It has to be something beside the query design. I'm pretty
sure the original query should be been updateable. But this is among a
string of bizarre behaviors I am encountering.

God Bless,

MarkAs


Ken Snell said:
Forgot a . in my original reply to you, Mark. Here is corrected query:

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID())
AND ((Customers.Custid) IN
(SELECT CustomerAccess.Custid FROM
CustomerAccess)))
ORDER BY [Order Entry Header].ordJob;

--

Ken Snell
http://www.accessmvp.com/KDSnell/



Hello,

I have a query as a Recordsource for a form. It originally contained
two tables, [Order Entry Header] and [Customers] with an inner join.
It was updateable.

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM [Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701))
ORDER BY [Order Entry Header].ordJob;



I added another table [CustomerAccess] which purpose is to limit the
records by Customer and UserID. This table simply shows which users
can access which customers. The function getUserID tells which User
who may have access to several customers.


SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID()))
ORDER BY [Order Entry Header].ordJob;


Suddenly the Recordset is not updateable. It doesn't make sense to me.
These are linked SQL Server tables, so I thought maybe something in the
field properties or indexes may be the problem, so I imported the
[CustomerAccess] table to see if a local table would fix it, but it
didn't.

Any siggestions on how to make this recorset updateable is apprecated.

God Bless,

Mark A. Sam
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

I am guessing that having three tables in your query may have made it not
updateable. Perhaps you can remove the customerAccess table from the query.

SELECT [Order Entry Header].*
, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers
ON [Order Entry Header].ordCustID = Customers.Custid)
WHERE DateDiff('d',[ordDate],Date())<=701
AND
Customers.Custid In (SELECT
CustomerAccess.Custid FROM CustomerAccess
WHERE CustomerAccess.UserID = 2)
ORDER BY [Order Entry Header].ordJob;

Another possibility is to try you returning the primary keys of all three
tables in the SELECT clause. I am assuming that all three tables have primary
keys.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Ken,

I imported the tables to the front end set the primary keys and appropriate
indexes and it didn't help. So the rules out an SQL Server connection
issue. Here is the recordsource when the form is open: Does it look
correct?

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=2) AND ((Customers.Custid) In (SELECT
CustomerAccess.Custid FROM CustomerAccess)))
ORDER BY [Order Entry Header].ordJob;

I tested this in a query, which returned a not updateable recordset.

Thank you for your input and God Bless,

Mark

Ken Snell said:
I am not familiar with using ACCESS as front-end to SQL Server, so I'm
guessing here. Perhaps something in your connection to the SQL Server
database has changed and this has made your recordsets to that database
nonupdateable?
--

Ken Snell
http://www.accessmvp.com/KDSnell/


Mark A. Sam said:
Ken,

This is bizarre. Suddenly the recordset (with the revised query) is not
updateable. It has to be something beside the query design. I'm pretty
sure the original query should be been updateable. But this is among a
string of bizarre behaviors I am encountering.

God Bless,

MarkAs


Forgot a . in my original reply to you, Mark. Here is corrected query:

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID())
AND ((Customers.Custid) IN
(SELECT CustomerAccess.Custid FROM
CustomerAccess)))
ORDER BY [Order Entry Header].ordJob;

--

Ken Snell
http://www.accessmvp.com/KDSnell/



Hello,

I have a query as a Recordsource for a form. It originally contained
two tables, [Order Entry Header] and [Customers] with an inner join.
It was updateable.

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM [Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701))
ORDER BY [Order Entry Header].ordJob;



I added another table [CustomerAccess] which purpose is to limit the
records by Customer and UserID. This table simply shows which users
can access which customers. The function getUserID tells which User
who may have access to several customers.


SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID()))
ORDER BY [Order Entry Header].ordJob;


Suddenly the Recordset is not updateable. It doesn't make sense to me.
These are linked SQL Server tables, so I thought maybe something in the
field properties or indexes may be the problem, so I imported the
[CustomerAccess] table to see if a local table would fix it, but it
didn't.

Any siggestions on how to make this recorset updateable is apprecated.

God Bless,

Mark A. Sam
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell

My goofup. Try this SQL statement:

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid)
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID())
AND ((Customers.Custid) IN
(SELECT CustomerAccess.Custid FROM
CustomerAccess)))
ORDER BY [Order Entry Header].ordJob;

--

Ken Snell
http://www.accessmvp.com/KDSnell/



Mark A. Sam said:
Ken,

I imported the tables to the front end set the primary keys and
appropriate indexes and it didn't help. So the rules out an SQL Server
connection issue. Here is the recordsource when the form is open: Does
it look correct?

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=2) AND ((Customers.Custid) In (SELECT
CustomerAccess.Custid FROM CustomerAccess)))
ORDER BY [Order Entry Header].ordJob;

I tested this in a query, which returned a not updateable recordset.

Thank you for your input and God Bless,

Mark

Ken Snell said:
I am not familiar with using ACCESS as front-end to SQL Server, so I'm
guessing here. Perhaps something in your connection to the SQL Server
database has changed and this has made your recordsets to that database
nonupdateable?
--

Ken Snell
http://www.accessmvp.com/KDSnell/


Mark A. Sam said:
Ken,

This is bizarre. Suddenly the recordset (with the revised query) is not
updateable. It has to be something beside the query design. I'm pretty
sure the original query should be been updateable. But this is among a
string of bizarre behaviors I am encountering.

God Bless,

MarkAs


Forgot a . in my original reply to you, Mark. Here is corrected query:

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID())
AND ((Customers.Custid) IN
(SELECT CustomerAccess.Custid FROM
CustomerAccess)))
ORDER BY [Order Entry Header].ordJob;

--

Ken Snell
http://www.accessmvp.com/KDSnell/



Hello,

I have a query as a Recordsource for a form. It originally contained
two tables, [Order Entry Header] and [Customers] with an inner join.
It was updateable.

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM [Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701))
ORDER BY [Order Entry Header].ordJob;



I added another table [CustomerAccess] which purpose is to limit the
records by Customer and UserID. This table simply shows which users
can access which customers. The function getUserID tells which User
who may have access to several customers.


SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID()))
ORDER BY [Order Entry Header].ordJob;


Suddenly the Recordset is not updateable. It doesn't make sense to
me. These are linked SQL Server tables, so I thought maybe something
in the field properties or indexes may be the problem, so I imported
the [CustomerAccess] table to see if a local table would fix it, but
it didn't.

Any siggestions on how to make this recorset updateable is apprecated.

God Bless,

Mark A. Sam
Click to expand...
Click to expand...
Click to expand...
 
M

Mark A. Sam

John,

I made the adjustment you recommended. The Recordsource SQL is created
through VBA and I made the necessary adjustment and a debug.print to access
the actual recordsource being created:

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM [Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND ((Customers.Custid) In
(SELECT CustomerAccess.Custid FROM CustomerAccess WHERE
CustomerAccess.UserID = 2)))
ORDER BY [Order Entry Header].ordJob;

The form was not updateable, but I pasted the above statement a query and it
was updateable, however, it took close to 3 minutes to open the recordset.
Ken's version worked for a while then stopped.

God Bless,

Mark



John Spencer said:
I am guessing that having three tables in your query may have made it not
updateable. Perhaps you can remove the customerAccess table from the query.

SELECT [Order Entry Header].*
, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers
ON [Order Entry Header].ordCustID = Customers.Custid)
WHERE DateDiff('d',[ordDate],Date())<=701
AND
Customers.Custid In (SELECT
CustomerAccess.Custid FROM CustomerAccess
WHERE CustomerAccess.UserID = 2)
ORDER BY [Order Entry Header].ordJob;

Another possibility is to try you returning the primary keys of all three
tables in the SELECT clause. I am assuming that all three tables have
primary keys.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Ken,

I imported the tables to the front end set the primary keys and
appropriate indexes and it didn't help. So the rules out an SQL Server
connection issue. Here is the recordsource when the form is open: Does
it look correct?

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=2) AND ((Customers.Custid) In (SELECT
CustomerAccess.Custid FROM CustomerAccess)))
ORDER BY [Order Entry Header].ordJob;

I tested this in a query, which returned a not updateable recordset.

Thank you for your input and God Bless,

Mark

Ken Snell said:
I am not familiar with using ACCESS as front-end to SQL Server, so I'm
guessing here. Perhaps something in your connection to the SQL Server
database has changed and this has made your recordsets to that database
nonupdateable?
--

Ken Snell
http://www.accessmvp.com/KDSnell/


Ken,

This is bizarre. Suddenly the recordset (with the revised query) is
not updateable. It has to be something beside the query design. I'm
pretty sure the original query should be been updateable. But this is
among a string of bizarre behaviors I am encountering.

God Bless,

MarkAs


Forgot a . in my original reply to you, Mark. Here is corrected query:

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID())
AND ((Customers.Custid) IN
(SELECT CustomerAccess.Custid FROM
CustomerAccess)))
ORDER BY [Order Entry Header].ordJob;

--

Ken Snell
http://www.accessmvp.com/KDSnell/



Hello,

I have a query as a Recordsource for a form. It originally contained
two tables, [Order Entry Header] and [Customers] with an inner join.
It was updateable.

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM [Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701))
ORDER BY [Order Entry Header].ordJob;



I added another table [CustomerAccess] which purpose is to limit the
records by Customer and UserID. This table simply shows which users
can access which customers. The function getUserID tells which User
who may have access to several customers.


SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID()))
ORDER BY [Order Entry Header].ordJob;


Suddenly the Recordset is not updateable. It doesn't make sense to
me. These are linked SQL Server tables, so I thought maybe something
in the field properties or indexes may be the problem, so I imported
the [CustomerAccess] table to see if a local table would fix it, but
it didn't.

Any siggestions on how to make this recorset updateable is
apprecated.

God Bless,

Mark A. Sam
Click to expand...
Click to expand...
Click to expand...
 
M

Mark A. Sam

Ken, Your first version worked for a while then it didn't. John gave me
another variation which is updateable in a query (takes a long time to open
though), but not in a form. A lot of bizarre things are occuring in this db
as well as one of my own. If I said what they were you would all run me out
of town. ;)
 
J

John Spencer

Unless you are working with a HUGE recordset (millions of records) and you
have no indexes on the fields in the joins or in the where clause that shold
not take as long as it does.

Oh, the datediff in the where clause is going to really slow things down since
it will have to do the calculation for every row (record) returned by the join

Reformulate that so the calculation only takes place ONE time. I think you
will need something like the following which will return records that have an
OrdDate that is after 10 December 2007:

WHERE [OrdDate] > DateAdd('d',-701,Date()))

If the query is updateable and the form is not then you need to determine why
the form won't allow updates.
== Are you sure you are using the query as the forms record source?
== Is the form's Allow Edits property set to Yes? Also check Allow Additions
and Allow Deletions properties if you want to add/delete records.
== Is the form's Recordset Type set to Dynaset or Dynaset(Inconsistent Updates)?

== IF none of those apply, is it possible that the form is corrupted? IF so,
check out
Try Allen Browne's article on Recovering from Corruption at:
http://allenbrowne.com/ser-47.html

Also you take a look at Tony Toews' site
http://www.granite.ab.ca/access/corruptmdbs.htm

Jerry Whittle, Microsoft Access MVP has a white paper in a Word document named
Fix Corrupt Access Database towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
John,

I made the adjustment you recommended. The Recordsource SQL is created
through VBA and I made the necessary adjustment and a debug.print to access
the actual recordsource being created:

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM [Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND ((Customers.Custid) In
(SELECT CustomerAccess.Custid FROM CustomerAccess WHERE
CustomerAccess.UserID = 2)))
ORDER BY [Order Entry Header].ordJob;

The form was not updateable, but I pasted the above statement a query and it
was updateable, however, it took close to 3 minutes to open the recordset.
Ken's version worked for a while then stopped.

God Bless,

Mark



John Spencer said:
I am guessing that having three tables in your query may have made it not
updateable. Perhaps you can remove the customerAccess table from the query.

SELECT [Order Entry Header].*
, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers
ON [Order Entry Header].ordCustID = Customers.Custid)
WHERE DateDiff('d',[ordDate],Date())<=701
AND
Customers.Custid In (SELECT
CustomerAccess.Custid FROM CustomerAccess
WHERE CustomerAccess.UserID = 2)
ORDER BY [Order Entry Header].ordJob;

Another possibility is to try you returning the primary keys of all three
tables in the SELECT clause. I am assuming that all three tables have
primary keys.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
Ken,

I imported the tables to the front end set the primary keys and
appropriate indexes and it didn't help. So the rules out an SQL Server
connection issue. Here is the recordsource when the form is open: Does
it look correct?

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=2) AND ((Customers.Custid) In (SELECT
CustomerAccess.Custid FROM CustomerAccess)))
ORDER BY [Order Entry Header].ordJob;

I tested this in a query, which returned a not updateable recordset.

Thank you for your input and God Bless,

Mark

I am not familiar with using ACCESS as front-end to SQL Server, so I'm
guessing here. Perhaps something in your connection to the SQL Server
database has changed and this has made your recordsets to that database
nonupdateable?
--

Ken Snell
http://www.accessmvp.com/KDSnell/


Ken,

This is bizarre. Suddenly the recordset (with the revised query) is
not updateable. It has to be something beside the query design. I'm
pretty sure the original query should be been updateable. But this is
among a string of bizarre behaviors I am encountering.

God Bless,

MarkAs


Forgot a . in my original reply to you, Mark. Here is corrected query:

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID())
AND ((Customers.Custid) IN
(SELECT CustomerAccess.Custid FROM
CustomerAccess)))
ORDER BY [Order Entry Header].ordJob;

--

Ken Snell
http://www.accessmvp.com/KDSnell/



Hello,

I have a query as a Recordsource for a form. It originally contained
two tables, [Order Entry Header] and [Customers] with an inner join.
It was updateable.

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM [Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701))
ORDER BY [Order Entry Header].ordJob;



I added another table [CustomerAccess] which purpose is to limit the
records by Customer and UserID. This table simply shows which users
can access which customers. The function getUserID tells which User
who may have access to several customers.


SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid) INNER JOIN CustomerAccess ON
Customers.Custid = CustomerAccess.Custid
WHERE (((DateDiff('d',[ordDate],Date()))<=701) AND
((CustomerAccess.UserID)=getUserID()))
ORDER BY [Order Entry Header].ordJob;


Suddenly the Recordset is not updateable. It doesn't make sense to
me. These are linked SQL Server tables, so I thought maybe something
in the field properties or indexes may be the problem, so I imported
the [CustomerAccess] table to see if a local table would fix it, but
it didn't.

Any siggestions on how to make this recorset updateable is
apprecated.

God Bless,

Mark A. Sam
Click to expand...
Click to expand...
Click to expand...
 
M

Mark A. Sam

I think that at this point I'm going to drop the UserAccess table and place
a UserID field in the Customer table and filter that.

Thanks for the help.
 
M

Mark A. Sam

Ooops. Can't do that. The reason for the table is to allow multiple users
to be assigned the same client. I'll have to figure out a work around.
 
K

Ken Snell

Let's try one more variation:

SELECT [Order Entry Header].*, Customers.Custcode,
DateDiff('d',[ordDate],Date()) AS Days
FROM ([Order Entry Header] INNER JOIN Customers ON [Order Entry
Header].ordCustID = Customers.Custid)
WHERE (((DateDiff('d',[ordDate],Date()))<=701)
AND ((Customers.Custid) IN
(SELECT CustomerAccess.Custid FROM
CustomerAccess
WHERE ((CustomerAccess.UserID)=getUserID()))))
ORDER BY [Order Entry Header].ordJob;
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Unique records 2
Object Doesn't Support this property or Method 3
Format the result field in a make table query 1
Too few paranetiers 3
Query String not structured correctly 3
Make Table Query error 1
Type Mismatch running query 13
cross tab confusion 2

Top