Lee-
Rather than store the employee salary in the "staging" table, can't you just do
a Join to the appropriate table to pick it up when the report runs?
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Lee
Sent: Tuesday, May 24, 2011 7:05 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Adding a record to a datasheet subform & filling in
hidden fields
It's a staging table for a report. The tables where the data is stored are
normalized. The report runs through queries up to a "confirmation" point, and
is stored on a table. A form pops up with the staging table data. The user can
double check/confirm certain info before outputting the report. The comp rate
is used to calculate several values, but should not be visible to the user.
The staging table design probably isn't the greatest, and I know the explanation
probably doesn't help a whole lot, but it's a super complex project, and I
couldn't figure out any way to get the report to run without a staging table and
a series of queries, since there are so many scenarios that have to factor in to
the final report.
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Lee-
>
> What is it you're recording in the table? I can perhaps see a case where you
> need to trap the *current* salary for some transaction whose total cost
depends
> on that. But if that's not the case, couldn't you do a join in the report
query
> to fetch the salary for calculation in the report? Please explain in more
> detail why you feel you need to *copy* the value from another table into this
> one. It's normally bad database design to have the same data in more than one
> place.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Lee
> Sent: Tuesday, May 24, 2011 5:47 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Adding a record to a datasheet subform & filling
in
> hidden fields
>
> The hidden fields are salary values that the database user should not see. I
> need to insert these salaries for a report that runs (the calculations are
based
> off of the table, but the calculated values are not stored)
>
> I tried this, hoping that forcing the save would allow me to just use an
update
> query to insert the values to the underlying table.
>
> Private Sub txtEmplID_AfterUpdate()
> If Len(Me.txtEmplID) <> 0 and Not IsNull(Me.txtEmplID) Then
> Me.Dirty = False
> Call FillEmployeeInfo
> End If
> End Sub
>
> Then I tried:
>
> Private Sub Form_AfterInsert()
>
> If Len(Me.txtEmplID) <> 0 and Not IsNull(Me.txtEmplID) Then
> Me.Dirty = False
> Call FillEmployeeInfo
> End If
>
> End Sub
>
> I tried similar things with before update and before insert, as well as a few
> other little tricks but I always get that same error, OR I get a notice saying
> another user is trying to update the same record, do I want to save the
changes.
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Lee-
> >
> > You should NOT have any field that contains a calculated value in your
table.
> > What are the fields in your table bound to the subform, what are the
"hidden"
> > fields you think you need to update, and what expressions are you trying to
> use?
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Lee
> > Sent: Tuesday, May 24, 2011 12:06 AM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Adding a record to a datasheet subform & filling in
> > hidden fields
> >
> > Hi,
> > I have a form with a bound datasheet subform. The table the subform is
bound
> to
> > has some data that needs to be hidden from the user, so when I created the
> > subform I just left those fields out.
> >
> > The problem I'm having is getting values to go into those hidden fields when
> > someone adds a new row. For the non-hidden fields I just do dlookups based
on
> > the value entered IE:
> > Me.txtFullName = DLookup("FullName", "Employees", "EmplID = " & Me.txtEmplID
&
> > "")
> > etc. and it works fine.
> >
> > For the hidden fields I tried to do an update query when the row is
inserted,
> > but I keep getting a "The macro or function set to the BeforeUpdate or
> > ValidationRule property for this field is preventing Microsoft Access from
> > saving the data in the field." error. I read this is because it's trying to
> > save things in 2 different places, but I've tried me.dirty = false in about
10
> > different places, and called the code from about 10 different events (form
> > before update, after update, before insert, after insert, and control events
> for
> > the same events), and I can't get rid of the error. At this point I think
I'm
> > over-complicating it.
> >
> > I need these hidden values to be populated if someone tries to add a new
row.
> > If I put the text boxes hidden on the datasheet subform they still show up
> when
> > it's not in design view. Is there any simple way I can do this?
> >
> > Thanks
> > -Lee
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar