Problem adding a new record.

G

Guest

I was given this code to allow me to add a new record and add seven days
from the previous record.

With Me.RecordsetClone
.AddNew
EmployeeID = Me.EmployeeID
WeekID = DateAdd("d", 7, Me.WeekID)
Department = Me.Dept
Subdept = Me.Subdept
Costcentre = Me.Costcentre
Rate = Me.Rate
Contracthrs = 0
timehalfhrs = 0
doublehrs = 0
.Update
End With

Instead of creating a new record, it updates the most recent record.

I am having a problem with the ".update" line. It says I have a "3201"
run-time error:

"You cannot add or change a record becasue a related record is required in
table 'employee' "

When I add a record normally, it works fine.

Any ideas,

cheers in advance.
 
E

e.mel

scubadiver said:
I was given this code to allow me to add a new record and add seven days
from the previous record.

With Me.RecordsetClone
.AddNew
EmployeeID = Me.EmployeeID
WeekID = DateAdd("d", 7, Me.WeekID)
Department = Me.Dept
Subdept = Me.Subdept
Costcentre = Me.Costcentre
Rate = Me.Rate
Contracthrs = 0
timehalfhrs = 0
doublehrs = 0
.Update
End With

Instead of creating a new record, it updates the most recent record.

I am having a problem with the ".update" line. It says I have a "3201"
run-time error:

"You cannot add or change a record becasue a related record is required in
table 'employee' "

When I add a record normally, it works fine.

Any ideas,

cheers in advance.
Click to expand...


you can start by adding a few dots . . .

.EmployeeID = Me.EmployeeID
.WeekID = DateAdd("d", 7, Me.WeekID)
.Department = Me.Dept
....and so on....

then the data will be sent to Me.RecordsetClone.EmployeeID rather than
just EmployeeID

and the error message is probably because your adding a new record but
not putting the EmployeeID (a required field?) into it, it's being sent
to the current record.
 
G

Gina Whipp

ScubaDiver,

I use that same code to copy an existing record to a new record (with an
additonal line after .Update that reads '.Bookmark = .LastModified'. Are
you on a new record when you attempt to run this code because if memory
serves me there is nothing to copy->add new to?

HTH,
Gina
 
G

Guest

I am currently on the last record when I try to run the code. Should I be on
a new record?

Also, I get the following message: "object doesn't support this property or
method"
with a 438 run-time error.
 
G

Gina Whipp

No you should be on the last record but I spotted something I didn't spot
before. Fix your code to reflect the below (notice the '!' (bang) added
before the table field names.



With Me.RecordsetClone
.AddNew
!EmployeeID = Me.EmployeeID
!WeekID = DateAdd("d", 7, Me.WeekID)
!Department = Me.Dept
!Subdept = Me.Subdept
!Costcentre = Me.Costcentre
!Rate = Me.Rate
!Contracthrs = 0
!timehalfhrs = 0
!doublehrs = 0
.Update
End With
Also, is this the main form or is there a subform and is that where you are
trying to add the record?
 
G

Guest

It is a subform, but the button I have which activates the event procedure is
also within the same subform.

thanks
 
G

Gina Whipp

Did you try the below? If it didn't work let me know (I don't think it will
work)...
 
G

Gina Whipp

ScubaDiver,

If you are just copy/appending a record you can use:

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 'Paste Append
DoCmd.GoToRecord , , acLast
DoCmd.GoToControl "YourControlName" 'Just someplace to start the tab
order on the subform not needed.

But if you need information from the main form AND the subform in the new
record Allen Browne explains how to do this quite well on his page:
http://allenbrowne.com/ser-57.html

HTH,
Gina
 
G

Guest

It works great, thanks very much.

Now what I would like to do is send the cursor to a particular field in the
new record.

I have the "GoToControl" and "GoToRecord" commands in a macro for when I
open the database. Can I put these in the event procedure after this code or
shall I create a similar macro and use the "after update" event.
 
G

Guest

I will try the "after insert" event

scubadiver said:
It works great, thanks very much.

Now what I would like to do is send the cursor to a particular field in the
new record.

I have the "GoToControl" and "GoToRecord" commands in a macro for when I
open the database. Can I put these in the event procedure after this code or
shall I create a similar macro and use the "after update" event.
Click to expand...
 
G

Guest

Once the button is clicked, I lose the cursor. How do I bring it back without
clicking on the field?
 
G

Gina Whipp

Place

DoCmd.GoToControl "YourControlName"

at the end of the code after the End If...
 
G

Guest

Hi,

thanks for that. It will set the focus to the desired field for the most
recent record, not the new record.

I found this in another thread:

DoCmd.GoToRecord , , acLast

It sends the cursor to the field in the new record!! :)

Thanks for your help.
 
G

Gina Whipp

Glad I could help!


scubadiver said:
Hi,

thanks for that. It will set the focus to the desired field for the most
recent record, not the new record.

I found this in another thread:

DoCmd.GoToRecord , , acLast

It sends the cursor to the field in the new record!! :)

Thanks for your help.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

replicating a record: quick query. 1
replicating a record: quick query 1
New record on subform 3
Duplicate record in Form and Subform 0
Allen Browne's Duplicate Record Code 7
Cloning a record 2
Problems adding new record from form / subform 6
Problem adding a new record to a subform 1

Top