Can't goto next record

[claytonjgordon]

I created a db with an access front end and three SQL back end tables. The users enter information into the access forms and its supposed to write to the SQL tables. On one form, this works perfectly: You hit a button on a menu form at it pulls up the data entry form as a new record.

Private Sub CreateAuditButton_Click()
On Error GoTo Err_CreateAuditButton_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    DoCmd.OpenQuery ("ExceptionQueueQry2")
    
    stDocName = "Mainform"
    DoCmd.OpenForm stDocName, acNormal, , , acFormAdd
    DoCmd.Close (acForm), "Calendar"

Exit_CreateAuditButton_Click:
    Exit Sub

Err_CreateAuditButton_Click:
    MsgBox Err.Description
    Resume Exit_CreateAuditButton_Click
    
End Sub

As i said, that works perfectly; but when I try to do the same exact thing with another form I can't get it to work. It insists on pulling up all the records currently on that table and doesn't let you add a new record, even if you the record navigator tool (greyed out).
The code I'm using is the same:

Private Sub Escalation_Button_Click()
On Error GoTo Err_Escalation_Button_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    
    stDocName = "ExceptionForm1"
    DoCmd.OpenForm stDocName, acNormal, , , acFormAdd
    DoCmd.Close acForm, "Mainform"
 

Exit_Escalation_Button_Click:
    Exit Sub

Err_Escalation_Button_Click:
    MsgBox Err.Description
    Resume Exit_Escalation_Button_Click
    
End Sub

and I check all the properties (details/Form) on both forms and everything is the same. Its as if the SQL table is write only (which I can't seem to verrify). I've also gone through all the properties on the SQL table sing SQL server Enterprise manager and the SQL tables seem to be set up exactly the same, too.

I'm not getting any error msgs indicating a possible error somewhere.

Does anyone have any ideas what could cuase this or how I could go about fixing it?


Dominus Nihil
(Master of Nothing)

 

[missinglinq]

If by "doesn't let you add a new record, even if you the record navigator tool (greyed out)" you mean that the icon for adding a new record is greyd out, then my guess would be that in the form's properties AllowAdditions is set to No. Don't know of any other reason for this to be so.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[claytonjgordon]

Allow additions is correctly set to yes.

And you are correct, it doesn't allow me to add a record. If there are more than one record in the table, i can go back and fort between them, but can't get to a new record via code, mouse wheel, or the record navigator.

Any other ideas? This is totally stumping me, I can't find any reason why it wouldn't work for this form and it works fine for the other one set up the same way. I've even deleted the SQL table and created a new one in hopes of removing some hidden read only property but that didn't work, either.

Thanks for your suggestion, does anyone know of anything else that could be causing this?


Dominus Nihil
(Master of Nothing)

 

[missinglinq]

First, I'd go into the code editor and use Search to hunt for AllowAdditions, on the off chance that you've set it to No in code somewhere, especially if you've used another db as a template, as many of us do.

Secondly, and this just came to me as I was typing: Have you per chance, transferred a copy of this db to your machine o using a CD? Most CD burners set the file's properties to Read Only, so you need to check this also.

And lastly, if all else fails, you need to consider corruption, and do the old "make a blank database and iimport all objects into it" shuffle!

Good Luck!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[claytonjgordon]

I'm leaning towards corruption, myself. I didn't transfer the db using a CD and I dont have allowadditions in the code anywhere, and I build the db from scratch.

Plus, I'm fairly sure that it was working at one point and then stopped working. I'll try to recreate the form from scratch and see if that does it, if not, then I'll try the transfer shuffle.


Dominus Nihil
(Master of Nothing)

 

[claytonjgordon]

Oh, and thanks for your help and input. This one was driving me crazy and I was half expecting it to be something easy I've overlooked as it usually is.


Dominus Nihil
(Master of Nothing)

 

[missinglinq]

Let us know when you solve it!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[JoeAtWork]

Your backend is SQL Server, correct?

In that case, the query that your form is based on most likely doesn't include the primary key. You need the primary key in order to do inserts (or edit existing records, for that matter).

Also, is the query based on one or more tables?

 

[claytonjgordon]

Joe at work, to answer your questions:

It IS a SQL server for the backend.

There is no query involved in this form (its a data entry form where the uses just add new records to the table), and the form does have the primary key which is an identity field. Its linked through an ODBC connection.

There is only one table that is truely involved in the form, although the primary key from another table is captured on the form load via the lookup command as a connection between the other two tables on the back end for reporting.


Dominus Nihil
(Master of Nothing)

 

[claytonjgordon]

I got it working but i didn't so much as fix it as it just started working. Still a bit of a mystery.

I created a new form with the same fields and functionality without worring about making it pretty and linked it to the SQL back end then refreshed all the links for good measure. I then created a new button on the previous form that would call this new form to a new 'add' record and it worked. Then I tried the other button that wasn't working, and that one then worked also. So it seemes, when the other button and form worked, it somehow fixed the issue on the first form without making any changes to the form or SQL table.

However, another form in the db is now giving me a write conflict error anytime I try to save the record after modifing a field which I can't find any reason why any data would have changed through code. Its very possible that the db is just courrupted.


Dominus Nihil
(Master of Nothing)