Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-21-2019, 05:57 AM   #1
mounty76
Newly Registered User
 
Join Date: Sep 2017
Posts: 87
Thanks: 11
Thanked 0 Times in 0 Posts
mounty76 is on a distinguished road
Preset values in a field

Hello!

I have a table (tblInfo) which has columns like ID(Autonumber), FirstName, LastName, DOB, Email, etc.

I need to have a child table (tlbcerts) to this that automatically fills in part of the records required for certain certificates.

For example, in tblInfo I have a record like 1, Joe, Bloggs, 01/01/80, joe.bloggs@gmail.com This information is entered via data entry form.

I need then to have a subform on the data entry form, which when a button is pressed tlbCerts opens up 9 preset records which correspond and are linked with the tblInfo.

So when I click the button in the subform it will always open 9 records which would look similar to the below:

PersonID FirstName LastName Cert CertNumber Expiry


The idea is that the PersonID is the same as the person just entered in tblInfo, this then also puts in the corresponding FirstName and LastName from the data just entered in tblInfo, this will then be the same for the next 9 preset records for each certificate, the user then has to enter the CertNumber and ExpiryDate.

So when you click on the button in the subform it will automatically make the tblCert look something like this:

1, Joe, Bloggs, AAA
1, Joe, Bloggs, BBB
1, Joe, Bloggs, CCC
1, Joe, Bloggs, DDD
1, Joe, Bloggs , EEE

Then the user can simply enter the cert number and expiry date for each of the certificates for that person.

I hope that makes sense, quite difficult to explain it properly!!

mounty76 is offline   Reply With Quote
Old 07-21-2019, 06:28 AM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,651
Thanks: 58
Thanked 1,459 Times in 1,440 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Preset values in a field

Hi. If I understand it correctly, there’s no need to pre-create those nine records, if the user will be entering the certs anyway. If you use a form/subform setup with a properly linked fields, then you’ll automatically get the ID, and there’s no need to duplicate the name.
__________________
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 offline   Reply With Quote
Old 07-21-2019, 06:30 AM   #3
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,367
Thanks: 555
Thanked 948 Times in 897 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Preset values in a field

I do something similar with checklists. Basically you have a separate table with the items you want to display, and you use code to add these items to the table behind the sub-form. I've covered the process in my blog here:-

https://www.niftyaccess.com/add-a-ch...cess-database/

You may be able to adapt the checklist idea to your particular case...

The records correctly displayed in the subform rely on the link that is created between the sub-form and the main form with the subform/subreport control. The subform/subreport control manages the parent-child link between the two forms.

