Using DLookUp to Control Form Text Box

[r.rickersonIII]

Here is my situation:

I have created a query where I take multiple fields from a single
record in the same table and sum them together. I would like to
display this query result back in the form where the data for the table
is intially being entered. My problem however, is how to only display
the "date-specific" query result rather than the results for every
record. For example, if the user selects a record with the date
02/13/2006, I only want the query result from the date 02/13/2006 to be
displayed on the form.
This way, the text box is controlled by the summing operation in the
query and will act as a "real-time" calculator as the user enters data
in each of the fields on the form.

I have researched the Dlookup function but have not seen good info on
comparing date specific criteria in a form. Also, do I need to build an
event so that the query calculation is updated as the user tabs to each
new field on the form?

Any help will be greatly appreciated!!

Regards,
Rob

 

[Mark Burns]

Rob,

So you need something like:

private sub AddItUp()
dim vRtn as variant

if me.dirty then me.dirty = false 'force the form to update the recordset
if it's dirty
vRtn = dsum("[YourFieldToAddUpHere]", _
"[YourTableNameHere]", _
"[YourDateFieldNameHere] = #" & _
nz(format$(me.DateFormControlNameHere.value, _
"mm/dd/yyyy"), "1/1/2000") & "#")
me.whatevercontrolyouwanthere.value = nz(vRtn, 0)

end sub

private sub myControl1_AfterUpdate(...)
AddItUp()
end sub

private sub myControl2_AfterUpdate(...)
AddItUp()
end sub

You should be aware that using any of the dSum/DLookup etc.,. domain
functions in any BeforeUpdate events can screw things up nicely...so don't go
there.

 

[Rob]

Mark,
Thanks for the speedy reply.
One quick question:
Do I create a new module and then add the code you provided after it
has been customized with my specific field and table names? How is the
module then triggered in the particular form? I have never worked with
VB, and I was previously trying to use a control expression to achieve
the "real-time" calculator I previously discussed. Also, when you
state "YourFieldToAddUpHere", does that take into account that I am
attempting to sum 9 fields in the same table/form? Like I said in my
first e-mail, I used a query and the Nz Function to sum the 9 fields
previously. Thanks again for your help and I will try not to annoy you
too much with my ignorance of VB code.

Regards,
Rob

 

[John Vinson]

Here is my situation:

I have created a query where I take multiple fields from a single
record in the same table and sum them together. I would like to
display this query result back in the form where the data for the table
is intially being entered. My problem however, is how to only display
the "date-specific" query result rather than the results for every
record. For example, if the user selects a record with the date
02/13/2006, I only want the query result from the date 02/13/2006 to be
displayed on the form.

Use a date criterion in the Query, referencing the Form control:

=[Forms]![YourFormName]![ControlName]

This way, the text box is controlled by the summing operation in the
query and will act as a "real-time" calculator as the user enters data
in each of the fields on the form.

I have researched the Dlookup function but have not seen good info on
comparing date specific criteria in a form. Also, do I need to build an
event so that the query calculation is updated as the user tabs to each
new field on the form?

Instead, consider using the COntrol Source of an unbound control on
the form itself to do the addition. You may not need the query at all.
You could just have the Control Source

=NZ([TextboxA] ) + NZ([TextboxB]) + NZ([TextboxC])

and it will sum whatever has been entered on that form, at that
moment, treating textboxes which are empty as if they were zero.

John W. Vinson[MVP]

 

[Mark Burns]

Event ProcedureRob,

