Insert Record into another Table

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

I have a form where the users enter information. I need to
be able to insert 1 record into another table when the
users click on a date field. I've tried creating an
append query and using a macro to execute the append query
and setting the "ON Click" to execute the macro. After
doing this, 20-30 blank records were added to the second
table. I need one record added with a value from the
original form. Any ideas?
 
For the Append Query, are you limiting the records to
ensure that only data for the current Record on the Form
is append to the new Table? If you are not doing this
then the append query is adding all data from the original
Table!!

1. Ensure that there is a Record Number Field on the Form
(call it "RecordNo").
2. Include the Record field for the Append Query, Add the
Criteria =Forms![MyForm]![RecordNo] to the query.

This will probably solve your problem.

HTH



Tony C
 
I tried, but no records are added. Using two
tables...TurnTbl and MemberTbl. On the form(displaying
info from TurnTbl) I have a field call TempTurnId which is
a numeric field. This is also should be the key to the
second table. I need to have the "ON CLICK" to envoke a
macro that runs an append query to insert a new record on
the MemberTbl with the value from TempTurnID.

-----Original Message-----
For the Append Query, are you limiting the records to
ensure that only data for the current Record on the Form
is append to the new Table? If you are not doing this
then the append query is adding all data from the original
Table!!

1. Ensure that there is a Record Number Field on the Form
(call it "RecordNo").
2. Include the Record field for the Append Query, Add the
Criteria =Forms![MyForm]![RecordNo] to the query.

This will probably solve your problem.

HTH



Tony C
-----Original Message-----
I have a form where the users enter information. I need to
be able to insert 1 record into another table when the
users click on a date field. I've tried creating an
append query and using a macro to execute the append query
and setting the "ON Click" to execute the macro. After
doing this, 20-30 blank records were added to the second
table. I need one record added with a value from the
original form. Any ideas?

.
Click to expand...
.
Click to expand...
 
For the Append Query, add the field TempTurnID and add the
Criteria Like Forms![TurnTbl]![TempTurnID]: -

For the benefit of this posting, I have referred to your
form as TurnTbl and the for field as TempTurnID, adjust
these values to reflect the actual names used by the Form.

I have also referred to the Field from MemberTbl as
TempTurnID on the assumption that this is the link field
to TurnTbl.
Do not forget to include the other fields that you want
appending, For the Append Query, from the Table TurnTBL
(which you should already have added) select the required
fields that contain the data you want to append, then
select the field that each value is to be appended to via
the "Append To" selection for each field you want to add
data to. If the required fields are not displayed in the
available list, then click the green Cross on the Toolbar,
this displays the Table the Query is attempting to append
data to. If this table is not MemberTbl then enter
MemberTbl then attempt to adjust the "Append To" values
again.

Finally, you do not need to have code that calls a Macro
that then runs the Query. Delete the Macro, remove all of
the code from the procedure that calls the Macro, then add
the line: -

DoCmd.OpenQuery, "ENTER APPEND QUERY NAME
HERE",acViewNormal

This should solve the problems that you are having. If it
does not then reply to this posting.

HTH



Tony C.
-----Original Message-----
I tried, but no records are added. Using two
tables...TurnTbl and MemberTbl. On the form(displaying
info from TurnTbl) I have a field call TempTurnId which is
a numeric field. This is also should be the key to the
second table. I need to have the "ON CLICK" to envoke a
macro that runs an append query to insert a new record on
the MemberTbl with the value from TempTurnID.

-----Original Message-----
For the Append Query, are you limiting the records to
ensure that only data for the current Record on the Form
is append to the new Table? If you are not doing this
then the append query is adding all data from the original
Table!!

1. Ensure that there is a Record Number Field on the Form
(call it "RecordNo").
2. Include the Record field for the Append Query, Add the
Criteria =Forms![MyForm]![RecordNo] to the query.

This will probably solve your problem.

HTH



Tony C
-----Original Message-----
I have a form where the users enter information. I need to
be able to insert 1 record into another table when the
users click on a date field. I've tried creating an
append query and using a macro to execute the append query
and setting the "ON Click" to execute the macro. After
doing this, 20-30 blank records were added to the second
table. I need one record added with a value from the
original form. Any ideas?

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Tony,
I appreciate the help and your patience...I added the code
below, but when it executes I get the error:
Compile Error, Argument not Optional.



Private Sub Install_Date_Click()
On Error GoTo Err_CloseDate_Click

DoCmd.OpenQuery , AddTurnIdApndQry, acViewNormal

End Sub
Err_CloseDate_Click:
MsgBox Err.Description
Resume Exit_CloseDate_Click

End Sub


-----Original Message-----
For the Append Query, add the field TempTurnID and add the
Criteria Like Forms![TurnTbl]![TempTurnID]: -

For the benefit of this posting, I have referred to your
form as TurnTbl and the for field as TempTurnID, adjust
these values to reflect the actual names used by the Form.

I have also referred to the Field from MemberTbl as
TempTurnID on the assumption that this is the link field
to TurnTbl.
Do not forget to include the other fields that you want
appending, For the Append Query, from the Table TurnTBL
(which you should already have added) select the required
fields that contain the data you want to append, then
select the field that each value is to be appended to via
the "Append To" selection for each field you want to add
data to. If the required fields are not displayed in the
available list, then click the green Cross on the Toolbar,
this displays the Table the Query is attempting to append
data to. If this table is not MemberTbl then enter
MemberTbl then attempt to adjust the "Append To" values
again.

Finally, you do not need to have code that calls a Macro
that then runs the Query. Delete the Macro, remove all of
the code from the procedure that calls the Macro, then add
the line: -

DoCmd.OpenQuery, "ENTER APPEND QUERY NAME
HERE",acViewNormal

This should solve the problems that you are having. If it
does not then reply to this posting.

HTH



Tony C.
-----Original Message-----
I tried, but no records are added. Using two
tables...TurnTbl and MemberTbl. On the form(displaying
info from TurnTbl) I have a field call TempTurnId which is
a numeric field. This is also should be the key to the
second table. I need to have the "ON CLICK" to envoke a
macro that runs an append query to insert a new record on
the MemberTbl with the value from TempTurnID.

-----Original Message-----
For the Append Query, are you limiting the records to
ensure that only data for the current Record on the Form
is append to the new Table? If you are not doing this
then the append query is adding all data from the original
Table!!

1. Ensure that there is a Record Number Field on the Form
(call it "RecordNo").
2. Include the Record field for the Append Query, Add the
Criteria =Forms![MyForm]![RecordNo] to the query.

This will probably solve your problem.

HTH



Tony C

-----Original Message-----
I have a form where the users enter information. I need
to
be able to insert 1 record into another table when the
users click on a date field. I've tried creating an
append query and using a macro to execute the append
query
and setting the "ON Click" to execute the macro. After
doing this, 20-30 blank records were added to the second
table. I need one record added with a value from the
original form. Any ideas?

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Try this: -

DoCmd.OpenQuery "AddTurnIdApndQry", acViewNormal

Also, I forgot to mention to add the line : -

Me.Refresh

Before running the Query...

So your vb code should now look like

Me.Refresh
DoCmd.OpenQuery "AddTurnIdApndQry", acViewNormal

Tony C.
-----Original Message-----
Tony,
I appreciate the help and your patience...I added the code
below, but when it executes I get the error:
Compile Error, Argument not Optional.



Private Sub Install_Date_Click()
On Error GoTo Err_CloseDate_Click

DoCmd.OpenQuery , AddTurnIdApndQry, acViewNormal

End Sub
Err_CloseDate_Click:
MsgBox Err.Description
Resume Exit_CloseDate_Click

End Sub


-----Original Message-----
For the Append Query, add the field TempTurnID and add the
Criteria Like Forms![TurnTbl]![TempTurnID]: -

For the benefit of this posting, I have referred to your
form as TurnTbl and the for field as TempTurnID, adjust
these values to reflect the actual names used by the Form.

I have also referred to the Field from MemberTbl as
TempTurnID on the assumption that this is the link field
to TurnTbl.
Do not forget to include the other fields that you want
appending, For the Append Query, from the Table TurnTBL
(which you should already have added) select the required
fields that contain the data you want to append, then
select the field that each value is to be appended to via
the "Append To" selection for each field you want to add
data to. If the required fields are not displayed in the
available list, then click the green Cross on the Toolbar,
this displays the Table the Query is attempting to append
data to. If this table is not MemberTbl then enter
MemberTbl then attempt to adjust the "Append To" values
again.

Finally, you do not need to have code that calls a Macro
that then runs the Query. Delete the Macro, remove all of
the code from the procedure that calls the Macro, then add
the line: -

DoCmd.OpenQuery, "ENTER APPEND QUERY NAME
HERE",acViewNormal

This should solve the problems that you are having. If it
does not then reply to this posting.

HTH



Tony C.
-----Original Message-----
I tried, but no records are added. Using two
tables...TurnTbl and MemberTbl. On the form (displaying
info from TurnTbl) I have a field call TempTurnId which is
a numeric field. This is also should be the key to the
second table. I need to have the "ON CLICK" to envoke a
macro that runs an append query to insert a new record on
the MemberTbl with the value from TempTurnID.


-----Original Message-----
For the Append Query, are you limiting the records to
ensure that only data for the current Record on the Form
is append to the new Table? If you are not doing this
then the append query is adding all data from the
original
Table!!

1. Ensure that there is a Record Number Field on the
Form
(call it "RecordNo").
2. Include the Record field for the Append Query, Add
the
Criteria =Forms![MyForm]![RecordNo] to the query.

This will probably solve your problem.

HTH



Tony C

-----Original Message-----
I have a form where the users enter information. I need
to
be able to insert 1 record into another table when the
users click on a date field. I've tried creating an
append query and using a macro to execute the append
query
and setting the "ON Click" to execute the macro. After
doing this, 20-30 blank records were added to the second
table. I need one record added with a value from the
original form. Any ideas?

.

.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top