Cascading Combo Box confusion...

[Synses]

I was just thinking that for the cboClientFacility combo to show the correct facility for existing records you will probably need to put

Me.cboClientFacility.Requery

in the form's On Current event too.

A good start for VBA is this video series.

Thanks, I don't see an "On Current" event...do I need to create it? (how?) or did you maybe mean "On Change" ?
Thank you,Synthia

 

[sneuberg]

Thanks, I don't see an "On Current" event...do I need to create it? (how?) or did you maybe mean "On Change" ?
Thank you,Synthia

Definitely not On Change. You want the form On Current event and I've confirmed that you do need it.

1. Open the frmLessonLearned form in design view
2. Right click in the form and select Form Properties
3. In the Event tab at the very top click on the ellipses of the On Current event
4. Choose code builder and click Ok
5. Enter Me.cboClientFacility.Requery

It should now look like:

Private Sub Form_Current()

Me.cboClientFacility.Requery

End Sub

 

[sneuberg]

Right now the form feeds info from the lesson learned field on the form to the Lesson Learned table.
(I incorrectly stated that it fed info to the "Faciltiies tables" in my question, there are no "Facilities tables" ther is one "Client Faciliies" table).

There is
one Client table (lists all clients) and
one Client Facilties table (lists all clients in one field and then all facilities for each client is a record in the "Client Facilities" field) and
one Lesson Learned table (lists all clients in one field and then all facilities for each client is a record in the "Client Facilities" field, then a Lesson Learned field for every record).

The main thing that is needed is that when the user selects a client, and then see choices for faciltiies for that client, they can choose "ALL" or select "SOME" of the facilities that the info they are going to enter on the form relates to. (Multiple values)

I think the answer to my question about how to connect the lesson learned info to the client table and to the facilities table is going to be in report generation - and I learning that and going to make those tomorrow. (the part about connecting the lesson learned to the client and to the facilities)

Sorry for typing so fast without thinking it through enough when I first posted.
Thank you

You current structure does not allow a Lesson Learned to be related to multiple facilities. To do that you would need to add a junction table between the facilities table and the lessons learned table. This would create a many-to-many relationship between facilities and lessons learned.

If you add many-to-many relationships things get a lot more difficult. I suggest that before you start on that you read up on normalization. This series is a good place to start. And I would definately get rid of those lookup fields.

 

[Synses]

Definitely not On Change. You want the form On Current event and I've confirmed that you do need it.

1. Open the frmLessonLearned form in design view
2. Right click in the form and select Form Properties
3. In the Event tab at the very top click on the ellipses of the On Current event
4. Choose code builder and click Ok
5. Enter Me.cboClientFacility.Requery

It should now look like:

Private Sub Form_Current()

Me.cboClientFacility.Requery

End Sub

THANK YOU SO MUCH!
My error is that I was looking at the cboClientFacility properties, not the form properties.
Thank you. I hope you are blessed enormously.
Synthia

 

[Synses]

You current structure does not allow a Lesson Learned to be related to multiple facilities. To do that you would need to add a junction table between the facilities table and the lessons learned table. This would create a many-to-many relationship between facilities and lessons learned.

If you add many-to-many relationships things get a lot more difficult. I suggest that before you start on that you read up on normalization. This series is a good place to start. And I would definately get rid of those lookup fields.

Thanks, I will get on that tomorrow!!!

 

[Synses]

You current structure does not allow a Lesson Learned to be related to multiple facilities. To do that you would need to add a junction table between the facilities table and the lessons learned table. This would create a many-to-many relationship between facilities and lessons learned.

If you add many-to-many relationships things get a lot more difficult. I suggest that before you start on that you read up on normalization. This series is a good place to start. And I would definately get rid of those lookup fields.

Thnaks, for the 'Lesson Learned" data entered to be connected for user observation/review, in assocation with the Client and the Facility that the "lesson Learned" is about/the form user selected, I think that creating and generating a report is the way to do that without changing any current designs on the form nor on the tables...sound right?

I believe you (times ten) about getting rid of the look ups and will review the info on the links you sent to see if I can find out how to eliminate the look up tables...do you think the links you sent me will show me how to do that?
Thanks, Synthia

 

[sneuberg]

Thnaks, for the 'Lesson Learned" data entered to be connected for user observation/review, in assocation with the Client and the Facility that the "lesson Learned" is about/the form user selected, I think that creating and generating a report is the way to do that without changing any current designs on the form nor on the tables...sound right?

I don't know what you mean by "the "lesson Learned" is about/the form user selected". If you could elaborate a bit more on this it might help.

