Help needed with adding a new record

[Guest]

I know it isn't best practice to place the same message on multiple boards
but I haven't had a response so apologies in advance.

I have tried using the "duplicate record" option with the command button but
it doesn't quite do what I need.

I have tried using an append query but if I use the DISTINCT command, it
will create as many rows as there are unique records which I obviously don't
want.

What I need is to press a button that will add one new record with a date
that adds seven days from the previous record.

At the moment, the button initiates a macro that will put focus on the date,
sort it and then go to the last record.

Now I need the last bit.

cheers.

 

[Guest]

An append query is the way to go here. The trick is that you have to put the
values you want to append in the Field row of the query and the fields you
want to append them to in the Append row. This means you don't need to have a
table loaded into the upper portion of the query designer. If you are
appending values based on what's displayed on your form, the Field row values
should map to controls on your form. For the Date+7 value, you'll need the
DateAdd function.

Barry

 

[Guest]

I will give it a go. Thanks

An append query is the way to go here. The trick is that you have to put the
values you want to append in the Field row of the query and the fields you
want to append them to in the Append row. This means you don't need to have a
table loaded into the upper portion of the query designer. If you are
appending values based on what's displayed on your form, the Field row values
should map to controls on your form. For the Date+7 value, you'll need the
DateAdd function.

Barry

 

[Guest]

How does the DateAdd function work?

at the moment I have:

Expr1: DateAdd("Short Date",0,[Department]![WeekID]+7)

How do I use the previous record to calculate the new date?

cheers

 

[Guest]

DateAdd(interval, number, date)

In your case:
DateAdd("d", 7, [Department]![WeekID])

Find more details here: http://msdn2.microsoft.com/en-us/library/cb7z8yf9.aspx

Barry

How does the DateAdd function work?

at the moment I have:

Expr1: DateAdd("Short Date",0,[Department]![WeekID]+7)

How do I use the previous record to calculate the new date?

cheers

An append query is the way to go here. The trick is that you have to put the
values you want to append in the Field row of the query and the fields you
want to append them to in the Append row. This means you don't need to have a
table loaded into the upper portion of the query designer. If you are
appending values based on what's displayed on your form, the Field row values
should map to controls on your form. For the Date+7 value, you'll need the
DateAdd function.

Barry

 

[Guest]

Your query has no Where clause, so it will insert one record for every record
that it finds in the employees table. The DMax function you're suing does
find the maximum weekid, but this just puts that value in every record you're
appending.

You have two challenges here. One is finding the last record in the table,
the one you want to mostly duplicate. The other is insert a duplicate of this
record with the date column incremented. If the record you want to duplicate
is always the one you're displaying on the form, it's a little easier. You
could write a bit of code like this:

With Me.RecordsetClone
!EmployeeId = Me.txtEmployeeId
!WeekId = DateAdd("d", 7, Me.txtWeekId)
!Department = Me.txtDepartment
' ...etc
.Update
End With

This writes a new record to the form's underlying table based on values
currently displayed in the form's controls.

If you don't have the luxury of basing the insert on the form's current
record, it'll involve either more code or a slightly trickier query.

Barry

Hello,

I am not sure whether your suggestion would work. Based on what I did
before, I have the following which does work:

Format(DMax("WeekID","Department")+7,"Short Date")

I have inserted the query into the macro which is started by the button. It
doesn't work when it says it will append 1081 rows! Obviously I only want to
add one row for the current employee. It did work before. The SQL is

