Using variable in table insert from loop

[ptenhet]

Trying to write day by day for a month's period from the counter in a loop as the day. When I run it, I get a pop up asking to enter the parameter cnt. How do I use that variable in the insert string?

Thanks
pt

Private Sub AppendRecs_Click()

Dim cnt As Integer
Dim sqlstrg As String
Dim check As Integer
Dim db As DAO.Database

Set db = CurrentDb
check = MsgBox("Do you wish to create the month?", vbYesNo, "Continue")

If check = vbYes Then
If DCount("Month", "test", "[Month]=" & Me.Fmonth & " And [Year]" = Me.Fyear) = 0 Then
cnt = 1
Do While cnt <= Me.MonthDays
DoCmd.SetWarnings False
sqlstrg = "INSERT INTO test ( [Month], [Day], Phase ) SELECT [forms]![CreateMonth]![Fmonth] AS NewMonth, [cnt] as NewDay, [forms]![CreateMonth]![Fphase] AS NewYear"
DoCmd.RunSQL sqlstrg
cnt = cnt + 1
Loop
Else: MsgBox "Month Already Exists! Try Different Month or Exit."
Me.Fdate = ""
Me.Fmonth = ""
Me.Fyear = ""
Me.MonthDays = ""
Me.Fphase = ""
Me.Fdate.SetFocus
End If
Else
Forms!Form1!Fday.SetFocus
End If

End Sub

 

[pbaldy]

You have to concatenate the value into the string:

"...NewMonth, " & cnt & " as NewDay..."

 

[MarkK]

You need to evaluate the variable out side the SQL string, then concatenate it into the string...

"INSERT INTO test " & _
  "( [Month], [Day], Phase ) " & _
"VALUES " & _
  "( [forms]![CreateMonth]![Fmonth], [COLOR="Red"]" & cnt & "[/COLOR], [forms]![CreateMonth]![Fphase]"

Really, it's a little bit of magic that those form references work. And you don't need a SELECT statement since you are not drawing the values from a table, and they don't need field names.
Cheers,

 

[MarkK]

Yeah. What Paul said.

 

[ptenhet]

Worked perfect, thank you. Having hard time adjusting from SQL...

 

[pbaldy]

Happy to help.