Running append query on recordset

[James]

I would like an append query to run when a button is clicked, but only on the
items that are diplayed in the form. So if someone has a filter on a group
of records, and then they click the button, all of the records that are
selected will be sent to the append qry.

Can anyone tell me the correct way to select many records in a multi-page
form. Each page is a product and I am trying to add information for each
product.

Example:
rs = Me.recordset

INSERT INTO tblAcqDetail ( Quantity, PriceEach, ProductID )
SELECT rs.[txtQty] AS txtQty, rs.[txtCost] AS txtCost, rs.[txtProductID] AS
txtProductID;

thanks

 

[OldPro]

I would like an append query to run when a button is clicked, but only on the
items that are diplayed in the form. So if someone has a filter on a group
of records, and then they click the button, all of the records that are
selected will be sent to the append qry.

Can anyone tell me the correct way to select many records in a multi-page
form. Each page is a product and I am trying to add information for each
product.

Example:
rs = Me.recordset

INSERT INTO tblAcqDetail ( Quantity, PriceEach, ProductID )
SELECT rs.[txtQty] AS txtQty, rs.[txtCost] AS txtCost, rs.[txtProductID] AS
txtProductID;

thanks

A listbox will allow you to select multiple records. What kind of a
form are you using that has multiple pages and displays multiple
records at the same time? If the user is selecting various records,
then you could maintain a comma delimited list containing the record
ID of each record to be included. The SQL "IN" statement will work
for querying a list of records. Here is an example of how to use it:
SELECT DISTINCTROW LastName, FirstName
FROM PhoneBook
WHERE ([FirstName] In ("john","james","joyce"))

or

WHERE [id] IN (23423,45343,34443)

 

[James]

Thanks for the help, but a list box will not work for what I am trying to
accomplish. I need the user to be able to view all records, but if they want
they can use a filter to filter by specific types of product or by vender,
etc. The user also has to be able to add the amount to purchase and the
price for each item.

Sorry it was late last night. I was refering to coninuos forms, not pages.
Basically I need to know how to count all of the visible records (the one
that are still showing when a filter is used).

I need to use the existing sql, but need to modify it to use the recordset
(rst).

I would like an append query to run when a button is clicked, but only on the
items that are diplayed in the form. So if someone has a filter on a group
of records, and then they click the button, all of the records that are
selected will be sent to the append qry.

Can anyone tell me the correct way to select many records in a multi-page
form. Each page is a product and I am trying to add information for each
product.

Example:
rs = Me.recordset

INSERT INTO tblAcqDetail ( Quantity, PriceEach, ProductID )
SELECT rs.[txtQty] AS txtQty, rs.[txtCost] AS txtCost, rs.[txtProductID] AS
txtProductID;

thanks

A listbox will allow you to select multiple records. What kind of a
form are you using that has multiple pages and displays multiple
records at the same time? If the user is selecting various records,
then you could maintain a comma delimited list containing the record
ID of each record to be included. The SQL "IN" statement will work
for querying a list of records. Here is an example of how to use it:
SELECT DISTINCTROW LastName, FirstName
FROM PhoneBook
WHERE ([FirstName] In ("john","james","joyce"))

or

WHERE [id] IN (23423,45343,34443)

 

[OldPro]

Thanks for the help, but a list box will not work for what I am trying to
accomplish. I need the user to be able to view all records, but if they want
they can use a filter to filter by specific types of product or by vender,
etc. The user also has to be able to add the amount to purchase and the
price for each item.

Sorry it was late last night. I was refering to coninuos forms, not pages.
Basically I need to know how to count all of the visible records (the one
that are still showing when a filter is used).

I need to use the existing sql, but need to modify it to use the recordset
(rst).

I would like an append query to run when a button is clicked, but only on the
items that are diplayed in the form. So if someone has a filter on a group
of records, and then they click the button, all of the records that are
selected will be sent to the append qry.
Can anyone tell me the correct way to select many records in a multi-page
form. Each page is a product and I am trying to add information for each
product.
Example:
rs = Me.recordset
INSERT INTO tblAcqDetail ( Quantity, PriceEach, ProductID )
SELECT rs.[txtQty] AS txtQty, rs.[txtCost] AS txtCost, rs.[txtProductID] AS
txtProductID;
thanks

