Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Feb 2009
    Posts
    22

    Unanswered: opening form to specific record.

    This seems simple but it is driving me up the wall at this point especially because it used to work! I would like to open a form to a specific record. I have two forms, form1 and form2. Both forms are based on the same table. In the table for these forms I have a record number field called sessionID that is a 7 digit number. I defined this field as a long integer. In form1 I enter some data and I would like to open form2 to the same record so I can continue entering more data.

    dim lSessionID as long

    lSessionID = ReturnSessionID() 'calls global function to retrieve sessionID

    DoCmd.OpenForm "form2", , , "[sessionID] = " & lSessionID


    Calling form 2 creates a new record in the table - it's driving me nuts. I've played with changing the data properties of both forms and all sorts of other crap I can't even begin to tell you about. I've googled and read many other posts to no avail. I'm not an experienced programmer making this all the more frustrating. Any help would be really appreciated!

    Thank you.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    First guess is that your code needs to save the current record before attempting to open the other form.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Feb 2009
    Posts
    22
    Thank you for your response. I don't ever explicitly save the record but in the past this has never caused a problem. I've tried putting the close form command both before and after the open form command and had no success. The data from form1 writes correctly to the table. I just read a bit online about explicitly saving the record at http://allenbrowne.com/ser-31.html and I'll try implementing this later today. Is that the right path or do you have any other suggestions before I do that?

    Thanks again.
    Last edited by eric2125; 04-22-09 at 11:22.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    In this situation unless you're explicitly saving the record, which you say you don't, Trekker is correct, it isn't being saved in time to be used in your criteria form the open form command. Just place

    If Me.Dirty = Then Me.Dirty = False

    in your code prior to opening the second form.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Feb 2009
    Posts
    22
    I tried that but am still running into the same problem. Here is all of the code:

    Dim lSessionID As Long
    lSessionID = ReturnSessionID()

    If Me.Dirty Then
    Me.Dirty = False
    End If
    DoCmd.Close acForm, Me.Name

    DoCmd.OpenForm "tDemo2", , , "[demo_sessionID] = " & lSessionID

    I've tried switching the order of the open and close commands... any other thoughts?

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    And ReturnSessionID()... what does that do exactly?

    Can't you just get the sessionID from the form that you're closing?

    Set a breakpoint on the openform command and when the code stops on it, point to lSessionID and make sure it's being returned.

    Is the demo_sessionID field numeric or text?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Feb 2009
    Posts
    22
    Thanks for your reply. ReturnSessionID() is a function in a module that returns a long integer that I have previously set. I've checked to make sure it is returning it correctly and it is. I've even hard coded lSessionID to a number I know already exists in the table and it still doesn't work. demo_sessionID is a long integer.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Well, there is nothing wrong with

    DoCmd.OpenForm "tDemo2", , , "[demo_sessionID] = " & lSessionID

    So, therefore, there are only a few possibilities I can see:

    1. The tDemo2 form's record source doesn't include the data you're trying to open.
    2. The data in the first form is not saved yet, so the second form cannot see the data to give it back.
    3. demo_sessionID is text and a number was expected.

    Can you upload the db?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Feb 2009
    Posts
    22
    Thanks so much for your help - I will be away from my pc through the weekend but will get back to you on Monday and send the db. Thanks again.

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    No probz. What's the bet that it will turn out to be number 4... the one that no-one is thinking of! ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Feb 2009
    Posts
    22
    Here is the DB. The open form command which has been the issue is in the form tDemo1. I've deleted some labels from the forms because of some personal data. Thanks again so much!
    Eric
    Attached Files Attached Files

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049


    Is there any other way you can make it more unnecessarily complicated? If you had just made all the questions on one form, you would need almost no code.

    There is no primary key for your demo table either, which is a bit odd.

    If this was my db, I would put the content of demo1 on one tab of a tab control and the content of demo2 on another tab of the same tab control and then my submit button would only need to have one single setfocus command.

    However, there is nothing wrong with your openform command. I suspect it is something to do with the other forms and the way you are moving data into global variables and back again, and how all that controls the flow of input, but I don't have the time to analyse the whole db atm.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  13. #13
    Join Date
    Feb 2009
    Posts
    22
    I initially tried putting this and other surveys in tab controls but the larger surveys (some of them are 2-3x the size of the one I posted) had more controls than Access would allow so I decided to split them up into individual forms. Thanks again - I appreciated you looking at this.

  14. #14
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    No problem, I hope you can track down what is going on.

    If you can hard code it to an existing value that you know is present... and it still doesn't work, can you tell me what happens when it doesn't work -- is there an error message?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  15. #15
    Join Date
    Feb 2009
    Posts
    22
    Thanks. When I hard code in the sessionID I do not get any type of error. The data from tdemo1 is inserted correctly into tblDemo. the data from tdemo2 is inserted into a new record in tblDemo, above the record that was just created.

Posting Permissions

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