Add a new record to a third table

[dnitz]

I am inexperienced at Visual Basic coding and I have a task I need to get
done quickly to a database that is in use daily. I have a form with subform
based on the Equipment and Leases tables having a one to many relationship. I
would like to add a block of code to the After Update event that will
automatically append a new record to a third table, Inventory and fill in
just the key field, Serial Number. The Inventory table has a one to one
relationship to Equipment with Serial Number as the key. Can any of you ace
coders out there help me?

 

[Allen Browne]

In the AfterUpdate event of the main form, execute an Append query statement
to insert the value into the 3rd table.

Assuming Access 2000 or later, you will end up with something like this:

Private Sub Form_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO table1 ( SerialNum ) SELECT """ & Me.[SerialNum] &
""" As Expr1;"
dbEngine(0)(0).Execute strSql, dbFailOnError
End Sub

If you have problems with the SQL statement, mock up a query using any
literal value, change it to an Append query (Query menu), switch to SQL View
(View menu), and you have an example to copy.

 

[dnitz]

Allen,

Thank you so much for your reply. I forgot to mention that I am using
Access 2003. In the Access form (Equipment and Leases) code window, the code
below shows red starting with the &. I looked for help on the syntax for
Insert into but it did not answer my questions.

Will I have to set each additional field to null or will it append the
record and do that for me? The only field I need to populate is SerialNum.

Private Sub Form_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO Inventory ( SerialNum ) SELECT """ & Me.[SerialNum]
&
""" As Expr1;"
DBEngine(0)(0).Execute strSql, dbFailOnError
End Sub

Thanks again for your help. I used to code a little but not in Visual Basic
and have forgotten most of what I knew.

In the AfterUpdate event of the main form, execute an Append query statement
to insert the value into the 3rd table.

Assuming Access 2000 or later, you will end up with something like this:

Private Sub Form_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO table1 ( SerialNum ) SELECT """ & Me.[SerialNum] &
""" As Expr1;"
dbEngine(0)(0).Execute strSql, dbFailOnError
End Sub

If you have problems with the SQL statement, mock up a query using any
literal value, change it to an Append query (Query menu), switch to SQL View
(View menu), and you have an example to copy.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am inexperienced at Visual Basic coding and I have a task I need to get
done quickly to a database that is in use daily. I have a form with
subform
based on the Equipment and Leases tables having a one to many
relationship. I
would like to add a block of code to the After Update event that will
automatically append a new record to a third table, Inventory and fill in
just the key field, Serial Number. The Inventory table has a one to one
relationship to Equipment with Serial Number as the key. Can any of you
ace
coders out there help me?

 

[Allen Browne]

Ignore the line breaks. They are forced by the newsreader, and are not
intended. The "strSQL = " line ends with Expr1;"

