select query not doing what i need

  • Thread starter Thread starter Matthew
  • Start date Start date
M

Matthew

Hi,

First off, I don't really have much idea what I'm doing with dbs.

Now the queries work fine but complain or create 'bad records' when I try
and add new ones. The problem is arising because the linkage fields amongst
tables don't have the correct value. I guess I was expecting that for
example with the sql below that Access would have no need to told what the
"correct' value for "ownerid" should be because doesn't it know this
already? So question is: How do you append records with this scenario. Any
reason why adding with SELECT isn't working. Thanks for helping, matthew.


SELECT tblLists.listid, tblLists.listname, tblLists.sorted, tblLists.public,
tblLists.ownerid
FROM tblLists
WHERE (((tblLists.ownerid)=get_global("userid")))
ORDER BY tblLists.listname;
 
Hi,

First off, I don't really have much idea what I'm doing with dbs.

Now the queries work fine but complain or create 'bad records' when I try
and add new ones. The problem is arising because the linkage fields amongst
tables don't have the correct value. I guess I was expecting that for
example with the sql below that Access would have no need to told what the
"correct' value for "ownerid" should be because doesn't it know this
already? So question is: How do you append records with this scenario. Any
reason why adding with SELECT isn't working. Thanks for helping, matthew.


SELECT tblLists.listid, tblLists.listname, tblLists.sorted, tblLists.public,
tblLists.ownerid
FROM tblLists
WHERE (((tblLists.ownerid)=get_global("userid")))
ORDER BY tblLists.listname;
Click to expand...

This query by itself has nothing whatsoever to do with relationships
or linkage fields or appending records. All it's doing is filtering
the table tblLists for those records where the OwnerID field is equal
to the value returned by the get_global function.

What value is returned by that function? (I'd guess the NT or XP user
logon ID)? What's the datatype of OwnerID, and what are some typical
values of OwnerID? What are you trying to do, and what specific error
is happening?
 
I am not sure I understood your question as you wrote
about other Tables / Linking Fields but you Query only
involves ONE Table. From what I understood, there may be
2 possible causes:

1. If the Field OwnerID is a required Field then you need
to enter a value for the new Record unless it is also an
AutoNumber Field.

2. If the Field OwnerID is a ForeignKey Field, i.e. the
value pointing to a related Record in another Table in a
One-to-Many relationship with your Table tblLists
(the "Many" end) and you have enforced Referential
Integrity, then the value you enter must exist in the
other Table.

HTH
Van T. Dinh
MVP (Access)


-----Original Message-----
Hi,

First off, I don't really have much idea what I'm doing with dbs.

Now the queries work fine but complain or create 'bad records' when I try
and add new ones. The problem is arising because the linkage fields amongst
tables don't have the correct value. I guess I was expecting that for
example with the sql below that Access would have no need to told what the
"correct' value for "ownerid" should be because doesn't it know this
already? So question is: How do you append records with this scenario. Any
reason why adding with SELECT isn't working. Thanks for helping, matthew.


SELECT tblLists.listid, tblLists.listname,
Click to expand...
tblLists.sorted, tblLists.public,
 
Hi,
Thanks for replies. I understand that my initial post would not win any
awards for clarity. The jnr db programmer for this project left under abit
of a cloud and I've been assigned <sigh> to try and pick this this up.
Scenario: There is local session data stored and accessed on each pc by
set_global(key, value) and get_global(key) funcs. Forms have been created
based on sql queries. Where I'm stuck is how-to fillin values for a new
record which are read-only (ie: based on session values which can't be
editied by the user because they shouldn't be). So given:

SELECT tblLists.listid, tblLists.listname,
tblLists.sorted, tblLists.public,
Click to expand...

as sql for form:
listid is autonumber
user can fill in: listname, sorted, public
and ownerid has to be filled in automatically from the session data via:
get_global("userid"). The field in form is not enabled so user can't edit
it.

I think what I need is:
a) trap an event for new record insertion
b) do something like Me.ownerid = get_global("userid")
c) save record if all other values are valid, etc.

Hopefully this is clearer and thanks for helping. matthew.
 
a) trap an event for new record insertion
Click to expand...

The Form's BeforeInsert event...
b) do something like Me.ownerid = get_global("userid")
Click to expand...

Well said:
c) save record if all other values are valid, etc.
Click to expand...

Use the Form's BeforeUpdate event to check for validity, and set
Cancel to true (with a MsgBox to warn the user) if it's not.

You might want to make the controls the user isn't supposed to edit
invisible, and/or Locked=True and Enabled=False. You can set them in
code but the user can't do anything with them.
 
Thanks. Works just fine. matthew.
John Vinson said:
The Form's BeforeInsert event...




Use the Form's BeforeUpdate event to check for validity, and set
Cancel to true (with a MsgBox to warn the user) if it's not.

You might want to make the controls the user isn't supposed to edit
invisible, and/or Locked=True and Enabled=False. You can set them in
code but the user can't do anything with them.
Click to expand...
 
Back
Top