Where to properly add data from a Form to a table?

[JamesDeckert]

>>Should. I combine these to make an event name?
Both of these are already contained in the data so there is rarely a reason to add another field which is essentially duplicating info from other fields.

[ssanfu]

Well I don't know if I need a name. . The way I would describe an event to a client would be a combination of the "Group type and location"

I suggested that you have a field for an event name, but if you can use "Group type and location", great. The event name can be 'calculated' in the query.


My question:

Q1) Can one event be at many locations AND one location have many events?

You posted:

A single event will have:
-1 Location

What I was headed for is, if 1 event is at 1 location, a junction table ("JUNC_EventLocationAndEvents") would not be necessary.


Consider this structure:
Attachment 22382

I see forms for (your vision may vary):
Clients
Event Locations
Group type
Employees

I would use a main form with 2 subforms:
main form - Events
1 subform - employees
1 subform - clients

--------------------
1 more question.
You posted

-Cost (some people may get a discount if they register for an event early)

By 'people', you mean clients? If so, consider moving "Tbl_Events.EventCost" to the table "JUNC_ClientsAndEvents".

[KingOf206]

Ssanfu,

you posted:

I see forms for (your vision may vary):
Clients
Event Locations
Group type
Employees

I would use a main form with 2 subforms:
main form - Events
1 subform - employees
1 subform - clients

But I am assuming you left out also having a main form of "events" correct?

And yes when i said "people" i did mean clients"

When I get home I will attempt to put together something based on what you have said. then I will post it. So far I am still confused as to how to add clients and employees to an event, but I will try it tonight and post my results.
I am hoping to get this all finished within the next week and then I can put my website together and hopefully launch this business end of the year... But I want to get this database down so its working and all my information will be solid.

I look forward to hearing your feedback!

I suggested that you have a field for an event name, but if you can use "Group type and location", great. The event name can be 'calculated' in the query.


My question:

You posted:

What I was headed for is, if 1 event is at 1 location, a junction table ("JUNC_EventLocationAndEvents") would not be necessary.


Consider this structure:
Attachment 22382

I see forms for (your vision may vary):
Clients
Event Locations
Group type
Employees

I would use a main form with 2 subforms:
main form - Events
1 subform - employees
1 subform - clients

--------------------
1 more question.
You posted
By 'people', you mean clients? If so, consider moving "Tbl_Events.EventCost" to the table "JUNC_ClientsAndEvents".

[ssanfu]

But I am assuming you left out also having a main form of "events" correct?

Nope..... did you see:

I would use a main form with 2 subforms:
main form - Events
1 subform - employees
1 subform - clients

Both subforms on the main form.
Create a new event, enter the data.
Click on the employee subform and enter (select from combo box) the employees.
Then click on the clients subform and enter (select from combo box) the clients.

-Cost (some people may get a discount if they register for an event early)

Some clients get a discount...so consider this structure:
Attachment 22390

[KingOf206]

Alright, I seem to be making some progress now... YAYY, however i have come to a road block ... so I have changed my structure again to match your suggestion, here are the 2 issues I am having...

Issues:
1) I have a the Events main form, and then added 2 sub forms, 1 for adding clients and 1 for adding employees, but they way I currently have it, the subforms only add clients/employees to the master list of clients/employees. I can add a "new event" which is really easy, but the hard part is figuring out how to attach clients/employees to a specific event. I cannot figure out how to add clients/employees to the specific events. This is where I am stuck.

2) Looking forward, I will be having a lot of "new clients" sign up for events, so I would prefer a way so that once I have added an event, I can Add a new client, and then place them in the event with just a couple clicks (if thats possible?) <--- this here is a huge deal as it will save lost of time going back and forth between forms

I have added a copy of my current database in hopes that my issue can be spotted, there is something I am missing but if I can get the 2 issues above solved, this will put me over the hump and I can focus on making my forms look smoother and more professional (right now I know, i know, they look like crap..)


