Hi John,
Glad to help - your reply gave me a much needed boost for the day
Your DLookup statement is close but it has some problems with the quotes.
From VBA, if you are doing a comparison on a text field and you are
appending the value into the condition, you must quote wrap the value so
that Jet (the database engine) will correctly see the value as a string. You
could also use the direct reference to the control instead of appending the
value into the condition. There is a subtle difference in doing this because
this allows Dlookup to use the Access Expression service to resolve the
control reference with the actual value and in doing so, the Expression
Service (apparently) puts the value into the correct format. Using VBA,
either of the following should work:
dim strX as string
dim strY as string
' Append in the value
strX=DLookUp("AcctUnit","Vendor","Vendor= """ _
& Forms![AddExpenses]![Vendor] & """")
' Use the control reference in the condition
strY=DLookUp("AcctUnit","Vendor", _
"Vendor=Forms![AddExpenses]![Vendor]")
msgbox strX & vbCRLF & strY
Also note that I used an embedded pair of double quote characters (")
everywhere in the string that I want a single double quote character to
appear. This pair of double quotes is in addition to the VBA string
delimiting pair of quotes. The issue is further confused by long lines so I
broke it into multiple lines and used line continuations.
The syntax in the DefaultValue property just uses the second form of the
Dlookup though either would work.
--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
John said:
Thanks Sandra thats brilliant!!
I'd tried Dlookup with
=DLookUp("AcctUnit","Vendor","Vendor= " & "'
" Forms![AddExpenses]![Vendor] "'")
the syntax of which I'd copied from some VB. I've got the
general idea of Dlookup but I'm baffled as to why the VB
syntax fails in the form ?
I'd love to know the secret since your answer worked first
time (I actually cut and pasted it) !!
With admiration and thanks
JOhn P
-----Original Message-----
You need to use the Dlookup function since you can't directly issue
a select statement:
=DLookUp("AcctUnit","Vendor","Vendor=Forms![AddExpenses]! [Vendor] ")
You might also want to look at help for the Dlookup function/
--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.
John said:
I'm trying to force a field in a subform to have a default
value selected from a table with a parameter based on a
value from the main form.
My guess was that the default value property in my subform
would need a statement like:-
=(select <field> from <table> where <field in table> =
<value from main form>)
I entered:-
=(SELECT [Vendor]![AcctUnit] FROM Vendor where [Vendor]!
[Vendor] = Forms![AddExpenses]![Vendor] )
The default value is always given as #Name? on my subform.
I've tried :-
=(select <field> from <table> where <field in table> =
"<absolute value>"
and ensured that the latter returns a single row by
running it as a query but still get #Name? as the default
value.
Thanks in anticipation of any help
John P
.
