Discrepancy in a Microsoft database form

[wire_jp]

Hello,

I have attached a linked Excel spreadsheet (which was linked to Google Sheet connected to a Google Forms Online Survey) called tbLFormResponses where its data is imported into the Microsoft Access database called BCTSurvey Database. The tblFormResponses form was populated with new data from the online Google Forms Online Survey. So the next steps which I took to update the Microsoft Access database, was to click the "Append to Forms" command button in the frmMainMenu form and append all of the forms. Afterwards, the form called frmBCTMemorabiliaSearchByName showed a field for BARP member no. 5067, time stamp: 1/18/2024 6:20:46 PM with data. However, if I checked the tblFormResponses for this BARP member no. 507 and the timestamp: 1/18/2024 6:20:46 PM, there was no data in the column called "Sale of BCT Memorabilia_Please select all areas which apply:-" I cannot figure out why this issue has occurred.

I am checking with you if I should append the forms in the Microsoft Access database each time new data is populated in the tblFormResponses form of Microsoft Access database in order to update the Microsoft Access database or once new data populates the tblFormResponses form should the existing database be cleared from the Microsoft data and then append the database to update the database?

OneDrive link with the shared files for the Excel spreadsheet and the Microsoft database is found here: - https://1drv.ms/f/s!AtOdPG-IN8CYgcV-YYl ... Q?e=4votI5

[HansV]

I think the problem is tblBCTMemorabiliaResponses - it records responses without the time stamp, so you don't know which survey for a BARP member the response belongs to.

[wire_jp]

Hi Hans,

Thank you for the explanation. I have updated the Responses tables, the CrossTab queries to include a timestamp field. Afterwards, I deleted the data in the Responses tables and then I ran the append queries. In doing so, the Respond tables have updated to show the timestamp data and the search forms are showing the correct results now. Thank you again.

[HansV]

Well done!

[wire_jp]

Hi Hans,

I am experiencing a similar issue with the tblEntertainmentResponses table, tblAnyOtherVolAreasResponses table and the tblAnyOtherIdeasResponses table, where the data in the tables is not updating correctly. I added a Timestamp field to each table, but I was unable to update the modules which you had created to include a timestamp field:-

. modPopulateOtherOptions
. modPopulateOtherVolAreasOptions
. modPopulateAnyOtherIdeasOptions

The OneDrive link is attached: - https://1drv.ms/f/s!AtOdPG-IN8CYgcV-YYl ... Q?e=crZ100

[HansV]

modPopulateOtherOptions populates tblOtherEntertainmentOptions. That table does not have a TimeStamp field. You have to add it, and you also have to remove the primary key, for there can now be multiple records with the same BARP number / Survey ID.
The code then looks like this:

Code: Select all

Sub PopulateOtherOptions()
    Dim dbs As DAO.Database
    Dim rstF As DAO.Recordset
    Dim rstE As DAO.Recordset
    Dim rstO As DAO.Recordset
    Dim strSQL As String
    Dim strVal As String
    Dim arrParts() As String
    Dim varPart As Variant
    Dim strNew As String
    Set dbs = CurrentDb
    strSQL = "DELETE * FROM tblOtherEntertainmentOptions"
    dbs.Execute strSQL, dbFailOnError
    strSQL = "SELECT TimeStamp, [BARP member number], " & _
        "[Entertainment Activities_Please select all areas which apply: -] FROM tblFormResponses"
    Set rstF = dbs.OpenRecordset(strSQL, dbOpenForwardOnly)
    Set rstE = dbs.OpenRecordset("tblEntertainmentOptions", dbOpenDynaset)
    Set rstO = dbs.OpenRecordset("tblOtherEntertainmentOptions", dbOpenDynaset)
    Do While Not rstF.EOF
        strVal = Nz(rstF.Fields("Entertainment Activities_Please select all areas which apply: -"))
        strNew = ""
        arrParts = Split(strVal, ", ")
        For Each varPart In arrParts
            rstE.FindFirst "EntertainmentOption='" & varPart & "'"
            If rstE.NoMatch Then
                strNew = strNew & ", " & varPart
            End If
        Next varPart
        If strNew <> "" Then
            rstO.AddNew
            rstO!TimeStamp = rstF.Fields("TimeStamp")
            rstO!EntertainmentSurveyID = rstF.Fields("BARP member number")
            rstO!OtherEntertainmentOptions = Mid(strNew, 3)
            rstO.Update
        End If
        rstF.MoveNext
    Loop
    rstF.Close
    rstE.Close
    rstO.Close
