Form to select the data from a table using multiple queries, save to another table

Hi all,
I am new to access and am finding it a bit unintuitive having worked
with SQL server in the past... And I am in a bit of a hurry because my
employer wants me to crank something out which at first seemed like a
piece of cake but I realize now I should have done a little more
research before I got started. Here is the issue:

I have a table with data relating to our pastry products. I built a
form with multiple combo boxes that are limited by category etc so that
the pastry chef can use the form to create his production schedule for
the week. This worked great and he can print the schedule. But now I
want to save the schedule as a record in another table so that next
week when he goes to do the schedule, he can see what he did the week
before.

Everything I read seems to discuss how to create a form to ENTER data
to the table it is associated with. But not how to take the data
selected in the form and to save it to another table. Any help will be
thoroughly appreciated.

Tara

re: Form to select the data from a table using multiple queries, save to another table

tarafinlay wrote:
[color=blue]
> Hi all,
> I am new to access and am finding it a bit unintuitive having worked
> with SQL server in the past... And I am in a bit of a hurry because my
> employer wants me to crank something out which at first seemed like a
> piece of cake but I realize now I should have done a little more
> research before I got started. Here is the issue:
>
> I have a table with data relating to our pastry products. I built a
> form with multiple combo boxes that are limited by category etc so that
> the pastry chef can use the form to create his production schedule for
> the week. This worked great and he can print the schedule. But now I
> want to save the schedule as a record in another table so that next
> week when he goes to do the schedule, he can see what he did the week
> before.
>
> Everything I read seems to discuss how to create a form to ENTER data
> to the table it is associated with. But not how to take the data
> selected in the form and to save it to another table. Any help will be
> thoroughly appreciated.
>
> Tara[/color]

Hi Tara,

The code that you need is:

Public Function WriteDetails()
Dim rsRead As New ADODB.Recordset
Dim rsWrite As New ADODB.Recordset
Dim sql As String
sql = "Detail1, Detail2, Detail3, Detail4, Detail5 "
sql = sql & "FROM tblPastry"
rsRead.Open sql, CurrentProject.AccessConnection,
adOpenForwardOnly, adLockOptimistic
sql = "SELECT Field1, Field2, Field3, Field4, Field5 "
sql = sql & "FROM tblTable1"
With rsWrite
.Open sql, CurrentProject.AccessConnection, adOpenDynamic,
adLockOptimistic
.AddNew
!Field1 = rsRead!Detail1
!Field2 = rsRead!Detail2
!Field3 = rsRead!Detail3
!Field4 = rsRead!Detail4
!Field5 = rsRead!Detail5
.Update
.Close
End With
Set rs = Nothing
End Function

Where the detail rows are the data that you have, and the Fields you
will have to create manually, but will be updated automatically. This
is how to do it using ADO but if you are from a SQL Server background
why do you not write an append query that references a form for the
date or some other unique number?

re: Form to select the data from a table using multiple queries, save to another table

