Auto add dates in a range to a table

[MandyHead1]

Hi there,
I am trying to create a forecasting database and I would like a button on my form (frmProjects) to Automatically fill in several records in a table (tblProjForecast) with dates in a date range specified in the form. The form (based on table 'tbl Projects') has the following fields:

[*]ProjectStatus
[*]ProjectValue
[*]EstStartDate
[*]EstFinishDate
[*]EstNoMonths (Calculated( EstFinishDate - EstStartDate) / 30

and the target Table, tblProjForecast has a few fields, but i only want the field FcstDate(s) to be entered.

I also have a table called tblMonths with a field 'FcstMonth' where I have manually entered months in the calendar year ie 1/8/18, 1/9/18 etc.

Example: EstStartDate = 1/8/18, EstFinishDate = 1/10/18.
I want the button to add records for 1/8/18, 1/9/18, 1/10/18

Please note, I am new to VBA so please be patient with me. I have looked up similar issues and came across one that I tried to adapt for my own circumstance. It worked until it got to the INSERT INTO bit, where it said 'Syntax error'.

Option Compare Database

Private Sub btnAddDates_Click()
Dim strSQL As String

'Validate fields are not Null
If IsNull(Me.EstStartDate) Then
      MsgBox "EstStartDate is Missing", vbOKOnly + vbCritical, "Error"
      Me.EstStartDate.SetFocus
ElseIf IsNull(Me.EstFinishDate) Then
      MsgBox "EstFinishDate is Missing", vbOKOnly + vbCritical, "Error"
      Me.EstFinishDate.SetFocus
ElseIf IsNull(Me.ProjectValue) Then
      MsgBox "ProjectValue is Missing", vbOKOnly + vbCritical, "Error"
      Me.ProjectValue.SetFocus

Else
      'build the SQL Statement
      strSQL = "INSERT INTO tblProjForecast ( ForecastDate)"
   
        
      strSQL = strSQL & " FROM Dates WHERE (((tblMonths.FcstMonth)>[Forms]![tblProjForecast]![EstStartDate] And (tblMonths.FcstMonth)<=[Forms]![tblProjForecast]![EstFinishDate]))"
      'Run the SQL Statement
      DoCmd.RunSQL (strSQL)
      
End If
Me.Requery
End Sub

If anyone can advise what i've done wrong, it will be greatly appreciated.

thanks,
MandyHead1

 

[CJ_London]

I don't see any reason for a syntax error on that line - it is just creating a string at that point, same for the next line.

However your total sql string isn't valid. You are extracting values from a table called Dates and then use a criteria based on a table called tblMonths. The code is also missing a SELECT statement

Always better to put debug.print strSQL before running the code, you can then see what you have actually created in the immediate window, then copy/paste to a new query to confirm it returns the expected result or get a better handle on any errors

 

[isladogs]

One more thing to add to the previous comments about various flaws in your strSql statement

In your date filter you should use >= ..... And <= ...
Or, and in my view easier, use Between .... And ......

 

[arnelgp]

i thnink you need a loop

dim i as date
for i = eststartDate to estFinishdate
strSQL = "INSERT INTO tblProjForecast ( ForecastDate) select #" format(i, "mm/dd/yyyy") & "#"
docmd.runsql strSql
next

 

[MandyHead1]

So I have been working on this all afternoon and now it's time to go home. I have mainly worked on CJ_London suggestions, but have read the other ones and will play with them tomorrow. I am starting to wonder though, if am on the right track for getting my button to create multiple date records.
The error message I now get is "Number of query values and destination fields are not the same".
I just wanted to give you all an update because i really appreciate your help. I have some ideas and will post my code tomorrow after I've looked at it.

Mandy

 

[isladogs]

The error message I now get is "Number of query values and destination fields are not the same"

You need the same number of field in the INSERT and SELECT parts of your query e.g.

INSERT INTO TableName (A, B, C, D )
SELECT A, B, C, D FROM TableName
WHERE some criteria is true

 

[CJ_London]

I agree with Ridders.

You can always write the query in the query design window, switch to sql view and copy/paste the sql to vba, then modify as required

I am starting to wonder though, if am on the right track for getting my button to create multiple date records.

there can be valid reasons for doing it - depends what the reasons are

 

[The_Doc_Man]

I'm going to second CJ's suggestion. Use the design window, switch to SQL View, copy & paste. I've done that a hundred times at least. It's the easiest way to go and, other than a slight tendency to add more parentheses than technically required, always comes up syntactically clean.

 

[MandyHead1]

Thanks so much all of you. I have amended my code as suggested by Ridders but it is still giving the same error. This is what it looks like now.

Option Compare Database

Private Sub btnAddDates_Click()
Dim strSQL As String

Debug.Print strSQL

'Validate fields are not Null
If IsNull(Me.EstStartDate) Then
      MsgBox "EstStartDate is Missing", vbOKOnly + vbCritical, "Error"
      Me.EstStartDate.SetFocus
ElseIf IsNull(Me.EstFinishDate) Then
      MsgBox "EstFinishDate is Missing", vbOKOnly + vbCritical, "Error"
      Me.EstFinishDate.SetFocus
ElseIf IsNull(Me.ProjectValue) Then
      MsgBox "ProjectValue is Missing", vbOKOnly + vbCritical, "Error"
      Me.ProjectValue.SetFocus

Else
      'build the SQL Statement
      strSQL = "INSERT INTO tblProjForecast (EstStartDate,ID,WBS,ClientCode,ProjectValue,ProjectStatus,EstFinishDate)"
      strSQL = strSQL & "SELECT tblMonths.FcstMonth, [FORMS]![frmProjects]![ID] AS Expr1, [FORMS]![frmProjects]![WBS] AS Expr2, [FORMS]![frmProjects]![ClientCode] AS Expr3, [FORMS]![frmProjects]![ProjectStatus] AS Expr4, [FORMS]![frmProjects]![ProjectValue] AS Expr5, [FORMS]![frmProjects]![EstStartDate] AS Expr6, FORMS]![frmProjects]![EstFinishDate] AS Expr7"
   
        
      strSQL = strSQL & " FROM tblMonths WHERE (((tblMonths.FcstMonth)>[Forms]![frmProjects]![EstStartDate] And (tblMonths.FcstMonth)<=[Forms]![frmProjects]![EstFinishDate]))"
      'Run the SQL Statement
      DoCmd.RunSQL (strSQL)
      
End If
Me.Requery
End Sub

I have to laugh though, CJ's suggestions does sound good - so the funny thing is, i didn't realise my code is creating a query - is that what it's doing? I'd love to go in and create a query then amend accordingly, but the reason why I'm doing code is because I couldn't figure out how to do the query (how do i make a query add records?)!! Sorry, I'm really such a vba novice or lower, I googled the original code from another user of this forum and then adapted it, it was the only way I could find to do what I wanted. I am now going to do a bit of reading and learning on VBA, I think it's time.

 

[isladogs]

You have 7 fields in the INSERT INTO section and 8 fields in the SELECT part.

You also have a missing space before SELECT
Finally the WHERE part should I think be >= and not >

 

[JHB]

Also move the Debug.Print strSQL to just before the DoCmd.RunSQL (strSQL).
Then read post #2 where CJ_London explain how to use the output from Debug.Print.

 

[CJ_London]

how do i make a query add records?)!!

