Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: Recordsets

  1. #1
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    9

    Question Unanswered: Recordsets

    Dear All,

    I have converted an Access2 Database to Access 97 recently - and I'm currently within the testing phase. I have the following run-time error:

    You can't go to the specified record
    You may be at the end of a recordset.

    Basically I use the New button from the Contact Overviews Form to open the Opportunities form at a new record. Several field values are set.

    The Opportunities form is based on a query which has three tables one of which is the record set.

    This works fine in Access 2 but not in Access 97.

    Thanks in advance for any help on this.

    Kind Regards

    Gary.

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Recordsets

    Originally posted by Gary003
    Dear All,

    I have converted an Access2 Database to Access 97 recently - and I'm currently within the testing phase. I have the following run-time error:

    You can't go to the specified record
    You may be at the end of a recordset.

    Basically I use the New button from the Contact Overviews Form to open the Opportunities form at a new record. Several field values are set.

    The Opportunities form is based on a query which has three tables one of which is the record set.

    This works fine in Access 2 but not in Access 97.

    Thanks in advance for any help on this.

    Kind Regards

    Gary.
    You will find this when you try to go beyond the end of the recordset. If you are using your own custom navigation buttons you can either customize the code to provide a more user friendly message or disable the appropriate button when at the end of a recordset. Basically, in recordset lingo you have either set the recordsets EOF or BOF to true and then tried to go beyond.

    Gregg

  3. #3
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    9

    Re: Recordsets

    Originally posted by basicmek
    You will find this when you try to go beyond the end of the recordset. If you are using your own custom navigation buttons you can either customize the code to provide a more user friendly message or disable the appropriate button when at the end of a recordset. Basically, in recordset lingo you have either set the recordsets EOF or BOF to true and then tried to go beyond.

    Gregg

    Thanks for the help Gregg - much appreciated.

    I've stepped through the code and there are EOF references. But I just re-linked my tables and it has just worked. Could it be that the improper links were causing upsetting the recordsets?

    Thanks again for the advice - I'm going to test this fully now just to make sure all is right.

    King Regards

    Gary.

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Recordsets

    Originally posted by Gary003
    Thanks for the help Gregg - much appreciated.

    I've stepped through the code and there are EOF references. But I just re-linked my tables and it has just worked. Could it be that the improper links were causing upsetting the recordsets?

    Thanks again for the advice - I'm going to test this fully now just to make sure all is right.

    King Regards

    Gary.
    It's hard to say as far as the linked tables are concerned. Access generates some peculiar errors sometimes that don't seem related to the problem at hand.

    Glad you're on the right track though.

    Gregg

  5. #5
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    9

    Re: Recordsets

    Originally posted by basicmek
    It's hard to say as far as the linked tables are concerned. Access generates some peculiar errors sometimes that don't seem related to the problem at hand.

    Glad you're on the right track though.

    Gregg
    Thank you again for the help, but now my working version has failed again.

    All of my runtime errors disappeared and all the functionality worked on several tests I closed and open the database and re-tested several times to prove this. But its began failing again and I have made no further changes.

    You’re definitely right Greg, about Access generating some weird errors. I'm completely puzzled.

    Now I'm not sure if its recordsets or indeed something else?

    Any help would be greatly appreciated.


    Gary.

  6. #6
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Question Re: Recordsets

    Originally posted by Gary003
    Thank you again for the help, but now my working version has failed again.

    All of my runtime errors disappeared and all the functionality worked on several tests I closed and open the database and re-tested several times to prove this. But its began failing again and I have made no further changes.

    You’re definitely right Greg, about Access generating some weird errors. I'm completely puzzled.

    Now I'm not sure if its recordsets or indeed something else?

    Any help would be greatly appreciated.


    Gary.
    Can you post any of the code being used for record navigation or any other that you think might be worth studying?

    Gregg

  7. #7
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Talking record navigation

    Dim HisConn As New ADODB.Connection ' ADOBD Connection 2 DataBase
    Dim HisRS As New ADODB.Recordset ' ADOBD Recordset
    Dim cmdSQL As ADODB.Command ' ADOBD Command String

    Set HisConn = New ADODB.Connection
    HisConn.Open AdodcCo

    cmdSQL.CommandText = "Select * from [something])

    HisRS.Open cmdSQL.CommandText, HisConn, 1, 1, adCmdText
    HisRS.MoveLast
    HisRS.MoveFirst
    'Comment : Do the Movelast and then MoveFirst to see how much records there are its like a refresh but better
    __________________________________________________ ___
    'Comment : From the first Rs to the last

    Stop 'Press F8 to go line by line trough the code or F9 to run it

    HisRS.moveFirst
    Do Until HisRS.EOF
    For i = 0 To HisRS.Count
    debug.print HisRS(i).Value
    Next
    HisRS.Movenext
    Loop

    __________________________________________________ _______

    'Comment : From the last Rs to the first 1

    Stop 'Press F8 to go line by line trough the code or F9 to run it
    HisRS.MoveLast
    Do Until HisRS.BOF
    For i = 0 To HisRS.Count
    debug.print HisRS(i).Value
    Next
    HisRS.MovePrevius
    Loop

    __________________________________________________ __

  8. #8
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    9

    Re: Recordsets

    Originally posted by basicmek
    Can you post any of the code being used for record navigation or any other that you think might be worth studying?

    Gregg
    The code is attached. Again many thanks for your help.

    Its in a word 97 document.
    Attached Files Attached Files

  9. #9
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Recordsets

    Originally posted by Gary003
    The code is attached. Again many thanks for your help.

    Its in a word 97 document.
    Have you tried checking the value of Real_ID when you first load the form?
    I did noticed that you checked for an empty string in the load procedure and then assign the filter to the Real_ID value but do you know what is actually there at that point in the code.

    I would insert a message box in there just before the assignment to see what the program sees.

    Do you want to concentrate on the original code or the change you made involving the acNewRec stuff?


    Gregg

  10. #10
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    9

    Re: Recordsets

    Originally posted by basicmek
    Have you tried checking the value of Real_ID when you first load the form?
    I did noticed that you checked for an empty string in the load procedure and then assign the filter to the Real_ID value but do you know what is actually there at that point in the code.

    I would insert a message box in there just before the assignment to see what the program sees.

    Do you want to concentrate on the original code or the change you made involving the acNewRec stuff?


    Gregg
    When the code runs the Real_IDOld takes the value of 50 from Real_ID which is also 50. This is in the access 2 version that works .

    In Access 97 (test case1) with the following in the Visible CBF module:

    DoCmd.OpenForm "Opportunities", A_NORMAL, , , A_ADD,
    A_NORMAL

    Real_IDOld is Null and the following message appears:

    Runtime Error 2113

    The value you entered isn't valid for this field.
    For example you may have entered text in a numeric field or a number that is larger than the fieldsize setting permits.

    I have checked these and these are integer the field only needs to hold a to digit number.

    In Access 97 (Test Case2) with:

    DoCmd.OpenForm " Opportunities "
    DoCmd.GotoRecord acDataForm, " Opportunities ",acNewRec
    instead of the above.

    I set msg box before and after to check the values of both Real_IDOld and Real_id. The value now seems to be going in fine - but the error I get now is:

    The object 'Opportunities isn't open
    The macro you are running either directly or indirectly contains a GoToRecord, RepaintObject or SelcectObject action, bu the Object Name argument names an object that is closed.

    The Opportunities form open but the Contact_ID combo-box and Real_ID combo -boxes are empty and I can not select from the list either - locked. These should be set with values upon opening.

    The wierd thing is that ths whole function has worked with Access 97 with case 1 and then all of a sudden stopped.

    Thanks for your time and effort on this much appreciated.

  11. #11
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Recordsets

    Originally posted by Gary003
    When the code runs the Real_IDOld takes the value of 50 from Real_ID which is also 50. This is in the access 2 version that works .

    In Access 97 (test case1) with the following in the Visible CBF module:

    DoCmd.OpenForm "Opportunities", A_NORMAL, , , A_ADD,
    A_NORMAL

    Real_IDOld is Null and the following message appears:

    Runtime Error 2113

    The value you entered isn't valid for this field.
    For example you may have entered text in a numeric field or a number that is larger than the fieldsize setting permits.

    I have checked these and these are integer the field only needs to hold a to digit number.

    In Access 97 (Test Case2) with:

    DoCmd.OpenForm " Opportunities "
    DoCmd.GotoRecord acDataForm, " Opportunities ",acNewRec
    instead of the above.

    I set msg box before and after to check the values of both Real_IDOld and Real_id. The value now seems to be going in fine - but the error I get now is:

    The object 'Opportunities isn't open
    The macro you are running either directly or indirectly contains a GoToRecord, RepaintObject or SelcectObject action, bu the Object Name argument names an object that is closed.

    The Opportunities form open but the Contact_ID combo-box and Real_ID combo -boxes are empty and I can not select from the list either - locked. These should be set with values upon opening.

    The wierd thing is that ths whole function has worked with Access 97 with case 1 and then all of a sudden stopped.

    Thanks for your time and effort on this much appreciated.
    Am I remembering correct, that you said Real_IDOld is an invisible textbox? If so, try making it visible.

    It's early AM, and work is about to commence but the thought occurred to me yesterday while I was reading the code. Why not a variable instead of the textbox? If I'm remembering wrong sorry. I'll have more time this weekend to look at it.

    From an early morning foggy head.

    Gregg

  12. #12
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    9

    Re: Recordsets

    Originally posted by basicmek
    Am I remembering correct, that you said Real_IDOld is an invisible textbox? If so, try making it visible.

    It's early AM, and work is about to commence but the thought occurred to me yesterday while I was reading the code. Why not a variable instead of the textbox? If I'm remembering wrong sorry. I'll have more time this weekend to look at it.

    From an early morning foggy head.

    Gregg
    Hi Gregg,

    Yep, the Real_IDOld text box is invisible - I've used Msgbox's and making it visible to see what its exact value was once the form loaded.

    I'm trying to make the code work by going back to test case 1. I've tried to set the value of the invisible textbox explicitly within the code beacuse its value is always 50 when the form loads/opens.

    I will try out using a variable instead of an invisible textbox too.

    I didn't write the original code that person has left before I arrived - so I've had to plough through it - its my role to maintain it.

    I find that a good cup of coffee does the trick. Its PM here and I'll be leaving in a few hrs time. Will you be working over the weeknd then? I'll probably look at it from home too - if I get the chance too.

    Kind Regards

    Gary

  13. #13
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Recordsets

    Originally posted by Gary003
    Hi Gregg,

    Yep, the Real_IDOld text box is invisible - I've used Msgbox's and making it visible to see what its exact value was once the form loaded.

    I'm trying to make the code work by going back to test case 1. I've tried to set the value of the invisible textbox explicitly within the code beacuse its value is always 50 when the form loads/opens.

    I will try out using a variable instead of an invisible textbox too.

    I didn't write the original code that person has left before I arrived - so I've had to plough through it - its my role to maintain it.

    I find that a good cup of coffee does the trick. Its PM here and I'll be leaving in a few hrs time. Will you be working over the weeknd then? I'll probably look at it from home too - if I get the chance too.

    Kind Regards

    Gary
    No, no work this weekend. But, this stuff is considered "fun" for me and is done on my off time so I'll probably spend a little time looking at it this weekend. Let me know of any new discoveries.

    Have a good weekend.

    Gregg

  14. #14
    Join Date
    Nov 2003
    Location
    London, UK
    Posts
    9

    Re: Recordsets

    Originally posted by basicmek
    No, no work this weekend. But, this stuff is considered "fun" for me and is done on my off time so I'll probably spend a little time looking at it this weekend. Let me know of any new discoveries.

    Have a good weekend.

    Gregg
    Hi the weekend was good apart form the non - stop rain. Hope you had a good one!

    I've tried to use a variable to set the value of the Real_ID field and also to use it instead of the Real_IDOld field thus removing the Real_IDOld field completely. e.g

    Dim Real_IDvar As Integer

    Real_IDvar = 50

    Real_IDOld = Real_IDvar

    I get the following message:

    'Can't assign a value to this object'

    The object may be a control on a read-only form
    The object may be on a form in design view
    The value may be too large for the object.

    The form properties allow Edits, Deletions, Additions are set to yes.
    The value is only a two digit integer and the field settings are integer too.

    The text-boxes are not locked and both are enabled. I have also tried to set these pre-hand

    Forms!Opportunities.Real_ID.Enabled = True
    Forms!Opportunities.Real_ID.Locked = False

    and still I am unable to set the value. If I bypass this then the code fails at some code for that checks the value of a another combo-box. The TypeCbo.

    If Not (Me!TypeCbo = 1 Or Me!TypeCbo = 10 Or Me!TypeCbo = 2) Then
    FOToggle = False
    FODeToggle = False
    FONIToggle = False

    The above code is called from: result = HideForeII() (at the end of the Opportunities Form OnOpen sub procedure.

    Both problems revlove around combo-boxes? and their values.

    Thanks in Advance for your help.

    Kind Regards

    Gary

  15. #15
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Question Re: Recordsets

    Originally posted by Gary003
    Hi the weekend was good apart form the non - stop rain. Hope you had a good one!

    I've tried to use a variable to set the value of the Real_ID field and also to use it instead of the Real_IDOld field thus removing the Real_IDOld field completely. e.g

    Dim Real_IDvar As Integer

    Real_IDvar = 50

    Real_IDOld = Real_IDvar

    I get the following message:

    'Can't assign a value to this object'

    The object may be a control on a read-only form
    The object may be on a form in design view
    The value may be too large for the object.

    The form properties allow Edits, Deletions, Additions are set to yes.
    The value is only a two digit integer and the field settings are integer too.

    The text-boxes are not locked and both are enabled. I have also tried to set these pre-hand

    Forms!Opportunities.Real_ID.Enabled = True
    Forms!Opportunities.Real_ID.Locked = False

    and still I am unable to set the value. If I bypass this then the code fails at some code for that checks the value of a another combo-box. The TypeCbo.

    If Not (Me!TypeCbo = 1 Or Me!TypeCbo = 10 Or Me!TypeCbo = 2) Then
    FOToggle = False
    FODeToggle = False
    FONIToggle = False

    The above code is called from: result = HideForeII() (at the end of the Opportunities Form OnOpen sub procedure.

    Both problems revlove around combo-boxes? and their values.

    Thanks in Advance for your help.

    Kind Regards

    Gary
    Hey Gary,

    I've just got a few minutes right now, but I was reading something this weekend that refreshed my memory concerning what occurs when in the form opening sequence. Am I remembering correct that some of the processing takes place in the Open event? Possibly the assignment of values to controls? If this is so, could you try to perform the actions in the Load event and see what happens? Hope I'm remembering correct.

    Gotta go. On the way to a funeral.

    Gregg

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •