Next Sequential Value Button 1

[tcolan]

Hi,

Here's what I want to do. I have a Test Case ID that is comprised of Business Process and a # (ie. Order Entry-1) In my test case table, the combination of these fields serves as the primary key.

Right now I have a form where the user can enter new Test Cases. The form is set up so the user can select the business function from a drop down and then they enter the number that they want. (ie. Order Entry-2)

I want to add a button to the form that will allow for the user to click and auto-generate the next sequential value into the # field for that Business Process. I still want to leave the option for the user to type the value in if they want to, but I want to be able to give them the auto-generate option.

I know how to write a max query. I guess my question is, how do I pass the selected Business Process (ie. Order Entry) on the form to the query and return the max + 1 value to # field.

Also, I only want this button to show on the form if the user is entering a new record.

Any help would be appreciated.

Thanks so much,
Tom

 

[TheAceMan1]

How are ya tcolan . . . . .

To take care of the buttons visibility. In form design view, set the buttons [blue]Visible Property[/blue] to false, then put the following code in the [blue]On Current Event [/blue]of the form (you substitute names in [purple]purple[/purple]):

[blue]   If Me.NewRecord Then
      Me![purple][b]YourButtonName[/b][/purple].Visible = True
   Else
      Me![purple][b]YourButtonName[/b][/purple].Visible = False
   End If[/blue]

Next, put the following code in the [blue]On Click Event[/blue] of the button:

[blue]   Me![[purple][b]#ControlNameOnForm[/b][/purple]] = DMax("[[purple][b]#FieldName[/b][/purple]]", "[purple][b]TableName[/b][/purple]")[/blue]

Give it a whirl & let me know . . . . .

See Ya! . . . . . .

 

[willir]

Short and sweet AceMan - exactly what I was going to provide, except I dont like purple. ;-)

 

[TheAceMan1]

tcolan . . . . .

There's an error. The button code should be:

[blue]Me![#ControlNameOnForm] = DMax("[#FieldName]", "TableName") + [purple][b]1[/b][/purple][/blue]

See Ya! . . . . . .

 

[TheAceMan1]

Haaaaaaaaaaay willir . . . .

Me & you both!

See Ya! . . . . . .

 

[tcolan]

Aceman,

Perfect!!!! Just what I was looking for, quick and easy.

Thanks so much!