INSERT INTO Department ( EmployeeID, WeekID, Dept, Subdept, Costcentre,
Rate, Contracthrs, timehalfhrs, doublehrs )
SELECT Department.EmployeeID, Format(DMax("WeekID","Department")+7,"Short
Date") AS Expr1, Department.Dept, Department.Subdept, Department.Costcentre,
Department.Rate, 0 AS Expr2, 0 AS Expr3, 0 AS Expr4
FROM Employee INNER JOIN Department ON Employee.EmployeeID =
Department.EmployeeID;

Im almost there!

DateAdd(interval, number, date)

In your case:
DateAdd("d", 7, [Department]![WeekID])

Find more details here: http://msdn2.microsoft.com/en-us/library/cb7z8yf9.aspx

Barry

How does the DateAdd function work?

at the moment I have:

Expr1: DateAdd("Short Date",0,[Department]![WeekID]+7)

How do I use the previous record to calculate the new date?

cheers

:

An append query is the way to go here. The trick is that you have to put the
values you want to append in the Field row of the query and the fields you
want to append them to in the Append row. This means you don't need to have a
table loaded into the upper portion of the query designer. If you are
appending values based on what's displayed on your form, the Field row values
should map to controls on your form. For the Date+7 value, you'll need the
DateAdd function.

Barry

:

I know it isn't best practice to place the same message on multiple boards
but I haven't had a response so apologies in advance.

I have tried using the "duplicate record" option with the command button but
it doesn't quite do what I need.

I have tried using an append query but if I use the DISTINCT command, it
will create as many rows as there are unique records which I obviously don't
want.

What I need is to press a button that will add one new record with a date
that adds seven days from the previous record.

At the moment, the button initiates a macro that will put focus on the date,
sort it and then go to the last record.

Now I need the last bit.

cheers.

 

[Guest]

Hello,

I am not sure whether your suggestion would work. Based on what I did
before, I have the following which does work:

Format(DMax("WeekID","Department")+7,"Short Date")

I have inserted the query into the macro which is started by the button. It
doesn't work when it says it will append 1081 rows! Obviously I only want to
add one row for the current employee. It did work before. The SQL is

INSERT INTO Department ( EmployeeID, WeekID, Dept, Subdept, Costcentre,
Rate, Contracthrs, timehalfhrs, doublehrs )
SELECT Department.EmployeeID, Format(DMax("WeekID","Department")+7,"Short
Date") AS Expr1, Department.Dept, Department.Subdept, Department.Costcentre,
Department.Rate, 0 AS Expr2, 0 AS Expr3, 0 AS Expr4
FROM Employee INNER JOIN Department ON Employee.EmployeeID =
Department.EmployeeID;

Im almost there!

DateAdd(interval, number, date)

In your case:
DateAdd("d", 7, [Department]![WeekID])

Find more details here: http://msdn2.microsoft.com/en-us/library/cb7z8yf9.aspx

Barry

How does the DateAdd function work?

at the moment I have:

Expr1: DateAdd("Short Date",0,[Department]![WeekID]+7)

How do I use the previous record to calculate the new date?

cheers

An append query is the way to go here. The trick is that you have to put the
values you want to append in the Field row of the query and the fields you
want to append them to in the Append row. This means you don't need to have a
table loaded into the upper portion of the query designer. If you are
appending values based on what's displayed on your form, the Field row values
should map to controls on your form. For the Date+7 value, you'll need the
DateAdd function.

Barry

:

I know it isn't best practice to place the same message on multiple boards
but I haven't had a response so apologies in advance.

I have tried using the "duplicate record" option with the command button but
it doesn't quite do what I need.

I have tried using an append query but if I use the DISTINCT command, it
will create as many rows as there are unique records which I obviously don't
want.

What I need is to press a button that will add one new record with a date
that adds seven days from the previous record.

At the moment, the button initiates a macro that will put focus on the date,
sort it and then go to the last record.

Now I need the last bit.

cheers.

 

[Guest]

I have a macro that runs when the form opens and it goes to the WeekID in the
subform, sorts its and then goes to the last record so the focus is on the
most recent date.

As far as the WHERE clause goes I've tried adding an extra form control to
replicate the employeeID and then using that to do the comparison with the
main form employeeID but that doesn't append any rows at all.

I am not an expert in programming but I have previously managed to append 1
row to a current record. I know there is something I am missing but I can't
figure it out.

Your query has no Where clause, so it will insert one record for every record
that it finds in the employees table. The DMax function you're suing does
find the maximum weekid, but this just puts that value in every record you're
appending.

You have two challenges here. One is finding the last record in the table,
the one you want to mostly duplicate. The other is insert a duplicate of this
record with the date column incremented. If the record you want to duplicate
is always the one you're displaying on the form, it's a little easier. You
could write a bit of code like this:

With Me.RecordsetClone
!EmployeeId = Me.txtEmployeeId
!WeekId = DateAdd("d", 7, Me.txtWeekId)
!Department = Me.txtDepartment
' ...etc
.Update
End With

This writes a new record to the form's underlying table based on values
currently displayed in the form's controls.

If you don't have the luxury of basing the insert on the form's current
record, it'll involve either more code or a slightly trickier query.

Barry

Hello,

I am not sure whether your suggestion would work. Based on what I did
before, I have the following which does work:

Format(DMax("WeekID","Department")+7,"Short Date")

I have inserted the query into the macro which is started by the button. It
doesn't work when it says it will append 1081 rows! Obviously I only want to
add one row for the current employee. It did work before. The SQL is

INSERT INTO Department ( EmployeeID, WeekID, Dept, Subdept, Costcentre,
Rate, Contracthrs, timehalfhrs, doublehrs )
SELECT Department.EmployeeID, Format(DMax("WeekID","Department")+7,"Short
Date") AS Expr1, Department.Dept, Department.Subdept, Department.Costcentre,
Department.Rate, 0 AS Expr2, 0 AS Expr3, 0 AS Expr4
FROM Employee INNER JOIN Department ON Employee.EmployeeID =
Department.EmployeeID;

Im almost there!

DateAdd(interval, number, date)

In your case:
DateAdd("d", 7, [Department]![WeekID])

Find more details here: http://msdn2.microsoft.com/en-us/library/cb7z8yf9.aspx

Barry

:

How does the DateAdd function work?

at the moment I have:

Expr1: DateAdd("Short Date",0,[Department]![WeekID]+7)

How do I use the previous record to calculate the new date?

cheers

:

An append query is the way to go here. The trick is that you have to put the
values you want to append in the Field row of the query and the fields you
want to append them to in the Append row. This means you don't need to have a
table loaded into the upper portion of the query designer. If you are
appending values based on what's displayed on your form, the Field row values
should map to controls on your form. For the Date+7 value, you'll need the
DateAdd function.

Barry

:

I know it isn't best practice to place the same message on multiple boards
but I haven't had a response so apologies in advance.

I have tried using the "duplicate record" option with the command button but
it doesn't quite do what I need.

I have tried using an append query but if I use the DISTINCT command, it
will create as many rows as there are unique records which I obviously don't
want.

What I need is to press a button that will add one new record with a date
that adds seven days from the previous record.

At the moment, the button initiates a macro that will put focus on the date,
sort it and then go to the last record.

Now I need the last bit.

cheers.

 

[Guest]

I have a macro that runs when the form opens and it goes to the WeekID in the
subform, sorts its and then goes to the last record so the focus is on the
most recent date.

This answers my question. You are already on the last record. This is good.
Now you just need to try the bit of code I suggested.

Barry

 

[Guest]

Where do I put this code?

This answers my question. You are already on the last record. This is good.
Now you just need to try the bit of code I suggested.

Barry

 

[Guest]

What about numerical values? Would this be correct:

With Me.RecordsetClone
!EmployeeId = Me.txtEmployeeId
!WeekId = DateAdd("d", 7, Me.txtWeekId)
!Department = Me.txtDept
!Subdept = Me.txtSubdept
!Costcentre = Me.txtCostCentre
!Rate = Me.txtRate
!Contracthrs = 0
!timehalfhrs = 0
!doublehrs = 0
.Update
End With

 

[Guest]

You mentioned early on that you want this record to be inserted when you
click a button. You should put it in the click event of that button. Select
the button in design view, click the elipsis to the right of the On Click
event, select Code Builder, and paste this into that procedure. You'll need
to flesh the code out to accomodate all your table fields.

Barry

 

[Guest]

So do I need an append query at all?

You mentioned early on that you want this record to be inserted when you
click a button. You should put it in the click event of that button. Select
the button in design view, click the elipsis to the right of the On Click
event, select Code Builder, and paste this into that procedure. You'll need
to flesh the code out to accomodate all your table fields.

Barry

 

[Guest]

That should work.

What about numerical values? Would this be correct:

With Me.RecordsetClone
!EmployeeId = Me.txtEmployeeId
!WeekId = DateAdd("d", 7, Me.txtWeekId)
!Department = Me.txtDept
!Subdept = Me.txtSubdept
!Costcentre = Me.txtCostCentre
!Rate = Me.txtRate
!Contracthrs = 0
!timehalfhrs = 0
!doublehrs = 0
.Update
End With

 

[Guest]

Thanks very much for your help but I do have another problem.

When I first opened the form the focus would go to the last record. I've
changed it to go to a new record. When I click the button I get a "3020"
runtime error which says "Updated or Cancel Update without AddNew or Edit".
The ".update" is highlighted in the code.

 

[Guest]

I've just discovered if I go to a new record, the event procedure has nothing
to update. So I need to duplicate the previous record and then update it at
the same time.

 

[Guest]

Oops. My mistake. You need to add .AddNew after With Me.RecordSetClone.

Barry