|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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?
|
|
What I forgot to add is that it would be nice that the new date can be the
last date + 7 days.
|
|
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, Microsoft Access MVP
scubadiver wrote:
[Quoted Text] > 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?
|
|
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?
--
Steve Schapel, Microsoft Access MVP
scubadiver wrote:
[Quoted Text] > What I forgot to add is that it would be nice that the new date can be the
> last date + 7 days.
|
|
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" wrote:
[Quoted Text] > 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, Microsoft Access MVP
>
> scubadiver wrote:
> > 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?
>
|
|
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.
--
Steve Schapel, Microsoft Access MVP
scubadiver wrote:
[Quoted Text] > 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.
|
|
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" wrote:
[Quoted Text] > 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.
>
> --
> Steve Schapel, Microsoft Access MVP
>
> scubadiver wrote:
> > 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.
>
|
|
Ok, great.
--
Steve Schapel, Microsoft Access MVP
scubadiver wrote:
[Quoted Text] > 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.
>
|
|
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
|
|
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
scubadiver wrote:
[Quoted Text] > 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
>
|
|
I will experiment.
Thanks
"Steve Schapel" wrote:
[Quoted Text] > 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
>
> scubadiver wrote:
> > 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
> >
>
|
|
|