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

[tarafinlay]

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

[Nick 'The Database Guy']

tarafinlay wrote:

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

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. AccessConnectio n,
adOpenForwardOn ly, adLockOptimisti c
sql = "SELECT Field1, Field2, Field3, Field4, Field5 "
sql = sql & "FROM tblTable1"
With rsWrite
.Open sql, CurrentProject. AccessConnectio n, adOpenDynamic,
adLockOptimisti c
.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?

[Nick 'The Database Guy']

Nick 'The Database Guy' wrote:

tarafinlay wrote:

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

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. AccessConnectio n,
adOpenForwardOn ly, adLockOptimisti c
sql = "SELECT Field1, Field2, Field3, Field4, Field5 "
sql = sql & "FROM tblTable1"
With rsWrite
.Open sql, CurrentProject. AccessConnectio n, adOpenDynamic,
adLockOptimisti c
.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?

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

[Nick 'The Database Guy']

Nick 'The Database Guy' wrote:

tarafinlay wrote:

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

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. AccessConnectio n,
adOpenForwardOn ly, adLockOptimisti c
sql = "SELECT Field1, Field2, Field3, Field4, Field5 "
sql = sql & "FROM tblTable1"
With rsWrite
.Open sql, CurrentProject. AccessConnectio n, adOpenDynamic,
adLockOptimisti c
.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?

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

[tarafinlay]

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:

tarafinlay wrote:

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

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. AccessConnectio n,
adOpenForwardOn ly, adLockOptimisti c
sql = "SELECT Field1, Field2, Field3, Field4, Field5 "
sql = sql & "FROM tblTable1"
With rsWrite
.Open sql, CurrentProject. AccessConnectio n, adOpenDynamic,
adLockOptimisti c
.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?