Question Help wanted with quote database (1 Viewer)

[remcopeters]

Hi,

I'm currently working an a quote database (see attachments). Prices etc. have been edited because the real prices of course may not be visible to everyone ;-)

Now, I'm pretty happy with what I've accomplished myself until now. Despite this fact, I'm now running in to some things I don't know.
The database is in Dutch. Below I describe what I want and I name the Dutch words for you. If this still is to complicated for you guys, just tell me, then I'll translate everything to English.

First off all I want to complete the form called "Offerte formulier". What I want to add to this form:

Solved
1) I got a table "Gebruikers" and a table "Offertenummers". Users enter their name before using the "program". This name is stored within the table "Gebruikers" and also on the form "Offerte formulier". What the table "Offertenummers" does is store the last used Quotenumber for the different users. In the form "Offerte formulier" I got a textfield with the label "Offerte-nr." next to it. What I want is:
The textfield "Offerte-nr." gets the last used Quotenumber for the current user and adds "1" to this. So if the last used quotenumber for user3 is "2012RP0001", I want the program to place "2012RP0002" in the textfield "Offerte-nr.".
I removed the table "offertenummers" and added the "gebruikers-ID" to the "Offertes" table. In the textfield "Offertenummer" on the form, I use this code to make it look up the quote number and add 1:

=DMax("[Offertenummer]";"[Offertes]";"[Offertes].[Gebruikers-ID]=" & Formulieren![Offerte formulier]!txtGebruikersID)+1

Solved
2) I got a table "Klanten" in which companies are being stored. I also got a table "Klant-contactpersonen" in which specific persons for the companies in "Klanten" are being stored.
In the form "Offerte formulier, behind the label "Huidige/nieuwe klant" a user can select whether the quote is being made for a new or a current customer. When the user selects that it is a quote for a new customer (he selects "nieuwe klant"), this customer isn't available in the table "klanten" so I want the program to present the user with a new screen if "Nieuwe klant" is selected. In this screen I want that the user can enter data about the new customer (company and person). Next I want a button on this new screen which saves the data to the designated tables. On clicking the button, I also want the company name of the added company to appear in the textfield behind "bedrijfsnaam" on the form "Offerte formulier" and the name of the added person to appear in the textfield behind "achternaam" on the form "Offerte formulier".
I just added a bit of "afterupdate" coding on the field whichs opens a new screen if "nieuwe klant" is selected.

Solved
3) If a company is selected in the dropdownbox behind "Bedrijfsnaam" on the form, what I want is:
- in the dropdownbox behind "Achternaam" I want that only those contacts are available working for the selected company. This can be done from the table "klanten-contactpersonen".
I added a dropdownbox called "cmbKlantID" and a dropdownbox called "cmbContactpersoon". The "cmbKlantID" shows a list of companies with their customer ID, when one is selected only its customerID shows up in the field. Next I set up "cmbContactpersoon" to show me "contactperson ID", customerID and Lastname of the contactperson. The criteria for "customer ID" of cmbContactperson" is set to match the customerID in "cmbKlantID". I don't know if this is the best option, but this works.

Unsolved
4) On the form "Offerte formulier" there is a section called "artikeloverzicht". Here users can select products. 1 product occupies 1 line of dropdownboxes and textboxes. At the moment, There are 6 of these lines there. What I want:
- if the first line is being filled in, I want the program to automatically display a new line below the first one. So if this second line isn't being filled in, I want the program to do nothing. However if this second line is being filled in, I want the program to add a third line etc.


This is my wishlist for this form please tell me what is possible and how I can get these things done. I'm keen to learn, but I can't figure these things out on my own, even with Google's help, I can't.

 

[JHB]

Insert a subform at the "Offerte formulier" for the section called "artikeloverzicht", and create a "DummyTable" that is linked to the fields in the subform.
Clear the "DummyTable" with code each time the form is open or major change is made, that influent the choices in the "artikeloverzicht".
I have made an example for you, look at the form "Offerte formulierPr" in the attached database.

 

[remcopeters]

That looks real great..... Exactly what I meant!

What do you mean by "DummyTable"?

 

[JHB]

The "DummyTable" is only used to hold the data/choices made in the "artikeloverzicht".
If you don't use a table as RecordSource for the subform, you can't get the effect you want, (... if the first line is being filled in, I want the program to automatically display a new line below the first one …)

 

[remcopeters]

I can't seem to get it to work. I can make a subform based on the correct table, but I can't get it to work as you did. Can you explain it a little more to me?

 

[remcopeters]

Got it!

Just copied the form in your example and edited it. Very much thank you kind sir

 

[JHB]

You're welcome.