Go To New Record with SQL tables error

Topic

New Reply

Hi,

I have an access database that works fine using access tables as the backend. Due to size of data, I am in the process of converting the tables to SQL tables. That has gone pretty smoothly (thank God) however I have some forms that give me an error on open when they OnLoad event is docmd.gotorecord,,acnewrec. It isn’t happening on all of the forms just certain ones. I’m trying to see what is different between the forms it is working on compared to the forms it isn’t Again, these forms were working fine with Access tables. Does anyone have any ideas of what I should look at?

Thanks!
Leesha

Reply | Quote

Replies

Have you checked whether it is possible to go to a new record at all in those forms (when bound to SQL Server)?

Reply | Quote

Hi Hans,

First I just noticed I posted this under Excel instead of Access. Sorry about that. Stressed I guess.

Anyway, as I said, the Access database is linked to sql tables. Forms that have a datasource that is a table vs a query seem to open fine with the go to new record onload event. It blows up whenever there form is linked to a query, especially if the query has any type of filter. As I was searching the internet I found a reference that stated its not actually the gotorecord,,acnewrec that is the issue ut just that that is what is highlighted due to being the first line of code. Sure enough I have another form where the first line of code is txtUsername.setfocus (vs gotorecord) and that blows up. If I take the query off the form it works fine. These are all working fine in when linked to access tables. It’s driving me nuts!

Leesha

Reply | Quote

Do the forms bound to queries work OK if you comment out the On Load event procedure (and if you have one, the On Open event procedure too)?

BTW, I have moved this topic to the Access forum.

Reply | Quote

I’ve never used the onOpen only the OnLoad (is one better than the other?). However I did comment out the code. The form opens but wth no records and if I try to go to a new record I get the error that that isn’t available now. I tried the code in the OnOpen event with the same results.

Leesha

Reply | Quote

OK, next question: do the queries themselves work correctly if you open them from the database window?

Reply | Quote

SQL runs fine if run on their own.

For example, I have the following code on the onload event:

DoCmd.GoToRecord , , acNewRec
Me.StatusAdmitNumber = 2
Me.StudentNameID = Forms!sfrmStudentReferralAdd!txtID

The SQL for the form is:

SELECT dbo_tblStudentStatus.StatusAdmitNumber, dbo_tblStudentStatus.StudentNameID, dbo_tblStudentStatus.StatusDefined, dbo_tblStudentStatus.StatusDate, dbo_tblStudentStatus.DischargeReason, dbo_tblStudentStatus.DischargeNoServiceComment, dbo_tblStudentStatus.DischargeOtherComment, dbo_tblStudentStatus.StatusComment
FROM dbo_tblStudentStatus;

I tried commenting out the second and third line of code and still it won’t load correctly. If I comment out all three lines it opens but there are no records and I can’t go to any.

Leesha

Reply | Quote

If you create a query with the SQL that you posted, and switch to datasheet view:
– Do you see existing records?
– Can you edit existing records?
– Can you go to a new record?

Reply | Quote

I get the same error if I convert to datasheet view etc and I can’t go to a new record. I can see the the existing records.

To answer your next post, I have the same problems if I use dbo_tblStudentStatus as the record source. It doesn’t seem to matter whether its a table or a query that its linked to.

Leesha

Reply | Quote

So the problem isn’t in the forms at all!

Can you actually open the linked tables?

Reply | Quote

I can open the linked table but can’t add or edit?????

Reply | Quote

Each table needs to have a unique index, otherwise it won’t be updateable from Access.
Also, to prevent problems in the future, each table should have a field of type TIMESTAMP.
After applying these changes in the SQL Server database, you must refresh the table links in your Access database.

See [post=”699758″]Post 699758[/post] for an explanation.

Reply | Quote

The tables that won’t allow an update or addition all have unique fields but none of the tables have timestamps. I’ll read up on the link you sent and go about putting those in and will post back if it works.

Thanks!
Leesha

Reply | Quote

I apologize in advance for how stupid this next question is going to sound, but do I code the the timestamp field to autofill somehow or does it do it on its own similar to an autonumber in access?

Thanks,
Leesha

Reply | Quote

A timestamp field is updated automatically with a number guaranteed to be unique each time any field in a record is modified.

I don’t work with SQL Server directly myself, so I don’t know whether it will automatically be populated when you add such a field to a table with existing records. I’m sure there are other Loungers who can tell you.

Reply | Quote

OK that’s good to know and is a relief. I would’ve died if I had to now write code to be sure that it was being written to!

BTW, I’ve tested a few of the problem tables and the addition of the timestamp is working!

As always you’ve been a lifesaver!

Thanks!
Leesha

Reply | Quote

FYI, the timestamp fields will automatically populate, so you don’t need to worry about initializing them. And they do solve a number of potential problems with ODBC linked SQL Server tables. Another suggestion – you may find it useful to link via ODBC to a view (a SQL Server equivalent of a query, and can be indexed for performance) instead of using a query as the data source for your form if performance becomes an issue. And finally, you will probably want to move all of your tables, even small lookup ones to SQL Server, as performance really suffers if you are joining an Access table toSsQL Server tables in a query.

Reply | Quote

>>FYI, the timestamp fields will automatically populate, so you don’t need to worry about initializing them.

Nothing shows in the Access table view but if I go into SQL Server MGMT Studio I see grayed out in each row. Is that the auto time stamp?

>> And finally, you will probably want to move all of your tables, even small lookup ones to SQL Server, as performance really suffers if you are joining an Access table toSsQL Server tables in a query.

Finally something I did by instinct was right! All the tables are sql.

I still have some “issues” with the forms but am working through those. My biggest issue is the whole autonumber being assigned after the save vs once you enter the record as in Access. That is affecting how some of my forms work.

Leesha

Reply | Quote

if I go into SQL Server MGMT Studio I see grayed out in each row. Is that the auto time stamp?

Yes, the timestamp field contains binary data.

Reply | Quote

Cool! Well, I’m getting there slowly but surely!!

Thanks,
Leesha

Reply | Quote

Cool! Well, I’m getting there slowly but surely!!

Thanks
Leesha

Check out [post=”701886″]Reading back SQL Server record just written[/post]

Reply | Quote

Thanks Pat! Will do.

Leesha

Reply | Quote

I still have some “issues” with the forms but am working through those. My biggest issue is the whole autonumber being assigned after the save vs once you enter the record as in Access. That is affecting how some of my forms work.

Leesha

Yes, you will need to address that in your code/forms. As you know, Access assigns the autonumber as soon as you “add” the record where SQL Server doesn’t assign it until you “save” the record.

When this has been an issue for me, I add and save a blank record to the SQL Server table, then the form queries for this new record, and I use the form to update the fields in the record. There may be a better solution, but that has worked for me up to this point.

Ken

Reply | Quote

Thanks for the hint. I found that by saving the record after the first field is updated also helps.

Leesha

Reply | Quote

Why don’t you use dbo_tblStudentStatus as record source for the form instead of the SQL statement?

Reply | Quote