Results 1 to 3 of 3
  1. #1
    CurtisC is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    11

    form load

    I am trying to get some code at the beginning of my form load to get the user to the correct screen.


    when the form opens I want them to enter a date into a message box and then compare that date with the previously entered dates.
    if the date has been previously entered, i want it to go to that record to have it modified to prevent multiple entries for the same date. if it isn't i want to go to a new record and have that date populated as the week ending date. This is the code that I have written but it does not want to do the go to new record command if the date is not found. what am i doing wrong

    Private Sub Form_Load()


    Dim Message, Title, MyValue
    Message = "Please enter a Friday Week End Date for which to enter Data" ' Set prompt.
    Title = "Weekly Report Entry" ' Set title.
    ' Display message, title, and default value.


    MyValue = InputBox(Message, Title)

    Dim UniqueIDCount As String
    Dim DateYr As String
    Dim DateDy As String
    Dim DateMo As String

    DateDy = Day(MyValue)
    DateMo = Month(MyValue)
    DateTemp = MyValue
    DateYr = Year(MyValue)
    WkNumber = DateYr & "-" & DatePart("ww", DateTemp, vbSaturday, vbFirstFourDays)


    UniqueIDCount = DCount("Wk_Number", "tbl_Weekly_Report", "Wk_Number ='" & WkNumber & "'")
    If UniqueIDCount > 0 Then
    Dim varX As Variant, rs As Dao.Recordset, lngID As Long
    Set rs = Me.RecordsetClone
    lngID = (DLookup("Weekly_Report_ID", "tbl_Weekly_Report", "Wk_Number ='" & WkNumber & "'"))
    rs.FindFirst "[Weekly_Report_ID]=" & lngID
    varX = rs.Bookmark
    Me.Bookmark = varX
    Set rs = Nothing
    Me.Wk_End_Date_Cmbo.Value = DateMo & "/" & DateDy & "/" & DateYr
    Else
    If Weekday(DateTemp) <> 6 Then
    MsgBox ("Please select a Friday")
    Else
    DoCmd.GoToRecord acDataForm, "frm_Weekly_Report", acNewRec 'this is where i am getting the error

    Me.Wk_End_Date.Value = DateMo & "/" & DateDy & "/" & DateYr
    Me.Wk_End_Date_Cmbo.Value = DateMo & "/" & DateDy & "/" & DateYr
    Me.WE_Year.Value = DateYr
    Me.Wk_Number.Value = WkNumber
    End If
    End If
    End Sub

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    In future, please post lengthy code between CODE tags to retain indentation and readability.

    Do you get error message?

    Command works for me in Load event.

    If you want to provide db for analysis, follow instructions at bottom of my post.

    Advise to avoid InputBox() because it is difficult to validate user input. Have a start form for input of this value to a textbox and validate user input. Set a global variable or TempVar for use by other form procedures or use OpenArgs to pass value to other form.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Also make sure the form's record source allows new records and that the form's Allow Additions property is Yes.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 37
    Last Post: 10-21-2019, 12:29 PM
  2. Replies: 9
    Last Post: 05-30-2017, 04:11 PM
  3. Replies: 3
    Last Post: 04-10-2015, 10:26 PM
  4. Replies: 1
    Last Post: 11-18-2014, 12:35 PM
  5. Replies: 2
    Last Post: 08-22-2011, 09:02 PM

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