action on a button to create a new record

[scubadiver]

One of the fields in one of my tables is not on the form (due to reasons of
sensitivity). If I use a "duplicate record" button it adds new data for what
is in the form but the field that is not on the form reverts back to zero.

How can I create a macro action on a button to replicate the unseen field?

 

[scubadiver]

What I forgot to add is that it would be nice that the new date can be the
last date + 7 days.

 

[Steve Schapel]

Scuba,

The outcome of your "duplicate record" button shouldn't make any
difference whether a field is represented on the form or not. So this
is puzzling. Can you please post back with details of the query being
run by the macro?

 

[Steve Schapel]

Scuba,

This should be ok... Are you experiencing a problem with this? Can you
give any more information about what you have tried so far?

 

[scubadiver]

that what I thought should happen but it doesn't. I haven't written a macro.

I have a duplicate record button that I created using the wizard. and it
does what I describe below. I checked the new entry in the table and there is
a zero when it should have 12.28.

Does it make any difference if (a) I inserted the field after the original
table was created (I have made some modifications) and (b) The default value
for the field is zero.

 

[Steve Schapel]

Scuba,

I was not familiar with the wizard, but I have just tried it out. It
does not use a macro, it creates some rubbish VBA code. I would
recommend to not use the wizard. You should make an Append Query to
return exactly the data you want for your duplicated record. Then, if
you want to use a macro to automate the process, you would use a
OpenQuery action to run the append.

 

[scubadiver]

I do agree with you about the VBA. It is a bit poor.

I did create an append query anyway because I wanted to add an extra record
for every employer and it does automatically change the date.

The users got confused because they were seeing records they thought
shouldn't have been there

I will just adapt it for each individual employee.

 

[Steve Schapel]

Ok, great.

 

[scubadiver]

I deleted the append query off the database but I managed to find it again
on this forum board when I posted the original question:

INSERT INTO Table2 ( EmployeeID, [Date], standardhrs, timehalf, doubletime )
SELECT DISTINCT [Table2].[EmployeeID], DMax("[Date]","Table2")+7,
[Table2].[standardhrs], [Table2].[timehalf], [Table2].[doubletime]
FROM Table1 INNER JOIN Table2 ON [Table1].[EmployeeID]=[Table2].[EmployeeID];

A couple of questions:

1) If I use the button to run this query within the subform do I need the
"FROM" statement?

2) When it duplicates the previous record it obviously duplicates all the
information. How do I set the fields "standardhrs", "timehalf", "doubletime"
to zero in the new record when the query is run?

thanks

 

[Steve Schapel]

Scuba,

If I understand you, I think it will be like this...
INSERT INTO Table2 ( EmployeeID, [Date], standardhrs, timehalf,
doubletime )
SELECT Table2.[EmployeeID], DMax("[Date]","Table2")+7, 0 As SH, 0 As
HH, 0 As DH
FROM Table2
WHERE EmployeeID = Forms!NameOfYourForm!EmployeeID

Or, if the standardhrs, timehalf, and doubletime fields have their
Default Value set to 0 in the table design, you don't need to specify, so...
INSERT INTO Table2 ( EmployeeID, [Date] )
SELECT Table2.[EmployeeID], DMax("[Date]","Table2")+7
FROM Table2
WHERE EmployeeID = Forms!NameOfYourForm!EmployeeID

Alternatively...
INSERT INTO Table2 ( EmployeeID, [Date] )
SELECT Table2.[EmployeeID], Max([Date])+7
FROM Table2
GROUP BY EmployeeID
HAVING EmployeeID = Forms!NameOfYourForm!EmployeeID

 

[scubadiver]

I will experiment.

Thanks

Scuba,

If I understand you, I think it will be like this...
INSERT INTO Table2 ( EmployeeID, [Date], standardhrs, timehalf,
doubletime )
SELECT Table2.[EmployeeID], DMax("[Date]","Table2")+7, 0 As SH, 0 As
HH, 0 As DH
FROM Table2
WHERE EmployeeID = Forms!NameOfYourForm!EmployeeID

Or, if the standardhrs, timehalf, and doubletime fields have their
Default Value set to 0 in the table design, you don't need to specify, so...
INSERT INTO Table2 ( EmployeeID, [Date] )
SELECT Table2.[EmployeeID], DMax("[Date]","Table2")+7
FROM Table2
WHERE EmployeeID = Forms!NameOfYourForm!EmployeeID

Alternatively...
INSERT INTO Table2 ( EmployeeID, [Date] )
SELECT Table2.[EmployeeID], Max([Date])+7
FROM Table2
GROUP BY EmployeeID
HAVING EmployeeID = Forms!NameOfYourForm!EmployeeID

--
Steve Schapel, Microsoft Access MVP

I deleted the append query off the database but I managed to find it again
on this forum board when I posted the original question:

INSERT INTO Table2 ( EmployeeID, [Date], standardhrs, timehalf, doubletime )
SELECT DISTINCT [Table2].[EmployeeID], DMax("[Date]","Table2")+7,
[Table2].[standardhrs], [Table2].[timehalf], [Table2].[doubletime]
FROM Table1 INNER JOIN Table2 ON [Table1].[EmployeeID]=[Table2].[EmployeeID];

A couple of questions:

1) If I use the button to run this query within the subform do I need the
"FROM" statement?

2) When it duplicates the previous record it obviously duplicates all the
information. How do I set the fields "standardhrs", "timehalf", "doubletime"
to zero in the new record when the query is run?

thanks