Why can't I add a new record? Form is based on Qry

[Real Wally]

Hi all,

Today I've made a few new forms in my DB that use a query as a source. Forms work fine except for one little (?) thing: I cannot create a new record on any of these forms!! I get the following error message: "You can't go to the specified record.
You may be at the end of a record set".

I don't understand what's causing this. The 'go to next record button' was made with the wizard.

I understand that it's very common, maybe even preferable to base forms on queries so it's not as if I'm trying something special.

Any ideas?

Cheers,

Wally

 

[AncientOne]

is your query updateable? How are you attempting to add a record? is your form set up to edit records? Is it set up to add records?

 

[Mile-O]

By the sounds of it, I think he's trying to move through the records to get to the end of the recordset and then fill out the new record that lurks at the end of a table that allows additions which isn't in the query.

 

[Mile-O]

Rather than go to next record command, the go to new record sounds more like what's wanted.

DoCmd.GoToRecord, , acNewRec

 

[Real Wally]

Hi AncientOne,

1. I'm using my form to add and edit records (at least, thats what I used to do and hope to do with this one). As far as I now my form is editable, i.e. I've done nothing different to these frms from what I used to do and I've always been able to add and edit.

Update query? Do you think that's it?
I've just tried to change the select-query to an update-query.
I've never used those, haven't needed them yet. I don't understand what it is that I have to fill in in the 'update-to' field.
Pretty stupid eh?

I cannot find an example in the NWind database so if you could give me an idea of what to put in thee that'd be great.

Thanks for helping me,

Wally

 

[Real Wally]

Hi Mile-O-Phile,

The piece of code you suggest is exactly what I've got there. I see why you think I'm just moving through the records. That's my error, I've actually got a 'New Record' button there.
That's always worked OK but not anymore. At least not on the new form. The old form, based on the table, still works fine with exactly the same code. I need to include a few fields from another table and from what I read the best way to set up a form in that case is a query. If that's not so I'd like to hear so.

I haven't got the experience to know why one would be prefered over the other but you obviously have. I'd appreciated your thoughts on this.

Thanks

Wally

 

[AncientOne]

No!

I didn't say use an update query! I said, is your query updateable? That is, if you open the query (not the form), can you add a record then, or does a message appear in the status bar "this recordset is not updatable"

And, as I asked, how are you attempting to add a record to the form? What Mile-O said may well be true. The message you are getting appears because you've navigated your way past the end of the recordset.

 

[Real Wally]

No!

I didn't say use an update query!

Oops! Sorry, I thought you did.
No, if I open the query it's not updatble, i.e. I don't have an empty record at the bottom. I don't get the error message as I wouldn't have a clue as to where to add a record in the query

And, as I asked, how are you attempting to add a record to the form? [/B]

Like I've just answered to Mile, I'm actually using a new record button made by the wizard with the code as mike suggested.

What should I do?

Wally

 

[AncientOne]

You might be using the wrong fields in your query. Post the SQL or the db. It'll be tomorrow before
I can answer again, though

 

[Rich]

If you can't add to the the query then you will not be able to add records to any form based on the query.
Post the sql of the query

 

[Real Wally]

I'll try the SQL first.
The DB is too big to upload and I'd have to strip it first.
I'm hoping this will give you an idea of where the problem is:

SELECT tblNewApplication.PDNumber, tblNewApplication.PriorityDocument, tblNewApplication.PriorityDate, tblNewApplication.Description, tblNewApplication.Title, tblApplication.AppID, tblApplication.ApplicationNum, tblApplication.PublicationDate, tblApplication.PatentAgencyID, tblApplication.AgentID, tblApplication.PriorityDocument, tblApplication.ResearchGroup, tblApplication.Assignee, tblApplication.NotesAppl
FROM (tblAssignee INNER JOIN tblPatents ON tblAssignee.Assignee = tblPatents.Assignee) INNER JOIN (tblNewApplication RIGHT JOIN ((tblApplication LEFT JOIN tblPD_App ON tblApplication.AppID = tblPD_App.ApplicationNum) INNER JOIN tblPD_App_Pub ON tblApplication.AppID = tblPD_App_Pub.ApplicationNum) ON tblNewApplication.PriorityDocument = tblPD_App.PriorityDocument) ON tblPatents.PublicationID = tblPD_App_Pub.PublicationID;

 

[Real Wally]

Forget the one above, I've copied from the DB I was working on and it's incomplete. It may not make all the difference, but here's the complete SQL

SELECT tblNewApplication.PDNumber, tblNewApplication.PriorityDocument, tblNewApplication.PriorityDate, tblNewApplication.Title, tblNewApplication.Description, tblNewApplication.NR_Person, tblApplication.AppID, tblApplication.ApplicationNum, tblAssignee.Assignee, tblApplication.PatentAgencyID, tblApplication.AgentID, tblApplication.NotesAppl, tblPatents.PatentAgentFileNum_Patent
FROM (tblAssignee INNER JOIN tblPatents ON tblAssignee.Assignee = tblPatents.Assignee) INNER JOIN (tblNewApplication RIGHT JOIN ((tblApplication LEFT JOIN tblPD_App ON tblApplication.AppID = tblPD_App.ApplicationNum) INNER JOIN tblPD_App_Pub ON tblApplication.AppID = tblPD_App_Pub.ApplicationNum) ON tblNewApplication.PriorityDocument = tblPD_App.PriorityDocument) ON tblPatents.PublicationID = tblPD_App_Pub.PublicationID;

 

[Kevin_S]

Real Wally,

The problem is the way you set up your query that you are attempting to use as the recordsource for you form. Problems that can occur include using tables that have many to one joins, or using a large number of tables in the query, etc.... To determine what problem you are having in particular search this forum for "query not updateable" - (I was actually involved in a long discussion on this just a few months ago so you can search for my user name and I should be one of the more recent posts). Also - search in MS Access help for "cant update query" and "query not updateable". The help file has a pretty extensive cause and effect senerio layout of query designs that should help you narrow down your problem and eliminate it.

HTH,
Kevin

 

[Real Wally]

Thanks all,

It looks like I had included too many tables in the qry. I've reorganised a little and was able to leave out two tables and now the qry is updatable, and so is the form.

Thanks for your assistance.

Wally