Results 1 to 3 of 3
  1. #1
    Smallz is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    3

    Red face Trouble Creating a Macro that auto fills with shorttext from a query

    Hi guys!

    I've been building a database for the company that I work for, and am having some trouble getting the macro that I wrote to function properly. It seems that no matter how I adjust it, the end result is always a crash of the access application.

    What I am trying to do, is create a form that operates similar to a dispatch board (though I do not need the text boxes to be free moving as they are on a dispatch board) where each schedule item in the Schedule Table is rendered into a textbox control on the WeekView Form. (WeekView Form is not a Single Form. It is Continuous.)

    I built the macro using the macro builder, and have converted the macro to Visual Basic so that I can step in, but I am still having trouble. Any assistance would be greatly appreciated!

    The column in the Schedule Table that holds the information to be entered in the WeekView Form is named: Task Defenition and Textbox control that I am trying to render this information to is named: Tuesday Tasks

    This is the Macro that I currently have written:


    Option Compare Database



    '------------------------------------------------------------
    ' Macro2
    '
    '------------------------------------------------------------
    Function Macro2()
    On Error GoTo Macro2_Err

    DoCmd.OpenQuery "WeekView", acViewNormal, acEdit
    DoCmd.SearchForRecord acQuery, "WeekView", acFirst, "[WeekView]![Weekday]=3"
    DoCmd.FindRecord "[Task Defenition]", acEntire, True, , True, acCurrent, True
    DoCmd.BrowseTo acForm, "WeekView Form", "", "[Tuesday Tasks]=""[WeekView]![Task Defenition]""", "1", 0
    DoCmd.GoToControl "[Tuesday Tasks]"
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.RunCommand acCmdRemoveFilterSort
    Exit Function


    Macro2_Exit:
    Exit Function

    Macro2_Err:
    MsgBox Error$
    Resume Macro2_Exit

    End Function


    As previously stated, any insight into the errors found in the code above or any additions to the above code that may result in the macro running the way that it should would be greatly appreciated. I've been stuck on this for a couple of days now to no avail and am seeking the insight of those more advanced than I to get this database completed and turned in to the owner of my company. Thank you!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    instead of 'searching' for the records, can you not just filter all records that are tuesday and see them all?
    while in the continuous form that has the DayOfWeek (DoW), user picks TUES:

    Code:
    sub cboDoW_Afterupdate()
    If IsNull(cboDoW) Then
      Me.FilterOn = False
    Else
      Me.Filter = "[DoW]=" & cboDoW 
      Me.FilterOn = True
    End If
    end sub

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Code:
    Option Compare Database
    Option Explicit   '<<  MISSING - should be in EVERY code module
    
    '------------------------------------------------------------
    ' Macro2
    '
    '------------------------------------------------------------
    Function Macro2()
        On Error GoTo Macro2_Err
    
        DoCmd.OpenQuery "WeekView", acViewNormal, acEdit
    
        '       DoCmd.SearchForRecord acQuery, "WeekView", acFirst, "[WeekView]![Weekday]=3"   '<<-- ERROR
        DoCmd.SearchForRecord acDataQuery, "WeekView", acFirst, "[Weekday]=3"
    
        DoCmd.FindRecord "[Task Defenition]", acEntire, True, , True, acCurrent, True
    
        '      DoCmd.BrowseTo acForm, "WeekView Form", "", "[Tuesday Tasks]=""[WeekView]![Task Defenition]""", "1", 0   '<<-- ERROR
        DoCmd.BrowseTo acBrowseToForm, "WeekView Form", "", "[Tuesday Tasks]= '" & [WeekView]![Task Defenition] &"'", , acFormAdd
    
        DoCmd.GoToControl "[Tuesday Tasks]"
        DoCmd.RunCommand acCmdSaveRecord    'nothing has changed, don't know why this command is here
        DoCmd.RunCommand acCmdRemoveFilterSort
        '   Exit Function  '<-not needed - the next command following this line is the Exit Function command.
    
    
    Macro2_Exit:
        Exit Function
    
    Macro2_Err:
        '     MsgBox Error$  
        MsgBox Err.Description       
        Resume Macro2_Exit
    
    End Function
    Well, you can see the errors.
    In the line " DoCmd.SearchForRecord",
    the argument "acQuery" should be "acDataQuery"

    In the line " DoCmd.BrowseTo",
    the argument "acForm" should be "acBrowseToForm"
    for the WHERE argument, you need to concatenate the value to the field name.
    you have "1" for the "PAGE" argument. But this argument is Web only!

    Also, the BROWSETO command loads an object into a sub form control... but the path is not specified


    I would have used a sub - you are not returning a value.....


    Don't understand what you are trying to do with this function.

    You open a query
    -You search for a record (DoCmd.SearchForRecord)
    -Then you find a record (DoCmd.FindRecord)
    -Next, DoCmd.BrowseTo (try to open an object in a sub form control)
    -You go to a control
    -You save the record (apparently without adding/editing the record)
    -You remove a filter/sort
    (But you never close the query.....)
    Then end the function.........

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Trouble creating a query with all the information I need
    By Nanuaraq in forum Database Design
    Replies: 2
    Last Post: 03-24-2017, 04:36 AM
  2. Information from one form auto fills another
    By Boatsmulcahy in forum Forms
    Replies: 2
    Last Post: 07-18-2016, 08:34 AM
  3. Replies: 3
    Last Post: 06-05-2014, 11:44 AM
  4. Help in creating an automated macro/query
    By rexer231 in forum Macros
    Replies: 1
    Last Post: 06-02-2014, 10:05 AM
  5. Replies: 1
    Last Post: 05-23-2013, 10:00 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums