Senin, 25 Juli 2011

RE: [MS_AccessPros] Display all records in subform datasheet

 

Deb-

Oh, my. Have you done any studying at all? Unlike the other Office apps, you
can't just start up Access and start using it with no knowledge of databases.
Well, you can load up one of the many templates if one meets your needs and
muddle along that way. But you cannot expect to start building something from
scratch and be successful. Please at least go watch Crystal's excellent
introductory videos on YouTube:

http://www.youtube.com/user/LearnAccessByCrystal

3-5: It's "against the rules" in design of a Relational Database to store ANY
calculated values. You don't say which version of Access you have, but only the
very latest version (2010) finally allows you to "store" calculated values. You
define those calculated fields in the table design, not in an expression on a
form. If you have an earlier version (which I suspect is true because you
uploaded an MDB file), you can certainly calculate values you need on the fly in
a query or control expression, but you cannot and should not attempt to store
the values in the tables.

You define indexes in Table Design. I told you to link on Part_Number, so you
need a copy of Part_Number in the Parts_Purchase table. Part_Number in the
Parts_Inventory table must either be the Primary Key or it must have an index -
indexed, no duplicates.

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 Deb
Sent: Sunday, July 24, 2011 11:43 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Display all records in subform datasheet

Thanks John! As I mentioned, this is my first experience with Access, so
been trying to learn as I go ;-)

I fixed #1 and #2, so they are now working fine.

On #3 - #6, I am confused, where do I put the expressions and how/where do I
store the results of those expressions? I thought that they would be
calculated on the formula and then stored in the table....

On #7. I changed the relationship to link Part_Name in the Parts_Inventory
main form to link to the Part_ID in the Parts_Purchase subform, is that
correct? It's still not working right, so not sure. Not sure what a unique
index is?

I really appreciate all of your help!!

Deb

On Sun, Jul 24, 2011 at 2:18 PM, John Viescas <john@viescas.com> wrote:

> Deb-
>
> I see several problems:
>
> 1) The partiddbl control on the outer form is bound to the expression
> =[ID].
> The user will NEVER be able to enter a part ID because you have the control
> bound to an expression. But I see ID is an AutoNumber, so maybe that's not
> a
> problem. The control should be bound to ID, Locked = Yes, and take it out
> of
> the tab order or set the tab order to the first editable control.
>
> 2) The PartDescriptionlbl control is bound to =[Part_Description]. As
> above,
> the user will never be able enter a description for a new part because the
> control is bound to an expression. You have the same problem with control
> Text12 (=[Part_Size]) and control stockidlbl (=[Manufacturer_Part_Number])
>
> 3) On the subform, you have control Ext Tax Price bound to the expression:
> =[Price]*[Tax]+[Price] There's a field in the underlying table called Ext
> Tax
> Price, but it will NEVER be updated. You probably should not have this
> field in
> your table - any calculated field like this should be calculated in a query
> expression or in an expression on a form control as you have done.
>
> 4) You have the same problem as #3 with the control Total Cost
>
> 5) You have the same problem as #3 with the control Unit Price
>
> 6) You have a 1-1 relationship between the ID field in Parts_Inventory and
> the
> ID field in Parts_Purchase, and both are AutoNumber. The form cannot deal
> with
> this relationship because you can NEVER set a value in an AutoNumber field.
> That's why you get "can't assign a value to this object" when you try to
> enter a
> row in the subform. The subform cannot assign the related linking value to
> ID
> in Parts_Purchase. And I assume you'll have multiple purchases over time,
> so
> the Parts_Purchase table should perhaps be linked on Part_Number (which
> should
> have a unique index in Parts_Inventory) and use the AutoNumber ID field in
> Parts_Purchase to distinguish multiple rows.
>
> The bottom line is your form doesn't work because you have a table design
> problem.
>
> 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)
>
>
>
>
>
> -
>

[Non-text portions of this message have been removed]

------------------------------------

Yahoo! Groups Links

__._,_.___
Recent Activity:
MARKETPLACE

Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

.

__,_._,___

Tidak ada komentar:

Posting Komentar