Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2008
    Posts
    28

    Unanswered: Changing form recordsource and loading "last" record

    Hey guys, I am relatively new to Access and VBA, so I'd appreciate any help. Right now I am creating a front-end for a database with quite a few tables, and I am using one main form with a bunch of sub-forms within it.

    For this sub-form, there is one table and one query. The query calls directly on the table and uses the data for calculations. In the parent form, I have an option group with "Existing part" and "new part". For "new part" the form has a recordsource from the table "tslot", and for "Existing part" its recordsource is "tshInfo" which is the query.

    Ok, so the issue I am having:

    When in "new part" mode, I have it as a data entry form. Once the user enters in a sufficent amount of data into the fields, I have a button "calcbtn" which I want them to be able to click, and have the form switch to the recordsource of tshInfo, and I want it to be on the record that they just added. This works with the code I have.

    I wanted to replicate the code I had, and have the user be able to click the button again to get back to be able to edit the data in "tslot" if they want to change it. This is what does not work. It changes the recordsource, but it will not pull up the last record.

    Here is basically the pertinent code I have:

    Private Sub calcbtn_Click()
    With Me
    If calcbtn.Caption = "Calculate..." Then
    DoCmd.Close acTable, "tslot", acSaveYes
    .RecordSource = "tshInfo"
    DoCmd.RunCommand acCmdRecordsGoToLast
    .AllowEdits = True
    .partno.Locked = True
    calcbtn.Caption = "Edit Attributes..."
    Else
    DoCmd.Close acTable, "tshInfo", acSaveYes
    .RecordSource = "tslot"
    DoCmd.RunCommand acCmdRecordsGoToLast
    .partno.Locked = False
    calcbtn.Caption = "Calculate..."
    End If
    End With
    End Sub
    Basically, the DoCmd.RunCommand method works going "up", but doesn't seem to the other way... does anyone know what I should do?

  2. #2
    Join Date
    Jun 2008
    Posts
    28
    Also, I feel I should add that I have tried other methods as well. I would like to be able to do all of this and not have another form window pop-up to the user, and I am not quite sure how to "open" the form as a subform in the main form, because with any commands i use it with it says the form is not open... so I have tried using methods such as FindRecord, SearchForRecord, and GoToRecord with no success...
    Last edited by lSTellYl; 06-03-08 at 17:41.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    My housemate reckons you just need a .Requery in there to force the save and reload of correct data.

    Very bizarre requirements in my eyes
    George
    Home | Blog

  4. #4
    Join Date
    Jun 2008
    Posts
    28
    hmm, well I wouldn't think so since this is finding info from the table, just as the query is, which apparently is updated or the first half (when the query is the recordsource) it wouldnt have worked.... i tried to use requery in the following way:


    With Me
    If calcbtn.Caption = "Calculate..." Then
    DoCmd.Close acTable, "tslot", acSaveYes
    .RecordSource = "tshInfo"
    DoCmd.RunCommand acCmdRecordsGoToLast
    .AllowEdits = True
    .partno.Locked = True
    Else
    .RecordSource = "tslot"
    .Requery
    .AllowEdits = True
    .DataEntry = True
    .partno.RowSource = "SELECT [tslot].[partno] FROM tslot;"
    .partno.ControlSource = "partno"
    DoCmd.RunCommand acCmdRecordsGoToLast
    End If
    End With
    End Sub

    I also used repaint and refresh...

    It is very odd what it is doing, as the value primary key of the data table that pops up is one that isn't even in the database at that point....

    is there a chance that the acCmdRecordsGoToLast only works for Queries?

    any more help is much appreciated, i am a little lost here
    Last edited by lSTellYl; 06-04-08 at 10:28.

Posting Permissions

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