Auto add a field line in Access?

[decay]

Hi I have made lots of Access databases over the years but it seems I have fogotton lots of basic principles since I have not used it for ages.

One thing I want to do is:

For instance when you send an email using Yahoo or similar webmail if you are adding an attachment there are 5 spaces to add 5 file attachments. If you are adding more than 5 you click on add more files and another box appears ready for you to add another attachment.

I want this to happen in Access only instead of adding file attachments I want it to add a como box each time that is controled by data in one of my tables.

I.e. 1 blank combo box that I can drop down and select a product. then click add new and another blank combo box would appear underneath it and so on. Also eventually the space would run out so the actual form size would need to auto grow to accomodate the extra fields.

Any ideas? Please be nice I am not a newbie but you may as well treat me as one until I remember how things roll.

[timothybard]

The easiest thing to do would be to make another table to store the "attachments" with the following fields:

ID, e-mail ID (FK), attachment

I realize that your situation not actually be e-mails and attachments, but I wanted to keep the same example to make things easier.

After you need to make an e-mail form to record data about the e-mail. On the e-mail form, you need an attachment sub-form, which lists all the attachments for that e-mail. You can format the attachment sub-form so that it only shows one record at first, so you can add an attachment, but after an attachment is added, another "attachment" combo box is displayed for an additional attachment. As you continue to add "attachments" and they take up more room, scroll bars will automatically appear on the subform so you can scroll through the attachments.

I know you mentioned adding fields to the table, but I think having a seperate table to store attachments would be more appropriate since, when you have multiple attachments, the database is still normalized.

Let me know if you need any more help.

[decay]

Thank you I see much logic to your reply.

I can also get as far as formatting the subform to only display one "attachment" at first however how do I get the next one to display after that one is entered?

I am also having another problem with relationships. I used to do this stuff a few years back and it came natural to me but my mind is blank. Every now and then the odd thing comes back.

Problem 2 I have a customer table and form and I have a product form (products are to be the email attachments if you are keeping with the association) These products are categorised such as marmalades, jams, vinegars, chutneys, etc. Now I can't decide the best way to store all the info. I got all the fields I need but can not decide weather to have one big product table with a product type field or to have seperate tables for each product type I.e. a table each for marmalades, jams, chutneys etc. I made all the tables and then also to be same imported them all into a massive " all products" table. I am having trouble relating the tables and do not know which approach to take.

Dilemma: 1 large table or lots of small ones?

I am basically trying to get a 1 to many customer order database so customer form has sub form for order and one customer can have many orders.

Thank you again for your time

[timothybard]

As far as the subform, access will control showing the additional "attachments" rows. Before you enter any attachments, there will be one row that is displayed to enter an attachment; however, keep in mind that the empty row does not actually exist in the database and Access will only create a row once you save data in the initially empty row.

Once you save a row, Access will add another blank row for you to add additional attachments; however, again, even though an empty row is displayed, no data is recorded until you fill in data into the row.

As far as the product table, it sounds like the structure you need for each product is the same, so I would strongly recommend using one table for all of the products and just have a field in the table to track the type of product that is being recorded. The only time an issue would arise from this structure is when some fields apply to products of one type and do not apply to products of different types.

[decay]

OK Thank you again! I think I am almost there. So I get rid of all my other tables which leaves me the following 4:

1) Customer Table
2) Customer Order Table
3) All Products Table
4) Product Type Table (This table only contains Jams, Marmalades, Chutneys etc no other data appart from ID field).

I now want to click a button on Customer (Main Home form) that opens customer order form (which carries accross the customer name and ID from customer table).

For starters this goes to record one and does not update to the relevant customer record I chose in the 1st form)

On the 2nd Customer order form I want a sub form that we mentioned with the blank rows that we talked about. I then fill in the order on that form. One customer can have many orders. So if I click next customer and that customers orders show. I am hoping I can now cope with this.

In the relationship Diagram I have dropped my 4 tables and now want to relate them They all have an ID field which is the Primary Key. I also have relevant fields I have linked such as customer name to customer name then product type to product type. It wont let me use referential integrity as it says I have no unique field. I want to make one to many relationships etc by drawing the line to fields. I am not sure how to link them and get it to carry the relevant record accross in the form.

[decay]

Ok scrap my last post I have finally got what we talked about working. I have a form with subform and have sussed the one to many relationship diagram. Now when I select a customer it allows me to add items for that customer.

However this is not entirely what I am after. I want each record in my subform to be a new order not a new item. An order can have many items on it so therefore each record would have to contain all the order items which could be quite large. This brings a bigger problem. I can not product type each item as they are all in one record and that whole record or "order" would be the same product type.

I really can't get my head round how to go about this. I think I need yet another table for orders but not sure how to link or what to have in it.

[timothybard]

If I understood everything correctly, what you need is an additional order table. The customer table would then use the order table as a subform. The issue you then have is how to add products to the order from the form.

On option I have used frequently is to allow the user to double-click on the order record in the subform, which would then open up an "order" form. The order form would then have a subform that lists all the products on that order.

Therefore, you would have one purchase form that has an order subform and then an order form that has a product subform. When a user double-clicks an order on the subform within the customer form, the order form opens up (displaying to selected order) and lists all the products on that order in the subform.

[decay]

Thank you so much for your advice. I have now got my relationships in order. I also had my foreign key not the same data type as my primary key and this did not allow referential integrity causing strange results.

I now have a customer table & form linking to order form which in turn stores products in order details sub form. Its working! However I have also linked my all products to order details but that is not working as I want it.

I want to choose product type giving me only fields from my all products table that match that product type. I then want to select my product (thats been filtered by product type previous) and the unit price and RRP values to auto fill in. This data is all stored in all products table.

But my product type does not filter my results and when I select my product the other price values do not fill

Any ideas please?

[oladapo sorinol]

Hello, I need your help please...mine is close to the issue above. I have a db for POS deployment to monitor Merchant requests through to deployment. When a Merchant requests for more than 1 POS machine, i want the user to click on the button "Add" to enter data for additional request belonging to same Merchant. This new line with have 3 parameters. 1- Location, 2- Name of Contact Person while 3rd is Phone number. User should be able to add as many new requests for a particular Merchant.

Thank you

[yeloraby]

Thanks the subform and the attachment table is a very good idea.
I an developing a real estate management system for Coldwell Banker New homes Egypt and I need to add multi-photo per single property, and this a good idea to use in such case.