Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Display record by selecting a subform Item (2003)

    Hi! having some trouble trying to select an item from a datasheet view subform (not linked to the master form) and display the selected item in the master form fields. I tried using the code to select an item from a listbox but it does not work. Here is a copy of the database. Any help.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Display record by selecting a subform Item (2003)

    Can you explain in more detail what you want to accomplish? Which field or fields do you want to display and where?

  3. #3
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Display record by selecting a subform Item (2003)

    The main form will be frmTask which will have the fields from the tblTask but it will not be link with the subfrmTask.... what I want to accomplish is use those fields in frmTask at the top to be use for inserting the data into the table, then as a user clicks on the subfrom items it will display on the fields on the top for editing. The subfrmTask as another subfrmRemarks which displays all comments pertaining to the task at hand but it will not be edit from the top fields... the plan is to have the list of all task display on the subfrm, when a record is selected on the subfrm it will display just specifics info from in the top fields. I dont know if i am a little clear, does that kind of make sense. I was trying to use the following to code to get the display of info into the top fields in subfrmTask


    Private Sub Form_Current()
    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmTask"
    stLinkCriteria = "[TaskID]=" & "'" & Me![TaskID] & "'"

    If acCmdSelectRecord Then

    DoCmd.FindRecord stDocName, stLinkCriteria

    End If

    End Sub


    if acCmdSelectRecord Then
    Cmd.FindRecord stDocName, stLinkCriteria
    End If

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Display record by selecting a subform Item (2003)

    It doesn't make sense - the main form and subform are both bound to tblTask, and they are not linked. Why use a subform at all? You only need the main form.

  5. #5
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Display record by selecting a subform Item (2003)

    The information is more user friendly to edit on the top fields and add new info in the main form, while on the subform is the one the have all the records and the comments to be inserted for any task by collapsing the record

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Display record by selecting a subform Item (2003)

    You could use this:

    Private Sub Form_Current()
    Dim rst As DAO.Recordset
    If Not IsNull(Me.TaskID) Then
    Set rst = Me.Parent.RecordsetClone
    rst.FindFirst "TaskID = " & Me.TaskID
    If Not rst.NoMatch Then
    Me.Parent.Bookmark = rst.Bookmark
    End If
    End If
    End Sub

    This only makes sense if you set the Data Entry property of the main form to No. You've set it to Yes meaning that the form will not display existing records, making the above code useless.

    See the attached version.
    Attached Files Attached Files

  7. #7
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Display record by selecting a subform Item (2003)

    Thanks, Hans that is really what I was looking for. Your are the best, you always have the answer.

  8. #8
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Display record by selecting a subform Item (2003)

    Hans, sorry to bother again. I have this problem with the main form of the database, When the frmTask loads I click on the add task and it comes up with the message "you Cant go to the specified record" same with the save task button. The subfrmTask - record source is qrytask. I want the users to use the top portion of the form; the allow addition, edit or deletion properties are set to no on the subform. frmTask record- source is tblTask and all propeties mention before are allotted. I notced the only way to get the buttons to function properly is by selecting the whole row from the subfrmTask, not just a field, and then press the button and it will create a new record. How can I make this automatically or prevent that error some other way.
    Attached Files Attached Files

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Display record by selecting a subform Item (2003)

    The code in the On Current event of the main form prevents you from moving to another record:
    - This code requeries the subform.
    - Requerying the subform activates the first record in the subform.
    - This triggers the On Current event of the subform.
    - This code synchronizes the main form to the subform, i.e. it activates the first record.
    In other words, you're stuck in the first record.
    If you remove the On Current event procedure of the main form, you can move to another record, and the Add button will work.

  10. #10
    2 Star Lounger
    Join Date
    Jul 2005
    Location
    North Carolina, USA
    Posts
    195
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Re: Display record by selecting a subform Item (2003)

    Thanks Hans, that really got the job done. It works perfecto!

Posting Permissions

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