auto add new record

  • Thread starter Thread starter Heather
  • Start date Start date
H

Heather

Good morning

I have a form that when the user enter's data in the
[date] field in table [tblreferral], I need for a record
to be automatically generated in another table
[tblservice]. The new record would contain standard
data, such as field [servicecode] = 43, and field
[serviceid] which is an autonumber would create a new
autonumber.

I'm sure this is basic code that would go in the After
Update of [date] but I can't find a reference for it
anywhere.

Thank you!
 
Under the assumption that you cannot use a subform in the form that would do
this for you, try code like this on the AfterUpdate of the control that is
bound to date field (NOTE: Not a good idea to use date as the name of a
field or a control: see below for more info):


Private Sub NameOfControl_AfterUpdate()
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblservice", dbOpenDynaset, dbAppendOnly)
rst.AddNew
rst.Fields("ServiceCode").Value = 43
rst.Update
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Sub




NOTE: Also, don't use Date as a field name. It's a reserved word in ACCESS,
and ACCESS can become quite confused. See KB article number 286335 for more
info:

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335
 
Thanks for your help Ken, I would never have gotten that
code on my own.
I entered it just as you have written, and when I tab out
of the [txtDate] field (changed the name) I get a compile
error "user-defined type not defined". Any ideas?

Heather

-----Original Message-----
Under the assumption that you cannot use a subform in the form that would do
this for you, try code like this on the AfterUpdate of the control that is
bound to date field (NOTE: Not a good idea to use date as the name of a
field or a control: see below for more info):


Private Sub NameOfControl_AfterUpdate()
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblservice", dbOpenDynaset, dbAppendOnly)
rst.AddNew
rst.Fields("ServiceCode").Value = 43
rst.Update
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Sub




NOTE: Also, don't use Date as a field name. It's a reserved word in ACCESS,
and ACCESS can become quite confused. See KB article number 286335 for more
info:

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en- us;286335


--

Ken Snell
<MS ACCESS MVP>

Good morning

I have a form that when the user enter's data in the
[date] field in table [tblreferral], I need for a record
to be automatically generated in another table
[tblservice]. The new record would contain standard
data, such as field [servicecode] = 43, and field
[serviceid] which is an autonumber would create a new
autonumber.

I'm sure this is basic code that would go in the After
Update of [date] but I can't find a reference for it
anywhere.

Thank you!
Click to expand...


.
Click to expand...
 
Likely you're using ACCESS 2000 or 2002? You'll need to set a reference to
the DAO library.

Open Visual Basic Editor, and click on Tools | References. Select the DAO
library in the list.

If that doesn't fix the problem, when you tab from the textbox and the error
message comes up, click Debug in the message box and see which line of code
is highlighted in yellow. Post back with that info.

--

Ken Snell
<MS ACCESS MVP>

Heather said:
Thanks for your help Ken, I would never have gotten that
code on my own.
I entered it just as you have written, and when I tab out
of the [txtDate] field (changed the name) I get a compile
error "user-defined type not defined". Any ideas?

Heather

-----Original Message-----
Under the assumption that you cannot use a subform in the form that would do
this for you, try code like this on the AfterUpdate of the control that is
bound to date field (NOTE: Not a good idea to use date as the name of a
field or a control: see below for more info):


Private Sub NameOfControl_AfterUpdate()
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblservice", dbOpenDynaset, dbAppendOnly)
rst.AddNew
rst.Fields("ServiceCode").Value = 43
rst.Update
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Sub




NOTE: Also, don't use Date as a field name. It's a reserved word in ACCESS,
and ACCESS can become quite confused. See KB article number 286335 for more
info:

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en- us;286335


--

Ken Snell
<MS ACCESS MVP>

Good morning

I have a form that when the user enter's data in the
[date] field in table [tblreferral], I need for a record
to be automatically generated in another table
[tblservice]. The new record would contain standard
data, such as field [servicecode] = 43, and field
[serviceid] which is an autonumber would create a new
autonumber.

