A few basic form problems

[cheese9799]

This may look like a massive question but I'm new to Access and this is just a few mini-questions which most of the people on here could answer in their sleep, and all 3 are very similar anyway.


I have a tblCERTIFICATE with a CertificateID and CertificateType field.

Then I have a tblJOB with a JobID and JobType field. I'd like to also have one more field to select a certificate type from the field in tblCERTIFICATE. I used the lookup wizard so that when this field is selected in the table, it creates a drop-down list of the certificate types, along with the respective CertificateID. When the appropriate one is selected, the CertificateID is placed into the box. (This is the same on both the table and the corresponding form.)

Question 1) How can I change this to give the following visualised solution: The user selects the certificate type from a drop-down on the form, where JUST the certificate type and not the ID is on the list. Then, the certificate type, not the ID, appears in the box. When the record is saved, the table saves the corresponding CertificateID (I think it's unnecessary for it to save the actual CertificateType too but I don't mind either way).


I have a tblBOOKING with BookingID, CustomerID, CertificateID and JobID. When I select the CustomerID field, CustomerID, FirstName and LastName fields from my tblCUSTOMER are represented. When one is selected, CustomerID is placed into the box. (This is the same on both the table and the form.)

Question 2) How can I change this to give the following visualised solution: The form has a 'Name' drop-down where the user selects from a list of full names (no IDs appear on the drop-down) (remembering that full names consist of two separate fields - first name and last name). Then, the database would save the CustomerID in the tblBOOKING. And would it be necessary to store the first and last names too?


In the tblBOOKING, when CertificateID is selected, it shows a drop down of the CertificateType values along with their ID, then the ID is saved in the field when selected (in both the form and the table).

Question 3) How can I change this to give the following visualised solution: The user selects one of the certificate types from a drop-down (ID isn't in the drop-down), and then the JobID offers a drop-down of all of the jobs that have the selected certificate type. Then, the table saves the CertificateID and JobID in the table.

[TheShabz]

1. It depends on the relationship between jobs and certificates. can one job have many certificates? can one certificate have many jobs? both?

2. On the dropdown, you create a query that pulls the ID, and then concatenates the names together, something like:
SELECT CustomerID, FirstName & " " & LastName AS FullName FROM tblCUSTOMER
Then you create a combobox bound to that query. In the properties, set the bound column to 1 (this will store the ID as the combo box's value) and set the width of column 1 to 0" (this will hide the ID field). Also, this can be used in your first question.

3. See #2

[cheese9799]

1. It depends on the relationship between jobs and certificates. can one job have many certificates? can one certificate have many jobs? both?

2. On the dropdown, you create a query that pulls the ID, and then concatenates the names together, something like:
SELECT CustomerID, FirstName & " " & LastName AS FullName FROM tblCUSTOMER
Then you create a combobox bound to that query. In the properties, set the bound column to 1 (this will store the ID as the combo box's value) and set the width of column 1 to 0" (this will hide the ID field). Also, this can be used in your first question.

3. See #2

Sorry, yeah, one certificate type can have many jobs. CertificateType is just one of three types: 'Periodic', 'Minor' or 'Installation'

Looking at your solutions now.

[TheShabz]

Create a field in the Job table called CertificateID and make sure the certificateID is stored in the table as well.

Let's assume your form is called myForm and your combobox on the form holding the certificate names is called cboCerts. Now, make sure you do the same to this combo as the ones in 2 and 3; hold the ID but display the name.

Now create a query:
SELECT JobID, JobName
FROM tblJobs
WHERE CertificateID = Forms!myForm!cboCerts

Now bind this query to whatever form control you wish to use to display the data.

[cheese9799]

Create a field in the Job table called CertificateID and make sure the certificateID is stored in the table as well.

Let's assume your form is called myForm and your combobox on the form holding the certificate names is called cboCerts. Now, make sure you do the same to this combo as the ones in 2 and 3; hold the ID but display the name.

Now create a query:
SELECT JobID, JobName
FROM tblJobs
WHERE CertificateID = Forms!myForm!cboCerts

Now bind this query to whatever form control you wish to use to display the data.

After reading your other 2 solutions, I did it a different way... I just added a combo box control, selected to take values from a table (tblCertificate(CertificateID, CertificateType)), hid the ID and made it store in tblJob.CertificateID. Probably the same as what you did but with the wizard?

And your solution to #2 works wonderfully - thank you! Not sure #3 is the same though - I want the 2nd combo box's results to change depending on the first... E.g. you choose a certain CertificateType and then the combo box underneath offers some job types, but if you selected a different certificate type, you'd be offered different job types. I can set the first combo box up but am unsure how to make the 2nd one change... I expect using AfterUpdate?

[TheShabz]

EDIT: Misread. Yea, pretty much the same.

[cheese9799]

EDIT: Misread. Yea, pretty much the same.

How do I make it so the 2nd combo box only shows values relating to what was chosen in the 1st combo box?

[TheShabz]

Let's assume your form is called myForm and your combobox on the form holding the certificate names is called cboCerts.

Now create a query:
SELECT JobID, JobName
FROM tblJobs
WHERE CertificateID = Forms!myForm!cboCerts

Now bind this query to whatever form control you wish to use to display the data.

How bout this?

[cheese9799]

How bout this?

Ah - got confused over which Q you were answering. Will try it later - thanks for everything!