Pass value from unbound textbox (dlookup) to bound textbox (both on same form)

Posted on 2005-03-10
Medium Priority
Last Modified: 2012-08-13
I pull a value from a query via Dlookup (unbound textbox).   That value consistently changes.    Whatever the current value is shown in the Dlookup textbox, I need to now pass it to a bound textbox and store in a table.  

I tried several events in the Dlookup control (AfterUpdate, OnChange, etc.)... the bound textbox doesn't show the value.    I also tried to put an update event in yet another textbox in a subform.   Essentially it is that other textbox that will change the value in the query hence the value in the Dlookup.  

I placed something like this in the AfterUpdate event of the subform textbox (again, the "driver textbox")...

Me.BoundTextBoxThatMustReceiveTheData.Value = Me.ControlNameofDlookup


Me.BoundTextBoxThatMustReceiveTheData.Value = Dlookup(function...)

I then put a break in the AfterUpdate event... "Me.BoundTextBoxThatMustReceiveTheData.Value" shows the proper value in VBA, but again, once back in form view, it does hold/store the data.

What am I doing wrong?

Question by:TomBock2004
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Expert Comment

ID: 13514211
You don't say which text box AfterUpdate event you refer to, but AfterUpdate fires only AFTER the changes have been stored, try putting your code into the OnChange or Exit event for the unbound text box.


Author Comment

ID: 13514248
Okay, here's what I have:

- subform with bound texbox (let's call its controlname Subtotal1)
- unbound textbox (let's call it Unbound1)... this one uses the dlookup
- bound textbox (let's call its control name TotalAmount1)

Here's the process:

- I change amount in Subtotal1 of subform
- dlookup (Unbound1) shows the updated total amount e.g $500
- TotalAmount shows $0 (default value)

... so I don't really click on Unbound1... its value changes based on changes in Subtotal1 of subform.   I just need to mirror the $500 value of Unbound1 and pass it to TotalAmount1 (linked to table).

Any ideas,
LVL 54

Expert Comment

ID: 13514619
You'll have to use the subform's BeforeUpdate event to move the value of the Unbound control to the bound control and to have it saved that way.

I would however like to warn you that storing such a calculated value is against the normalization rules and it has the danger of getting inconsistent data when another form is changing one of the "elementary" fields of the calculation and doesn't recalc the stored value...
The general solution is to put calculations in a query, thus only showing the real value based on the elementary fields on the moment the query is exectued.

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 41

Expert Comment

ID: 13516103

Is this the same issue as your other question?

Author Comment

ID: 13516327

great suggestion... I probably ignore the passing of the value.   I will have to make some modication to the query and form strucutures since the "OverallBudget" is used in multiple other places.   But I guess I should be alright.

Let me check it out first... will post another question if I encouter some unexpected problems.


Author Comment

ID: 13516704

Okay, one follow up question...

Query1 produces the following records:

TrackingNumber      Subtotal
1                        50.00
1                        25.00
1                      100.00
2                      500.00
2                    1000.00

Now, I use Query1 as source for Query2... it's records are:

TrackingNumber      RunSum
1                      175.00
1                      175.00
1                      175.00
2                    1500.00
2                    1500.00

Currently, in my main form I show the 2 records.   Below a subform which shows (3 subrecords for tracking #3; 2 subrecords for tracking #2 and their respective "Subtotals") I placed the Dlookup on the mainform.

The Dlookup shows $175 for the first record & $1,500 for the 2nd record... so far so good.

Now, my original question was to pass "175" & "1500" to the bound textbox (BudgetAmount).   You suggeted that this goes against normalization rules... ok, I can see that.

Now, to replace with "BudgetAmount" field in other queries and forms, I am attemting to pull those 2 values (175 and 1500) from Query2.   For that, I created Query3, used Query2 as its datasource, and then attempted to group the 5 records.

Unfortunately, no matter on which PC I try this, Query3 crashes Access.

Here's how I' pulling the RunSum (SubTotalRunSum):
SubTotalRunSum: (Select Sum(T.Subtotal) From Query1 As T Where  T.TrackingNumber=Query1.TrackingNumber And T.TrackingNumber<=Query1.TrackingNumber;)

Is this subselect the cause for this problem?   If yes, is there another way around to find the total sum of all records, grouped by trackingnumber?


LVL 54

Accepted Solution

nico5038 earned 1000 total points
ID: 13516874
For a total per TrackingNumber just use a groupby on query1 like:

select TrackingNumber, Sum(Subtotal) as TotalSum from query1 group by TrackingNumber;

This query can also be used for a DLOOKUP, but When you need to show it per TrackingNumber (as your form shows one) I would probably add a linked subform to query2 and Access will synchronize ot for you automatically.

For a runningsum you need an additional field that's different per TrackingNumber as you need a <= to do the summing.
In general I would expect that you have a tracking date or some unique autonumber that can be used for that like:

SubTotalRunSum: (Select Sum(T.Subtotal) From Query1 As T Where  T.TrackingNumber=Query1.TrackingNumber And T.TrackingDate<=Query1.TrackingDate;)

Clearer ?


Author Comment

ID: 13517269
Great!  That works fine.

Thanks for the help!


Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question