A listbox will allow you to select multiple records. What kind of a
form are you using that has multiple pages and displays multiple
records at the same time? If the user is selecting various records,
then you could maintain a comma delimited list containing the record
ID of each record to be included. The SQL "IN" statement will work
for querying a list of records. Here is an example of how to use it:
SELECT DISTINCTROW LastName, FirstName
FROM PhoneBook
WHERE ([FirstName] In ("john","james","joyce"))

or

WHERE [id] IN (23423,45343,34443)- Hide quoted text -

- Show quoted text -

If you want the user to be able to select or unselect each item in a
continuous form, then go to http://www.mvps.org/access/forms/frm0047.htm
for instructions on how to hilite a record in a continuous form. The
OnCurrent event is used to return the record-specific-data each time a
record is selected.

 

[Jana]

Thanks for the help, but a list box will not work for what I am trying to
accomplish. I need the user to be able to view all records, but if they want
they can use a filter to filter by specific types of product or by vender,
etc. The user also has to be able to add the amount to purchase and the
price for each item.

Sorry it was late last night. I was refering to coninuos forms, not pages.
Basically I need to know how to count all of the visible records (the one
that are still showing when a filter is used).

I need to use the existing sql, but need to modify it to use the recordset
(rst).

I would like an append query to run when a button is clicked, but only on the
items that are diplayed in the form. So if someone has a filter on a group
of records, and then they click the button, all of the records that are
selected will be sent to the append qry.
Can anyone tell me the correct way to select many records in a multi-page
form. Each page is a product and I am trying to add information for each
product.
Example:
rs = Me.recordset
INSERT INTO tblAcqDetail ( Quantity, PriceEach, ProductID )
SELECT rs.[txtQty] AS txtQty, rs.[txtCost] AS txtCost, rs.[txtProductID] AS
txtProductID;
thanks

A listbox will allow you to select multiple records. What kind of a
form are you using that has multiple pages and displays multiple
records at the same time? If the user is selecting various records,
then you could maintain a comma delimited list containing the record
ID of each record to be included. The SQL "IN" statement will work
for querying a list of records. Here is an example of how to use it:
SELECT DISTINCTROW LastName, FirstName
FROM PhoneBook
WHERE ([FirstName] In ("john","james","joyce"))

or

WHERE [id] IN (23423,45343,34443)- Hide quoted text -

- Show quoted text -

James:

I think that what you're looking for is RecordSetClone, which grabs a
recordset of the displayed records. Here's some *air code* to give
you an idea of where to head for this:

Private Sub MyButton_Click()
Dim rstForm as DAO.Recordset
Dim rstDestination as DAO.Recordset
Dim dbs as DAO.Database
Set dbs = CurrentDb
Set rstForm = Me.RecordsetClone
Set rstDestination = dbs.OpenRecordSet("tblAcqDetail")
Do Until rstForm.eof
rstDestination.AddNew
rstDestination!Quantity = rstForm!Quantity
'You should use the form field Control Sources, not the form
field names
'after the = signs!!!
rstDestination!PriceEach = rstForm!Cost
rstDestination!ProductID = rstForm!ProductID
rstDestination.Update
rstForm.MoveNext
Loop
End Sub

HTH,
Jana

 

[James]

Thanks Jana! That is exactly what I was looking for.

Thanks for the help, but a list box will not work for what I am trying to
accomplish. I need the user to be able to view all records, but if they want
they can use a filter to filter by specific types of product or by vender,
etc. The user also has to be able to add the amount to purchase and the
price for each item.

Sorry it was late last night. I was refering to coninuos forms, not pages.
Basically I need to know how to count all of the visible records (the one
that are still showing when a filter is used).

I need to use the existing sql, but need to modify it to use the recordset
(rst).

I would like an append query to run when a button is clicked, but only on the
items that are diplayed in the form. So if someone has a filter on a group
of records, and then they click the button, all of the records that are
selected will be sent to the append qry.

Can anyone tell me the correct way to select many records in a multi-page
form. Each page is a product and I am trying to add information for each
product.

Example:
rs = Me.recordset

