Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    display last record in subform (2003)

    I have an form (co-scan-in) where I scan employee badges, pull emp info from table, update record and goto new record. I placed a subform (co-scan-last) that I want to display info from last record scanned while mainform is waiting for next scan.
    Main form works great but I'm missing code to display last record scanned info to subform. My attempt is below but error message tells me that "co-scan-last" is not open. As a subform, shouldn't the form open when the main is?

    DoCmd.GoToControl "text7" 'goto scan field
    DoCmd.Save , "" 'saves current scan
    DoCmd.SelectObject acForm, "co-last-scan", False **** this is failing line
    DoCmd.GoToRecord , "", acLast

    DoCmd.SelectObject acForm, "co-scan-in", False
    DoCmd.GoToRecord , "", acNext

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

    Re: display last record in subform (2003)

    In the first place, DoCmd.Save does not save the current scan. It saves the design of the currently active form. To save the current record, use

    RunCommand acCmdSaveRecord

    In the second place, DoCmd.SelectObject can only be used to select objects that are open by themselves. A subform is not open by itself, it is a part of the main form, so you can't use DoCmd.SelectObject on it. Instead, use DoCmd.GoToControl or the SetFocus method. For either of them, you need to know the name of the subform as a control on the main form. This is not necessarily the same as the name it has in the database window. To find the control name, open the main form in design view, and click once on the subform. The control name will be displayed in the Object dropdown list in the Formatting toolbar, and in the caption of the Properties window. If this name is co-last-scan, you can use

    DoCmd.GoToControl "co-last-scan"

    or

    Me.[co-last-scan].SetFocus

  3. #3
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: display last record in subform (2003)

    Last record data now gets updated to "co-last-scan" subform but focus not being set back to mainform ready to accept new-record. It is leaving last scanned info. If I manually select new record on mainform, it blanks out data in subform.

    RunCommand acCmdSaveRecord
    DoCmd.GoToRecord , "", acNew
    DoCmd.GoToControl "co-last-scan"
    DoCmd.GoToRecord , "", acLast
    Me.[Text7].SetFocus ' Scan input field of mainform
    DoCmd.GoToRecord , "", acNew

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

    Re: display last record in subform (2003)

    We will have to know (much) more about the way main form and sub form are related. Could you provide complete information, or attach a stripped down copy of the database? See <post#=401925>post 401925</post#> for instructions.

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

    Re: display last record in subform (2003)

    This is a reply to <post#=439122>post 439122</post#> which you posted in the wrong thread.

    The most serious problem was that you have linked the form and subform on the ID field, so that they are completely synchronized. You want the form and subform to display different records from the same query, so they should not be linked. There were some problems with the code too - you opened a recordset but never closed it, didn't release object memory, and put the recordset in edit mode while there was no need for it. Here is a modified version of the Text7_AfterUpdate event procedure:

    Private Sub Text7_AfterUpdate()
    Dim rst As DAO.Recordset
    Dim dbs As DAO.Database
    Dim qdf As DAO.QueryDef

    On Error GoTo no_badge

    Set dbs = CurrentDb
    Set qdf = dbs.QueryDefs("query-active-badge-badge#")
    qdf.Parameters(0) = Me.Text7
    Set rst = qdf.OpenRecordset(dbOpenDynaset)
    Me.Text9 = rst!firstname
    Me.Text11 = rst!lastname
    Me.Text13 = rst!cisco_empnum
    Me.Text17 = rst!badge_status
    Me.Text15 = rst!cisco_id
    RunCommand acCmdSaveRecord
    Me.<!t>[co-last-scan]<!/t>.SetFocus
    Me.<!t>[co-last-scan]<!/t>.Form.Requery
    RunCommand acCmdRecordsGoToLast
    Me.Text7.SetFocus
    RunCommand acCmdRecordsGoToNew
    Exit Sub

    exit_sub:
    rst.Close
    Set rst = Nothing
    Set qdf = Nothing
    Set dbs = Nothing

    no_badge:
    MsgBox "** BADGE NOT FOUND **", vbInformation
    Me.Undo
    Me.Text9.SetFocus
    Me.Text7.SetFocus
    End Sub

    Note: your code would be much easier to read and to maintain if you gave your controls meaningful names instead of Text7 etc. I have attached a stripped down version of your database with the above code.

  6. #6
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: display last record in subform (2003)

    Thanks for assistance. Code works as required. One more fix is needed. The 'no-badge' subroutine needs to go back to new record/badge# entry after clearing user message. Right now focus goes to the badge field of [co-last scan]
    Regards, Chris

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

    Re: display last record in subform (2003)

    Sorry, I don't understand why that happens. When I enter a non-existent badge#, the focus returns to the badge# text box in the main form.

  8. #8
    Star Lounger
    Join Date
    Aug 2001
    Location
    Can Jose, CA
    Posts
    55
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: display last record in subform (2003)

    I had commented out 'ME.text9.setfocus' and set the field VISIBLE=No. I just put that back in and it all works fine. I appreciate your help. Clearly, I'm not proficient at this. Your assistance and Woody's are a great asset to the success of my DBs. Have a good New Year's holiday.

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

    Re: display last record in subform (2003)

    If you wish, you can set focus to any other visible control before setting it back to Text7. The crucial point is that you must set the focus away from Text7, then back.

Posting Permissions

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