Multiple Record(12 Entries) Into Table w/ Form

  • Thread starter Thread starter Graffyn
  • Start date Start date
G

Graffyn

I am attempting to send more than one record with one form to the table and I
can't seem to get INSERT ... VALUES to enter records separately and the text
boxes have different names but the same Control Source so when i change the
number all the fields update with the same number when i want each field to
have different numbers... You help is very appreciated
 
Hi Graffyn,

The method of using insert and values to add data to a table works very well
to do one record as you have found. To update several records at a time
instead of the values part of the query, you need a select query that
returns the values for all the records you want to update. There is
information in help. Go to help when you have the code window and type in
something like update query.

Jeanette Cunningham
 
Oops, that should have been append query instead of update query.

Jeanette Cunningham
 
I believe i have to intregrate this syntax but not sure how:
Syntax
Multiple-record append query:
INSERT INTO target [(field1[, field2[, ...]])] [IN externaldatabase]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

This is what I have:
a = Me.DateField.Text
b = Me.SSN1.Text
c = Me.SSN2.Text



DoCmd.RunSQL "INSERT INTO PayrollTable (Date, SSN) SELECT
[Me.]DateField[,SSN1] FROM PayrollForm "
'This should be record one
DoCmd.RunSQL "INSERT INTO PayrollTable (Date, SSN) SELECT
[Me.]DateField[,SSN2] FROM PayrollForm "
'this should be record two
 
Graffyn,
Usually all the data on the form would relate to a single record, or if the
form is in continuous view all the data would relate to one row on the form.
The append query would take the data and append it to the same row in the
table.

Your setup seems to be a bit different. Does a user enter the details for
SSN for two different clients? If the two records are all for the same
client, why would they need to go on 2 separate records?

If you can post back with some more details, we can work it out.

Jeanette Cunningham

Graffyn said:
I believe i have to intregrate this syntax but not sure how:
Syntax
Multiple-record append query:
INSERT INTO target [(field1[, field2[, ...]])] [IN externaldatabase]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

This is what I have:
a = Me.DateField.Text
b = Me.SSN1.Text
c = Me.SSN2.Text



DoCmd.RunSQL "INSERT INTO PayrollTable (Date, SSN) SELECT
[Me.]DateField[,SSN1] FROM PayrollForm "
'This should be record one
DoCmd.RunSQL "INSERT INTO PayrollTable (Date, SSN) SELECT
[Me.]DateField[,SSN2] FROM PayrollForm "
'this should be record two



Graffyn said:
I am attempting to send more than one record with one form to the table
and I
can't seem to get INSERT ... VALUES to enter records separately and the
text
boxes have different names but the same Control Source so when i change
the
number all the fields update with the same number when i want each field
to
have different numbers... You help is very appreciated
Click to expand...
Click to expand...
 
Graffyn,
I would expect that your table would have a DateField and an SSN field.
When you open the table and look at the data, there would be one field with
all the dates and a second field with all the SSN's.

After re-reading your posts, I am wondering if your table has one DateField
and 12 SSN fields. If your table is set up this way, this would explain the
difficulty with updating the SSN numbers. Please let me know.

Jeanette Cunningham


Graffyn said:
I believe i have to intregrate this syntax but not sure how:
Syntax
Multiple-record append query:
INSERT INTO target [(field1[, field2[, ...]])] [IN externaldatabase]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

This is what I have:
a = Me.DateField.Text
b = Me.SSN1.Text
c = Me.SSN2.Text



DoCmd.RunSQL "INSERT INTO PayrollTable (Date, SSN) SELECT
[Me.]DateField[,SSN1] FROM PayrollForm "
'This should be record one
DoCmd.RunSQL "INSERT INTO PayrollTable (Date, SSN) SELECT
[Me.]DateField[,SSN2] FROM PayrollForm "
'this should be record two



Graffyn said:
I am attempting to send more than one record with one form to the table
and I
can't seem to get INSERT ... VALUES to enter records separately and the
text
boxes have different names but the same Control Source so when i change
the
number all the fields update with the same number when i want each field
to
have different numbers... You help is very appreciated
Click to expand...
Click to expand...
 
