Multiple forms linked to one data Table

[antjonz]

Hello everyone!! I'm a newbie here so please no big words. :-) This is my first time building a database, but I feel like I am learning and making progress. Here is my problem:

The database I'm building is strictly for retrevial of data, not updating (for now). I have one data table that contains information on 14 different customers (businesses), and I also only created one query for a report. I created a switchboard that has a button linked to 14 different forms I created for each customer. The reason I did that was because I wanted the customer's logo to appear when their button is pressed, so I thought that was the easiest way (I was able to setup each form with a picture of each customer's logo). Each form is basically set up identical to each other as to retreive the same types of information. I am able to set up my 1st customer to get the report I want, using this expression ( [Forms]![AIR PACIFIC]![cboAirline] )in the criteria spot in the customer field of the query. I also use this expression ( Between[Form]![AIR PACIFIC]![txtStartDate] And [Form]![AIR PACIFIC]![txtEndDate] ) in the Request Date criteria field of the same query. However, when I go to setup my 2nd customer, I'm not able to. Before I waste my time even further, is it even possible to do what I am trying to do? Any help anyone provides will be greatly appreciated. Thanks in advance!!

[Rabbit]

You should only use one form with the location of the logo stored in the table and the image on the form linked to that location.

[Seth Schrock]

You don't really say exactly what you are trying to do, only what you have tried. However, here are a few tips:
1) Read Database Normalization and Table Structures. This is something everyone who designs databases should know and follow.
2) In the table that stores your customer information, include a field for the logo. That way, you have one form that works for all of your customers. Otherwise, you will have to do a lot of work to add another customer.

[antjonz]

Seth...

Thanks for the reply and the info. I am trying to create a database that the people in my office can use to pull data for a specific customer. I'm trying to link all 14 forms to the one data table and one query. I was trying to use a "BETWEEN" function in the query (Between[Form]![AIR PACIFIC]![txtStartDate] And [Form]![SUN COUNTRY]![txtEndDate]), so regardless of which form is being used, it will pull the information for that customer only, but I guess the "BETWEEN" function doesn't work.

[Rabbit]

What we're trying to say is that your design is flawed and you should have only one form. You can still achieve "customized " forms (i.e. your different logos) merely be using one form and linking the picture to a location stored in the table.

[Seth Schrock]

The between function does work. Did you change the form name for each form?

You really should change your design to have all of the customers on one form. You will save yourself all kinds of headaches.

[antjonz]

Rabbit...yes, I understood what you were saying about the logos in the table. So it doesn't matter if my table is an excel file I imported into access?

[antjonz]

Seth...I did change the form name on each form. I guess I will go back to the drawing board on my design. lol. So I'll be able to use the "between" function in the query if I use it on just one form? Will a switchboard be necessary?

[Rabbit]

@anton, it doesn't matter, you just need to store the location of the image file somewhere. That way you don't need 14 forms and 14 different between clauses.

[antjonz]

Thanks again Rabbit...would it be an OLE object,hyperlin k, or an Attachment? Also, is there a way to copy and paste into multiple fields? For example, my first customer has 335 rows of data in the data table. In the new field I created, I will have to format each of the 335 new fields into the one that will have the logo, but I don't see a way that I can copy and paste like in excel. All I can do is copy and paste one at a time. After I update all the fields with the customer's logos, how do I set it up so that the logo show up on the form?

[Seth Schrock]

Here is where you need to make sure that your database is normalized (see the link in post #3). You should have a customers table that would be related to your current table with all of its information. The logo would then be put in the customers table. That way, the customer information is only entered once.

Personally, I choose the Attachment file type for pictures as long as I don't have to be compatible with Access 2003 or earlier.

[antjonz]

Ok Seth, I created the 2nd table with each customer's name and attached the logo in the adjacent field. I also "related" both customer fields in the tables. Now...how do I get the logo to show up? I have a combo box on the main form that will drop down and show each customer that I can select(I will delete the other 13 forms). So how do I get the selected customer's logo to appear on the main form?

[Seth Schrock]

Just put an Image control on the form and then set its control source to the field that has the picture.

[antjonz]

Seth, I'm beginning to feel really stupid because I do not see an "image control" option. I'm looking at the "Controls" section in the "Form Design" tools and do not see it. I see bound/unbound object frame, text box, label, image and various other buttons.

[Seth Schrock]

What version of Access are you using? I'm looking at Access 2010, and I thought that 2007 had it as well.