End Sub

modPopulateOtherVolAreaOptions is similar:

Code: Select all

Sub modPopulateOtherVolAreasOptions()
    Dim dbs As DAO.Database
    Dim rstF As DAO.Recordset
    Dim rstE As DAO.Recordset
    Dim rstO As DAO.Recordset
    Dim strSQL As String
    Dim strVal As String
    Dim arrParts() As String
    Dim varPart As Variant
    Dim strNew As String
    Set dbs = CurrentDb
    strSQL = "DELETE * FROM tblAnyOtherVolAreasResponses"
    dbs.Execute strSQL, dbFailOnError
    strSQL = "SELECT TimeStamp, [BARP member number], " & _
        "[Any other areas in which you are willing to volunteer# Please sp] FROM tblFormResponses"
    Set rstF = dbs.OpenRecordset(strSQL, dbOpenForwardOnly)
    Set rstE = dbs.OpenRecordset("tblAnyOtherVolAreasOptions", dbOpenDynaset)
    Set rstO = dbs.OpenRecordset("tblAnyOtherVolAreasResponses", dbOpenDynaset)
    Do While Not rstF.EOF
        strVal = Nz(rstF.Fields("Any other areas in which you are willing to volunteer# Please sp"))
        strNew = ""
        arrParts = Split(strVal, ", ")
        For Each varPart In arrParts
            rstE.FindFirst "AnyOtherVolAreasOption='" & varPart & "'"
            If rstE.NoMatch Then
                strNew = strNew & ", " & varPart
            End If
        Next varPart
        If strNew <> "" Then
            rstO.AddNew
            rstO!TimeStamp = rstF!TimeStamp
            rstO!AnyOtherVolAreasSurveyID = rstF.Fields("BARP member number")
            rstO!AnyOtherVolAreasOption = Mid(strNew, 3)
            rstO.Update
        End If
        rstF.MoveNext
    Loop
    rstF.Close
    rstE.Close
    rstO.Close
End Sub

It's the same for modPopulateAbyOtherIdeasOptions:

Code: Select all

Sub modPopulateAnyOtherIdeasOptions()
    Dim dbs As DAO.Database
    Dim rstF As DAO.Recordset
    Dim rstE As DAO.Recordset
    Dim rstO As DAO.Recordset
    Dim strSQL As String
    Dim strVal As String
    Dim arrParts() As String
    Dim varPart As Variant
    Dim strNew As String
    Set dbs = CurrentDb
    strSQL = "DELETE * FROM tblAnyOtherIdeasResponses"
    dbs.Execute strSQL, dbFailOnError
    strSQL = "SELECT TimeStamp, [BARP member number], " & _
        "[Please share any other ideas which you may have to assist the BC] FROM tblFormResponses"
    Set rstF = dbs.OpenRecordset(strSQL, dbOpenForwardOnly)
    Set rstE = dbs.OpenRecordset("tblAnyOtherIdeasOptions", dbOpenDynaset)
    Set rstO = dbs.OpenRecordset("tblAnyOtherIdeasResponses", dbOpenDynaset)
    Do While Not rstF.EOF
        strVal = Nz(rstF.Fields("Please share any other ideas which you may have to assist the BC"))
        strNew = ""
        arrParts = Split(strVal, ", ")
        For Each varPart In arrParts
            rstE.FindFirst "AnyOtherIdeasOption='" & varPart & "'"
            If rstE.NoMatch Then
                strNew = strNew & ", " & varPart
            End If
        Next varPart
        If strNew <> "" Then
            rstO.AddNew
            rstO!TimeStamp = rstF!TimeStamp
            rstO!AnyOtherIdeasSurveyID = rstF.Fields("BARP member number")
            rstO!AnyOtherIdeasOptionID = Mid(strNew, 3)
            rstO.Update
        End If
        rstF.MoveNext
    Loop
    rstF.Close
    rstE.Close
    rstO.Close
End Sub

[wire_jp]

Hi Hans,

Thank you for the information.