Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-15-2019, 04:31 PM   #31
got_access:]
Newly Registered User
 
Join Date: Jun 2018
Posts: 73
Thanks: 0
Thanked 1 Time in 1 Post
got_access:] is on a distinguished road
Re: best practice for lookups in tables?

Yup!
I totally agree!
Someone told me that that feature was predicated on the lookup being established.
However, I just did my own experiment and built a few new tables with similar data and then tried different variants to fine out which one would be needed for the system to auto-populate fields.

A lookup was definitely not needed. But (given a parent/child and one-to-many relationship) link between the Customer table and the Orders table. Where the tblCustomer.CustomerID (primary key) is linked to tblOrders.CustomerID (foreign key) - that did the trick.

It appears to be a feature of the query in datasheet mode. And you're right its not really performing an insert of those fields into a table. The only data value that is inserted is the customerID value inserted by the user. The other fields are simply being displayed as the results of the query - kind of like an auto-refresh - because that link is established. My curiosity was getting the better of me - so I had to find out what it was going on there.

But you're absolutely right - users should not be given access to a query or a table for data-entry. They should be limited to forms and reports as a rule of risk-assessment.

Huge thanks for taking the time on this for me!!


Last edited by got_access:]; 05-15-2019 at 04:50 PM.
got_access:] is offline   Reply With Quote
Old 05-15-2019, 05:35 PM   #32
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 3,040
Thanks: 36
Thanked 722 Times in 705 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: best practice for lookups in tables?

Quote:
Originally Posted by got_access:] View Post
Yup!
I totally agree!
Someone told me that that feature was predicated on the lookup being established.
However, I just did my own experiment and built a few new tables with similar data and then tried different variants to fine out which one would be needed for the system to auto-populate fields.

A lookup was definitely not needed. But (given a parent/child and one-to-many relationship) link between the Customer table and the Orders table. Where the tblCustomer.CustomerID (primary key) is linked to tblOrders.CustomerID (foreign key) - that did the trick.

It appears to be a feature of the query in datasheet mode. And you're right its not really performing an insert of those fields into a table. The only data value that is inserted is the customerID value inserted by the user. The other fields are simply being displayed as the results of the query - kind of like an auto-refresh - because that link is established. My curiosity was getting the better of me - so I had to find out what it was going on there.

But you're absolutely right - users should not be given access to a query or a table for data-entry. They should be limited to forms and reports as a rule of risk-assessment.

Huge thanks for taking the time on this for me!!
Hi. Good discussions all around. I just hope no one else gets confused when they read this thread. Access offers a lot of ways to try to help the user, and as Dave said earlier, using them doesn't mean the end of the world. It's just some of them tend to include "surprises" if the user is not aware of them. So, the common advice is to avoid them until you get a better grasp of what they can do and are prepared to handle the included consequences. For now, I say focus on your project and try to build it using only best practice suggestions. Once it's working as it's supposed to, you are then free to tweak it any which way you see fit (or willing to experiment with). Good luck!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 05-15-2019, 05:37 PM   #33
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Posts: 1,958
Thanks: 0
Thanked 463 Times in 459 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: best practice for lookups in tables?

Doesn't matter if form is datasheet, single, or continuous.


__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Lookups in tables lynxbci Tables 6 03-06-2014 05:54 AM
Lookups with related tables SueB77 Forms 1 02-02-2012 06:00 AM
when making tables with lookups icemonster Tables 2 05-28-2010 07:55 AM
Tables - without Lookups flebber Tables 6 03-18-2010 06:31 AM
Lookups with related tables Conor Forms 6 10-20-2008 01:13 PM




All times are GMT -8. The time now is 05:12 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World