I'm sure this is basic code that would go in the After
Update of [date] but I can't find a reference for it
anywhere.

Thank you!
Click to expand...


.
Click to expand...
Click to expand...
 
Thanks Ken, the DAO was not set, now it is. One thing I
didn't mention (as I didn't think of) is that the new
service record requires the date and clientid as well.
How do I add the code that says "copy the same [txtDate]
and [ClientID] that have already been entered in
[frmReferral]?

I can't tell you how much I appreciate this, its probably
time for me to take a coding class or something :-)

Heather

-----Original Message-----
Likely you're using ACCESS 2000 or 2002? You'll need to set a reference to
the DAO library.

Open Visual Basic Editor, and click on Tools | References. Select the DAO
library in the list.

If that doesn't fix the problem, when you tab from the textbox and the error
message comes up, click Debug in the message box and see which line of code
is highlighted in yellow. Post back with that info.

--

Ken Snell
<MS ACCESS MVP>

Thanks for your help Ken, I would never have gotten that
code on my own.
I entered it just as you have written, and when I tab out
of the [txtDate] field (changed the name) I get a compile
error "user-defined type not defined". Any ideas?

Heather

-----Original Message-----
Under the assumption that you cannot use a subform in the form that would do
this for you, try code like this on the AfterUpdate of the control that is
bound to date field (NOTE: Not a good idea to use date as the name of a
field or a control: see below for more info):


Private Sub NameOfControl_AfterUpdate()
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblservice", dbOpenDynaset, dbAppendOnly)
rst.AddNew
rst.Fields("ServiceCode").Value = 43
rst.Update
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Sub




NOTE: Also, don't use Date as a field name. It's a reserved word in ACCESS,
and ACCESS can become quite confused. See KB article number 286335 for more
info:

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en- us;286335


--

Ken Snell
<MS ACCESS MVP>

"Heather" <[email protected]> wrote
Click to expand...
in
message
Good morning

I have a form that when the user enter's data in the
[date] field in table [tblreferral], I need for a record
to be automatically generated in another table
[tblservice]. The new record would contain standard
data, such as field [servicecode] = 43, and field
[serviceid] which is an autonumber would create a new
autonumber.

I'm sure this is basic code that would go in the After
Update of [date] but I can't find a reference for it
anywhere.

Thank you!


.
Click to expand...
Click to expand...


.
Click to expand...
 
You just add steps in the ".AddNew" section for those fields:

Private Sub NameOfControl_AfterUpdate()
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblservice", dbOpenDynaset, dbAppendOnly)
rst.AddNew
rst.Fields("ServiceCode").Value = 43
rst.Fields("NameOfDateField").Value = Me.txtDate.Value
rst.Fields("NameOfClientIDField").Value = Me.ClientID.Value
rst.Update
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Sub


--

Ken Snell
<MS ACCESS MVP>

Heather said:
Thanks Ken, the DAO was not set, now it is. One thing I
didn't mention (as I didn't think of) is that the new
service record requires the date and clientid as well.
How do I add the code that says "copy the same [txtDate]
and [ClientID] that have already been entered in
[frmReferral]?

I can't tell you how much I appreciate this, its probably
time for me to take a coding class or something :-)

Heather

-----Original Message-----
Likely you're using ACCESS 2000 or 2002? You'll need to set a reference to
the DAO library.

Open Visual Basic Editor, and click on Tools | References. Select the DAO
library in the list.

If that doesn't fix the problem, when you tab from the textbox and the error
message comes up, click Debug in the message box and see which line of code
is highlighted in yellow. Post back with that info.

--

Ken Snell
<MS ACCESS MVP>

Thanks for your help Ken, I would never have gotten that
code on my own.
I entered it just as you have written, and when I tab out
of the [txtDate] field (changed the name) I get a compile
error "user-defined type not defined". Any ideas?

Heather


-----Original Message-----
Under the assumption that you cannot use a subform in
the form that would do
this for you, try code like this on the AfterUpdate of
the control that is
bound to date field (NOTE: Not a good idea to use date
as the name of a
field or a control: see below for more info):


