Need to design form to add existing records (people) to event or add new records to same event.

[WeekendCoder]

Basic Story:

I have an event table. Each event may have 1 or more people involved. I'd like to design a subform on the event form which will allow me to enter in a person's ID# and if they are already in the people table to add them to the event, if not, allow me to enter additional information about them (firstname, lastname) and add them to the event in the same form.



My Attempts so far:

I've tried a subform with a continuous form with a combo box which worked well if they were already in the table. It would be easier for me to input the persons ID# than search a combo box. Also, it's more likely that a person would not already be in the people table yet but i'd like to prevent the form to attempt to enter a duplicate (even though it won't).

My thinking:

I am currently trying a similar attack with a subform with a continuous form but with all the captions. The user would input the person's ID# and if found would populate the rest of the captions but if not would allow the user to enter the rest of the required captions.

Is this possible with regular form design, or will it require more advance VBA?
My VBA is basic to intermediate.


Any help would be much appreciated,
Thanks in Advance,

Jim

[orange]

I recommend you start with tables and relationships.

From your post I see you have

People and Events
1 or more People can be involved an Event

Form and subform are ideal to handle 1 to Many relationship. Form represents the 1, subform the Many.



So 1 Event may involve may People.
Can 1 Person/People be involved in Many Events?

[WeekendCoder]

Thank you for your quick response. I have all the tables set up with a few many to many relationships with linking tables. I have created most of the rest of the form. I have another similar many to many relationship set up where I choose from a set list of choices. The difference here is i want the ability to add new people to the event which also adds them to the people table. The difficulty is setting up the form properly so i can see those who are already connected to the event and the ability to find out if the new person is already in the table and if not add them.

So your question at the end:
1 event may have more than 1 person involved
1 person may be involved in more than 1 event

[WeekendCoder]

In addition, the form will show one event at a time.

[orange]

So you will have a set up that includes
This is just a sample for consideration:

People----->PersonIsInvolvedInEvent<-----Event

The junction table can have its own PK eg PersonEventID, but you should create a composite unique index on
PersonID, EventID to prevent duplicates.

So you may have records
People
01 John
02 Mary
03 Ezra
04 Otis
05 Slim

and Events

01 Arts Festival
02 JazzFest
03 DrinkingParty

And you junction could have

PersonEventID
PersonId FK to Person make a unique composite index on PersonId,EventId to prevent duplicates No NULLs
EventId FK to Event

with data eg
01 01 01 Pk1 John is involved in Arts Festival
02 04 01 pk2 Otis is involved in Arts Festival
02 04 03 pk3 Otis is involved in DrinkingParty

Good luck.

[WeekendCoder]

Let me clarify something, i'm going to refer to the persons ID# as their employee # to not confuse it with the autogenerated ID number.

I thought I found a solution using VBA to lookup the values of the other fields based on the employee ID and insert them into the form. The only issue is the autogenerated ID number, i cannot overwrite that number in the new record field. Is there any go around for this dilemma?

Would is be proper to remove the autogenerated field and use the employee ID (not autogenerated) as the primary key?

[WeekendCoder]

Maybe I'm not explaining myself well enough. I have all that. I have two tables, one for the event, one for the people and then i have one to link the two for a many to many relationship. The problem i am encountering is how to input the data using a form.

I need to add people to the event.
1. I'd like the user to enter the employee ID
2. If the employee is already in the employee table, auto fill the other captions with the information.
3. If the employee is NOT already in the employee table, allow the user to complete the captions with the required information

This is why I added the post to the forms section and not another. I hope this helps.

As always, i appreciate your help. Thank you.

[orange]

I wouldn't, you could, but a few things first.
You will do yourself a big favor if you restrict field and object names to alphanumerics and "_" (underscore) characters only.
Access doesn't like embedded spaces or "special characters" such as #%+/ etc in names.

Relational database tables require each record to be unique. It is easiest to leave that issue to an autonumber (autogenerated) field. The database software will take care of that. That is typically called the PersonId or ProductID etc.
If you have a field such as EmployeeNumber that is displayed and/or used by staff, then I would use that as a separate field called EmployeeNumber and lahe it as a text data type. (You could use a Number data type, but because you aren't going to do any arithmetic with it, I'd use text.

The very first thing I suggest when designing a database is to identify the Things and the attributes (Tables and Fields eventually), and how the things are related (relationships). Build a data model - can be pencil and paper. Then some test data and scenarios. Work the test scenarios against the model, make sure everything is accounted for. (eg if something doesn't quite work -get ot the bottom of it --is it the model, a relationship or test data. Reconcile every anomaly then retest and repeat until it all works. Now you have a blueprint for your design. Too many people jump in expecting Acces (or other dbms) to "do it" for them.

Enough soap box. Purists and academics would offer different descriptions, but the above is sufficient for now.
Good luck.

Also, there are a number of free data models here that may help. These models are generic in nature and reresent typical scenarios. Yours may be different or only involve a portion of the sample.

[ssanfu]

Would is be proper to remove the autogenerated field and use the employee ID (not autogenerated) as the primary key?

I wouldn't.
See
Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

[WeekendCoder]

Ok, so how would I create a simple subform within my event form that will allow me to enter in an employee ID# and do 1 of 2 things?

1. If the employee is already in the employee table have the firstname and lastname fields populate and add the employee to this event.
2. If the employee is NOT in the employee table the user will be able to enter the information into the firstname and lastname fields and add the employee to this event.

[orange]

Just from a logic point of view, if you have an Event Form (based on the event table) and a PersonAttendsEvent type of subform,

you could have a list of Employees show up and select those Employee(s) involved in the event.
If however, the Employee was not in your table, you could add the Employee to the table (or add the Employee to the list), then requery the list to get the latest Employee(s), then select the newly added Employee for inclusion in this Event..

[WeekendCoder]

Thanks for the quick response once again. I'm attempting that now. I tried making an "Add Employee" button which pop opens another form with all the captions. Got it working... it adds the employee to the employee table and the event but I tried a requery of the combo box on the main form's subform and it doesn't seem to be refreshing. I have to click the combo box and click refresh from the ribbon.

Do you have a fix for this? Maybe I'm not referencing the combo box correctly.

EventLogFrm = Main Form
MOSSubform = Subform Control housing MOSFrm
MOSFrm = Employee at Event Subform
MOSAddFrm = Pop up form to add employee
cboFullName = Combo box with employee ID, lastname, firstname

I'm doing it on a "Close" event. Should i do it "AfterInsert" or something else?

Private Sub Form_Close()
    DoCmd.Requery Forms!EventLogFrm!MOSFrm.Form!cboFullName
End Sub

As always, Thank you!

[orange]

For which specific form is this the Close Event?


My best guess is that when you close MOSAddFrm (= Pop up form to add employee)

You could add a line of vba (untested)
Forms!EventLogFrm!MOSFrm.Requery