Access form design record look up

[RockKandee]

I have tried to search for answers that already exist, but I lack the proper vocabulary.

I am using Windows 8 - Access 2013

I need to add existing and new customers to events.

I am looking for a good way to do this in a form.

Adding new customers is a breeze. Adding existing customers is not so easy and I have to believe it should be.

Could someone point me to an example database?

[zmbd]

RockKandee:
What are you asking?
What is it you're trying to do?

If you are asking someone to point you to the correct templates or help you design a project from scratch, then that's really not what we're here to do... nor could we from what little you've provided.

If you are just starting out with Access then you should work thru the following tutorials so as to "get to speed" with the proper terms and foundation skill set:
---

• MS Access 2010 Tutorials
• Access Basics - By Crystal
• > Database Normalization and Table Structures.
• Training courses for Access 2013

---

[RockKandee]

No I wasn't asking anyone to help me design from scratch or point me to a template I could use. I was hoping someone knew of an example I could look at that would help me understand.

I am trying to find the best way to to create my forms for data entry when I need to connect info from 2 tables together.

Such as:
Table 1 = Event + Event details (time, location, etc)
Table 2 = Customers + contact info
I want to connect a customer to an event.
I want to do it by pulling up the event once and listing the attendees and be able to add attendees as/if needed.

Currently I have a search name set up to give me the customer ID and then I add this to the event. I know I can automate the addition (will deal with that later).

I need this type of connection with more that just the 2 example tables I used. For every search I set up on a form, it requires its own query just for the search part. I think there has to exist a way to avoid making all of these queries. I tried to use SQL but I don't have enough experience yet to make this work.

So I thought I would ask what a common way of doing this is.

I will read through the tutorials you posted and see if I can find the proper language to use to ask my question in a better way.

Thank You

[zmbd]

RockKandee
It was really difficult to figure out what you wanted from the OP.
Actually, once you work thru the first tutorial I think you will actually have the tools you need to do what you want!

And it sounds very much like you're on the way to having the tables normalized which is a good thing!

Comboboxes are quite friendly in this case; however, Stay away from lookup fields at the table level unless you are going to use sharepoint (lookup fields are CBO equivalent at the table level).

Basically, you're going to go into databasetools and setup relationships between the primary key in one table and a linking table... take a look at :

I've a very simple database (faithformationtracking) at: http://bytes.com/topic/access/answer...esign-question

You should be able to download here:
http://bytes.com/attachments/attachm...ontracking.zip

Keep in mind that the database was designed for the thread and isn't actually intended to be a day to day use... although it would be serviceable... needs a tweek or two to finish normalizing and a slightly better form.

You'll want to look at the table relationships... once you get thru the tutorials it will make much more sense.

[RockKandee]

My tables are normalized and already have there relationships in place.

I have too many customers to use a form like the one you attached.

I had heard not to use DLookup which is why I did the search this way....

I am using 3 unbound text boxes (First,Nick,Last Name) with a macro that runs on update of the last box - set filter - where -

[ID]=Forms![FormName]!UnbtxName

I have a query with this in the criteria for each of the name fields I am using in my search.

Like [Forms]![FormName]![UnbtxName]

Like "*" & [Forms]![FormName]![unbtxName] & "*"

When I enter names in the unbtx it populates a unbound list box with any matching names. When I select a name from the list the record for that student is displayed or a blank record if no record already exists.

This is set up a bit differently depending on the form.

I have a form where I enter a date and get a list box with events on that date. When I select the event in the list box it populates the sub form with a list of who is attending the event and has blank records to add new customers. When I need to add existing customers, I use a pop up form with the same search method to get the customer ID and add them to the event.

I hope that gives a basic idea of what I am currently doing.

However, this requires a separate query for each form because the form name is specified in each query and each form has a different name.

I was hoping to find something that didn't require a separate query every time I use the method OR a better option.

Thanks again for your time

[NeoPa]

Essentially, such a form would be built on the table which is used as the bridge. This is a many-to-many link table which, in its most common form, simply contains two foreign keys (FKs).

Each of the FKs would be represented by a ComboBox from where you can select an item from each of the relevant tables. This way the record being added or edited can be a link between the two tables.

Does that help to put the situation into clearer perspective?

[NeoPa]

I see you may be beyond that stage now. Not sure exactly where you're going with all your unbound controls but it's possible the idea of Cascaded Form Filtering might be of use. If not just ignore this. Best of luck anyway.

[zmbd]

RockKandee:

Perhaps, you did NOT read what I wrote in #4?

My tables are normalized and already have there relationships in place

I said that your tables sounded normalized from what you posted in #3... in fact, if you had started with the information in #5 we could have been well beyond basics.

I have too many customers to use a form like the one you attached.

I also said that the database was just an example of how the linking table would work, that you should take a look at how the tables were related. (which Neopa re-explains in #6, what I explained in #4 paragraph #4 and pointed you to in the database in #4 last paragraph... which, because you didn't appear to know about linking tables from either of your first two posts, I figured you would understand better after looking thru the tutorials)

I am using 3 unbound text boxes (...)

Now, finally we get to your third post (#5) where you start to provide some real details; however, even Neopa is having troubles determining what you are after and he has considerably more time in this forum and programs for a living.

The more details you provide to start with, and the clearer your question, the better the solution offered in a more timely manner. However, when you start out with such vague questions as in #1 be prepared to either not receive an answer or to get pointed to something as equally generic and try not to take offense - we've used what you've given us in order to provide the best answer within the context of the thread.

Indeed, if you had started out with the major concepts in post#1, post#3, and post#5; I would have pointed you both to the linking table and the cascading forms with my first reply.

[RockKandee]

NeoPa:

Thank you for the information. I will work on this.

I appreciate the time and help from everyone --- THANK YOU!!

[RockKandee]

I am trying to understand the Cascaded form filtering to see if it is what I am looking for.

Does this basically filter out matching records of the first box and then the second box selects from remaining records, and so on?

For example if I were to set this up using
last name, first name, and nick name
When I enter last name = Smith
then enter first name = Joe
only Joe with the last name of Smith would show up?

I am trying to understand what it does not how to do it.

[NeoPa]

Cascaded form filtering, as opposed to simple form filtering with multiple filters, filters the lower level ComboBoxes as well as the form itself.

So, for instance, you may have a State ComboBox as well as a City ComboBox. Once a State has been selected all Cities that are not in that State get excluded from the City ComboBox. The form would also, typically, be filtered by the State ComboBox at the same time.

Does that help explain what is meant by Cascading ComboBoxes or Filtering?

[RockKandee]

Yes it does explain. The use of states and cities made for an excellent example. Thank you!

[NeoPa]

Very pleased to hear it. Not all situations benefit from cascaded filtering, but if yours does then it's a good idea to use it, and quite user-friendly.

Do you now have enough to be getting along with?

[RockKandee]

I can't use the cascaded filters for my question on this post.

I was just saying you explained how it worked very clearly.

I have something I am going to try as soon as I finish fixing other things I broke. I am kinda drowning right now.

I will either be back to this post with my solution or with a "help me please".

[NeoPa]

Example Filtering on a Form is another one that may prove helpful when you get back to looking at this. I'll leave it with you for now.

[RockKandee]

Here is what I ended up doing. I basically left it the way it was except I moved the query criteria into the list box row source query builder so I could eliminate having a query in my nav pane for every form I use this process with.

Having too many of these queries was the reason I began this post.

I appreciate all the info on form filtering.

For this question, I couldn't use it because I needed more flexibility with my searches.

I am sure I will need the info provided in the future.

Thanks Again!