By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,970 Members | 1,668 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,970 IT Pros & Developers. It's quick & easy.

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

P: 67
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
Attached Files
File Type: zip TEST DB II.zip (254.1 KB, 125 views)
Jul 19 '10 #1

✓ answered by NeoPa

My apologies. It should read :
Expand|Select|Wrap|Line Numbers
  1. Private Form_Load()
  2.   Me.INVNUM.DefaultValue = DMax("[INVNUM]", _
  3.                                 "[INVOICE]", _
  4.                                 "[INVNUM]<900000") + 1
  5. End Sub

Share this Question
Share on Google+
20 Replies


NeoPa
Expert Mod 15k+
P: 31,769
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.
Jul 20 '10 #2

P: 67
@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.
Jul 20 '10 #3

NeoPa
Expert Mod 15k+
P: 31,769
You can probably use the INVOICE table anyway for this.
Expand|Select|Wrap|Line Numbers
  1. Private Form_Load()
  2.   Me.INVNUM.Default = DMax("[INVNUM]", _
  3.                            "[INVOICE]", _
  4.                            "[INVNUM]<900000") + 1
  5. End Sub
You may need to change the value when new items are added too. It depends on how the form is used.
Jul 20 '10 #4

P: 67
When I attempt to put in a new [Event Procedure] I am immediately shown the following:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.  
  3. 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:
Expand|Select|Wrap|Line Numbers
  1. Private Form_Load()
  2.  Me.INVNUM.Default = DMax("[INVNUM]", _
  3.                           "[INVOICE]", _
  4.                           "[INVNUM]<900000") + 1
  5. 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?
Jul 20 '10 #5

NeoPa
Expert Mod 15k+
P: 31,769
My apologies. It should read :
Expand|Select|Wrap|Line Numbers
  1. Private Form_Load()
  2.   Me.INVNUM.DefaultValue = DMax("[INVNUM]", _
  3.                                 "[INVOICE]", _
  4.                                 "[INVNUM]<900000") + 1
  5. End Sub
Jul 20 '10 #6

P: 67
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!!
Jul 22 '10 #7

NeoPa
Expert Mod 15k+
P: 31,769
I'm pleased I could help :)
Jul 22 '10 #8

P: 67
@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.
Jul 22 '10 #9

NeoPa
Expert Mod 15k+
P: 31,769
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 :)
Jul 22 '10 #10

P: 67
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!!
Jul 23 '10 #11

NeoPa
Expert Mod 15k+
P: 31,769
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.
Expand|Select|Wrap|Line Numbers
  1. Private Form_Load()
  2.   Dim lngInvNum As Long
  3.  
  4.   lngInvNum = DMax("[INVNUM]", "[DH Tax Computation Query II]")
  5.   Me.Tax.DefaultValue = DLookup("[SumOfTax]", _
  6.                                 "[DH Tax Computation Query II]", _
  7.                                 "[INVNUM]=" & lngInvNum)
  8. End Sub
Jul 23 '10 #12

NeoPa
Expert Mod 15k+
P: 31,769
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.
Jul 23 '10 #13

P: 67
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?
Jul 23 '10 #14

NeoPa
Expert Mod 15k+
P: 31,769
If there is indeed always one, and only one, record returned by [DH Tax Computation Query II], then your code could indeed be simpler :
Expand|Select|Wrap|Line Numbers
  1. Private Form_Load()
  2.   Me.Tax.DefaultValue = DLookup("[SumOfTax]", _
  3.                                 "[DH Tax Computation Query II]")
  4. End Sub
Jul 23 '10 #15

P: 67
Unfortunately, I tried your solution but it failed to change the blank text box. Might it be because the form is no longer one for a new item but rather an existing form which I am now editing a record? Recall in my last note, I have created the invoice in a prior form and now am editing that invoice to include the computed tax from the Query "[DH Tax Computation Query II]".
Please note, this amount should remain in the table that contains the record of values associated with this invoice.
Thanks for providing your assistance.
Jul 23 '10 #16

NeoPa
Expert Mod 15k+
P: 31,769
Frankly I'm failing to see any overlap with the original question at all. Every post seems to introduce new information and/or contradict something I've already been led to believe.

It now seems you want to set the value of the Tax control instead of the default as was discussed. This is easily done simply by leaving off the .DefaultValue portion of the line, but how much sense it makes is another matter. It seems to me that to apply that value into a stored field is breaking the rules of Normalisation (See Normalisation and Table structures).

I'll leave this one with you as I feel I've gone as far as I can on this one.
Jul 23 '10 #17

P: 67
A thousand apologies for what appeared to be a moving target. I should have stopped and structured my request more carefully before submitting. As with most neophytes, I imagine, I was anxious to get an answer and continued to work on my program before receiving an answer. Then, with subsequent inquiries, the program had changed and it appeared to be totally unrelated to the original submission. I will work harder to be more succinct in future submissions and then work on other aspects of the program while awaiting an answer.
As to the storing of a value in multiple places. I understand and appreciate the beauty of a relational database. And thank you for suggesting that article on Normalisation and Table structures. However, in my program there are some items that must be permanently recorded, rather than relating back to other tables, due to the dynamics of the data.
Case in point,as mentioned in the article. A customer table should record an individual's name and address, while Invoice tables would simply record the customer's id from the customer table. However, in my case, if the customer's address changes (or tax rates change), I must have a record of where the original invoice was sent (and what tax rates were applied) when the original invoice was issued, regardless of what the current address of the customer or current tax rates. Thus my need to record data in multiple records, in some cases.
Thank you again for your assistance and patience. I look forward to being able to continue as a member of this august group.
Jul 23 '10 #18

NeoPa
Expert Mod 15k+
P: 31,769
A perfect response of some beauty (for those that appreciate such things). Your understanding of all the issues is clearly represented and remarkably full, leaving me little to say other than I agree with everything you've said. Even about the normalisation issues. There can be exceptions, and you've shown that your data certainly fits into that category.

That only leaves me to ask if your situation is fully resolved?
Jul 24 '10 #19

P: 67
Your solution worked!! Indeed, I applied the principal to another text box and it worked beautifully. I was able to couple the two operations into a single Event procedure. Thanks again for your assistance.
Jul 26 '10 #20

NeoPa
Expert Mod 15k+
P: 31,769
Always good to hear. Especially applying the principles elsewhere. That indicates learning, which we love to engender. Far better than simply providing solutions.
Jul 26 '10 #21

Post your reply

Sign in to post your reply or Sign up for a free account.