469,927 Members | 1,357 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,927 developers. It's quick & easy.

Error When Converting to Access 2007 Record Not Available

I inherited an Access 2003 database runs fine in 2003 but in 2007 I get an error on updating a form. Record Not available on the last statement

Does anyone know why Access 2007 is different and any clues on how to get it to work?

Expand|Select|Wrap|Line Numbers
  1. With Forms!frmMain!frmSKUInput.Form
  2.     !WhichSKUCombo.Value = ""
  3.     !WhichSKUList.Value = ""
  4.     .RecordSource = strSQL1
  5.     .Requery
  6.     !WhichSKUList.RowSource = strSQL2
  7.     !WhichSKUCombo.RowSource = strSQL2
  8.     !WhichSKUList.Requery
  9.     !WhichSKUCombo.Requery
  10.     !ItemNumber.SetFocus
  11. End With   
Oct 11 '10 #1
4 1412
1,263 Expert 1GB
Where do you want to be in the recordset? If at the beginning, try adding a .movefirst after the .requery.

But I suspect the recordset may be empty. Have you tried monitoring through and looking at the value in strSQL1? I don't see you setting any value for that, is that a global variable? If so, it should have a g in the name to indicate that. Ditto strSQL2

Oct 12 '10 #2
Thanks, I tried adding .movefirst after the requery but it gave me an error, Run-time error '2465' Application-defined or object defined error.

The strSQL statements get initialized just befort the with loop, I just didn't show them to make the code shorter. What I don't get is it all works with 2003.
I just want to go to the first record in the set

Any help would be greatly appreciated

Expand|Select|Wrap|Line Numbers
  1.  strSQL1 = _
  2.     "SELECT tblItem.ID, tblItem.ItemNumber, tblItem.ItemDescription, tblItem.TypeID, tblItem.FamilyID, tblItem.BaseQty, tblItem.BaseUnitID, tblItem.File, tblItem.UnitsPerCase, tblItem.UnitsPerSU, tblItem.QtyPerPallet, tblItem.QPPUOMID, tblItem.Bulk, tblItem.StorLocID, tblItem.WE, tblItem.ForecastPct, tblItem.BulkTruckKg, tblItem.BulkCapicityKg " & _
  3.     "FROM tblCompCat INNER JOIN tblItem ON tblCompCat.ID = tblItem.TypeID " & _
  4.     "WHERE tblCompCat.ClassificationID = " & TypeID & " ORDER BY tblItem.ItemNumber"
  6. strSQL2 = _
  7.     "SELECT tblItem.ID, tblItem.ItemNumber, tblItem.ItemDescription " & _
  8.     "FROM tblCompCat INNER JOIN tblItem ON tblCompCat.ID = tblItem.TypeID " & _
  9.     "WHERE tblCompCat.ClassificationID = " & TypeID & " ORDER BY tblItem.ItemNumber" 
Oct 12 '10 #3
1,263 Expert 1GB
Well, obviously error 2465 on a simple .movefirst tells us something is seriously wrong. I might delete the form and import from a backup copy of the DB and see if it behaves the same way.

Oct 13 '10 #4
255 100+
If you are declaring variables within the form's coding module, you can skip all those [Form]! things. As one thing you need to double check, the format that's accepting by Access 2007 and 2003 are different. Such as Forms!frmMain needs to be [Forms]![frmMain].
Oct 15 '10 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by KivanÁ Karaca | last post: by
reply views Thread by susan | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.