Leland's Event Management Database (2).zip

Nope..... did you see:

I would use a main form with 2 subforms:
main form - Events
1 subform - employees
1 subform - clients

Both subforms on the main form.
Create a new event, enter the data.
Click on the employee subform and enter (select from combo box) the employees.
Then click on the clients subform and enter (select from combo box) the clients.

[JamesDeckert]

>>but the hard part is figuring out how to attach clients/employees to a specific event. I cannot figure out how to add clients/employees to the specific events.
If you are entering them in the subform on the event main form (which is properly built), the id fields will be filled in automatically for you. Open the tables & junction tables and find out.
>>once I have added an event, I can Add a new client, and then place them in the event with just a couple clicks
that should already be the case now.

[ssanfu]

Your subform record sources are wrong. You have to use the junction tables in the subforms.

I modified you dB to demonstrate how to set up a main form/sub form. I changed colors, added (some) buttons, changed the record sources from tables to queries.

In the form "AddEvents" use the NEXT button to go to the 3rd event. I added clients and employees.

Have fun........

[KingOf206]

ssanfu,

Wow, just wow, this is really great! You have been more than helpful in my progression with this!

With that said, now I have a few additional questions about things that can/cant be done, here is a picture with some questions:

A) I like this, my only concern is if I have an employee going and adding people to a few events at a time, how can I prevent them from changing the "event location"? It looks like from this design they can scroll through all the events (which is awesome!) however it is also really easy for a user to change the event location, which could have disastrous impacts! EEKKK thoughts on this? ( I definitely would like to be able to select the location when I am "ADDING an event" but once event is added I would like it to not be editable. Although If I needed to edit the location (for what ever reason) I could go into the tables and change it)

B) There seems to be a lot of unused space right here. I tried to go into design view and make this space usable to see the rest of the event information, but I cant seem to make use of this space. I would prefer to be able to see all of the event information at once, without having to scroll down. Its only a few lines anyways... is this possible?

C) [2 Part Question] -- My events need to have a fairly similar amount of Males to Females (which was a field I forgot to add, but I have now placed it in there). It would be a great visual if I could 1- see the gender of each client & 2 - could there be a running count of how many Males/Females are in each event shown on this form? (the running count of Male/Female clients isn't a big deal, I can always run a query/report for this, but just seeing if it was possible to have this calculated field on a form, it only needs to be visible, but this is not a deal breaker at all! Just something nice to have)

D) This last one is a huge deal. It looks like in testing this out, the ability to add the same client multiple times to an event is possible (see screen shot). Is there some sort of unique index that can be made so that the same client cannot be booked into the same event twice? <-----** SLOVED** I made a unique index for the client and event junction table. it will no longer let me add the same client twice to an event.




Here is the updated version of my database (I have made a few changes, but they have mostly been to the "addClients" form)

Leland's Event Management Database SS_10142015.zip

Your subform record sources are wrong. You have to use the junction tables in the subforms.

I modified you dB to demonstrate how to set up a main form/sub form. I changed colors, added (some) buttons, changed the record sources from tables to queries.

In the form "AddEvents" use the NEXT button to go to the 3rd event. I added clients and employees.

Have fun........

[Homegrownandy]

A- open the form in 'add' mode. on the button you use to open the form.. go into the on click event and change data mode to add.
b- try popup on the form? show us the design view also.
C- If you can gather this from a query as you said, add that query to a text box. then in the after update event of location requery it

Not great answers I know but they may help.

[JamesDeckert]

B) there's an easy way to do this in 2013 if/when you upgrade.

[ssanfu]

A) Use the Form Current event and unlock the control if it is on a new record, otherwise, lock the control.

B) You can change the amount of space by changing the form length (in normal view). I changed the form border style to Dialog Style.

C) How about a total at the bottom?

D) Unique compound index wasn't in the updated version - I added it.


