Click and Add new order year to all customers?

[Dustin]

I have a main form with customer info. A subform below has a yearly record of
the progression of the order.

I would like to click a button and instantly add a new year record for each
customer.

I currently have it set up that the user has to look up each customer
individually and go down to the subform click the combo and select order year.

How can I do this with one click instead?

Thanks Dustin

 

[Allen Browne]

Execute an append query statement to add the records.

This example adds a record to tblOrderInfo for each customer in your
tblCustomer table, using the year of today's date:

Dim strSql As String
strSql = "SELECT INTO tblOrderInfo (CustomerID, TheYear) " & _
"SELECT CustomerID, Year(Date()) AS TheYear FROM tblCustomer;"
dbEngine(0)(0).Exeucute strSql, dbFailOnError

 

[BruceM]

INSERT INTO, isn't it?

Execute an append query statement to add the records.

This example adds a record to tblOrderInfo for each customer in your
tblCustomer table, using the year of today's date:

Dim strSql As String
strSql = "SELECT INTO tblOrderInfo (CustomerID, TheYear) " & _
"SELECT CustomerID, Year(Date()) AS TheYear FROM tblCustomer;"
dbEngine(0)(0).Exeucute strSql, dbFailOnError

 

[Dustin]

Thanks for the response!

This would work accept that the users orders are made in November and
December for the following year. So how would I be able to enter the year
needed?

Thanks,

Dustin

 

[Allen Browne]

Concatenate the year into the string.
For example, if it's in a text box named txtWotYear:

strSql = "INSERT INTO tblOrderInfo (CustomerID, TheYear) " & _
"SELECT CustomerID, " & Me.txtWotYear & " AS TheYear FROM tblCustomer;"

Bruce is right: it should have been INSERT INTO.

 

[Dustin]

I am a novice.

So bear with me. I am confused on where to put formula. Is there a form
bound to an append query as well?

Thank you very much

 

[Allen Browne]

Your original post said:
I would like to click a button and instantly add a new year record
for each customer.

1. Set the On Click property of this command button to:
[Event Procedure]

2. Click the Build button (...) beside this.
Access opens the code window.

3. Paste the code between the "Private Sub..." and "End Sub" lines.

The code then executes when you click the button.
There is no other form bound to the append query.