Automation of number on Estimate Form

[brendageorge]

I have a table named tblEstimates, a form named frmEstimates, and a field named Estimate#. How can it get the form to automatically assign a new number to each new estimate? In case you need to know the form has a sub form in it and the parent/child link is the field Estimate#. Thanks for any help.

 

[TheAceMan1]

How are ya brendageorge . . .

Although the unique values you require can be generated with ...

[blue]Me.Estimate# = Nz(DMax("[Estimate#]", "tblEstimates"), 0) + 1[/blue]

... why aren't you using primarykey/foreignkey as [blue]master/child[/blue] links to synchronize the forms?

Understand, [blue]your in a prime area of design here[/blue]. If not done properly you'll spend more time redesigning than you'd care too. There's also the issue of wether the db will be used on a network with more than one concurrent user.

[blue]Your Thoughts? . . .[/blue]

BTW: post back tablenames, primarykeynames, foreignkeynames,and relationships for the tables.

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[brendageorge]

Thanks for your interest. I have the following tables: Jobs, Estimates,Line, and about 20 tables of different lines of products. I have not set a primarily key to any tables. The Jobs table has only one field [job], Line table lists all available lines with only one field [line]and the estimate table is to record the data entered in the estimate/subform estimate1 forms. I choose the estimate# as master/child link so that each line of data record contained the same number.

I also have another question, I use a DLookup for the price of a product from the product tables. I need an IF statement that makes it look at the "Line" entered on form Estimates before it does the DLookup on the subform Estimates1.

Any advise is greatly appreciated! Thanks again.

BTW I placed your code in the source property and it does not create a estimate #. Did I put it in the wrong place?

 

[TheAceMan1]

brendageorge . . .

Be sure to read & understand the following ...

Fundamentals of Relational Database Design

... there's just too much to cover at this point.

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[brendageorge]

Thanks. Read the article and it was a great help. Making changes to my database now.