Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2012
    Posts
    9

    Unanswered: Display data in form based on recordset

    Hi,
    I am trying to enter new/or review data in a table using a form (FWQRainfall) to specify the start (StartDate) and end (EndDate) dates. If records between these start and end dates do not exist they need to be created in the table. I found some code which gets me part of the way there - it generates new records if required, but i cannot get the records to udate and display on the form based on the specified dates. I am not sure what is wrong with syntax on the highlighted line of code below, if i block this line out the new records are created in the table (t_WQ_Rainfall) would appreciate any help. Thanks

    Private Sub cmdGenRecords_Click()
    Dim rs As DAO.Recordset
    Dim sSQL As String
    Dim sSDate As String
    Dim sEDate As String

    sSDate = "#" & Format(Me.txtStartDate, "yyyy/mm/dd") & "#"
    sEDate = "#" & Format(Me.txtEndDate, "yyyy/mm/dd") & "#"
    sSQL = "SELECT * FROM t_WQ_Rainfall WHERE DataDate Between " & sSDate _
    & " AND " & sEDate

    Set rs = CurrentDb.OpenRecordset(sSQL)

    If rs.RecordCount < (Me.txtEndDate - Me.txtStartDate) Then
    AddRecords sSDate, sEDate

    End If

    Me.fWQRainfall.Recordset = sSQL


    End Sub



    Sub AddRecords(sSDate, sEDate)

    sSQL = "INSERT INTO t_WQ_Rainfall (DataDate) " _
    & "SELECT AddDate FROM " _
    & "(SELECT " & sSDate _
    & " + [counter.ID] AS AddDate " _
    & "FROM [Counter] " _
    & "WHERE " & sSDate _
    & "+ [counter.ID] Between " & sSDate _
    & " And " & sEDate & ") a " _
    & "WHERE AddDate NOT In (SELECT DataDate FROM t_WQ_Rainfall)"

    CurrentDb.Execute sSQL, dbFailOnError
    End Sub
    Last edited by Rhabdo2; 10-19-12 at 03:28.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Try using the Requery method of the Form object:
    Code:
    Sub AddRecords(sSDate, sEDate)
    
        sSQL = "INSERT INTO t_WQ_Rainfall (DataDate) " _
        & "SELECT AddDate FROM " _
        & "(SELECT " & sSDate _
        & " + [counter.ID] AS AddDate " _
        & "FROM [Counter] " _
        & "WHERE " & sSDate _
        & "+ [counter.ID] Between " & sSDate _
        & " And " & sEDate & ") a " _
        & "WHERE AddDate NOT In (SELECT DataDate FROM t_WQ_Rainfall)"
    
        CurrentDb.Execute sSQL, dbFailOnError
        Me.Requery
    
    End Sub
    Have a nice day!

  3. #3
    Join Date
    Sep 2012
    Posts
    9
    Hi thanks, but it is falling over at the following line:
    Me.fWQRainfall.Recordset = sSQL

    i am not sure what is wrong with the syntax.

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I did not notice that one. Replace:
    Code:
    Me.fWQRainfall.Recordset = sSQL
    with:
    Code:
    Me.fWQRainfall.RecordSource = sSQL
    or:
    Code:
    Me.fWQRainfall.Recordset = CurrentDb.Openrecordset(sSQL)
    Have a nice day!

  5. #5
    Join Date
    Sep 2012
    Posts
    9
    It is giving me a Compile error - Method or data member not found and highlighting the .recordset or .recordsource.

    fWQRainfall is the name of the form, do i need to reference the column in the table (DataDate) or the text box in the form (DataDate) as wll?
    thanks

Posting Permissions

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