__________________
Code:
                 |||||
               @(~‘^‘~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 07-21-2019, 06:35 AM   #4
mounty76
Newly Registered User
 
Join Date: Sep 2017
Posts: 87
Thanks: 11
Thanked 0 Times in 0 Posts
mounty76 is on a distinguished road
Re: Preset values in a field

Thanks guys, I'll have a look at your blog when work stops bothering me! I do need the 9 preset cert names so it prompts people to enter the right certs I need, otherwise I know people won't enter all the required certs.

Thanks again.
mounty76 is offline   Reply With Quote
Old 07-21-2019, 06:54 AM   #5
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 6,651
Thanks: 58
Thanked 1,459 Times in 1,440 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Preset values in a field

Quote:
Originally Posted by mounty76 View Post
Thanks guys, I'll have a look at your blog when work stops bothering me! I do need the 9 preset cert names so it prompts people to enter the right certs I need, otherwise I know people won't enter all the required certs.

Thanks again.
Oh, if the records are required, then I guess there's no harm in pre-creating them. Cheers!
__________________
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 offline   Reply With Quote
Old 07-21-2019, 07:00 AM   #6
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,433
Thanks: 11
Thanked 2,286 Times in 2,238 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Preset values in a field

Quote:
The idea is that the PersonID is the same as the person just entered in tblInfo, this then also puts in the corresponding FirstName and LastName from the data just entered in tblInfo
This isn't how relational databases work. They don't move and repopulate data, they relate it.

When you need to relate data in one table to that in another, you don't use multiple fields, you simply use the ID field. With that ID field you can then link that second table to the first and have access to all the other data in that record of the first table. So, you don't put First Name, LastName or DOB in that second table, you just put the ID value.

As for generating 9 new records in that second table you can do this via a function that runs an INSERT INTO query (https://www.w3schools.com/sql/sql_insert.asp) 9 times and then refreshes your subform to show those records.
plog is offline   Reply With Quote
Old 07-21-2019, 07:23 AM   #7
Uncle Gizmo
Nifty Access Guy
 
Uncle Gizmo's Avatar
 
Join Date: Jul 2003
Location: Newbury Berks UK
Posts: 10,367
Thanks: 555
Thanked 948 Times in 897 Posts
Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough Uncle Gizmo is a jewel in the rough
Send a message via Skype™ to Uncle Gizmo
Re: Preset values in a field

Quote:
Originally Posted by plog View Post
As for generating 9 new records in that second table you can do this via a function that runs an INSERT INTO query (https://www.w3schools.com/sql/sql_insert.asp) 9 times and then refreshes your subform to show those records.
Do you mean this one Jason?

https://www.w3schools.com/sql/sql_in...nto_select.asp

__________________
Code:
                 |||||
               @(~‘^‘~)@
-------------oOo---U---oOo-------------
|                                     |
|      Uncle Gizmo              |
|                                     |
|                                     |
| Get $20 worth of "Nifty Code"       |
|      
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
| | Ooo | |_________________ooO____( )________| ( ) ) / \ ( (_/ \_)
Uncle Gizmo is offline   Reply With Quote
Old 07-21-2019, 08:08 AM   #8
plog
Banishment Pending
 
Join Date: May 2011
Posts: 9,433
Thanks: 11
Thanked 2,286 Times in 2,238 Posts
plog is a jewel in the rough plog is a jewel in the rough plog is a jewel in the rough
Re: Preset values in a field

No I didn't.
plog is offline   Reply With Quote
Old 07-21-2019, 08:37 AM   #9
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Preset values in a field

firstly you don't need the firstname and lastname on tblCerts.
you can get it from tblInfo by linking both tables (left join) by ID and PersonID fields.
you don't also need the other fields in your tblCerts.

what you need to retain or add are:

PersonID (foreign key to tblInfo)
CertID (foreign key to tblCertificates ID)
Expiry Date


create another table (tblCertificateMaster), which holds the 9 certificates to be inserted to tblCerts.
you only need to run a query on the click of the button:
Code:
Private sub button_Click()
'first check if the certificate is already created.
If Nz(DCount("1","tblCerts", "PersonID=" & Me.ID), 0) <> 0 Then
    Msgbox "Certificates already exist"
Else
   CurrentDb.Execute("Insert Into tblCerts(PersonID, CertID) SELECT " & _
     ME.ID & ",tblCertificate.ID FROM tblCertificateMaster)
    Me.subformCerts.Requery
End If
End Sub
you must put a Master/Child link fields on the subform using ID (Master) from tblInfo and PersonID (tblCerts).
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 07-21-2019, 08:59 AM   #10
mounty76
Newly Registered User
 
Join Date: Sep 2017
Posts: 87
Thanks: 11
Thanked 0 Times in 0 Posts
mounty76 is on a distinguished road
Re: Preset values in a field

Thank you to everyone. I'll try and do this in the next few hours or so. I'll keep you posted!
mounty76 is offline   Reply With Quote
Old 07-21-2019, 09:24 AM   #11
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,761
Thanks: 93
Thanked 1,725 Times in 1,596 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Preset values in a field

I'm going to step in with supplemental information because everyone is telling you what to do but their "why" is a bit thin. (Not wrong, just doesn't explain in detail.)

You had a design for table tblCerts that would have produced THIS:

Code:
1, Joe, Bloggs, AAA
1, Joe, Bloggs, BBB
1, Joe, Bloggs, CCC
1, Joe, Bloggs, DDD
1, Joe, Bloggs , EEE
You don't need that much. It should look like this:

Code:
1, AAA
1, BBB
1, CCC
1, DDD
1, EEE
Then if you wanted to see everybody's name and certs, build a QUERY that joins the tblInfo to tblCerts (joined via the prime key of tblInfo, which is a foreign key in tblCerts) and select the fields you wanted to see together.

A query takes up very little space when it is closed. Like, only as much space as the querydef structure. Size-wise, having the first name & last name actually duplicated in tblCerts adds probably 20 to 40 bytes (depending on name sizes) to each tblCerts record when you could think "QUERY" for pulling things together. If you have 1000 persons and 9 certs per year and 30 bytes per name, taking the approach just described saves you 270k bytes per year in storage space.

I admit I am making an inference here but I think I'm right. You seem to be relatively new to Access so possibly haven't yet learned one of the more valuable tools in the toolbox. You have the ability to use queries to build recordsets customized to a specific purpose in a way to store the minimum data in a table but "flesh out" the repetitive data from another table where that info isn't repetitive. Forms and reports work quite well with properly-built queries.

It's a mind-set that takes a while to form. This is a minimal example but I hope illustrates for you the valuable point that you can save space through data normalization which in this case results in the ability to not store the person's name in the certificates table, but merely pick it up from the info table based on the info table's prime key.

Look a bit more into the ideas of normalization because your decision to store extra data simply means you are still in a flat-file mind set. THIS IS NOT A KNOCK! Every one of us went through this before we had that "AHA" moment. It is merely my suggestion that I think a little more study on the ideas and goals of normalization might be helpful.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 07-21-2019, 02:55 PM   #12
mounty76
Newly Registered User
 
Join Date: Sep 2017
Posts: 87
Thanks: 11
Thanked 0 Times in 0 Posts
mounty76 is on a distinguished road
Re: Preset values in a field

HI Doc Man and everyone else,

Thanks for all the info, much appreciated, yes I have the flat mind set, I've been using access for a few years but most likely doing things the hard way for most of that. Thanks again
mounty76 is offline   Reply With Quote
Old 07-21-2019, 03:43 PM   #13
mounty76
Newly Registered User
 
Join Date: Sep 2017
Posts: 87
Thanks: 11
Thanked 0 Times in 0 Posts
mounty76 is on a distinguished road
Re: Preset values in a field

Hi arnelgp,

What you say makes perfect sense to me when I read however in practice I cannot get it to work. This is first time I've used relationships in tables.

Is it possible to put it into a quick example db so I can see how it works then apply it to my db?

Thank you very much in advance!
mounty76 is offline   Reply With Quote
Old 07-21-2019, 06:39 PM   #14
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,761
Thanks: 93
Thanked 1,725 Times in 1,596 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Preset values in a field

Quote:
Is it possible to put it into a quick example db so I can see how it works then apply it to my db?
You have a ready-made relationship. Your table tblCerts is a child; your table tblInfo is a parent. There is another way of saying this: tblCerts DEPENDS ON tblInfo because if you don't have a person in tblInfo, it makes no sense to have a record for that person in tblCerts - so tblInfo is the independent table and tblCerts is a dependent table. You will see a mixture of usages on the web. Parent/child and independent/dependent are both common.

You have a person-number in tblInfo that acts as a prime key or PK. You were going to have a copy of the tblInfo PK and some other data in tblCerts except we are telling you that you don't need all of the other info. If the stuff you need is in tblInfo and the other tables have the appropriate copy of the person's number from tblInfo as the foreign key (FK), you've got a parent/child relationship based on PK/FK being the same meaning.

In the relationships panel (Database Tools ribbon has the icon for it), you click on Show Tables to bring in the two tables. I'm going to assume that PersonID is used for the same purpose in both tables - to identify the person by number. Once both tables are in the relationships panel and you have mouse-grabbed the corners to expose all the field names for each table, you create a relationship by doing what looks like a drag-n-drop on the fields you want to relate.

You have PersonID under tblCerts and PersonID under tblInfo. Click on either one of those names and (holding down the mouse button) drag to the other field of the same name. A dialog comes up. Click your JOIN TYPE button and select "All records in tblCerts and only the matching records from tblInfo" - which gives you a many-to-one reference of certificates to persons - or, since it is all relative, a one-to-many reference of persons to certificates. Click CREATE and you have a relationship. There are details such as whether you want relational integrity. For a beginner, you probably don't want that until you understand it enough to know that it does impose an order in doing things. I would say to read up on that topic before using it - but once you understand that it actually protects you from making mistakes, you WILL want to use it.

What good is this relationship? When you go to the query-builder grid to build a JOIN query between two tables that have a relationship, Access can SEE it and will take it into account. You have already told Access all it needs to know to get the LEFT JOIN or RIGHT JOIN syntax right. And when in doubt, you can build the query in the grid and then switch to SQL view to see what you just did.

It goes farther than that. If you want to build a form with personal info in one part and a list of that person's data in a sub-form, having the relationship already designed means that the form builder can see it and make helpful suggestions on linking the tables correctly. Ditto, report-builder. You can always override what the wizards suggest, but they are pretty darned good if you give them relationship data to work with.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
mounty76 (07-22-2019)
Old 07-21-2019, 07:54 PM   #15
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Preset values in a field

your quick example is now ready.
Attached Files
File Type: zip Certificates.zip (49.0 KB, 4 views)

__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
mounty76 (07-22-2019)
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] Preset Notes Ideas? billgyrotech General 17 07-03-2019 08:37 AM
how create a button that filter by preset value? laghetto Modules & VBA 1 04-27-2012 11:10 AM
Report Format Preset? Naka Reports 0 08-02-2007 08:40 AM
preset fields in list box $lim Forms 7 12-14-2004 12:48 PM
Preset Labels PaulA Reports 1 08-07-2002 11:00 AM




All times are GMT -8. The time now is 02:13 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