in the query window in design view, on the ribbon are options for the type of query you want to create - the default is Select, one of the options is Append

 

[MandyHead1]

Thanks all, you've given me some great advice. I can't believe I have more select fields than insert (or vice versa) - I counted them so mamy times!! Anywho, it's friday night, I don't want to look at the computer now, Ill check it out in morning.

 

[isladogs]

You really will find it easier to work in the query designer at this stage.
Once you have it working you can then change to SQL view, then copy and paste into the VBE.

You may also find this utility useful SQL to VBA and back again.
I created this some years ago when I still found it hard to convert queries to SQL but it can also be used the other way round.

 

[MandyHead1]

Oh my goodness, it's actually working now! I didn't take into consideration the data inserted from Months table in the INSERT bit so yes there were 8. I realise that I'm not familiar enough with VBA for this to be a realistic solution so I will now go and create an Append Query as suggest by Ridders. I did not realise that, in fact, that is what I was doing with this button. Thank's for all your help, I've learnt so much, especially that I have a LOT to learn and understand! I'm sure this won't be the last of my posts though.

 

[isladogs]

Glad you've got it working.

Not sure it was me who first suggested using an append query but as already stated, you should find it easier to start with this before (if you wish) converting to SQL.

Good luck with the next step. We'll be here if and when you need further assistance

 

[MandyHead1]

You may also find this utility useful SQL to VBA and back again.
I created this some years ago when I still found it hard to convert queries to SQL but it can also be used the other way round.

this is a very cool tool, I have created it and I think it will be very handy.