The other fields will be null (unless you have set a DefaultValue for them.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you so much for your reply. I forgot to mention that I am using
Access 2003. In the Access form (Equipment and Leases) code window, the
code
below shows red starting with the &. I looked for help on the syntax for
Insert into but it did not answer my questions.

Will I have to set each additional field to null or will it append the
record and do that for me? The only field I need to populate is
SerialNum.

Private Sub Form_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO Inventory ( SerialNum ) SELECT """ &
Me.[SerialNum]
&
""" As Expr1;"
DBEngine(0)(0).Execute strSql, dbFailOnError
End Sub

Thanks again for your help. I used to code a little but not in Visual
Basic
and have forgotten most of what I knew.

In the AfterUpdate event of the main form, execute an Append query
statement
to insert the value into the 3rd table.

Assuming Access 2000 or later, you will end up with something like this:

Private Sub Form_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO table1 ( SerialNum ) SELECT """ &
Me.[SerialNum] &
""" As Expr1;"
dbEngine(0)(0).Execute strSql, dbFailOnError
End Sub

If you have problems with the SQL statement, mock up a query using any
literal value, change it to an Append query (Query menu), switch to SQL
View
(View menu), and you have an example to copy.

I am inexperienced at Visual Basic coding and I have a task I need to
get
done quickly to a database that is in use daily. I have a form with
subform
based on the Equipment and Leases tables having a one to many
relationship. I
would like to add a block of code to the After Update event that will
automatically append a new record to a third table, Inventory and fill
in
just the key field, Serial Number. The Inventory table has a one to one
relationship to Equipment with Serial Number as the key. Can any of
you
ace
coders out there help me?

 

[dnitz]

Allen, Thanks to you I'm making progress! It is writing a record into the
Inventory table but is inserting some default values into the fields where
they want all the other fields to be blank. I'm trying to add the field list
into the select portion of the statement and assign the text fields "" for
blank but am getting syntax errors again. This is very frustrating. Now I
remember why I dodn't want to be a programmer when I grow up. Ha Ha. Some
are long integer fields. What would I assign them to? Here is the code as
it stands now. I've put stuff in then cut it out trying to get it to work.

Private Sub Form_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO Inventory ( SerialNum, Barcode, [Domain], IP,
ServerName, BuildingID, Rack, MakeID, Model, OS, OSVersion, PL, Environment,
CostFunctionID, Function, Area, Owner, SAUR, ServiceDate, Building, Room,
EntryPerson, OperatingSystem ) SELECT """ & Me.[SerialNum] & """ As Expr1;"
DBEngine(0)(0).Execute strSql, dbFailOnError
End Sub

Ignore the line breaks. They are forced by the newsreader, and are not
intended. The "strSQL = " line ends with Expr1;"

The other fields will be null (unless you have set a DefaultValue for them.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you so much for your reply. I forgot to mention that I am using
Access 2003. In the Access form (Equipment and Leases) code window, the
code
below shows red starting with the &. I looked for help on the syntax for
Insert into but it did not answer my questions.

Will I have to set each additional field to null or will it append the
record and do that for me? The only field I need to populate is
SerialNum.

Private Sub Form_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO Inventory ( SerialNum ) SELECT """ &
Me.[SerialNum]
&
""" As Expr1;"
DBEngine(0)(0).Execute strSql, dbFailOnError
End Sub

Thanks again for your help. I used to code a little but not in Visual
Basic
and have forgotten most of what I knew.

In the AfterUpdate event of the main form, execute an Append query
statement
to insert the value into the 3rd table.

Assuming Access 2000 or later, you will end up with something like this:

Private Sub Form_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO table1 ( SerialNum ) SELECT """ &
Me.[SerialNum] &
""" As Expr1;"
dbEngine(0)(0).Execute strSql, dbFailOnError
End Sub

If you have problems with the SQL statement, mock up a query using any
literal value, change it to an Append query (Query menu), switch to SQL
View
(View menu), and you have an example to copy.

I am inexperienced at Visual Basic coding and I have a task I need to
get
done quickly to a database that is in use daily. I have a form with
subform
based on the Equipment and Leases tables having a one to many
relationship. I
would like to add a block of code to the After Update event that will
automatically append a new record to a third table, Inventory and fill
in
just the key field, Serial Number. The Inventory table has a one to one
relationship to Equipment with Serial Number as the key. Can any of
you
ace
coders out there help me?

 

[Allen Browne]

Simplest solution would be to open the table in design view, and remove the
Default Value from each of the Number fields.

If you don't want to do that, you can specify a Null as the value to insert:
INSERT INTO MyTable ( Field1, Field2 )
SELECT Null As Field1, Null As Field2, ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen, Thanks to you I'm making progress! It is writing a record into the
Inventory table but is inserting some default values into the fields where
they want all the other fields to be blank. I'm trying to add the field
list
into the select portion of the statement and assign the text fields "" for
blank but am getting syntax errors again. This is very frustrating. Now
I
remember why I dodn't want to be a programmer when I grow up. Ha Ha. Some
are long integer fields. What would I assign them to? Here is the code
as
it stands now. I've put stuff in then cut it out trying to get it to work.

Private Sub Form_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO Inventory ( SerialNum, Barcode, [Domain], IP,
ServerName, BuildingID, Rack, MakeID, Model, OS, OSVersion, PL,
Environment,
CostFunctionID, Function, Area, Owner, SAUR, ServiceDate, Building, Room,
EntryPerson, OperatingSystem ) SELECT """ & Me.[SerialNum] & """ As
Expr1;"
DBEngine(0)(0).Execute strSql, dbFailOnError
End Sub

Ignore the line breaks. They are forced by the newsreader, and are not
intended. The "strSQL = " line ends with Expr1;"

The other fields will be null (unless you have set a DefaultValue for
them.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thank you so much for your reply. I forgot to mention that I am using
Access 2003. In the Access form (Equipment and Leases) code window,
the
code
below shows red starting with the &. I looked for help on the syntax
for
Insert into but it did not answer my questions.

Will I have to set each additional field to null or will it append the
record and do that for me? The only field I need to populate is
SerialNum.

Private Sub Form_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO Inventory ( SerialNum ) SELECT """ &
Me.[SerialNum]
&
""" As Expr1;"
DBEngine(0)(0).Execute strSql, dbFailOnError
End Sub

Thanks again for your help. I used to code a little but not in Visual
Basic
and have forgotten most of what I knew.


:

In the AfterUpdate event of the main form, execute an Append query
statement
to insert the value into the 3rd table.

Assuming Access 2000 or later, you will end up with something like
this:

Private Sub Form_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO table1 ( SerialNum ) SELECT """ &
Me.[SerialNum] &
""" As Expr1;"
dbEngine(0)(0).Execute strSql, dbFailOnError
End Sub

If you have problems with the SQL statement, mock up a query using any
literal value, change it to an Append query (Query menu), switch to
SQL
View
(View menu), and you have an example to copy.

I am inexperienced at Visual Basic coding and I have a task I need to
get
done quickly to a database that is in use daily. I have a form with
subform
based on the Equipment and Leases tables having a one to many
relationship. I
would like to add a block of code to the After Update event that
will
automatically append a new record to a third table, Inventory and
fill
in
just the key field, Serial Number. The Inventory table has a one to
one
relationship to Equipment with Serial Number as the key. Can any of
you
ace
coders out there help me?

 

[dnitz]

Allen,

I'm still battleing with syntax errors in this insert into statement. Can
you see anything wrong with the statement below?


Private Sub Form_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO Inventory ( SerialNum, Barcode, [Domain], IP,
ServerName, BuildingID, Rack, MakeID, Model, OS, OSVersion, PL, Environment,
CostFunctionID, Function, Area, Owner, SAUR, ServiceDate, Building,
EntryPerson, OperatingSystem ) SELECT """ & Me.[SerialNum], Null As Barcode,
Null As [Domain], Null As IP, Null As ServerName, Null As BuildingID, Null As
Rack, Null As MakeID, Null As Model, Null As OS, Null As OSVersion, Null As
PL, Null As Environment, Null As CostFunctionID, Null As Function, Null As
Area, Null As.Owner, Null As SAUR, Null As ServiceDate, Null As Building,
Null As EntryPerson, Null As OperatingSystem & """ As Expr1;"
DBEngine(0)(0).Execute strSql, dbFailOnError
End Sub

Simplest solution would be to open the table in design view, and remove the
Default Value from each of the Number fields.

If you don't want to do that, you can specify a Null as the value to insert:
INSERT INTO MyTable ( Field1, Field2 )
SELECT Null As Field1, Null As Field2, ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen, Thanks to you I'm making progress! It is writing a record into the
Inventory table but is inserting some default values into the fields where
they want all the other fields to be blank. I'm trying to add the field
list
into the select portion of the statement and assign the text fields "" for
blank but am getting syntax errors again. This is very frustrating. Now
I
remember why I dodn't want to be a programmer when I grow up. Ha Ha. Some
are long integer fields. What would I assign them to? Here is the code
as
it stands now. I've put stuff in then cut it out trying to get it to work.

Private Sub Form_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO Inventory ( SerialNum, Barcode, [Domain], IP,
ServerName, BuildingID, Rack, MakeID, Model, OS, OSVersion, PL,
Environment,
CostFunctionID, Function, Area, Owner, SAUR, ServiceDate, Building, Room,
EntryPerson, OperatingSystem ) SELECT """ & Me.[SerialNum] & """ As
Expr1;"
DBEngine(0)(0).Execute strSql, dbFailOnError
End Sub

Ignore the line breaks. They are forced by the newsreader, and are not
intended. The "strSQL = " line ends with Expr1;"

The other fields will be null (unless you have set a DefaultValue for
them.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


Thank you so much for your reply. I forgot to mention that I am using
Access 2003. In the Access form (Equipment and Leases) code window,
the
code
below shows red starting with the &. I looked for help on the syntax
for
Insert into but it did not answer my questions.

Will I have to set each additional field to null or will it append the
record and do that for me? The only field I need to populate is
SerialNum.

Private Sub Form_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO Inventory ( SerialNum ) SELECT """ &
Me.[SerialNum]
&
""" As Expr1;"
DBEngine(0)(0).Execute strSql, dbFailOnError
End Sub

Thanks again for your help. I used to code a little but not in Visual
Basic
and have forgotten most of what I knew.


:

In the AfterUpdate event of the main form, execute an Append query
statement
to insert the value into the 3rd table.

Assuming Access 2000 or later, you will end up with something like
this:

Private Sub Form_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO table1 ( SerialNum ) SELECT """ &
Me.[SerialNum] &
""" As Expr1;"
dbEngine(0)(0).Execute strSql, dbFailOnError
End Sub

If you have problems with the SQL statement, mock up a query using any
literal value, change it to an Append query (Query menu), switch to
SQL
View
(View menu), and you have an example to copy.

I am inexperienced at Visual Basic coding and I have a task I need to
get
done quickly to a database that is in use daily. I have a form with
subform
based on the Equipment and Leases tables having a one to many
relationship. I
would like to add a block of code to the After Update event that
will
automatically append a new record to a third table, Inventory and
fill
in
just the key field, Serial Number. The Inventory table has a one to
one
relationship to Equipment with Serial Number as the key. Can any of
you
ace
coders out there help me?

 

[Allen Browne]

Add the line:
Debug.Print strSql

When it fails, open the Immediate Window (Ctrl+G). You will see that the
quotes don't make sense, i.e. you have quotes around a whole bunch of text
which all becomes one big long string that Access will try to write into the
SerialNum field, and it then has no value to assign to the other fields.

Once you get the statement nearly right, you can run it, and copy the
resultant string from the Immediate Window to the clipboard. Then create a
new query (no table), switch to SQL View (View menu from query design), and
paste. That window may be able to help you identify whats' wrong.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I'm still battleing with syntax errors in this insert into statement. Can
you see anything wrong with the statement below?


Private Sub Form_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO Inventory ( SerialNum, Barcode, [Domain], IP,
ServerName, BuildingID, Rack, MakeID, Model, OS, OSVersion, PL,
Environment,
CostFunctionID, Function, Area, Owner, SAUR, ServiceDate, Building,
EntryPerson, OperatingSystem ) SELECT """ & Me.[SerialNum], Null As
Barcode,
Null As [Domain], Null As IP, Null As ServerName, Null As BuildingID, Null
As
Rack, Null As MakeID, Null As Model, Null As OS, Null As OSVersion, Null
As
PL, Null As Environment, Null As CostFunctionID, Null As Function, Null As
Area, Null As.Owner, Null As SAUR, Null As ServiceDate, Null As Building,
Null As EntryPerson, Null As OperatingSystem & """ As Expr1;"
DBEngine(0)(0).Execute strSql, dbFailOnError
End Sub

Simplest solution would be to open the table in design view, and remove
the
Default Value from each of the Number fields.

If you don't want to do that, you can specify a Null as the value to
insert:
INSERT INTO MyTable ( Field1, Field2 )
SELECT Null As Field1, Null As Field2, ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen, Thanks to you I'm making progress! It is writing a record into
the
Inventory table but is inserting some default values into the fields
where
they want all the other fields to be blank. I'm trying to add the
field
list
into the select portion of the statement and assign the text fields ""
for
blank but am getting syntax errors again. This is very frustrating.
Now
I
remember why I dodn't want to be a programmer when I grow up. Ha Ha.
Some
are long integer fields. What would I assign them to? Here is the
code
as
it stands now. I've put stuff in then cut it out trying to get it to
work.

Private Sub Form_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO Inventory ( SerialNum, Barcode, [Domain], IP,
ServerName, BuildingID, Rack, MakeID, Model, OS, OSVersion, PL,
Environment,
CostFunctionID, Function, Area, Owner, SAUR, ServiceDate, Building,
Room,
EntryPerson, OperatingSystem ) SELECT """ & Me.[SerialNum] & """ As
Expr1;"
DBEngine(0)(0).Execute strSql, dbFailOnError
End Sub

:

Ignore the line breaks. They are forced by the newsreader, and are not
intended. The "strSQL = " line ends with Expr1;"

The other fields will be null (unless you have set a DefaultValue for
them.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


Thank you so much for your reply. I forgot to mention that I am
using
Access 2003. In the Access form (Equipment and Leases) code window,
the
code
below shows red starting with the &. I looked for help on the
syntax
for
Insert into but it did not answer my questions.

Will I have to set each additional field to null or will it append
the
record and do that for me? The only field I need to populate is
SerialNum.

Private Sub Form_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO Inventory ( SerialNum ) SELECT """ &
Me.[SerialNum]
&
""" As Expr1;"
DBEngine(0)(0).Execute strSql, dbFailOnError
End Sub

Thanks again for your help. I used to code a little but not in
Visual
Basic
and have forgotten most of what I knew.


:

In the AfterUpdate event of the main form, execute an Append query
statement
to insert the value into the 3rd table.

Assuming Access 2000 or later, you will end up with something like
this:

Private Sub Form_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO table1 ( SerialNum ) SELECT """ &
Me.[SerialNum] &
""" As Expr1;"
dbEngine(0)(0).Execute strSql, dbFailOnError
End Sub

If you have problems with the SQL statement, mock up a query using
any
literal value, change it to an Append query (Query menu), switch to
SQL
View
(View menu), and you have an example to copy.

I am inexperienced at Visual Basic coding and I have a task I need
to
get
done quickly to a database that is in use daily. I have a form
with
subform
based on the Equipment and Leases tables having a one to many
relationship. I
would like to add a block of code to the After Update event that
will
automatically append a new record to a third table, Inventory and
fill
in
just the key field, Serial Number. The Inventory table has a one
to
one
relationship to Equipment with Serial Number as the key. Can any
of
you
ace
coders out there help me?

 

[dnitz]

Allen,

I tried to code this using Execute StrSQL but could not get it to work.
I explored another route as shown below and it is doing everything I needed.

Private Sub Form_AfterUpdate()
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim NewSerialNum As String
If Me.[Description] = "Server" Then
NewSerialNum = Me.[SerialNum]
Set Db = CurrentDb
Set rs = Db.OpenRecordset("Inventory", dbOpenDynaset)
rs.AddNew
rs![SerialNum] = NewSerialNum
rs![BuildingID] = Null
rs![MakeID] = Null
rs![OS] = Null
rs![PL] = "L"
rs![Environment] = Null
rs![CostFunctionID] = Null
rs.Update
End If
End Sub

Add the line:
Debug.Print strSql

When it fails, open the Immediate Window (Ctrl+G). You will see that the
quotes don't make sense, i.e. you have quotes around a whole bunch of text
which all becomes one big long string that Access will try to write into the
SerialNum field, and it then has no value to assign to the other fields.

Once you get the statement nearly right, you can run it, and copy the
resultant string from the Immediate Window to the clipboard. Then create a
new query (no table), switch to SQL View (View menu from query design), and
paste. That window may be able to help you identify whats' wrong.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Allen,

I'm still battleing with syntax errors in this insert into statement. Can
you see anything wrong with the statement below?


Private Sub Form_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO Inventory ( SerialNum, Barcode, [Domain], IP,
ServerName, BuildingID, Rack, MakeID, Model, OS, OSVersion, PL,
Environment,
CostFunctionID, Function, Area, Owner, SAUR, ServiceDate, Building,
EntryPerson, OperatingSystem ) SELECT """ & Me.[SerialNum], Null As
Barcode,
Null As [Domain], Null As IP, Null As ServerName, Null As BuildingID, Null
As
Rack, Null As MakeID, Null As Model, Null As OS, Null As OSVersion, Null
As
PL, Null As Environment, Null As CostFunctionID, Null As Function, Null As
Area, Null As.Owner, Null As SAUR, Null As ServiceDate, Null As Building,
Null As EntryPerson, Null As OperatingSystem & """ As Expr1;"
DBEngine(0)(0).Execute strSql, dbFailOnError
End Sub

Simplest solution would be to open the table in design view, and remove
the
Default Value from each of the Number fields.

If you don't want to do that, you can specify a Null as the value to
insert:
INSERT INTO MyTable ( Field1, Field2 )
SELECT Null As Field1, Null As Field2, ...

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Allen, Thanks to you I'm making progress! It is writing a record into
the
Inventory table but is inserting some default values into the fields
where
they want all the other fields to be blank. I'm trying to add the
field
list
into the select portion of the statement and assign the text fields ""
for
blank but am getting syntax errors again. This is very frustrating.
Now
I
remember why I dodn't want to be a programmer when I grow up. Ha Ha.
Some
are long integer fields. What would I assign them to? Here is the
code
as
it stands now. I've put stuff in then cut it out trying to get it to
work.

Private Sub Form_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO Inventory ( SerialNum, Barcode, [Domain], IP,
ServerName, BuildingID, Rack, MakeID, Model, OS, OSVersion, PL,
Environment,
CostFunctionID, Function, Area, Owner, SAUR, ServiceDate, Building,
Room,
EntryPerson, OperatingSystem ) SELECT """ & Me.[SerialNum] & """ As
Expr1;"
DBEngine(0)(0).Execute strSql, dbFailOnError
End Sub

:

Ignore the line breaks. They are forced by the newsreader, and are not
intended. The "strSQL = " line ends with Expr1;"

The other fields will be null (unless you have set a DefaultValue for
them.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.


Thank you so much for your reply. I forgot to mention that I am
using
Access 2003. In the Access form (Equipment and Leases) code window,
the
code
below shows red starting with the &. I looked for help on the
syntax
for
Insert into but it did not answer my questions.

Will I have to set each additional field to null or will it append
the
record and do that for me? The only field I need to populate is
SerialNum.

Private Sub Form_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO Inventory ( SerialNum ) SELECT """ &
Me.[SerialNum]
&
""" As Expr1;"
DBEngine(0)(0).Execute strSql, dbFailOnError
End Sub

Thanks again for your help. I used to code a little but not in
Visual
Basic
and have forgotten most of what I knew.


:

In the AfterUpdate event of the main form, execute an Append query
statement
to insert the value into the 3rd table.

Assuming Access 2000 or later, you will end up with something like
this:

Private Sub Form_AfterUpdate()
Dim strSql As String
strSql = "INSERT INTO table1 ( SerialNum ) SELECT """ &
Me.[SerialNum] &
""" As Expr1;"
dbEngine(0)(0).Execute strSql, dbFailOnError
End Sub

If you have problems with the SQL statement, mock up a query using
any
literal value, change it to an Append query (Query menu), switch to
SQL
View
(View menu), and you have an example to copy.

I am inexperienced at Visual Basic coding and I have a task I need
to
get
done quickly to a database that is in use daily. I have a form
with
subform
based on the Equipment and Leases tables having a one to many
relationship. I
would like to add a block of code to the After Update event that
will
automatically append a new record to a third table, Inventory and
fill
in
just the key field, Serial Number. The Inventory table has a one
to
one
relationship to Equipment with Serial Number as the key. Can any
of
you
ace
coders out there help me?