Nick 'The Database Guy' wrote:
[color=blue]
> tarafinlay wrote:
>[color=green]
> > Hi all,
> > I am new to access and am finding it a bit unintuitive having worked
> > with SQL server in the past... And I am in a bit of a hurry because my
> > employer wants me to crank something out which at first seemed like a
> > piece of cake but I realize now I should have done a little more
> > research before I got started. Here is the issue:
> >
> > I have a table with data relating to our pastry products. I built a
> > form with multiple combo boxes that are limited by category etc so that
> > the pastry chef can use the form to create his production schedule for
> > the week. This worked great and he can print the schedule. But now I
> > want to save the schedule as a record in another table so that next
> > week when he goes to do the schedule, he can see what he did the week
> > before.
> >
> > Everything I read seems to discuss how to create a form to ENTER data
> > to the table it is associated with. But not how to take the data
> > selected in the form and to save it to another table. Any help will be
> > thoroughly appreciated.
> >
> > Tara[/color]
>
> Hi Tara,
>
> The code that you need is:
>
> Public Function WriteDetails()
> Dim rsRead As New ADODB.Recordset
> Dim rsWrite As New ADODB.Recordset
> Dim sql As String
> sql = "Detail1, Detail2, Detail3, Detail4, Detail5 "
> sql = sql & "FROM tblPastry"
> rsRead.Open sql, CurrentProject.AccessConnection,
> adOpenForwardOnly, adLockOptimistic
> sql = "SELECT Field1, Field2, Field3, Field4, Field5 "
> sql = sql & "FROM tblTable1"
> With rsWrite
> .Open sql, CurrentProject.AccessConnection, adOpenDynamic,
> adLockOptimistic
> .AddNew
> !Field1 = rsRead!Detail1
> !Field2 = rsRead!Detail2
> !Field3 = rsRead!Detail3
> !Field4 = rsRead!Detail4
> !Field5 = rsRead!Detail5
> .Update
> .Close
> End With
> Set rs = Nothing
> End Function
>
> Where the detail rows are the data that you have, and the Fields you
> will have to create manually, but will be updated automatically. This
> is how to do it using ADO but if you are from a SQL Server background
> why do you not write an append query that references a form for the
> date or some other unique number?[/color]

Addendum

The end of the code in the preceeding example should read:

.Update
.Close
End With
Set rsRead = Nothing
Set rsWrite = Nothing
End Function

Thankyou

re: Form to select the data from a table using multiple queries, save to another table

Nick 'The Database Guy' wrote:
[color=blue]
> tarafinlay wrote:
>[color=green]
> > Hi all,
> > I am new to access and am finding it a bit unintuitive having worked
> > with SQL server in the past... And I am in a bit of a hurry because my
> > employer wants me to crank something out which at first seemed like a
> > piece of cake but I realize now I should have done a little more
> > research before I got started. Here is the issue:
> >
> > I have a table with data relating to our pastry products. I built a
> > form with multiple combo boxes that are limited by category etc so that
> > the pastry chef can use the form to create his production schedule for
> > the week. This worked great and he can print the schedule. But now I
> > want to save the schedule as a record in another table so that next
> > week when he goes to do the schedule, he can see what he did the week
> > before.
> >
> > Everything I read seems to discuss how to create a form to ENTER data
> > to the table it is associated with. But not how to take the data
> > selected in the form and to save it to another table. Any help will be
> > thoroughly appreciated.
> >
> > Tara[/color]
>
> Hi Tara,
>
> The code that you need is:
>
> Public Function WriteDetails()
> Dim rsRead As New ADODB.Recordset
> Dim rsWrite As New ADODB.Recordset
> Dim sql As String
> sql = "Detail1, Detail2, Detail3, Detail4, Detail5 "
> sql = sql & "FROM tblPastry"
> rsRead.Open sql, CurrentProject.AccessConnection,
> adOpenForwardOnly, adLockOptimistic
> sql = "SELECT Field1, Field2, Field3, Field4, Field5 "
> sql = sql & "FROM tblTable1"
> With rsWrite
> .Open sql, CurrentProject.AccessConnection, adOpenDynamic,
> adLockOptimistic
> .AddNew
> !Field1 = rsRead!Detail1
> !Field2 = rsRead!Detail2
> !Field3 = rsRead!Detail3
> !Field4 = rsRead!Detail4
> !Field5 = rsRead!Detail5
> .Update
> .Close
> End With
> Set rs = Nothing
> End Function
>
> Where the detail rows are the data that you have, and the Fields you
> will have to create manually, but will be updated automatically. This
> is how to do it using ADO but if you are from a SQL Server background
> why do you not write an append query that references a form for the
> date or some other unique number?[/color]

Addendum

The end of the code in the preceeding example should read:

.Update
.Close
End With
Set rsRead = Nothing
Set rsWrite = Nothing
End Function

Thankyou

re: Form to select the data from a table using multiple queries, save to another table

Hi Nick,
Thanks for your reply! The issue is really that I don't know how to
make the button do something :) I assume that I would assign this code
to the button to save the record? Forgive my ignorance...
Tara



