Preset values in a field (1 Viewer)

mounty76

Registered User.
Local time
Yesterday, 19:11
Joined
Sep 14, 2017
Messages
341
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!! :banghead:
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:11
Joined
Oct 29, 2018
Messages
21,358
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.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 02:11
Joined
Jul 9, 2003
Messages
16,245
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-check-list-to-your-ms-access-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.
 

mounty76

Registered User.
Local time
Yesterday, 19:11
Joined
Sep 14, 2017
Messages
341
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.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 19:11
Joined
Oct 29, 2018
Messages
21,358
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!
 

plog

Banishment Pending
Local time
Yesterday, 21:11
Joined
May 11, 2011
Messages
11,613
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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:11
Joined
May 7, 2009
Messages
19,169
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).
 

mounty76

Registered User.
Local time
Yesterday, 19:11
Joined
Sep 14, 2017
Messages
341
Thank you to everyone. I'll try and do this in the next few hours or so. I'll keep you posted!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 21:11
Joined
Feb 28, 2001
Messages
27,001
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.
 

mounty76

Registered User.
Local time
Yesterday, 19:11
Joined
Sep 14, 2017
Messages
341
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

Registered User.
Local time
Yesterday, 19:11
Joined
Sep 14, 2017
Messages
341
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!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Yesterday, 21:11
Joined
Feb 28, 2001
Messages
27,001
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.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:11
Joined
May 7, 2009
Messages
19,169
your quick example is now ready.
 

Attachments

  • Certificates.zip
    49 KB · Views: 131

mounty76

Registered User.
Local time
Yesterday, 19:11
Joined
Sep 14, 2017
Messages
341
Hi arnelgp,

Amazing, thank you.

Thanks to you all for all the information and advice, much appreciated!
 

Users who are viewing this thread

Top Bottom