Set a Value on a form's bound field based on field in separate table

[Bigdaddrock]

Using attached database. Open Form "DH ADD ORDERS w/Tax". I would like to autopopulate the bound control "INVNUM". It's source would be the field INVNUM in the table INVOICE.
I was hoping to use a SetValue Macro, using a DMAX expression to set the value.
Problem is that INVOICE contains some very old INVNUM above 900,000. I want to pick up where the current INVNUM end at 46822, so my next INVNUM would be 46823.
I think I need to couple a Where statement in my expression, but am not sure how to do it.
In essence it would say, "Set Value of INVNUM on the form at a value one greater than the greatest INVNUM in the Table INVOICE that is below 900,000.
Any suggestions?
Thanks

[NeoPa]

What data source is the form bound to?
And what is the name of the control where the invoice number is displayed?

The trick involves setting the Default value of the control, so that it is only used if data is added to the form.

[Bigdaddrock]

@NeoPa
The form in question is bound to the a query DH INVOICE Query (perhaps I should change this and bind the form directly to the Table INVOICE?). The control on the form is INVNUM.
Thanks for your continuing assistance.

[NeoPa]

You can probably use the INVOICE table anyway for this.

Private Form_Load()
  Me.INVNUM.Default = DMax("[INVNUM]", _
                           "[INVOICE]", _
                           "[INVNUM]<900000") + 1
End Sub

You may need to change the value when new items are added too. It depends on how the form is used.

[Bigdaddrock]

When I attempt to put in a new [Event Procedure] I am immediately shown the following:

Private Sub Form_Load()
 
End Sub

I tried inserting all of the lines of code you suggested, but it failed. I then attempted to drop your first and last lines: Private Form_Load() and End Sub, and saved the follwing:

Private Form_Load()
 Me.INVNUM.Default = DMax("[INVNUM]", _
                          "[INVOICE]", _
                          "[INVNUM]<900000") + 1
End Sub

But, alas, it too failed. I get the following message:
Compile Error:
Method or data member not found.

In addition the word "Default" is highlighted in yellow.

Any further suggestions?

[NeoPa]

My apologies. It should read :

Private Form_Load()
  Me.INVNUM.DefaultValue = DMax("[INVNUM]", _
                                "[INVOICE]", _
                                "[INVNUM]<900000") + 1
End Sub

[Bigdaddrock]

GREAT!!! I TRIED IT AND IT DOES INDEED WORK. THANKS SO MUCH FOR YOUR PATIENCE AND WILLINGNESS TO STICK WITH ME WITH THIS ISSUE. I look forward to more interaction with you and the group. You are indeed the best!!
Thanks again!!

[NeoPa]

I'm pleased I could help :)

[Bigdaddrock]

@NeoPa
I would now like to apply the same procedure to another item in a form. However, in the prior instance I used the same field name "INVNUM in the source table and in the form.
In this case I am seeking to have the Default Value of the field Tax with the value associated with a record from a query called "DH Tax Computation Query II".
The field in the query is "SumOfTax", and the specific specific record is the greatest value in the field INVNUM within the same query.
Could you possibly provide the code to do so?
Thanks.

[NeoPa]

I could do that, but that would be like fishing for you. I'd rather teach you to fish.

Go give it a try first and I'll help you along if you stumble. Your first step is to put the cursor within the word DMax then press F1 for Context-sensitive help. Read what it says. It makes it clear what is used where, and why. I expect after that you'll understand the code well enough to replicate it for your slightly different circumstances.

Post what you come up with and how pleased you are that it's working and it was all your own doing :)