Filling a Textbox without a Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am trying to fill a text box by pulling data without using a query. I am
trying to do this because I and the client do not want 288 queries.

The last reply off of my last post gave this code:

Me.Text582=dlookup("TempAverage","Average","Month=" & me.txtMonth & " And
Year=" & me.txtYear)

somewhere in this there is a syntax error. I also want to state that the
names of the fields have been changed because I now know they are reserved
words.

The table TempTransfer Average, now has three fields:
TransferMonth
TransferYear
TransferAverage

I tried to change the code to this:
strMonth = 10
strYear = 2004
Me.Text585 = DLookup("[TransferAverage]", "TempTransferAverage",
"TransferMonth = " & strMonth And " TransferYear = " & strYear)

Yet, now I get a type mismatch error.

Does anyone know how to fix this error and fill a text box using code
instead of a query?

Thanks in Advance,
Elena
 
In the source, you can paste in an SQL statement to represent a query. It
does not have to be a stored query.

In the source of the field, evoke the query builder (...) and build the
query as usual.


Rick B
 
hi,
all table fields in a dlookup should be in square brackets
try changing tranfermonth to [transfermonth] and
transferyear to [transferyear]
-----Original Message-----
I am trying to fill a text box by pulling data without using a query. I am
trying to do this because I and the client do not want 288 queries.

The last reply off of my last post gave this code:

Me.Text582=dlookup("TempAverage","Average","Month=" & me.txtMonth & " And
Year=" & me.txtYear)

somewhere in this there is a syntax error. I also want to state that the
names of the fields have been changed because I now know they are reserved
words.

The table TempTransfer Average, now has three fields:
TransferMonth
TransferYear
TransferAverage

I tried to change the code to this:
strMonth = 10
strYear = 2004
Me.Text585 = DLookup
Click to expand...
("[TransferAverage]", "TempTransferAverage",
 
A few more details...

Row Source Type = Table/Qery


Row Source <---- that is where you click the builder.



Rick B said:
In the source, you can paste in an SQL statement to represent a query. It
does not have to be a stored query.

In the source of the field, evoke the query builder (...) and build the
query as usual.


Rick B



Elena said:
I am trying to fill a text box by pulling data without using a query. I am
trying to do this because I and the client do not want 288 queries.

The last reply off of my last post gave this code:

Me.Text582=dlookup("TempAverage","Average","Month=" & me.txtMonth & " And
Year=" & me.txtYear)

somewhere in this there is a syntax error. I also want to state that the
names of the fields have been changed because I now know they are reserved
words.

The table TempTransfer Average, now has three fields:
TransferMonth
TransferYear
TransferAverage

I tried to change the code to this:
strMonth = 10
strYear = 2004
Me.Text585 = DLookup("[TransferAverage]", "TempTransferAverage",
"TransferMonth = " & strMonth And " TransferYear = " & strYear)

Yet, now I get a type mismatch error.

Does anyone know how to fix this error and fill a text box using code
instead of a query?

Thanks in Advance,
Elena
Click to expand...
Click to expand...
 
I am trying to fill a text box by pulling data without using a query. I am
trying to do this because I and the client do not want 288 queries.
Click to expand...

You need one query, not 288. If you're assuming that queries can only
use hardcoded literal criteria, you're making a common mistake - use a
Parameter Query instead.
The last reply off of my last post gave this code:

Me.Text582=dlookup("TempAverage","Average","Month=" & me.txtMonth & " And
Year=" & me.txtYear)

somewhere in this there is a syntax error. I also want to state that the
names of the fields have been changed because I now know they are reserved
words.

The table TempTransfer Average, now has three fields:
TransferMonth
TransferYear
TransferAverage

I tried to change the code to this:
strMonth = 10
strYear = 2004
Me.Text585 = DLookup("[TransferAverage]", "TempTransferAverage",
"TransferMonth = " & strMonth And " TransferYear = " & strYear)

Yet, now I get a type mismatch error.

Does anyone know how to fix this error and fill a text box using code
instead of a query?
Click to expand...

You also have some misplaced quotemarks. Try

Me.Text585 = DLookup("[TransferAverage]", "TempTransferAverage",
"TransferMonth = " & strMonth " And TransferYear = " & strYear)

When you concatenate the string literals with the variables, the third
argument will come out to something like

"TransferMonth = 8 And TransferYear = 2001"

Are these Text fields? If so you need the syntactically required
quotemarks. If that's the case try:

Me.Text585 = DLookup("[TransferAverage]", "TempTransferAverage",
"TransferMonth = '" & strMonth & "' And TransferYear = '" & strYear &
"'")


John W. Vinson[MVP]
 
Hi there,

These are numbers not text fields. I tried cutting and pasting your code
(the first one) into my database. I get this error:

Expected: list separator or )

I even tried puting the fields in brackets as previously suggested. I still
get the same error.

Me.Text585 = DLookup("[TransferAverage]", "TempTransferAverage",
"[TransferMonth] = " & strMonth " And [TransferYear] = " & strYear)


John Vinson said:
I am trying to fill a text box by pulling data without using a query. I am
trying to do this because I and the client do not want 288 queries.
Click to expand...

You need one query, not 288. If you're assuming that queries can only
use hardcoded literal criteria, you're making a common mistake - use a
Parameter Query instead.
The last reply off of my last post gave this code:

Me.Text582=dlookup("TempAverage","Average","Month=" & me.txtMonth & " And
Year=" & me.txtYear)

somewhere in this there is a syntax error. I also want to state that the
names of the fields have been changed because I now know they are reserved
words.

The table TempTransfer Average, now has three fields:
TransferMonth
TransferYear
TransferAverage

I tried to change the code to this:
strMonth = 10
strYear = 2004
Me.Text585 = DLookup("[TransferAverage]", "TempTransferAverage",
"TransferMonth = " & strMonth And " TransferYear = " & strYear)

Yet, now I get a type mismatch error.

Does anyone know how to fix this error and fill a text box using code
instead of a query?
Click to expand...

You also have some misplaced quotemarks. Try

Me.Text585 = DLookup("[TransferAverage]", "TempTransferAverage",
"TransferMonth = " & strMonth " And TransferYear = " & strYear)

When you concatenate the string literals with the variables, the third
argument will come out to something like

"TransferMonth = 8 And TransferYear = 2001"

Are these Text fields? If so you need the syntactically required
quotemarks. If that's the case try:

Me.Text585 = DLookup("[TransferAverage]", "TempTransferAverage",
"TransferMonth = '" & strMonth & "' And TransferYear = '" & strYear &
"'")


John W. Vinson[MVP]
Click to expand...
 
I can't find Row Source on a text box. What is it under? I found control
source and I can't seem to get that to work.

[email protected] said:
hi,
all table fields in a dlookup should be in square brackets
try changing tranfermonth to [transfermonth] and
transferyear to [transferyear]
-----Original Message-----
I am trying to fill a text box by pulling data without using a query. I am
trying to do this because I and the client do not want 288 queries.

The last reply off of my last post gave this code:

Me.Text582=dlookup("TempAverage","Average","Month=" & me.txtMonth & " And
Year=" & me.txtYear)

somewhere in this there is a syntax error. I also want to state that the
names of the fields have been changed because I now know they are reserved
words.

The table TempTransfer Average, now has three fields:
TransferMonth
TransferYear
TransferAverage

I tried to change the code to this:
strMonth = 10
strYear = 2004
Me.Text585 = DLookup
Click to expand...
("[TransferAverage]", "TempTransferAverage",
"TransferMonth = " & strMonth And " TransferYear = " & strYear)

Yet, now I get a type mismatch error.

Does anyone know how to fix this error and fill a text box using code
instead of a query?

Thanks in Advance,
Elena

.
Click to expand...
Click to expand...
 
I get this error:
Expected: list separator or )
Click to expand...
I even tried puting the fields in brackets as previously suggested. I
still get the same error.

Me.Text585 = DLookup("[TransferAverage]", "TempTransferAverage",
"[TransferMonth] = " & strMonth " And [TransferYear] = " & strYear)
Click to expand...

There is a missing ampersand after the strMonth identifier.

Advice: whenever you are putting together a complicated string, do it in
bits and check it before sending it. Furthermore, never rely on VBA to
format stuff for you but always do it yourself:

' build the criterion string carefully
strWhere = "TransferMonth = " & Format(CInt(strMonth), "00")
strWhere = strWhere & vbNewLine & "And" & vNewLine
strWhere = strWhere & "TransferYear = " & Format(CInt(strYear), "0000")

' this is the debugging line: cannot overemphasise how important
' it is...
MsgBox strWhere,,"Debugging: check this line carefully"

' this variable should be a variant, in order to catch the possible
' null answer
varTemp = DLookUp("TransferAverage", "TempTransferAverage", strWhere)

' now do something useful with it
If IsNull(varTemp) Then
MsgBox "OOps: it's not there!!", vbExclamation, "System Error"

Else
' you cannot seriously call a form control Text585
txtDisplayCurrentAverage = varTemp

End If


' and so on...


Hope that helps


Tim F
 
Hi there,

These are numbers not text fields. I tried cutting and pasting your code
(the first one) into my database. I get this error:

Expected: list separator or )

I even tried puting the fields in brackets as previously suggested. I still
get the same error.

Me.Text585 = DLookup("[TransferAverage]", "TempTransferAverage",
"[TransferMonth] = " & strMonth " And [TransferYear] = " & strYear)
Click to expand...

Sorry, my typo: missing &.

Me.Text585 = DLookup("[TransferAverage]", "TempTransferAverage",
"[TransferMonth] = " & strMonth & " And [TransferYear] = " & strYear)


John W. Vinson[MVP]
 
Back
Top