Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


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

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.

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

571 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