OK, just remembered..... I created a new form - "frmNewLocations" . This is just an example of how I create my "add" forms.


Why are you adding the PK field to the forms? Shouldn't do that. An autonumber field has no real world value/info and shouldn't be displayed.

See:
Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm

Might also be useful:
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

[KingOf206]

ssanfu:
You have been a huge help! This database is coming along great!! Ok, here is what IM realizing, I really like how you made the "EventLocation" only editable when there is a "new record". I have come to the conclusion that now I would like to make all the "event fields" this way (I am thinking ahead to when I have users entering in clients, i dont want them messing any information up. And I fI do nee to change any of this, I would just go into the table myself and make the change)

Question #1:
Here is a pic of the code I beleived you used, I tried to replicate the code for the other feilds (they are circled in blue) but it would not let me. I would like to make all the feilds in blue only editable if the form is new, but once its added I would liek to lock all of these feilds. Here is pic:

How can I do this?



I also really like how the "Add Event" form is looking and how its working...

Question #2:
Is there a way I can duplicate this form (Frm_AddEvent) and have the exact same form BUT have it so that it locates any existing event by using a combo-box or some sort (does not add new events, can only pull a single event) and a user can then modify the clients list? (This would have the same logic from my question above where they cannot edit any event information except for the "client list")

Questions #3
I want to be able to generate a printable report which is based on a single event (Im thinking I could pull this report based on event location & Date, unless you can think of a better way ). I would like to be able to select from a list of current events and then click on one. THen this report would have the event info at the top, and below would have a list of all the clients attending (Including the counts of males/females as well ) as well as the list of the employee(s) working this event. I tried to make one myself but all I could get was ID #'s and I could not get it to work.

I am hoping to use this for when I work the events I will have all the information printed out at my finger tips



Questions #4 <-----**Overall if this takes more than 5 min to do... forget it!!** not a big deal
I also have just found out when I use the "Frm_Addevent" (the one with the client and employee subform at the bototm) If I enter in my clients and employees 1st, and then I populate my event information, as soon as i begin entereing my event info, all of the clients and employees dissappear from the list! Is this normal? is there a way to prevent this? Otherise this isnt a big deal at all. I can easily have instructions to enter event information 1st, but was just wondering if there was an easy way to prevent losing the list of clients/employee if they are entered in before the event information.

Here is my new version of the database:
Leland's Event Management Database_2015_Oct_16.zip

[JamesDeckert]

King,
#2 Here's how to make a lookup combobox once you've copied the form
https://www.youtube.com/watch?v=EdH1aIWaD-0

[ssanfu]

A1) Use the control names just like I did. See the code. Cannot edit the event info.

A2) Yes. See Frm_EditEvent.

A3) Create a query, base the report on the query.

A4) Using the AddEvent form, you MUST add the event first, then the clients and employees. In the junction tables, there is a field Event_FK. If the event has not been entered, what gets entered into that field?
How do you assign clients/employees to an event that does not exist?

[KingOf206]

Ok Great! Thank you Ssanfu.. I am stuck on your answer to q#3 and am having three issues:

I have already attempted to make this query a few times, but I come up short each time.. THis is as far as I have got (See image below the current qry is called "Qry_SearchByEventWithListOfClientsAndEmployee s") But there are issues with this query, such as:


1 - Its not pulling all the records! EEKK --- I have a total of 25 people attending all the events when i count them up, however this query is only pulling 23 of them (the 2 its missing are from the event @ Earls on 12.25.2015, why is this?)

2 - I cannot add the employees to this query as it adds multiple extra records

3 - I would like it to be searchable by "GroupType/EventLocation/ & Date". I know I could type in the brackets in the criteria field, but I would prefer a search by combo/list box, this would make this qry/report so much easy to pull. How can I accomplish this?






Leland's Event Management Database_2015_Oct_19-B.zip

A3) Create a query, base the report on the query.