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

    vba code go to record or rs

    I have a database where my supervisors are really concerned about data being inadvertently overwritten by careless data entry. I am trying to write in some code to prevent this. in a form called Weekly_Report it opens to a new form every time and moves to the control of WE_End_Date_Cmbo which is a date picker combo list. The user will input the date ending date (a Friday) and based on this selection i want it to do 2 things. First to check to see if this week has already been entered and if it has to go to that records so that the new data can be edited and if it has not check this date to make sure that it is a Friday date and continue on with this form. I have the following code written but I am having trouble with the code for navigating to the correct record if it is already existing:



    Private Sub Wk_End_Date_Cmbo_AfterUpdate()


    Me.Wk_End_Date_Cmbo_Relay_Txt.Value = Me.Wk_End_Date_Cmbo.Value
    Dim UniqueIDCount As String
    Dim DateYr As String

    UniqueIDCount = DCount("Wk_End_Date", "Weekly_Report", "Wk_End_Date = Wk_End_Date_Cmbo_Relay_Txt")
    If UniqueIDCount > 0 Then
    Me.Wk_End_Date.Value = ""
    Dim varX As Variant
    varX = (DLookup("Weekly_Report_ID", "Weekly_Report", "Wk_Number = '" & Me.Wk_Number & "'"))
    DoCmd.GoToRecord acDataForm, "Weekly_Report", acGoTo = varX
    DateTemp = Me.Wk_End_Date_Cmbo_Relay_Txt.Value
    DateYr = Year(Me.Wk_End_Date_Cmbo_Relay_Txt.Value)
    Me.WE_Year.Value = DateYr

    Me.Wk_Number.Value = DateYr & "-" & DatePart("ww", Me.Wk_End_Date_Cmbo.Value, vbSaturday, vbFirstFourDays)
    WkNumber = Me.Wk_Number.Value



    Else
    If Weekday(DateTemp) <> 6 Then
    MsgBox ("Please select a Friday")
    Else
    DoCmd.GoToRecord acDataForm, "Weekly_Report", acNewRec
    Me.Wk_End_Date.Value = DateMo & "/" & DateDy & "/" & DateYr
    End If
    End If
    End Sub

    Also I am not sure if I need to do the else go to new record if it is already set to a new record. However if they are entering/changing data from another week and enter a new date I want it to go to a new record and not overwrite.

    C.

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I don't have time to help you right now but strongly recommend you repost your code and use the code tags (highlight the code and click the # key) to preserve the indentation. At the moment your code is very difficult to read

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Please try:
    Code:
    Dim varX As Variant,rs as Dao.Recordset, lngID as long
    set rs=Me.recordsetclone
    lngID = (DLookup("Weekly_Report_ID", "Weekly_Report", "Wk_Number = '" & Me.Wk_Number & "'"))
    rs.FindFirst "[Weekly_Report_ID]=" & lngID
    varX =rs.Bookmark
    Me.bookmark=varX
    Set rs=nothing
    'DoCmd.GoToRecord acDataForm, "Weekly_Report", acGoTo = varX
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  4. #4
    CurtisC is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    11
    you are great thank you. worked like a charm.

  5. #5
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Glad to hear, good luck with your project!
    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

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

Similar Threads

  1. Replies: 7
    Last Post: 02-11-2020, 01:43 PM
  2. Replies: 5
    Last Post: 02-16-2019, 12:49 PM
  3. Replies: 5
    Last Post: 03-29-2017, 11:22 AM
  4. Replies: 2
    Last Post: 06-28-2013, 12:58 PM
  5. Replies: 5
    Last Post: 06-16-2013, 05:25 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