Why can't I enter info into query?

[jsdjsd]

For some reason the query I built will not let me enter any info into it.

Is this a relationship problem or because I am using the querie in a form that has subforms, (I can enter new info into the subforms.) or because I wrote code to just print the report to the current record?

Or something else????

Thanks!
Julie

 

[WildHare]

Well, Julie, I'd guess it's a relation ship problem. As well as a syntactical problem.

All queries are editable. But not all datasets returned by a query are. So your question is really, "why is not the dataset returned by my query editable?"

First off, make sure that the dataset is 'editable' - run the query, and see if you can change any of the data in the datasheet as it opens. If you can NOT, then the dataset is NOT updateable, and we have to look at why, by editing your query - perhaps there are joins that in some way prohibit changing the data. A non-editable dataset can result from, among other things, a query that does totals or any kind of collective functions like AVG or SUM...

If you can edit the data in the raw datasheet, then we need to look at why you can't edit it on the form that the query is the record source for.

I'm not sure how your last part of the question "or because I wrote code to just print the report to the current record" has any bearing - I'm totally puzzled by this statement.

Make sure the dataset is editable and then get back to us..we'll figger it out somehow.

Jim


--------------------------------------
"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman

 

[jsdjsd]

Thanks Jim!

I can't edit the query. It is built from one table, but that table is related to another table. The related table is not part of the query.

In the Query: I have a Contact Table that has a (PK)contactID;

a student table that has a (PK)studentID and a(FK)contact ID.

They are related via the contact ID. Not sure why this is causing a problem. The query asks for some of the info in the Contact Table, and nothing from the student table.

My thought about "or because I wrote code to just print the report to the current record" (should have been from the current record)is because the other queries that can't be updated are the "newbie letter" and "renewal letter". In the form, there are command buttons that print these two letters, with just the current form's info on them.

The other queries are all updatable.

I looked in help to see why queries weren't updateable, and I think the "newbie" and "renewal" letters aren't related to the other table used in the those queries.

Unfortunatly, I tried to relate them and then the query didn't return anything at all. I don't know how to relate them. Any good tutorials out there on how to relate tables?

Thanks!
Julie

 

[fneily]

Harnessing the Power of Updatable Queries

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_harness.asp

 

[WildHare]

Julie - it would seem from your description that you have a fairly straightforward One (Contact) to Many (Student) relationship.

If your query is pulling entirely from the Contact table, why is the Student table in the query at all?

You should be able to do this stuff fairly easily - there seems to be nothing in your description that would make it difficult. You're just missing one basic step somewhere.

If you'd like, go to my website where I have some Access tutorials and such, and shoot me an email - perhaps we can take this offline and I'll get you squared away.

http://www.jmhare.com/wildhare.htm

You'll see an email graphic about 3/4s of the way down the page.

I think this would be easier than posting back and forth for the next three days...

Jim

--------------------------------------
"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman

 

[gapmiller]

My problem is almost identical. I used to be able to update data using a form that uses query. The old copy of the database from February still works. The current copy of the database does not. I have not revised any of the code that I can remember. Today I imported some data and I noticed the problem when I tried to edit it.

Here's what I have figured out so far:
I went to the query that the form uses. I cannot update the data using that query. The query uses two tables; one is for Sites and one for Jobs and the two tables are related. It is set to only show records where the fields match. If I make a new query using only Jobs, I can edit the data using that query. If I add the table Sites to the query, I can't edit the Job data in the query any more.

Again, I don't have this problem with the old copy. Any ideas on what changed so that the new copy doesn't work any more?

 

[fneily]

I guess you didn't read the article I mentioned because it addresses your problem.

Also, why don't you post the SQL from the "old" query and the "new" query. Then we can see if they'll identical. To get the SQL, go to design view of your query, click on the dropdown arrow next to the view button and select SQL View and copy and paste to tek-tips.

 

[gapmiller]

I read it, but it didn't make sense until I was able to look at the SQL code for the query. I've only ever used design view and the wizards, and I never looked for the SQL view before. Thanks.

SQL from the query that allows user to update the data from the query:

SELECT DISTINCTROW tblJobNumbers.ID, tblJobNumbers.fldJobNumber, tblJobNumbers.fldJobName, tblJobNumbers.fldWarrStart, tblJobNumbers.fldWarrEnd, tblJobNumbers.fldSiteID, tblSites.ID, tblSites.fldSiteType, tblSites.fldGlobalControllerID, tblSites.fldSiteName, tblJobNumbers.fldPreviousJob, tblJobNumbers.fldSalesman, tblJobNumbers.fldProjectManager, tblJobNumbers.fldEngineer, tblJobNumbers.fldLeadInstaller, tblJobNumbers.fldJobNotes, tblJobNumbers.fldProjectStart
FROM tblSites INNER JOIN tblJobNumbers ON tblSites.ID = tblJobNumbers.fldSiteID
ORDER BY tblJobNumbers.fldJobNumber;


SQL from the query that does NOT allow user to update the data from the query:

SELECT DISTINCTROW tblJobNumbers.ID, tblJobNumbers.fldJobNumber, tblJobNumbers.fldJobName, tblJobNumbers.fldWarrStart, tblJobNumbers.fldWarrEnd, tblJobNumbers.fldSiteID, tblSites.ID, tblSites.fldSiteType, tblSites.fldGlobalControllerID, tblSites.fldSiteName, tblJobNumbers.fldPreviousJob, tblJobNumbers.fldSalesman, tblJobNumbers.fldProjectManager, tblJobNumbers.fldEngineer, tblJobNumbers.fldLeadInstaller, tblJobNumbers.fldJobNotes, tblJobNumbers.fldProjectStart
FROM tblSites INNER JOIN tblJobNumbers ON tblSites.ID = tblJobNumbers.fldSiteID
ORDER BY tblJobNumbers.fldJobNumber;