Open form from a drop down list

[ildanach]

Hey folks,

Yet another basic and beginners question from yours truly.



I have a multitude of forms to handling the data entry of correspondence for a number of project. The forms are seperated by project and if they are incoming or outgoing correspondence.

What I would Like to know is how to create a form to open the correct form for data entry by selecting the project from a drop down list and then clicking either a button for "Incoming" or "Outgoing".

Thanks a million in advance!
David

[RuralGuy]

How about using a table with the names of your forms in it as the RowSource of the ComboBox?

[Feliks]

Greetings,
I also am a new user and am hoping you can elaborate on how you would make a combo box on a form open another form or even better, open a subform on the same form with the combo box.
thanks

[RuralGuy]

A SubForm on the MainForm is basically already open so I don't understand your question very well.

[Feliks]

So basically what I am trying to do is set up a database for new parts. The database is set up so each type of part (gear, screw, nut, block, etc) has a corresponding three digit prefix (110, 120, 130, etc) so each type of part has its own table. I want each type of part to have its own table so that it can automatically increment that type of parts three digit suffix (120-001, 120-002, etc). So my basic plan is to have a form for each type of part to take out new part numbers. Now that you have the whole story, what would be really nice is instead of having to work with a large number of forms for each type of part, to have one single form with a combo box at the top to select the type of part, once the type is selected it would open a subform with the selected type. Thats basically what I want to do but I can't quite make it happen. Sorry for the novel version

[RuralGuy]

If you keep the suffix in a separate field from the prefix, you can keep all of the parts in the same table and easily create the next part number *and* display your SubForm without a big hassle.

[Feliks]

That would work but then i might have 140-001, 110-002, 130-003, 120-004, 140-005. I want to use the primary key as my 3 digit suffix and if I do that then in the above example 140-002 though 140-004 are never used. If I plan on having a large number of parts in the database I would run out relatively quickly. that is to say if I understand what you're suggesting.

[RuralGuy]

I was suggesting 1000 numbers for each prefix, which is still rather limiting. I would not recomment that either of these fields be a primary key.

[Feliks]

I guess I don't understand how to have different prefix's and have the suffix automatically increment for each different prefix all in one table. Quick Access lesson perhaps?

[RuralGuy]

You use the DMax() function in the BeforeUpdate event of the form to get the next number.
[SufixField] = Nz(DMax("[SufixField]","YourTable","[Prefix]= '120'"),0) +1
You can also use this same code in the BeforeInsert event if you want the user to be able to see the new value but also put the code in the BeforeUpdate event of the *FORM*.

[Feliks]

How would that script allow for a prefix other than 120 in one table? Why wouldn't you recommend the suffix be a primary key?

[RuralGuy]

I thought you wanted the numerical suffix to start from 001 for each part? A primary key can not have any duplicates. This code will work for all prefixes.
[SufixField] = Nz(DMax("[SufixField]","YourTable","[Prefix]= '" & [PrefixField] & "'),0) +1

[Feliks]

So I put the following expression into the form as an event procedure
[Suffix] = Nz(DMax("[Suffix]","Test","[Prefix]= '" & [Prefix] & "'),0) +1
Yet it keeps giving me
compile error: expected: list seperator or )
I tried it where .."[Prefix]='120'"),0)+1 and it worked so I dont know what to do... Thanks for all your help by the way, this stuff is pretty exciting

[RuralGuy]

Oops...missed one quote:
[Suffix] = Nz(DMax("[Suffix]","Test","[Prefix]= '" & [Prefix] & "'"),0) +1
...that air code compiler just does not work as well as it used to.

[Feliks]

Thanks a lot, that code works great if I use it with a button. If I use it with both the BeforeInsert and BeforeUpdate events it only puts in 1, I guess that makes sense. If I only put it in BeforeUpdate then the user wont see the number until they create a new record and go back to the one they just made. AfterUpdate is another mess. I put it with a button that pops a textbox saying "You're Part Number is: [Prefix]-[Suffix]" that works except that [Suffix] is displayed as only as many digits as the number (1, I'd prefer 001. Is there a 'format' vba code to set this?) thanks for all your help thus far, its made this database a success.