473,320 Members | 1,854 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

current record lost

I have a database that has a complex screen, when it is in "exclusive" mode for develoment the screen with a subscreen works perfectly but when I change to normal running with shared mode the screen appears to loose its current record and i get run time error '3021'

Expand|Select|Wrap|Line Numbers
  1. Private Sub stockID_AfterUpdate()
  2.  
  3.     If (Me.Dirty = True) Then
  4.         Me.Dirty = False
  5.     End If
  6.     Me.current_record = Me.lineID
  7.  
  8.     Dim db As Database
  9.     Dim floor_cost As dao.Recordset
  10.     Dim floorSQL As String
  11.     Dim cstockID As dao.Recordset
  12.     Dim cslineid As Integer
  13.     Dim update_room As String
  14.  
  15.  
  16.     DoCmd.SetWarnings FalseSet db = CurrentDb
  17.     Set cstockID = db.OpenRecordset("SELECT stockID FROM estimate_lines WHERE lineID = " & Me.current_record & " ;", dbOpenDynaset)
  18.     MsgBox cstockID.RecordCount   - debug to check record
  19.     cstockID.MoveFirst
  20.     cslineid = cstockID.Fields("stockID")
  21.  
  22.  
  23.     floorSQL = "SELECT stockID, cost_price, sales_price " & _
  24.                                       "  FROM stock " & _
  25.                                       " WHERE stockID = " & cslineid & " ;"
  26.  
  27.     Set floor_cost = db.OpenRecordset(floorSQL, dbOpenDynaset)
  28.     floor_cost.MoveFirst
  29.  
  30. ...........   much more code
  31.  
on the line where I have put "debug to chweck error" I see 1 record when in development mode which then does make cslineid equal to stockID but in normal running stockID can be seen to be 655 (for instance) but cslineid is zero.

I simply don't understand, I have been trying different ways for nearly 4 days now.

can anyone help me please ?




Is there a running option that may control this
Sep 20 '16 #1
1 850
jforbes
1,107 Expert 1GB
What line is throwing the error?

You may want to use this type of pattern for opening a RecordSet. Moving to the First record on an empty recordset will throw an error. That may be what is happening for you, but it's hard to tell for sure. Using a code pattern like the following it would be easier to tell:
Expand|Select|Wrap|Line Numbers
  1.     Dim iCount As Integer
  2.     Dim sSQL As String
  3.     Dim oRst As Recordset
  4.  
  5.     sSQL = "SELECT * FROM SomeTable WHERE Something LIKE 'AValue*' ORDER BY Something DESC"
  6.     Set oRst = CurrentDB.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)
  7.     If oRst.RecordCount > 0 Then
  8.  
  9.         ' The following is only needed to if you need an accurate record count
  10.         ' it will be greater than Zero, but not accurate until the last record is loaded
  11.         oRst.MoveLast
  12.         oRst.MoveFirst
  13.         iCount = oRst.RecordCount
  14.  
  15.         ' Get a single record's values        
  16.         sSomeValue = oRst!SomeValue
  17.         iCount = Nz(oRst!SomeCountValue, 0)
  18.  
  19.         ' Or use a While to loop through all the records
  20.         While Not oRst.EOF
  21.             ' Do some Stuff
  22.             iCount = iCount + Nz(oRst!SomeCountValue, 0)
  23.             oRst.MoveNext
  24.         Wend
  25.     Else
  26.         Call MsgBox("Empty Recordset... more descriptive error.")
  27.     End If
  28.     oRst.Close
  29.  
Alternatively, depending on what you are doing, you may want to look into DLookup Function. DLookup is slightly slower than what you are doing, but it's also much simpler and easier to troubleshoot.
Sep 20 '16 #2

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

Similar topics

4
by: John | last post by:
Hi Is there a way to print/e-mail the current record as it appears on a form? Thanks Regards
1
by: Tony | last post by:
I have a form in access that has 5 fields for a certain record: 1)Date mailed, 2)Date received, 3)Date completed 4)Foreign Content amount and 5)record number. If the foreign content (FC)is >15% of...
2
by: Tony | last post by:
Hello, I am having difficulty in getting the current record of the current form to show after pressing a command button that takes me to another form. The command button takes me to another...
4
by: DBQueen | last post by:
I have a subform which is in Continuous Forms view. I have added a button to the bottom of the page to move to the next record using the button wizard (result: DoCmd.GoToRecord , , acNext). I...
1
by: Richard Coutts | last post by:
I have a Continuous Form where each record has a button that activates another form that simplifies entering values into the record. The activated form has the equivalent of a "Done" button. I'd...
3
by: Maria | last post by:
Is there another way to delete the current record in a subform from the main form, another subform or a sub-subform other than setting focus on a field in the subform and using run command...
8
by: Zlatko Matić | last post by:
There is a form (single form) and a combobox. I want that current record of the form is adjusted according to selected value in the combobox. Cuurrent record should be the same as the value in the...
1
by: 4004 | last post by:
I would like to open a columnar form (so I can see all the details) from a datasheet form (so I can see what is there) but keep the same recordset and current record. I can do the recordset set...
1
by: Reginald Bal | last post by:
Main Form/SubForm When I requery or recalc my parent form after I updated a record in my subform, I lose the focus of that particular record in my subform. Instead, the first record is selected....
1
by: Jeff Karli | last post by:
I think I now understand how to use this forum. I hope I am following the rules now. I am a college student working on a database project that requires some VBA coding for the Event Procedures and...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.