I am trying to develop a form to enter rainfall data into a table. I would like to specify the start and end dates, and site for which i want to enter the data and press a command button to open the records if they are present (for editing) or generate records if none are present for those dates and site. I do not know much about VB coding but found some script which works up to a point - it generates the records for date in the table but i cannot get the form to display the entries in the recordset. (it is falling over at: Me.f_WQ_Rainfall.Form.Recordset = sSQL)
I am also not sure how to assign the site (selected from a combo box on the form) to the records which are generated.
My table (named (t_WQ_Rainfall) has the following fields: ID, DataDate, Site, Rainfall1, Rainfall2
My form is named f_WQ_Rainfall
The code i am using is as follows:
Private Sub cmdGenRecords_Click()
Dim rs As DAO.Recordset
Dim sSQL As String
Dim sSDate As String
Dim sEDate As String
Dim sSite As String
sSDate = "#" & Format(Me.txtStartDate, "yyyy/mm/dd") & "#"
sEDate = "#" & Format(Me.txtEndDate, "yyyy/mm/dd") & "#"
sSite = "#" & Format(Me.Site) & "#"
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.f_WQ_Rainfall.Form.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
Me.Requery
End Sub