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,hyperlink, 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.

[antjonz]

I am using Access 2007.

[Rabbit]

2007 does have an image control. It looks like a mountain with a sun in the upper right.

[antjonz]

Sorry it took me a while to respond. I do see that now. I placed the image (control) on the form in design view, and set the control source to the field that has the image attachment. However it still didn't work. I will try to mess with it tonight to see if I can figure out what I did wrong.

[zmbd]

We currently have two active threads that really amount to the same question:
“How Do I get an image in/on-to my form/report”
So for a little background and one method:
Use images in your Access forms, reports, and controls (v2003) I think it is very important that there is an understanding of the fundamental methods of storage methods in the older MDB files as there is some carry over into the V2007/2010 formats (maybe 2013 too; however, I know very little about that version).
Provided in this link is also a link for a video for V2010 Video: Add, reuse, and update images on forms and reports
Finally, the way I’ve done this for years is to create a custom function and use it as the “picture” value for a “linked” image. The function refers to another control on the form for the file name and then works out the relative path via the currentproject.path value. Perhaps I may post an example database using my function.
However, shortly after I had figured out how to do this on my own I came across the following information that does something similar in that it uses a custom function; however, still much different in the final implementation: How to display images from a folder in a form, a report, or a data access page Article ID: 285820 (v2003) where in the events are used for updating the images, whereas my does not use any events.

If there's enough interest, I might create that generic db using my people template and some clipart... wont take me but a moment or two as the rest of the code is already in production.

[antjonz]

Zmbd, Rabbit and Seth,

Thanks for all of your help. I appreciate you guys taking the time in helping me. Unfortunately I still cannot get the pictures to open when I select a customer from the dropdown on my main form. I've attached a .jpg of my customer Table and my main form in Report view and Design view(image control property is displayed). I don't know if that will help any, but maybe you guys can see something I dont.