Nick 'The Database Guy' wrote:[color=blue]
> tarafinlay wrote:
>[color=green]
> > Hi all,
> > I am new to access and am finding it a bit unintuitive having worked
> > with SQL server in the past... And I am in a bit of a hurry because my
> > employer wants me to crank something out which at first seemed like a
> > piece of cake but I realize now I should have done a little more
> > research before I got started. Here is the issue:
> >
> > I have a table with data relating to our pastry products. I built a
> > form with multiple combo boxes that are limited by category etc so that
> > the pastry chef can use the form to create his production schedule for
> > the week. This worked great and he can print the schedule. But now I
> > want to save the schedule as a record in another table so that next
> > week when he goes to do the schedule, he can see what he did the week
> > before.
> >
> > Everything I read seems to discuss how to create a form to ENTER data
> > to the table it is associated with. But not how to take the data
> > selected in the form and to save it to another table. Any help will be
> > thoroughly appreciated.
> >
> > Tara[/color]
>
> Hi Tara,
>
> The code that you need is:
>
> Public Function WriteDetails()
> Dim rsRead As New ADODB.Recordset
> Dim rsWrite As New ADODB.Recordset
> Dim sql As String
> sql = "Detail1, Detail2, Detail3, Detail4, Detail5 "
> sql = sql & "FROM tblPastry"
> rsRead.Open sql, CurrentProject.AccessConnection,
> adOpenForwardOnly, adLockOptimistic
> sql = "SELECT Field1, Field2, Field3, Field4, Field5 "
> sql = sql & "FROM tblTable1"
> With rsWrite
> .Open sql, CurrentProject.AccessConnection, adOpenDynamic,
> adLockOptimistic
> .AddNew
> !Field1 = rsRead!Detail1
> !Field2 = rsRead!Detail2
> !Field3 = rsRead!Detail3
> !Field4 = rsRead!Detail4
> !Field5 = rsRead!Detail5
> .Update
> .Close
> End With
> Set rs = Nothing
> End Function
>
> Where the detail rows are the data that you have, and the Fields you
> will have to create manually, but will be updated automatically. This
> is how to do it using ADO but if you are from a SQL Server background
> why do you not write an append query that references a form for the
> date or some other unique number?[/color]

re: Form to select the data from a table using multiple queries, save to another table

Hello! Are you looking for Nike shoes? Welcome to Putian Fujian China
There are hundreds of hot Nike shoes with high quality and low price.
Hurry up!
our website: http://www.nike3721.com
http://nike3721.en.alibaba.com/

E-mail: nike3721c@hotmail.com nike2691100c@yahoo.com.cn

We are a leading company that wholesales brand SHOES like Nike,
Jordans, Puma, Reebo, Adidas, Timberlands etc. Our company is located
in Putian where you can find nearly any kinds of world brand shoes and
all of them are enjoying good reputation for its excellent quality,
competitive price, fashionable styles, attractive colors etc. All the
shoes are 100% authentic and packed with original box. The tags and
style code number is 100% correct. We can provide the following
products:Air Jordan series I-XX Air Max series 95/97/03/04/05Nike Shox
series TL /R4 / NZAir Force OneNike Dunk Nike JamesNike KobeApe Bape
starTimberlandFoampositeRiftThe wholesale price depends on your order
quantity. For all our products, the price list below:1prs
$55/pair2-12prs $35/pair13-24prs $34 / pair25-48prs $33 / pair49-96prs
$32 / pair97-??prs $30 / pairThe price of Timberland boots: Adds $5
more in the above priceWe can deliver the shoes of our customers within
24 hours upon receiving the payment.We never mind starting the business
from minimum order even 1 pair. Normally the minimum order is12 pair
shoes. Drop ship is available here.
[image: http://www.nike3721.com/UploadFiles/...185632605.jpg]
[image: http://www.nike3721.com/UploadFiles/...822423927.jpg]


--
nike3721c
------------------------------------------------------------------------
nike3721c's Profile: http://www.dbtalk.net/m458
View this thread: http://www.dbtalk.net/t314592