By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,882 Members | 2,465 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,882 IT Pros & Developers. It's quick & easy.

Error When Converting to Access 2007 Record Not Available

P: 27
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
!ItemNumber.SetFocus

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
Share this Question
Share on Google+
4 Replies


Expert 100+
P: 1,221
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

Jim
Oct 12 '10 #2

P: 27
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"
  5.  
  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

Expert 100+
P: 1,221
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.

Jim
Oct 13 '10 #4

100+
P: 255
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.