Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264

    Question Unanswered: Determine and use record number

    I have a form with a subform on it. During the process records will be removed from the subform. After they are removed I need to be able to go back to the same position in the recordset so the user can continue (they traverse the recordset from start to finish). I can get the record number of the current record, I just can't figure out how to move to a specific record number.

    Thanks.
    Steve

  2. #2
    Join Date
    Jul 2004
    Posts
    125
    The answer depends if the forms are bound/unbound and what you use DAO? ADO? Macros? Also would be helpful to know the number of records being deleted in one session by a user, etc. This determines the simplicity/complexity of the coding. If you wish to upload a sample db with some addtional info, we think we can help.

  3. #3
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264

    Smile

    I choose a simple route for this form. The main form is unbound, it has two subforms. The main form has choices that allow the user to filter the records they would like to see in the subforms. Once they do this, the record source of the subform is replaced with this new SQL string and the form is refreshed.

    Subform 1 displays the main records, one at a time. Subform 2 display a list (datasheet) of records to match with the main record. The user will select one record from subform 2 to match with the main record and click the match records button which will open a new form with the record id's. The user then chooses the criteria for the match, makes the match and is returned to the original form.

    If the main record was considered a full match then it is no longer displayed on the form, if it was considered a partial match then it remains. Either way, I want to be able to return the user to the same location in the recordset where they were before they matched the records. If the main record stays, then it will be visible again, if it was removed then the next record will be visible.

    The users are balancing these accounts so they will eventually remove 90 - 95% of the records shown (some will clear in future months). The list is filtered by account and part number. There are 25 accounts, within each account there are between 10 and 2000 records (most accounts < 400). Approximately 60% of these will be matched up with this process.

    There are 3 challenges I am facing right now. 1. The question I originally asked, is how to get back to the same position in the recordset before the match was made. 2. How to change the view of a subform between a form and a datasheet in code. 3. How to allow the user to mark several subform records to be used in some kind of operation (calculating the sum of field, matching with the main record or making a group record change/move them to a different location.)

    Hope that clarifies.
    Thanks again.
    Steve.

  4. #4
    Join Date
    Jul 2004
    Posts
    125
    Your explanation is crystal clear! It requires several sql strings, each creating the required recordset.

    I assume that the main form selection is from a listbox or a combo box. I also assume this has a (hidden) column with a key field (unique) which sets the filter for the 2nd recordset (1st subform) and the 3rd recordset (subform2)

    The 3rd recordset displays unmatched or unpaid records. If the matches are to be selected randomly in the 2nd subform then you should consider a listbox instead of a subform because random selection is difficult in subforms. Once the records are selected, the form can pop up to select the record ID.

    Once this is accepted and updated, the next sql string sums the marked records and updates the sum in the appropriate table. (another recordset) and refreshes the record source of the 2nd subform whereby the records disappear.

    To return to the same record of the 1st recordset (the main form) is also is simple. First of all, in DAO, it's not necessary to close a recordset while you operate on mutliple other recordsets, nested in the successive operations and calling on a form for additional input like selecting record id's.

    If you wish to email me a sample database with a few records, I can code it for you and return it. Since I am not sure if this forum permits the posting of email addresses, I am spelling it here for you. ineuw at aei dot ca

    I hope this helps.

  5. #5
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264

    Smile

    Ok, so I made some progress, I found the command that I want to use, I am just having some trouble making the syntax work. What I want to do is use

    DoCmd.GoToRecord acActiveDataObject, , acGoTo, txtPrevRecord

    Where txtPrevRecord is the number that I saved before the match. The actual matching is done with a different form that is triggered by this form. From that other form, I want to execute the above statement on the subform of my main form.

    The main form is called frmReconcile, the subform in question is apInfo and the matching form is frmMatching.

    frmMatching makes the match, tags the records as matched and then requeries the apInfo form which removes the record. After the requery, I want to execute the above statement to move to the prior location in the recordset. I have tried several version of the above command but I can't seem to make it work into the subform. It works if I put a button directly on the subform but I can't get it to work from the matching form.

    Any ideas how to manipulate the syntax to make this work?

    Thanks
    Steve

  6. #6
    Join Date
    Jul 2004
    Posts
    125
    Sorry for the delay in response. Unfortunately, I don't code with macros but looked up your syntax and noticed that you left out the reference to the form object. Also, bear in mind that the record number displayed on the navigator bar is not an accurate way to return to the same record when changes may occur to the recordset. Implement a key in the data table and refer to that when moving to a record. In the macro syntax you must use the = operator to return to the record, as in:

    DoCmd.GoToRecord acActiveDataObject, [object] , acGoTo, PrimaryKey = Me!Primarykey

  7. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    The example below is pasted directly from the MS Access Help example


    The following example uses the GoToRecord method to make the seventh record in the form Employees current:

    DoCmd.GoToRecord acDataForm, "Employees", acGoTo, 7


    If you can remember the AbsolutePosition of the record you have deleted then this "should!" take you the record after the one deleted after the form is requeried.

    Use the subForm name in place of "Employees" in the example on the AbsolutePosition of the record just deleted in place of the 7 (ie variable AbsPos below)

    I believe the AbsolutePosition can be otained as follows

    AbsPos=me.RecordsetClone.AbsolutePosition

    If you are runningthe code in the main form then

    AbsPos=me.[subformContolName].Form.RecordsetClone.AbsolutePosition
    in which case AbsPos would need to be a Global variable, not a module variable.

    Note you will also have to trap/prevent trying to move after the last record if you delete it!

    HTH

    MTB

  8. #8
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Thanks for the response. I understand that the navigation number may not return to the same record, infact, that is what I am counting on. I only want to return to the same location in the recordset and view whatever record happens to be in that location. By the logic of the database, that will be the next record that the user should be considering.

    Out of curiousity, why did you consider the code that I posted to be macro programming? Is that where the docmd is usually used? I have the code inside of a procedure and seems to be working fine, maybe I should have used something else instead.

    Steve.

  9. #9
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi Steve

    I to was counting on not going to the same record, because it has been deleted ! However the order of the remaining records after they have been requeried.
    The AbsolutePosition should be remembered before it is deleted.

    I assume reference to macro programming was to previous post.

    MTB

  10. #10
    Join Date
    Aug 2003
    Posts
    68

    Sps

    Hey, sounds somewhat like what I'm trying to do but for a different purpose.
    I'm stuck also. I'm setting up a wizard like program that allows people to do content extraction out of a database. They answer questions on unbound forms step by step until the extraction. Problem is I want to be able to save the profile they set up with the extraction selections(SQL). I'm not sure how to interact with the DB from form to form and then either save the profile or discard the record. Should I create the record in the Wizard Profile Table right from the first form or save the information somewhere until the last form and then save or discard. Any help you can give would be great.
    jm
    jm

  11. #11
    Join Date
    Aug 2004
    Location
    Cary, NC
    Posts
    264
    Quote Originally Posted by jmayo
    Hey, sounds somewhat like what I'm trying to do but for a different purpose.
    I'm stuck also. I'm setting up a wizard like program that allows people to do content extraction out of a database. They answer questions on unbound forms step by step until the extraction. Problem is I want to be able to save the profile they set up with the extraction selections(SQL). I'm not sure how to interact with the DB from form to form and then either save the profile or discard the record. Should I create the record in the Wizard Profile Table right from the first form or save the information somewhere until the last form and then save or discard. Any help you can give would be great.
    jm
    Sounds different enough that you might be better off starting a new thread and get some clean advice

  12. #12
    Join Date
    Aug 2003
    Posts
    68

    Thanks SPS

    I did start a new thread. Just needed help badly..
    Thx
    jm

Posting Permissions

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