Hello Jeanette,
I found out about the continuous form, and this is much easier than
working SQL to accomplish something that is already done by the program. I
have 12 entries and now one date field is at the header and possibly a
division number. Can i have one entry text box in the header that would
apply to the rest of the page. And can I have a the same header for each new
printable page. As I may have said before I am trying to recreate a payroll
form. The form would work the same way as a payroll form on paper but now
they could print out the form neatly and have all the entries typed into the
spaces provided. Also the form would be connected to a table so that when
this form begins to get used, the entries would would get saved into this
table, maybe a table for each divison and queries and reports could be made
with the table. I am trying to upgrade a paper system with an access adp
system. Thanks for you patience

Jeanette Cunningham said:
Graffyn,
I would expect that your table would have a DateField and an SSN field.
When you open the table and look at the data, there would be one field with
all the dates and a second field with all the SSN's.

After re-reading your posts, I am wondering if your table has one DateField
and 12 SSN fields. If your table is set up this way, this would explain the
difficulty with updating the SSN numbers. Please let me know.

Jeanette Cunningham


Graffyn said:
I believe i have to intregrate this syntax but not sure how:
Syntax
Multiple-record append query:
INSERT INTO target [(field1[, field2[, ...]])] [IN externaldatabase]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

This is what I have:
a = Me.DateField.Text
b = Me.SSN1.Text
c = Me.SSN2.Text



DoCmd.RunSQL "INSERT INTO PayrollTable (Date, SSN) SELECT
[Me.]DateField[,SSN1] FROM PayrollForm "
'This should be record one
DoCmd.RunSQL "INSERT INTO PayrollTable (Date, SSN) SELECT
[Me.]DateField[,SSN2] FROM PayrollForm "
'this should be record two



Graffyn said:
I am attempting to send more than one record with one form to the table
and I
can't seem to get INSERT ... VALUES to enter records separately and the
text
boxes have different names but the same Control Source so when i change
the
number all the fields update with the same number when i want each field
to
have different numbers... You help is very appreciated
Click to expand...
Click to expand...
Click to expand...
 
Hi again,
Glad you are getting there with the continuous form.
No, you can't have one date txtbox in the header, you need a date txtbox on
every record on the continuous form.
You can have a default value for date after the first date is entered.
On the after update of the date field:
Me.txtPayDate.DefaultValue = "#" & me.txtPayDate & "#"
Printing the form is not the way to go.
To print your pay data, use a report.
The report will be based on a query and the report can have one text box in
the header for PayDate.
By the way I have not used adp, so had better leave any more answers to
someone with experience in adp's.

Jeanette Cunningham


Graffyn said:
Hello Jeanette,
I found out about the continuous form, and this is much easier than
working SQL to accomplish something that is already done by the program.
I
have 12 entries and now one date field is at the header and possibly a
division number. Can i have one entry text box in the header that would
apply to the rest of the page. And can I have a the same header for each
new
printable page. As I may have said before I am trying to recreate a
payroll
form. The form would work the same way as a payroll form on paper but now
they could print out the form neatly and have all the entries typed into
the
spaces provided. Also the form would be connected to a table so that when
this form begins to get used, the entries would would get saved into this
table, maybe a table for each divison and queries and reports could be
made
with the table. I am trying to upgrade a paper system with an access adp
system. Thanks for you patience

Jeanette Cunningham said:
Graffyn,
I would expect that your table would have a DateField and an SSN field.
When you open the table and look at the data, there would be one field
with
all the dates and a second field with all the SSN's.

After re-reading your posts, I am wondering if your table has one
DateField
and 12 SSN fields. If your table is set up this way, this would explain
the
difficulty with updating the SSN numbers. Please let me know.

Jeanette Cunningham


Graffyn said:
I believe i have to intregrate this syntax but not sure how:
Syntax
Multiple-record append query:
INSERT INTO target [(field1[, field2[, ...]])] [IN externaldatabase]
SELECT [source.]field1[, field2[, ...]
FROM tableexpression

This is what I have:
a = Me.DateField.Text
b = Me.SSN1.Text
c = Me.SSN2.Text



DoCmd.RunSQL "INSERT INTO PayrollTable (Date, SSN) SELECT
[Me.]DateField[,SSN1] FROM PayrollForm "
'This should be record one
DoCmd.RunSQL "INSERT INTO PayrollTable (Date, SSN) SELECT
[Me.]DateField[,SSN2] FROM PayrollForm "
'this should be record two



:

I am attempting to send more than one record with one form to the
table
and I
can't seem to get INSERT ... VALUES to enter records separately and
the
text
boxes have different names but the same Control Source so when i
change
the
number all the fields update with the same number when i want each
field
to
have different numbers... You help is very appreciated
Click to expand...
Click to expand...
Click to expand...
 
Back
Top