Vba code to insert dates to table not working (1 Viewer)

[Manos39]

Hi,
as i am unfamiliar with VBA i need help with code on this purpose
i 've designed a form with a button to insert records in a table which has a date column.
When run from my query it is doing the inserts as expected (inserts as many dates in rows as month dates excist in calendar because the number of records should be equal to number of days in selected month) with SQL:

INSERT INTO dutyrecordstbl ( YpiresiaDate )
SELECT DateSerial([Forms]![createschedulefrm]![ZYEAR],[Forms]![createschedulefrm]![ZMONTH],[dd]) AS IDate
FROM DayOfMonth
WHERE (((Month(DateSerial([Forms]![createschedulefrm]![ZYEAR],[Forms]![createschedulefrm]![ZMONTH],[dd])))=[Forms]![createschedulefrm]![ZMONTH]))
GROUP BY DateSerial([Forms]![createschedulefrm]![ZYEAR],[Forms]![createschedulefrm]![ZMONTH],[dd]);

Command in form on click has:

Private Sub cboDates_Click()

  CurrentDb.Execute "INSERT INTO dutyrecordstbl ( YpiresiaDate ) " & _
" SELECT DateSerial([Forms]![createschedulefrm]![ZYEAR],[Forms]![createschedulefrm]![ZMONTH],[dd]) AS IDate " & _
" FROM DayOfMonth " & _
" WHERE (((Month(DateSerial([Forms]![createschedulefrm]![ZYEAR],[Forms]![createschedulefrm]![ZMONTH],[dd])))=[Forms]![createschedulefrm]![ZMONTH])) " & _
" GROUP BY DateSerial([Forms]![createschedulefrm]![ZYEAR],[Forms]![createschedulefrm]![ZMONTH],[dd]);"


End Sub

in form and after click it returns " Runtime Error '3061'
Too few parameters, Expected 2

I dont know what to do next ..

 

[arnelgp]

what are the records of DayOfMonth table?
1 to 31 days?

if so:

 CurrentDb.Execute "INSERT INTO dutyrecordstbl ( YpiresiaDate ) " & _
" SELECT DateSerial([Forms]![createschedulefrm]![ZYEAR],[Forms]![createschedulefrm]![ZMONTH],[dd]) AS IDate " & _
" FROM DayOfMonth " & _
" WHERE [dd] <= " & Day(DateSerial([Forms]![createschedulefrm]![ZYEAR],[Forms]![createschedulefrm]![ZMONTH]+1,0))

 

[Manos39]

Yes they are.

 

[Manos39]

Same problem arnelgp
too few parameters

 

[Manos39]

cant find the field |1 in expression
Arnel in touch later have to pick up kid

 

[Gasman]

Put it all into a string variable and debug.print, then if you cannot see the problem, post back the output here.

 

[arnelgp]

ok, create another query

(qryDateToAdd):

SELECT DateSerial([Forms]![createschedulefrm]![ZYear],[Forms]![createschedulefrm]![ZMonth],[dd]) AS Expr1
FROM DayOfMonth
WHERE (((DateSerial([Forms]![createschedulefrm]![ZYear],[Forms]![createschedulefrm]![ZMonth],[dd]))<=DateSerial([Forms]![createschedulefrm]![ZYear],[Forms]![createschedulefrm]![ZMonth]+1,0)));

Now, Relace your code with this:

Currentdb.Execute "INSERT INTO dutyrecordstbl ( YpiresiaDate ) " & _
"SELECT qryDateToAdd.Expr1 FROM qryDateToAdd;"

 

[Manos39]

hello arnelgp same again
too few parameters

 

[Pat Hartman]

The Insert query has TWO formats.
A. with a Select clause - used to select data from a table or a query and append it
B. with a Value clause - used to append literal values provided in the query

1. You are using the first format but you are not selecting any columns from the table/query
2. The number of columns in the Select/Value is different from the number in the Insert part

I can't even begin to guess what you actually want to do so you'll have to tell us. But, I'll ask the obvious question - why are you not using a bound form?

 

[arnelgp]

you already made query qryDateToAdd, now
create another query, qryAppendDates:

INSERT INTO dutyrecordstbl ( YpiresiaDate )
SELECT qryDateToAdd.Expr1
FROM qryDateToAdd;


now on the your code open this query:

DoCmd.OpenQuery "qryAppendDates"


see this demo.

 

[Galaxiom]

The scope of Currentdb.Execute does not include the Application so those references to the Form are interpreted as parameters.
You need to concatenate the values from the form controls into the SQL command.

DoCmd.OpenQuery understands the application references

 

[Manos39]

Thank you i shall try your solutions. To not be misadrestood in my first post first code is sql from an insert query witch without need of a select query produces the result.
All i wanted was code on click of a button to update the records with the parameters from month and year based on that query. It is difficult for one to refrase that in vba and i used code from a useful db called sql2vba still it didnt succeed. That is what i asked because its a convinient way to just select year and month then click and you have your dates. Also avoid in code any warnings and also a line in start to delete previous records since there us no need to keep the data
This is the sql of append query which i want in vba to do tge job

INSERT INTO dutyrecordstbl ( YpiresiaDate )
SELECT DateSerial([Forms]![createschedulefrm]![ZYEAR],[Forms]![createschedulefrm]![ZMONTH],[dd]) AS IDate
FROM DayOfMonth
WHERE (((Month(DateSerial([Forms]![createschedulefrm]![ZYEAR],[Forms]![createschedulefrm]![ZMONTH],[dd])))=[Forms]![createschedulefrm]![ZMONTH]))
GROUP BY DateSerial([Forms]![createschedulefrm]![ZYEAR],[Forms]![createschedulefrm]![ZMONTH],[dd]);

 

[ebs17]

Your query contains form references. The Execute method passes the query directly to Jet/ACE. The database machine knows no VBA variables and no access objects. Therefore you get the query for the parameters. There are several ways to solve:

1) DoCmd.OpenQuery does a little more than the Execute. As a method of Access, it can also internally resolve the form references and pass an executable query to the database engine.

2) Use Eval. Replace [Forms]![createschedulefrm]![ZYEAR] with Eval([Forms]![createschedulefrm]![ZYEAR]) This determines the value of the text field and transfers it to the database engine.

3) Use of a public function instead of the form reference, see https://www.donkarl.com?FAQ3.15

4) You can compose the query using VBA and thus also resolve values from text fields and use them in the query.

5) One can use proper parameter queries.