Open House db

[cardgunner]

I have been given the task to create a db for our open house where we can record who shows up.

We have our current businesses[extend_names], new bussinesses or corrections to be made to our current bussinesses[2011_attendee] and the contacts for both[2011_attendee_contacts]

When someone arrives at our open house I would like our registars to look up and see if the business is in our system already. if it is the registar would ask them if the information we have for them is correct. If it was we record the name, email, phone, and possibly address of the contact. If the information was not correct we record the corrected, or additional, info and then go on to record the contact info. If the business is not there they record the new info and then the new contact.

The corrected, new, or additional info on the bussinesses needs to be recorded in [2011_attendee]. I will review this later to verify the info collected is correct. I don't want them updateing or changing any info in [extend names].

[extend names] has a unique field, bpid. [2011_attendee] has a unique field, alt_id. [2011_attendee_contacts] is p_id.

I have combo box, bpid_box, on main form for the user to select the business name. The record source for this combo box is a union query between [extend_names] and [2011_attendee].The selection of the combox box displays info in form.extend_listing.

If the user needs to record new info on the business they do it in form.2011_attendee_form. This creates a new record in [2011_attendee] and this creates a new unique value in alt_id.

The contact info is recorded in form.2011_attendee_contact.

I have form.extend-Listings linked to the bpid_box on field bpid.
I have form.2011_attendee_form linked to bpid and alt_id.
I have form.2011_attendee_contacts linked to bpid and alt_id

The issues I am having is when we create a new record in form.2011_attendee_form it dissappears. I have to refresh data and select record from combo box. Then finish recording the rest of the info. This is because that form is linked by the bpid and the alt_id. The alt_id doesn't get generated till a record is created. Once a record is created the link no longer is valid. How can this work?

Imagine people standing in 2 lines registering. 4 people from same new business, random in line. First person comes up - new business is recorded - new contact is recorded. Another person comes up a little later from same new business. I want to select that new business and not have to record all that info again.

 

[YNWA]

Sounds like a complicated way of assiging people to businesses.

Why not have alist of business stored, then assign contacts to that business.

So you only enter business info once, then for each person that turns up, you just enter their name, dob etc.. The link then assigns them to this business.

You have a 1 to many from busineess table to people table.

you have

Businesstbl with fields ID, BusinessName, Address etc...

Then Peopletbl with fields peopleID, businessID, name, dob etc...

The link then connects from ID to businessID.

 

[cardgunner]

If it was that simple I would do that.

But what about new businesses?
What about the corrections to the existing businesses?
What about addition info of an existing businesses gathered at the open house?

If only existing business where coming it would be alot easier.

 

[YNWA]

Create a form to input new entries for businesses.

Use the form to edit any details for existing businesses.

Create a template for the info you gather and make your table and form replicate that template.

I'm getting confused in what you are actually trying to record? What addition info is there?

 

[cardgunner]

Create a form to input new entries for businesses.

Use the form to edit any details for existing businesses.

Thank you. Yes I could, and I might have to. I have been trying different things for days now. I wanted to keep the user on one form. If I can't get a workable solution that keeps them on one form I will add a command button that will open a form where they enter will enter the corrected, additional, or new info. This new form, let's call it form.new_info, will be tied to form.register by the bpid. It will have a subform for contact. I could do that.

However I wanted to keep them everything on one form.

I'm getting confused in what you are actually trying to record?

I'm trying to record who has attended our Open House. I also would like to verify the information we have for our existing businesses is correct. If it is not correct then what is the corrected info.

What addition info is there?

Alternate mailing addresses, shipping addresses, email addresses, phone numbers. Maybe what we have is correct however the peoople attending says "that is only for billing if you want to stop out go to ..." That would be additional info.

Thank you for your help.

 

[YNWA]

Thank you. Yes I could, and I might have to. I have been trying different things for days now. I wanted to keep the user on one form. If I can't get a workable solution that keeps them on one form I will add a command button that will open a form where they enter will enter the corrected, additional, or new info. This new form, let's call it form.new_info, will be tied to form.register by the bpid. It will have a subform for contact. I could do that.

However I wanted to keep them everything on one form. - Use Tabs within your form and put fields or subforms on each tab, link the subforms through IDs.

Tab1=Business, Tab2=people


I'm trying to record who has attended our Open House. I also would like to verify the information we have for our existing businesses is correct. If it is not correct then what is the corrected info. - using a form you can simply ask them if the details are correct, if not overwrite current fields in the form.


Alternate mailing addresses, shipping addresses, email addresses, phone numbers. Maybe what we have is correct however the peoople attending says "that is only for billing if you want to stop out go to ..." That would be additional info. - Create your table to record all this info anyway, if you dont know it for a business leave it blank, no biggie, some businesses will use the fields and some wont. But have all the fields visible on your form to use as and when needed??

Thank you for your help.

You want your table set up to be BusinessID in Businesstbl to BusinessID in Peopletbl.

On your forms you have BusinessForm, then subform on another tab if you wish PeopleForm. The MasterID will be businessID in Businesstbl to businessID in peopletbl. Therefore linking each person back to a business.