I believe you (times ten) about getting rid of the look ups and will review the info on the links you sent to see if I can find out how to eliminate the look up tables...do you think the links you sent me will show me how to do that?
Thanks, Synthia

No, the links I've posted do not say anything about getting rid of them. I believe in your case all you would need to do is change the Display Control from Combo Box to Text Box on the Lookup tabs of the field that have a lookup. Basically just click on each field in the tables in design view with the Lookup tab exposed and change any Display Control that's a Combo Box to a Text Box. When you change to Datasheet View the foreign keys will then be displaying numbers as they should. After this you will use queries to get the views you were getting with the lookup fields.

 

[Synses]

Thanks, I eliminated the look ups and created a report.

The problem with the report is that it shows lessons learned (that had been entered for one client, or for one facility) for all facilities of the client.
(example: client Exelon)
Is there a way to make it so that when the report generates a report of lessons learned it shows:
If a lesson learned was entered on the form/is shown on the table for only one facility of a certain client, it is only shown for that one facility, not for all the facilities?
I attached the revised database if needed.
Thanks for any help you can offer!
Synthia

 

[sneuberg]

First I suggest you make the text format of the LessonLearned field the same in the table, form and report. A Rich Text format will allow some formatting whereas the Text Format is just text. I think you have the report field set to text format as I was seeing HTML tags.

I don't understand what you are trying to do but the joins in this query which is the record source of the report look strange to me. This is what's causing the output to have so many records.

I suggest change them to the following and see if that's closer to what you want.

 

[Synses]

I can't figure out how to make the join look like the join in your picture. I got to the report Lesson Learned Query Builder but am stuck there....would you please tell me how to make it
one to many from
ClientFacilityId in tblClientFacility to
clientFacilityIdFK in tblLessonLearned
the way you did it?
Sorry so ...amateur...and thank you!
Synthia

First I suggest you make the text format of the LessonLearned field the same in the table, form and report. A Rich Text format will allow some formatting whereas the Text Format is just text. I think you have the report field set to text format as I was seeing HTML tags.

I don't understand what you are trying to do but the joins in this query which is the record source of the report look strange to me. This is what's causing the output to have so many records.

I suggest change them to the following and see if that's closer to what you want.

 

[Synses]

Meant to attach screen shot....this is where I am...

 

[sneuberg]

To remove a line click on it to select it. It will get slightly thicker when selected. Then just press the delete key or you can right click on the line and click Delete. To add a line just click on one of the field names in the join, hold the left mouse button down and drag to the other field. To change the join properties just double click on a line.

 

[Synses]

Sorry, oops thought I did...

Normally the bound column of the combo box for the Client would have the primary key of the client table. The row source query of the facilities combo box would also have this as a foreign key. In the criteria of this field you would put a reference to the client combo box. Something like

Forms!TheNameOfTheForm!TheNameOfTheCombo

which would limit the selection of that combo box to that client.

But I can't see how you have this set up. It would help if you uploaded your database so that I could give you specifics.

 

[sneuberg]

Sorry, oops thought I did...

That was post #6. We are way past that now.

 

[Synses]

Thank you for your patience. Lesson learned about navigating the messages!

I had followed the excellent instructions/picture showing how to change the join lines, but it did not look like yours, it did not have the one to many symbols and I could not make them appear so I messaged you again, and you kindly replied with more specific instructions, which were what I had done, I thought, so I opened the file and guess what, now the one to many symbols on the join were visible-and looked just like yours -and it works perfectly.

Next lesson learned was: save, close and reopen after edits before asking again.

You are amazing help.

I bought/received the book ACCESS 2016 Bible. I will study and learn this month. I am so grateful for your help. If only more people in this world were this kind and generous and patient!
THANK YOU so much!

Next (and I think last) step for this database, I need to give the user an option to generate a report for just one facility. (they would generate a report (as pdf..?) for one facility and attach it to an e-mail and send it to the associated PM, engineer, etc.)
Would it be possible?
Synthia

 

[sneuberg]

Next (and I think last) step for this database, I need to give the user an option to generate a report for just one facility. (they would generate a report (as pdf..?) for one facility and attach it to an e-mail and send it to the associated PM, engineer, etc.)
Would it be possible?
Synthia

I just got some work rehabbing a PHP/MYSQL Web site so regretfully I won't be able to contribute much on the AWF for a couple of months. I suggest you post this question in a new thread so that it gets more attention from other forum members.

Good luck on your project.

 

[Synses]

Thank you Steve, you have been much more than generous and I appreciate it greatly!