Form won't go to new record

[Steve@trop]

I just discovered a couple forms in my database that won't go to a new record when they are opened. I can add records directly to the table so I know it's not locked. I have many other forms that are set up this way that don't have this problem.

On the Form_Load Event I have the following code:

DoCmd.GoToRecord , PDMVitalityF, acNewRec

I get the following error:
"Run-Time error '2105'
You can't go to the specified record."
The allow additions property is set to "Yes". The biggest difference I can see between the forms that work and these forms is that these forms contain a subform. The subform is for informational purposes only. It shows a list of recent previous records for this detector. I tried removing the subform but I get the same error so it may have nothing to do with the problem.

Any ideas on this?

 

[jdraw]

If PDMVitalityF is the name of your form, I think it should be in quotes --- "PDMVitalityF"

see syntax here
http://msdn.microsoft.com/en-us/library/office/aa189832(v=office.10).aspx

 

[Steve@trop]

I tried it but I get the same error. Nice try though, if only it were that simple. The other forms that work don't even specify the form at all, it's just a blank space between the commas. I tried doing that but I still get the error.

It may have nothing to do with the code at all. When I hit the "End" button and get into the form I can see that the "New Record" button is greyed out, as though it were locked against creating new records. I just can't see where it could be locked from. The table isn't locked and the form is set to allow new additions and there are no record locks on the form.

 

[pr2-eugin]

So what is the code you have changed to now?

Is the Form's Recordsource an Updatable Query?

 

[Steve@trop]

I'm pretty sure it has nothing to do with the code as the same code works perfectly in other forms. You are correct, the problem is the recordset is a query and it's not updatable. When I go to the properties of the query, it doesn't show as locked but when I view the query results, there is no new record button. I think I'm on to something now. I'm going to create a seperate query and use that as the recordsource. Stay tuned.

 

[pr2-eugin]

Allen Brown's link on Why is my Query read-only, should help you to get on something..

Good Luck..

 

[Steve@trop]

Well, I think the query doesn't allow new records because it joins two tables. Here is the SQL for the query:

SELECT PDMVitalityT.*, AmmoniaDetectorT.CalibrationFrequency, AmmoniaDetectorT.Capacity
FROM AmmoniaDetectorT INNER JOIN PDMVitalityT ON AmmoniaDetectorT.DetectorNumber = PDMVitalityT.DetectorNumber;

What it is doing is picking up information about the detector the user is entering data for. The calibration frequency and the Capacity.

I've got to figure out a way to make this work. Funny thing is, I think it DID work when I first created this form...

 

[Steve@trop]

I got it! The link you sent me helped find the problem. I was missing an index on one of the fields in the joined table. I manually created an relationship between the joined fields and also set the field to be indexed and now it works correctly again!

Now on to the next problem....

Thanks for your help!

Steve

 

[pr2-eugin]

Glad to help mate.. Good luck..