No, that code goes into the FORM's code module.
You can get to it by opening the form in design mode and either:
clicking on the "View Code" button in the toolbar (will get you into the
whole module, but not necessarily at the mose useful spot)
- or,(and better for you...) -
open the properties window for any control, select the Event tab,
select (click in) an event (like the AfterUpdate event),
if there's nothing there now, pull the list down and select [Event Procedure]
(if it's already there, skip that step), then hit the[...] Button, and the
VBA window will open up right into that event code module (creating the event
procedure declaration for you in the process if it doesn't already exist).

For Multiple fields instead of one where I had [YourFieldToAddUpHere], you
might need to change that to something like:
[MyField1] + [MyField2] + [MyField3] + ... + [MyFieldn]

Keep in mind that what you'll get from that dSUM call is a value like
SampleTable:
myField1, myField2, MyField3, MyField4
Rec1: 1, 1, 1, 1
Rec2: 2, 3, 2, 1

dsum("[myField1] + [myField2] + [myField3] + [myField4]", "[SampleTable]") =
12
(I omitted your date field filtering above for example simplicity & clarity)

Also, you will need to put that call to the function I gave you in EACH of
the 9 control's AfterUpdate event if you want the user to see the total after
changing the value in each control.

 

[Rob]

John,
I attempted to use the Nz Function in a Control on the form itself, and
all I could get the text box to output was "#Name?". I also thought
something simple like that could be used in the form itself, but I
haven't got anything to work. Any more suggestions would be
appreciated.

Mark,
I have not tried to enter your VB code yet. However, based on what you
said about the dSUM call value, I do not know if that's exactly what
I'm looking for. If possible, I would like to only sum the fields of
the record that are currently displayed on the form. Your example
showed me that the fields from both Records 1 and 2 would be summed.
Below is simple example of what my form looks like:
Date:
"Product Built"
7-8:
8-9:
9-10:
10-11:
11-12:
12:1:
1-2:
2-3:30:
Total Built During Shift:


As you can see, each record is the production schedule from a unique
day, and the product built for each hour is a unique field. Obviously,
I want the ability for the user to enter in the hourly totals, and the
Total Built during the day will be calculated automatically to use as a
reference. Also, this number will be used later on to generate
productivity reports. Hopefully this explanation will better explain my
problem.

Kindest Regards,
Rob

 

[John Vinson]

I attempted to use the Nz Function in a Control on the form itself, and
all I could get the text box to output was "#Name?".

This may be as simple as a missing = sign before the NZ.

If you use an expression, such as a function call, as the Control
Source of a textbox, you must prefix it with an equals:

=NZ([fieldname])...

John W. Vinson[MVP]

 

[Mark Burns]

Rob,

Ah, well then I misunderstood your initial question - sorry.
it sounds like what you need to to is exactly what John is telling you to do.

<see inline below>

John,
I attempted to use the Nz Function in a Control on the form itself, and
all I could get the text box to output was "#Name?". I also thought
something simple like that could be used in the form itself, but I
haven't got anything to work. Any more suggestions would be
appreciated.

Mark,
I have not tried to enter your VB code yet. However, based on what you
said about the dSUM call value, I do not know if that's exactly what
I'm looking for. If possible, I would like to only sum the fields of
the record that are currently displayed on the form. Your example
showed me that the fields from both Records 1 and 2 would be summed.

* I'm going to assume some control names here like Text1, Text2, etc.,.

Below is simple example of what my form looks like:
Date: <Text1>
"Product Built" <Text2>
7-8: <Text3>
8-9: <Text4>
9-10: <Text5>
10-11: <Text6>
11-12: <Text7>
12:1: <Text8>
1-2: <Text9>
2-3:30: <Text10>
Total Built During Shift: <Text11>

text11.ControlSource = "=Nz([Text3], 0)+nz([text4], 0)+nz([Text5],
0)+...+Nz([Text10], 0)"

should be all you need (fill in the missing Textbox control names where the
.... is above)
....just like John was telling you to do.

 

[Rob]

John & Mark,
On my form, I created a text box and entered the Nz Fuction exactly as
you both described into the control source on my text box. When I save
and then switch my form into the "user" view, it still displays
"#Name?". I double-checked for typographical errors and actually
rewrote the formula twice. Any thoughts on why this is not working for
me, or any ideas on another method to achieve the same result.

Thanks again for all your time,
Rob

 

[Rob]

Guys,
I also forgot to mention that I am running Access 97 if that makes a
difference.

Rob

 

[Mark Burns]

Guys,
I also forgot to mention that I am running Access 97 if that makes a
difference.

It does, I think. the NZ function was there in Access 97, but I'm not sure
if the forms evaluator understood how to use it right...?

Anyway, the NZ function's purpose is to handle the case where a field value
might be NULL and to substitute a non-null value (0 by default, but you can
make it return whatever you wish) instead <ex:
nz([fieldorformcontrolnameWithValue=Null], 987654321) = 987654321, or
nz([fieldorformcontrolwithvalue=Null]) = 0>

So, assuming that we can't get Access97 to use the nz function in our
expression for some reason, we can still work around this problem in your
Access 97 form 2 ways:
1) a more complex expression syntax using iif() and isnull() instead of nz()
<i.e. DataSource="=iif(isnull([Text1]), 0, [Text1]) + iif(isnull([Text2]),
0, [Text2]) + iif(isnull([Text3]), 0, [Text3]) + ... + iif(isnull([Text9]),
0, [Text9])">

2) modify your form's datasource to be a QUERY instead of a table directly,
and in the query, modify all 9 of the fields to use a similar expression
syntax <i.e. MyFieldName1: iif(isnull([myTable].[myfieldname1]), 0,
[myTable].[myfieldname1]) > Then your form's total textbox control expression
syntax simplifies to:
Datasource = "=[text1] + [text2] + [text3} + ... + [text9]"

 

[Rob]

Mark,
I tried your first suggestion using the Iif() and Isnull() functions,
but the form output was still "#Name?". In regards to your second
suggestion, if the datasource for my form is Query rather than a Table,
where does the data from user-inputed records go? In other words, how
will the info that the user enters into the form end up in my table?
Or are you suggesting that I simply take the data from the table,
manipulate it in the Query and then use the Query as the control source
for only ONE of the form text boxes (the TotalBuilt text box)? I'm not
sure what the issue is with your other suggestions, but I am having my
IT administrator load Access 2003 on my PC to help fix this problem.
Will I simply be able to use the Nz function with Access 2003? Again,
thanks for your time and patience.

Regards,
Rob

 

[Rob]

Guys,
Any other suggestions on my comments listed above?

Thanks,
Rob

 

[Rob]

Also,
My IT administrator just loaded Access 2003 on my PC, so any help
regarding that would be greatly appreciated. Hopefully, I will simply
be able to create the same type of table and form, and the Nz function
will work in 2003. Thanks again.

Rob