Question How to let user add fields to form (without going in to design view)

[remcopeters]

Hi,

I got a form called "Make quote". In this form a user can select a product from a line of fields. This "line" contains: article code, product, price and quantity the customer requests.

Each single quote can contain a very different amount of products. Some quotes just have 1 product, other quotes have 25 products.
I'm looking for a way to let the user add as many "lines" as necessary for the quote. BUT I don't want him to need to go into design view.

I'm thinking of maybe adding a button to the form which does this, but I'm not sure how.

Does anyone have an idea about this?

 

[mahenkj2]

I think Acces has already this feature in the form of Sub form. You can allow users to enter new records.

 

[Swemebegur]

Hi Remcopeters

I suspect that you may be "committing spreadsheet" with an un-normalised table structure. With properly normalised tables, with the quote table as the parent and the quote lines in a separate child table, you would be able to use a standard form and subform with the lines table records in the subform. Then the user can add as many lines as he likes.

Can you tell us your table structure?

HTH
Swemebegur

 

[nanscombe]

As has been mentioned previously a Subform is probably the best way to go.

Have a look at the attached example.

 

[remcopeters]

Working with the subform looks good Nanscombe! But I can't figure out how to make a new line appear by itself :banghead:

 

[nanscombe]

Just start entering data into the blank record at the bottom (the one with the *), it's automatic.

 

[remcopeters]

Yeah, that I did figure out, haha.....what I mean is how to create such a thing.....I tried it by just making a form based on the table I want the products and quantity to be in and then adding this form as a subform to the "make quote" form. This doesn't do the trick..........

 

[nanscombe]

The quote is one table / form which is the master form.

The items are a separate table / form and is the child form.

The child form is dragged on to the master form, that creates the sub form part.

What you then have to do is select the Subform object and look at the property Sheet.

There are a pair of properties Link Master Field and Link Child Field. The Master field would be the ID of the quotation. The Child field would be the field in the quotation items table which links back to the quotation table.

tblQuote:
ID (autonumber) - Master field
Client (Text)

tblQuoteItems:
ID (autonumber)
qiQuoteId (number) - Child field, this links it back to tblQuote
qiItemId (number) - This is derived from the lookup table tblItems
qiQuantity (number) - The Quantity

So, in this case, Link Master Field would be ID (from tblQuote) and Link Child Field would be qiQuoteId (from tblQuoteItems).

 

[remcopeters]

I really am to stupid for this.......Can't get it to work

 

[remcopeters]

I want the form to do the above and look things up in another table at the same time. Can someone please help me? I'm new to Access and have no clue about this.

 

[nanscombe]

There are two files in the attached Zip file:

remcopeters_02.mdb - Which is the example database, as above.

remcopeters01_StepByStep_v2.pdf - A PDF document which should take you through the processes needed to recreate the database, step by step.

I hope it's useful.