Pass query result to Text Box Default Value?

[stan.mi...@gmail.com]

I have a query that generates a field with one record in the expression "TestNumber". That is, my query looks at a lot of tables and calculates one and only one value, e.g. "1234". The query's datasheet view shows TestNumber with 1234 under it. That's it. There are no more values, just the one single value "1234."

I want to use TestNumber as the Default Value (the Data Property) of a text box but I can't get the number onto the form no matter what I try. I thought it should be =[TestQry]![TestNumber] but that doesn't work. I've tried creating an invisible text box on the form so that TestNumber shows as a field on the form object. It would not. I've tried a macro and I've tried VBA code. Both would run the query but neither would pass the result of the query (1234) to the form object.

Any help would be much appreciated.

[Patrick Finucane]

For something like your example, a quick and dirty approach would be
to use
Dlookup()
Review Dlookup in help or from Google search results.

Your syntax of =[TestQry]![TestNumber, if it is a control source,
does not look like it would do much except return an error.

[Aikistan]

It does. Unfortunately, so does DLookup.

[Neil]

"Aikistan" <stan.mi...@gmail.com> wrote in message
news:5008936.274.1334191689796.JavaMail.geo-discussion-forums@ynbv36...

==================

DLookup should not return an error if used properly. Did you use the syntax:

DLookup("TestNumber", "TestQuery")

?

That should not return an error.

Neil

[Phil]

2 thoughts
What is the query that generates 1234

Have you tried creating the query in VBA to obtain your TestNumber, and then
on the Current (or posssibly BeforeInsert) If IsNull(MyTextBox) then
MyTextBox.DefaultValue = TestNumber
End If

Check that the data type of the MyTextBox and TextNumber ar both the same
(Long???) otherwise you may need something like MyTextBox.DefaultValue =
CLng(TestNumber)

Phil

[Aikistan]

On Thursday, April 12, 2012 7:16:15 AM UTC-4, Phil wrote:
> >
> > DLookup should not return an error if used properly. Did you use the
> > syntax:
> >
> > DLookup("TestNumber", "TestQuery")
> >
> > ?
> >
> > That should not return an error.
> >
> > Neil
> >
> >
> 2 thoughts
> What is the query that generates 1234
>
> Have you tried creating the query in VBA to obtain your TestNumber, and then
> on the Current (or posssibly BeforeInsert) If IsNull(MyTextBox) then
> MyTextBox.DefaultValue = TestNumber
> End If
>
> Check that the data type of the MyTextBox and TextNumber ar both the same
> (Long???) otherwise you may need something like MyTextBox.DefaultValue =
> CLng(TestNumber)
>
> Phil

The query is irrelevant...it generates "1234". I had tried creating the query in VBA and populating the box with it just like you suggested. I received the #Name? error. Thinking that my form did not like the fact that I want to use a control that isn't in its object list, I put TestNum in an invisible box on the form and it still gave #Name?.

DLookup("TestNum","TestQuery") generates #Error?. The syntax on MS's site is DLookup("[TestNum]","TestQuery"), which also generates #Error?.

Seriously, is there not some simple way to pass a value from a query to a textbox' default value? I don't care if it's efficient or elegant or even sanitary...I just want it to WORK.

Stan

[Patrick Finucane]

> Stan- Hide quoted text -
>
> - Show quoted text -

Create a new table, call it Junk. Add 1 field, call it AField. Add 3
records; 1,2,3.

Now In the Debug window try

[Aikistan]

I created your 1-field, 3-record table in a blank database and, in the debug window Immediate, I tried:

DLookup("AField","Junk") Error expected =
DLookup("[AField]","Junk") Error expected =
DLookup("[AField]","[Junk]") Error expected =
DLookup("AField","[Junk]") Error expected =

The second one is exactly the same syntax as what MS had on their DLookup page. So I added another field called Bfield with A, B, and C in it and tried it with the optional criteria.

DLookup("Afield","Junk","Bfield =" B) Error expected separator or )
DLookup("Afield","Junk","Bfield =" & "B") Error expected separator or )
...
(all permutations of above)