Private Sub NameOfControl_AfterUpdate()
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblservice",
dbOpenDynaset, dbAppendOnly)
rst.AddNew
rst.Fields("ServiceCode").Value = 43
rst.Update
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Sub




NOTE: Also, don't use Date as a field name. It's a
reserved word in ACCESS,
and ACCESS can become quite confused. See KB article
number 286335 for more
info:

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-
us;286335


--

Ken Snell
<MS ACCESS MVP>

message
Good morning

I have a form that when the user enter's data in the
[date] field in table [tblreferral], I need for a
record
to be automatically generated in another table
[tblservice]. The new record would contain standard
data, such as field [servicecode] = 43, and field
[serviceid] which is an autonumber would create a new
autonumber.

I'm sure this is basic code that would go in the After
Update of [date] but I can't find a reference for it
anywhere.

Thank you!


.
Click to expand...


.
Click to expand...
Click to expand...
 
two words.......YOU ROCK!

Thanks so much, you have changed the scope of my entire
weekend!

Heather

-----Original Message-----
You just add steps in the ".AddNew" section for those fields:

Private Sub NameOfControl_AfterUpdate()
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblservice", dbOpenDynaset, dbAppendOnly)
rst.AddNew
rst.Fields("ServiceCode").Value = 43
rst.Fields("NameOfDateField").Value = Me.txtDate.Value
rst.Fields("NameOfClientIDField").Value = Me.ClientID.Value
rst.Update
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Sub


--

Ken Snell
<MS ACCESS MVP>

Thanks Ken, the DAO was not set, now it is. One thing I
didn't mention (as I didn't think of) is that the new
service record requires the date and clientid as well.
How do I add the code that says "copy the same [txtDate]
and [ClientID] that have already been entered in
[frmReferral]?

I can't tell you how much I appreciate this, its probably
time for me to take a coding class or something :-)

Heather

-----Original Message-----
Likely you're using ACCESS 2000 or 2002? You'll need
Click to expand...
to
set a reference to
the DAO library.

Open Visual Basic Editor, and click on Tools | References. Select the DAO
library in the list.

If that doesn't fix the problem, when you tab from the textbox and the error
message comes up, click Debug in the message box and
Click to expand...
see
which line of code
is highlighted in yellow. Post back with that info.

--

Ken Snell
<MS ACCESS MVP>

"Heather" <[email protected]> wrote
Click to expand...
in
message
Thanks for your help Ken, I would never have gotten that
code on my own.
I entered it just as you have written, and when I
Click to expand...
tab
out
of the [txtDate] field (changed the name) I get a compile
error "user-defined type not defined". Any ideas?

Heather


-----Original Message-----
Under the assumption that you cannot use a subform in
the form that would do
this for you, try code like this on the AfterUpdate of
the control that is
bound to date field (NOTE: Not a good idea to use date
as the name of a
field or a control: see below for more info):


Private Sub NameOfControl_AfterUpdate()
Dim rst As DAO.Recordset
Dim dbs As DAO.Database
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("tblservice",
dbOpenDynaset, dbAppendOnly)
rst.AddNew
rst.Fields("ServiceCode").Value = 43
rst.Update
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing
End Sub




NOTE: Also, don't use Date as a field name. It's a
reserved word in ACCESS,
and ACCESS can become quite confused. See KB article
number 286335 for more
info:

ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx? scid=kb;en-
us;286335


--

Ken Snell
<MS ACCESS MVP>

"Heather" <[email protected]>
Click to expand...
wrote
in
message
Good morning

I have a form that when the user enter's data in the
[date] field in table [tblreferral], I need for a
record
to be automatically generated in another table
[tblservice]. The new record would contain standard
data, such as field [servicecode] = 43, and field
[serviceid] which is an autonumber would create a new
autonumber.

I'm sure this is basic code that would go in the After
Update of [date] but I can't find a reference for it
anywhere.

Thank you!


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top