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]

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.

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]

Latest suggestion resulted in failure as noted

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]

I'm pleased I could help :)

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 :)

[Bigdaddrock]

Alright, I tried numerous iterations, all to no avail. I even tried using the Default control on the control's text box, but realized that only works for new items and I am doing an edit of an existing item.
I have amended my queries to allow the Query DH Computation Query II to only contain a single record. The INVNUM and a field called TAX.
So I went to the first control that is focused on the form upon opening and tried placing in the After Update the following:
Me.TAX = "DH Tax Computation Query C"!(TAX)
But I keep getting a failure notice. I am struggling with the VBA language. Can you provide some suggestions on how I may insert the value of TAX from the DH Tax Computation Query C into the Text Box TAX.
Oh, and one last thing, this form is used to EDIT a record. Might that be a problem with what I am doing? (Which is why I did not use the Use as Default Value route.
Thanks again, mon professore!!

[NeoPa]

Looking at this more closely, it's not really the same procedure at all is it? Not surprising you found it a little complicated.

First we need to find the maximum value of the [INVNUM] field. Then we use this to pick up the related value of [MaxOfTax]. It could all be done in a single, but more complicated, line of code, but for ease of understanding we'll save the first value before using it.

Private Form_Load()
  Dim lngInvNum As Long

  lngInvNum = DMax("[INVNUM]", "[DH Tax Computation Query II]")
  Me.Tax.DefaultValue = DLookup("[SumOfTax]", _
                                "[DH Tax Computation Query II]", _
                                "[INVNUM]=" & lngInvNum)
End Sub

[NeoPa]

Bigdaddrock: Oh, and one last thing, this form is used to EDIT a record. Might that be a problem with what I am doing? (Which is why I did not use the Use as Default Value route.

As you are not changing the RecordSource (nor even a value but only the DefaultValue), this should not affect whether the form is updatable or not. See Reasons for a Query to be Non-Updatable for more on that subject.

[Bigdaddrock]

Perhaps it was due to a long day of programming missteps that I may have misled you on my latest request. I have but a single record sitting in the Query. It has two fields, INVNUM and TAX.
My form is one that is being edited, and due to your previous assistance, it now contains a field called INVNUM, with a populated number. Further down in this form I have a field called TAX. I would like to import the value in the TAX field of the Query into the same named field, TAX, on my form.
Does that simplify the code that I would enter in the Form Load?

[NeoPa]

If there is indeed always one, and only one, record returned by [DH Tax Computation Query II], then your code could indeed be simpler :

Private Form_Load()
  Me.Tax.DefaultValue = DLookup("[SumOfTax]", _
                                "[DH Tax Computation Query II]")
End Sub