INSERT INTO tblAcqDetail ( Quantity, PriceEach, ProductID )
SELECT rs.[txtQty] AS txtQty, rs.[txtCost] AS txtCost, rs.[txtProductID] AS
txtProductID;

thanks

A listbox will allow you to select multiple records. What kind of a
form are you using that has multiple pages and displays multiple
records at the same time? If the user is selecting various records,
then you could maintain a comma delimited list containing the record
ID of each record to be included. The SQL "IN" statement will work
for querying a list of records. Here is an example of how to use it:
SELECT DISTINCTROW LastName, FirstName
FROM PhoneBook
WHERE ([FirstName] In ("john","james","joyce"))

or

WHERE [id] IN (23423,45343,34443)- Hide quoted text -

- Show quoted text -

James:

I think that what you're looking for is RecordSetClone, which grabs a
recordset of the displayed records. Here's some *air code* to give
you an idea of where to head for this:

Private Sub MyButton_Click()
Dim rstForm as DAO.Recordset
Dim rstDestination as DAO.Recordset
Dim dbs as DAO.Database
Set dbs = CurrentDb
Set rstForm = Me.RecordsetClone
Set rstDestination = dbs.OpenRecordSet("tblAcqDetail")
Do Until rstForm.eof
rstDestination.AddNew
rstDestination!Quantity = rstForm!Quantity
'You should use the form field Control Sources, not the form
field names
'after the = signs!!!
rstDestination!PriceEach = rstForm!Cost
rstDestination!ProductID = rstForm!ProductID
rstDestination.Update
rstForm.MoveNext
Loop
End Sub

HTH,
Jana

 

[Jana]

Thanks Jana! That is exactly what I was looking for.

Thanks for the help, but a list box will not work for what I am trying to
accomplish. I need the user to be able to view all records, but if they want
they can use a filter to filter by specific types of product or by vender,
etc. The user also has to be able to add the amount to purchase and the
price for each item.
Sorry it was late last night. I was refering to coninuos forms, not pages.
Basically I need to know how to count all of the visible records (the one
that are still showing when a filter is used).
I need to use the existing sql, but need to modify it to use the recordset
(rst).
:
I would like an append query to run when a button is clicked, but only on the
items that are diplayed in the form. So if someone has a filter on a group
of records, and then they click the button, all of the records that are
selected will be sent to the append qry.
Can anyone tell me the correct way to select many records in a multi-page
form. Each page is a product and I am trying to add information for each
product.
Example:
rs = Me.recordset
INSERT INTO tblAcqDetail ( Quantity, PriceEach, ProductID )
SELECT rs.[txtQty] AS txtQty, rs.[txtCost] AS txtCost, rs.[txtProductID] AS
txtProductID;
thanks
A listbox will allow you to select multiple records. What kind of a
form are you using that has multiple pages and displays multiple
records at the same time? If the user is selecting various records,
then you could maintain a comma delimited list containing the record
ID of each record to be included. The SQL "IN" statement will work
for querying a list of records. Here is an example of how to use it:
SELECT DISTINCTROW LastName, FirstName
FROM PhoneBook
WHERE ([FirstName] In ("john","james","joyce"))
or
WHERE [id] IN (23423,45343,34443)- Hide quoted text -
- Show quoted text -

James:

I think that what you're looking for is RecordSetClone, which grabs a
recordset of the displayed records. Here's some *air code* to give
you an idea of where to head for this:

Private Sub MyButton_Click()
Dim rstForm as DAO.Recordset
Dim rstDestination as DAO.Recordset
Dim dbs as DAO.Database
Set dbs = CurrentDb
Set rstForm = Me.RecordsetClone
Set rstDestination = dbs.OpenRecordSet("tblAcqDetail")
Do Until rstForm.eof
rstDestination.AddNew
rstDestination!Quantity = rstForm!Quantity
'You should use the form field Control Sources, not the form
field names
'after the = signs!!!
rstDestination!PriceEach = rstForm!Cost
rstDestination!ProductID = rstForm!ProductID
rstDestination.Update
rstForm.MoveNext
Loop
End Sub

HTH,
Jana- Hide quoted text -

- Show quoted text -

James:

My grin is so big as to blind my co-workers.

Glad to hear it!

Jana