Dlookup function doesnt retrieve query result

[gomsah]

Hello there

I am a newbie in access and i ve been doing some forms for our company's work. I ve come across a problem which is a bit wierd i guess.

I dont know if i have to tell every detail but i ll try.

I have a combox in my form which gets its data from another tabel, and i got 2 textboxes that is updated when the combobox's value is updated.

First textbox(lets say textbox1) has a control source Is_Number which is incremented +1 everytime new record is set.
And default value is set to :
DLookUp("Max(

![Is_Number])+1";"AAA")
Textbox1 works perfectly. Everytime new record is entered new record has a new value which is max value +1(I didnt get ID autonumber here because ID is incremented by 1 even record is deleted or dropped when editing the form)

But 2nd textbox is problematic(textbox2). I need textbox2 to count each combobox's selection and bring me the number. I ve done this using a query and textbox's control source is this dlookup function
=DLookUp("Report_No_C";"Report_no";[Calc_field1])
This works fine and brings the query's result.

When i change the combobox's selection, textbox2's value is changed accordingly. But data is not entered anywhere. So i changed the control source to "Report_no" field in AAA table.
And i placed the dlookup function to default value of textbox2.
But this seems not working. Even data is not showing when form is opened

My question is: How can dlookup work with a table field but not with a query field.
or Is there another way to add query's result into table field while working with forms

Thank you in advance

 

[vbaInet]

Welcome to AWF!

You can use a DLookup() function to find values from a table or a query. Here are some good links on its useage:

http://www.mvps.org/access/general/gen0018.htm
http://www.techonthenet.com/access/functions/domain/dlookup.php

But remember to catch Null values using the Nz() function:

http://www.techonthenet.com/access/functions/advanced/nz.php

Nz(DLookup(...), "")

 

[gomsah]

Thank you for quick answer, but i dont have problems with the usage. I also eliminate null values in query itself using If(Isnull(Field))

My problem is, dlookup() function is working as intended when it is in control source but it doesnt work in default value.
I checked syntaxes values, parantheses, everything. I even copy/pasted the working control source Dlookup into default value.

It is so annoying when it is working in control source and not working in default value.
As i said i have another Dlookup in same form which works ok in control source or in default value.

 

[vbaInet]

Have you tried it in code?

 

[gomsah]

uhmm, no.
How do u get the query result in code?
Besides as far as i ve read, it is better to get it done with dlookup() instead of code, right?
Btw I can even upload the unworking form if need be.

 

[vbaInet]

Let's see the exact DLookup statement that isn't working.

 

[gomsah]

=DLookUp("Rapor_No_C";"Report_no";[Brans_adi_L])

Report No is the name of the query, and Brans_adi_L is a field in the form. So when user selects a record in the combobox, query calculates the new Rapor_No_C.
As i have said. This Dlookup function works when it is in the control source of the textbox. But when i try to use it in default value of a field in a table, it doesnt give any error but it doesnt show the result either.

 

[vbaInet]

That's not the correct syntax:

=DLookUp("Rapor_No_C", "Report_no", [Brans_adi_L])

The third argument (in red) must be a criteria.

Something like:

=DLookUp("Rapor_No_C", "Report_no", "[FieldName] =" & [Brans_adi_L])

 

[gomsah]

[Brans_adi_L] is there to update the textbox every time combobox changed.
I ve uploaded my db to here.
http://www.2shared.com/file/3-vdbEsG/Db1.html

You can see that entry form is Ihbargiris
On Ihbargiris form, whenever u change the Brans_adi_L combobox, text19 textbox is updated with the right value. I copy/paste the control source of text19 to a new textbox's(text33) default value and change the new textbox's control source to Ihbar.Rapor_no to insert the query calculated value.

But nothing happens. what am i doing wrong?

 

[vbaInet]

Like I mentioned, your syntax is incorrect. Read the links I initially provided regarding the third argument of the DLookup() function.

By the way, you can upload files on the forum. Click Go Advanced.

 

[gomsah]

If syntax was wrong, wouldnt it give an error or something else?
Well, if i change [Brans_adi_L] part of the dlookup function it doesnt update the textbox when combobox is changed.
You can see it for yourself in attached file.

I just want to insert the query result into table, depending on the selected combobox record. Real time update of Rapor_no field is important so it needs to be recalculated when combobox is changed.

 

[vbaInet]

I see. Before you consider saving the value of that field, you should read this:

http://allenbrowne.com/casu-14.html

If after careful consideration you still want to go ahead then this is what you do:

1. Create a textbox with the DLookup() and make it hidden (by setting the Visible property to No.

2. Set the Control Source of Brands_adi_L textbox to Rapor_No field

3. In the AFTER UPDATE event of the hidden textbox in step 1 you put this:

Me.Brands_adi_L = Me.[COLOR=Red][B]Name_Of_Hidden_Textbox[/B][/COLOR]

Put the name of the hidden textbox there.