None of them worked. I'm beginning to think that there's something wrong with my version of Access (2007 on WinXP SP3, Office SP2).

Stan

[Bob Barrows]

Aikistan wrote:
>
> I created your 1-field, 3-record table in a blank database and, in
> the debug window Immediate, I tried:
>
> DLookup("AField","Junk") Error expected =
> DLookup("[AField]","Junk") Error expected =
> DLookup("[AField]","[Junk]") Error expected =
> DLookup("AField","[Junk]") Error expected =
>

Sigh
DLookup is a function that returns a result. You cannot call it without
assigning the result to something, a variable or another method. That's what
the "Expected =" error is referring to.
Do this in the immediate window:

?DLookup("AField","Junk")

The "?" is shorthand for "Debug.Print" and what happens is that DLookup
passes its return result to the Print method which consumes it as it writes
it to the immediate window.

This will also work:
retval=DLookup("AField","Junk")

But you won't see a result. To see the result do this:
retval=DLookup("AField","Junk") : ? retval

The colon allows you to enter multiple statements on a single line so they
can both be executed as a single batch in the immediate window.

[Aikistan]

On Thursday, April 12, 2012 1:57:53 PM UTC-4, Bob Barrows wrote:

> Aikistan wrote:
> >
> Sigh
> DLookup is a function that returns a result. You cannot call it without
> assigning the result to something, a variable or another method. That's what
> the "Expected =" error is referring to.
> Do this in the immediate window:
>
> ?DLookup("AField","Junk")

Progress! No error...but nothing else happened, either. I assume it was supposed to output "1" for the first record?

[Bob Barrows]

Yes ... at least that's how it works for me...
Without the criteria argument, the content of the field in the first row is
supposed to be returned.

I've just tested to be sure and it definitely works that way ... at least in
A2003. I assume nothing's changed. I can't test in a later version of
Access, sorry.

[Patrick Finucane]

Most likely yes. Since you did not specify a filter.

Dlookup will return a null if nothing is found that matches.

Look for Allen Browne via Google. He has routines similar to Dlookup
and Dmax, etc on his web site but they begin with the letter E, like
Elookup. You could then step thru the code and see the values
change...or in your case not change.

Another thing. from the debug window
var = DLookup("AField","Junk")
Debug.print var

If you assign a value to a variable with DLookup it will not display
it's value unless you use a ? or Debug.print.

[Aikistan]

>
> If you assign a value to a variable with DLookup it will not display
> it's value unless you use a ? or Debug.print.

It works! IT came by and updated my Adobe Acrobat...when I rebooted and started again, the darn thing worked. This is why I need to work on this sort of stuff at home. I have control over my PC.

Thanks to all for the assistance.

Stan

[Aikistan]

If you were curious, this is the kludge that I ended up with. First, this query to generate a list of the next number for its corresponding fiscal year based on the last number in the table:

SELECT Mid([tbl_Master]![IDNumber],4,4) AS IDMatch, CStr(Max(Right([tbl_Master]![IDNumber],4))+1) AS NextNum
FROM tbl_Master
GROUP BY Mid([tbl_Master]![IDNumber],4,4);

This generates the middle 4 digits of our standard ID numbers, which correspond to our fiscal year, and the corresponding next number for that fiscal year. Then this statement is in the text box default value:

"04-" & IIf(Now()>DateSerial(Year(Now()),10,1),Year(Now())+1,Year(Now())) & IIf(DLookUp("NextNum","Next_DN_qry","IDMatch = " & "IIf(Now()>DateSerial(Year(Now()),10,1),Year(Now())+1,Year(Now()))") Is Null,"9100",DLookUp("NextNum","Next_DN_qry","IDMatch = " & "IIf(Now()>DateSerial(Year(Now()),10,1),Year(Now())+1,Year(Now()))"))

Which says that the default value should display 04-YYYY-NNNN where YYYY is the fiscal year, starting on 1 Oct, and NNNN is the next number for the current fiscal year, unless there is no number (it's a new year, so it's null), in which case it defaults to 9100, the start of our alloted series of numbers.

Thanks again,

Stan