Dlookup frustration

  • Thread starter Thread starter Rpettis31
  • Start date Start date
R

Rpettis31

Private Sub List8_Click()

lstItem = Me.List8.Column(1)

Me.txtOH.ControlSource = DLookup("TotalOH", "[tblInventory3]", "Item =" &
lstItem)


End Sub
 
You don't tell us what problem you are having, so here is a guess:

1. Need to declare lstItem:
dim lstItem as variant

2. Line with dlookup should be:

Me.txtOH = DLookup("TotalOH", "tblInventory3", "Item = " & "'" & lstItem &
"'") ' if Item is text, or
Me.txtOH = DLookup("TotalOH", "tblInventory3", "Item = " & lstItem ) ' if it
is numeric

Damon
 
I am getting a you cancelled the operation error with the Dlookup so , I have
changed to using sql, however the value is coming up as #NAME? in the text
box.

I am trying to obtain the TotalOH qty from the Inventory3 table when the
item = to the item selected from the list box.

Private Sub List8_Click()

lstItem = Me.List8.Column(1)

strSQL = "Select tblInventory3.[TotalOH]FROM tblInventory3" & _
"Where tblInventory3.[Item]='" & lstItem & "';"

Me.txtOH.ControlSource = strSQL

Douglas J. Steele said:
It would really help if you indicated what problem you're having...

At a glance, it doesn't seem appropriate to be setting a ControlSource to
the result of a DLookup. What you likely want is either:

Me.txtOH = DLookup("TotalOH", "[tblInventory3]", "Item =" & lstItem)

or

Me.txtOH.ControlSource = "=DLookup(""TotalOH"", " & _
"""[tblInventory3]"", ""Item ="" & lstItem)"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rpettis31 said:
Private Sub List8_Click()

lstItem = Me.List8.Column(1)

Me.txtOH.ControlSource = DLookup("TotalOH", "[tblInventory3]", "Item =" &
lstItem)


End Sub
Click to expand...
Click to expand...
 
You can't set the ControlSource of a text box to a SQL statement.

Note that you've now introduced quotes around the value of lstItem. If Item
is a text field, you need those same quotes in your DLookup statement.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rpettis31 said:
I am getting a you cancelled the operation error with the Dlookup so , I
have
changed to using sql, however the value is coming up as #NAME? in the text
box.

I am trying to obtain the TotalOH qty from the Inventory3 table when the
item = to the item selected from the list box.

Private Sub List8_Click()

lstItem = Me.List8.Column(1)

strSQL = "Select tblInventory3.[TotalOH]FROM tblInventory3" & _
"Where tblInventory3.[Item]='" & lstItem & "';"

Me.txtOH.ControlSource = strSQL

Douglas J. Steele said:
It would really help if you indicated what problem you're having...

At a glance, it doesn't seem appropriate to be setting a ControlSource to
the result of a DLookup. What you likely want is either:

Me.txtOH = DLookup("TotalOH", "[tblInventory3]", "Item =" & lstItem)

or

Me.txtOH.ControlSource = "=DLookup(""TotalOH"", " & _
"""[tblInventory3]"", ""Item ="" & lstItem)"



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Rpettis31 said:
Private Sub List8_Click()

lstItem = Me.List8.Column(1)

Me.txtOH.ControlSource = DLookup("TotalOH", "[tblInventory3]", "Item ="
&
lstItem)


End Sub
Click to expand...
Click to expand...
Click to expand...
 
Thanks for your help, I always forget the " ' " in the syntax...

Damon Heron said:
You don't tell us what problem you are having, so here is a guess:

1. Need to declare lstItem:
dim lstItem as variant

2. Line with dlookup should be:

Me.txtOH = DLookup("TotalOH", "tblInventory3", "Item = " & "'" & lstItem &
"'") ' if Item is text, or
Me.txtOH = DLookup("TotalOH", "tblInventory3", "Item = " & lstItem ) ' if it
is numeric

Damon

Rpettis31 said:
Private Sub List8_Click()

lstItem = Me.List8.Column(1)

Me.txtOH.ControlSource = DLookup("TotalOH", "[tblInventory3]", "Item =" &
lstItem)


End Sub
Click to expand...
Click to expand...
 
I do not understand the DLookup very well apparently as I am getting a
runtime error 2448 from this code. I was trying to get the values associated
with the item selected to fill in the text boxes. I am assuming there is a
better way to do this?

Me.txtprojqty.Value = DLookup("Projected", "tblInventory3", "Item = " & "'"
& lstItem & "'")
Me.txtNetQty = DLookup("NetAvail", "tblInventory3", "Item = " & "'" &
lstItem & "'")
Me.txtOHDollars = DLookup("DollarsOH", "tblInventory3", "Item = " & "'" &
lstItem & "'")
Me.txtProDollars = DLookup("ProjectedDollars", "tblInventory3", "Item = " &
"'" & lstItem & "'")


Rpettis31 said:
Thanks for your help, I always forget the " ' " in the syntax...

Damon Heron said:
You don't tell us what problem you are having, so here is a guess:

1. Need to declare lstItem:
dim lstItem as variant

2. Line with dlookup should be:

Me.txtOH = DLookup("TotalOH", "tblInventory3", "Item = " & "'" & lstItem &
"'") ' if Item is text, or
Me.txtOH = DLookup("TotalOH", "tblInventory3", "Item = " & lstItem ) ' if it
is numeric

Damon

Rpettis31 said:
Private Sub List8_Click()

lstItem = Me.List8.Column(1)

Me.txtOH.ControlSource = DLookup("TotalOH", "[tblInventory3]", "Item =" &
lstItem)


End Sub
Click to expand...
Click to expand...
Click to expand...
 
That error code about assigning a value to some object, can be applied to
many things. Which line is the offender? Have you put a breakpoint at the
beginning of the click event and stepped thru the code?
Also, I would delete the .Value on the first line.

Damon

Rpettis31 said:
I do not understand the DLookup very well apparently as I am getting a
runtime error 2448 from this code. I was trying to get the values
associated
with the item selected to fill in the text boxes. I am assuming there is
a
better way to do this?

Me.txtprojqty.Value = DLookup("Projected", "tblInventory3", "Item = " &
"'"
& lstItem & "'")
Me.txtNetQty = DLookup("NetAvail", "tblInventory3", "Item = " & "'" &
lstItem & "'")
Me.txtOHDollars = DLookup("DollarsOH", "tblInventory3", "Item = " & "'" &
lstItem & "'")
Me.txtProDollars = DLookup("ProjectedDollars", "tblInventory3", "Item = "
&
"'" & lstItem & "'")


Rpettis31 said:
Thanks for your help, I always forget the " ' " in the syntax...

Damon Heron said:
You don't tell us what problem you are having, so here is a guess:

1. Need to declare lstItem:
dim lstItem as variant

2. Line with dlookup should be:

Me.txtOH = DLookup("TotalOH", "tblInventory3", "Item = " & "'" &
lstItem &
"'") ' if Item is text, or
Me.txtOH = DLookup("TotalOH", "tblInventory3", "Item = " & lstItem ) '
if it
is numeric

Damon

Private Sub List8_Click()

lstItem = Me.List8.Column(1)

Me.txtOH.ControlSource = DLookup("TotalOH", "[tblInventory3]", "Item
=" &
lstItem)


End Sub
Click to expand...
Click to expand...
Click to expand...
 
The text associated with that error number is:

You can't assign a value to this object.
* The object may be a control on a read-only form.
* The object may be on a form that is open in Design view.
* The value may be too large for this field

Have you confirmed that none of those three conditions is true?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Rpettis31 said:
I do not understand the DLookup very well apparently as I am getting a
runtime error 2448 from this code. I was trying to get the values
associated
with the item selected to fill in the text boxes. I am assuming there is
a
better way to do this?

Me.txtprojqty.Value = DLookup("Projected", "tblInventory3", "Item = " &
"'"
& lstItem & "'")
Me.txtNetQty = DLookup("NetAvail", "tblInventory3", "Item = " & "'" &
lstItem & "'")
Me.txtOHDollars = DLookup("DollarsOH", "tblInventory3", "Item = " & "'" &
lstItem & "'")
Me.txtProDollars = DLookup("ProjectedDollars", "tblInventory3", "Item = "
&
"'" & lstItem & "'")


Rpettis31 said:
Thanks for your help, I always forget the " ' " in the syntax...

Damon Heron said:
You don't tell us what problem you are having, so here is a guess:

1. Need to declare lstItem:
dim lstItem as variant

2. Line with dlookup should be:

Me.txtOH = DLookup("TotalOH", "tblInventory3", "Item = " & "'" &
lstItem &
"'") ' if Item is text, or
Me.txtOH = DLookup("TotalOH", "tblInventory3", "Item = " & lstItem ) '
if it
is numeric

Damon

Private Sub List8_Click()

lstItem = Me.List8.Column(1)

Me.txtOH.ControlSource = DLookup("TotalOH", "[tblInventory3]", "Item
=" &
lstItem)


End Sub
Click to expand...
Click to expand...
Click to expand...
 
Rather than using a collection of DLookup statements, why not
add those fields as columns in your list box, then set the Control
Source of each text box to the appropriate column. So each
text box would have a Control Source like;

=[lstItem].Column(x)

where x is the numeric value of each column in the query. It is a
zero based index, so the first column is Column(0), the second is
Column(1), etc.

_________

Sean Bailey

Rpettis31 said:
I do not understand the DLookup very well apparently as I am getting a
runtime error 2448 from this code. I was trying to get the values
associated
with the item selected to fill in the text boxes. I am assuming there is
a
better way to do this?

Me.txtprojqty.Value = DLookup("Projected", "tblInventory3", "Item = " &
"'"
& lstItem & "'")
Me.txtNetQty = DLookup("NetAvail", "tblInventory3", "Item = " & "'" &
lstItem & "'")
Me.txtOHDollars = DLookup("DollarsOH", "tblInventory3", "Item = " & "'" &
lstItem & "'")
Me.txtProDollars = DLookup("ProjectedDollars", "tblInventory3", "Item = "
&
"'" & lstItem & "'")


Rpettis31 said:
Thanks for your help, I always forget the " ' " in the syntax...

Damon Heron said:
You don't tell us what problem you are having, so here is a guess:

1. Need to declare lstItem:
dim lstItem as variant

2. Line with dlookup should be:

Me.txtOH = DLookup("TotalOH", "tblInventory3", "Item = " & "'" &
lstItem &
"'") ' if Item is text, or
Me.txtOH = DLookup("TotalOH", "tblInventory3", "Item = " & lstItem ) '
if it
is numeric

Damon

Private Sub List8_Click()

lstItem = Me.List8.Column(1)

Me.txtOH.ControlSource = DLookup("TotalOH", "[tblInventory3]", "Item
=" &
lstItem)


End Sub
Click to expand...
Click to expand...
Click to expand...
 
I have created another list box to try this method and get the same error on
the if I comment out the error line the error goes to the next line. The
text boxes are set to =DLookUp("SumofNetAvail","qryNetAvailQty") when the
form loads.



Private Sub lstMainItems_Click()


lstItem = Me.lstMainItems.Column(1)
Me.txtOH = Me.lstMainItems.Column(4)
Me.txtNetDollars = Me.lstMainItems.Column(8) "Error HERE"
Me.txtprojqty = Me.lstMainItems.Column(6)
Me.txtNetQty = Me.lstMainItems.Column(5)
Me.txtOHDollars = Me.lstMainItems.Column(7)
Me.txtProDollars = Me.lstMainItems.Column(9)

End Sub

Beetle said:
Rather than using a collection of DLookup statements, why not
add those fields as columns in your list box, then set the Control
Source of each text box to the appropriate column. So each
text box would have a Control Source like;

=[lstItem].Column(x)

where x is the numeric value of each column in the query. It is a
zero based index, so the first column is Column(0), the second is
Column(1), etc.

_________

Sean Bailey

Rpettis31 said:
I do not understand the DLookup very well apparently as I am getting a
runtime error 2448 from this code. I was trying to get the values
associated
with the item selected to fill in the text boxes. I am assuming there is
a
better way to do this?

Me.txtprojqty.Value = DLookup("Projected", "tblInventory3", "Item = " &
"'"
& lstItem & "'")
Me.txtNetQty = DLookup("NetAvail", "tblInventory3", "Item = " & "'" &
lstItem & "'")
Me.txtOHDollars = DLookup("DollarsOH", "tblInventory3", "Item = " & "'" &
lstItem & "'")
Me.txtProDollars = DLookup("ProjectedDollars", "tblInventory3", "Item = "
&
"'" & lstItem & "'")


Rpettis31 said:
Thanks for your help, I always forget the " ' " in the syntax...

:

You don't tell us what problem you are having, so here is a guess:

1. Need to declare lstItem:
dim lstItem as variant

2. Line with dlookup should be:

Me.txtOH = DLookup("TotalOH", "tblInventory3", "Item = " & "'" &
lstItem &
"'") ' if Item is text, or
Me.txtOH = DLookup("TotalOH", "tblInventory3", "Item = " & lstItem ) '
if it
is numeric

Damon

Private Sub List8_Click()

lstItem = Me.List8.Column(1)

Me.txtOH.ControlSource = DLookup("TotalOH", "[tblInventory3]", "Item
=" &
lstItem)


End Sub
Click to expand...
Click to expand...
Click to expand...
 
Try doing it directly in the control source of each text box (not in code)
as I suggested and see what happens.

_________

Sean Bailey

Rpettis31 said:
I have created another list box to try this method and get the same error
on
the if I comment out the error line the error goes to the next line. The
text boxes are set to =DLookUp("SumofNetAvail","qryNetAvailQty") when the
form loads.



Private Sub lstMainItems_Click()


lstItem = Me.lstMainItems.Column(1)
Me.txtOH = Me.lstMainItems.Column(4)
Me.txtNetDollars = Me.lstMainItems.Column(8) "Error HERE"
Me.txtprojqty = Me.lstMainItems.Column(6)
Me.txtNetQty = Me.lstMainItems.Column(5)
Me.txtOHDollars = Me.lstMainItems.Column(7)
Me.txtProDollars = Me.lstMainItems.Column(9)

End Sub

Beetle said:
Rather than using a collection of DLookup statements, why not
add those fields as columns in your list box, then set the Control
Source of each text box to the appropriate column. So each
text box would have a Control Source like;

=[lstItem].Column(x)

where x is the numeric value of each column in the query. It is a
zero based index, so the first column is Column(0), the second is
Column(1), etc.

_________

Sean Bailey

Rpettis31 said:
I do not understand the DLookup very well apparently as I am getting a
runtime error 2448 from this code. I was trying to get the values
associated
with the item selected to fill in the text boxes. I am assuming there
is
a
better way to do this?

Me.txtprojqty.Value = DLookup("Projected", "tblInventory3", "Item = " &
"'"
& lstItem & "'")
Me.txtNetQty = DLookup("NetAvail", "tblInventory3", "Item = " & "'" &
lstItem & "'")
Me.txtOHDollars = DLookup("DollarsOH", "tblInventory3", "Item = " & "'"
&
lstItem & "'")
Me.txtProDollars = DLookup("ProjectedDollars", "tblInventory3", "Item =
"
&
"'" & lstItem & "'")


:

Thanks for your help, I always forget the " ' " in the syntax...

:

You don't tell us what problem you are having, so here is a guess:

1. Need to declare lstItem:
dim lstItem as variant

2. Line with dlookup should be:

Me.txtOH = DLookup("TotalOH", "tblInventory3", "Item = " & "'" &
lstItem &
"'") ' if Item is text, or
Me.txtOH = DLookup("TotalOH", "tblInventory3", "Item = " & lstItem )
'
if it
is numeric

Damon

Private Sub List8_Click()

lstItem = Me.List8.Column(1)

Me.txtOH.ControlSource = DLookup("TotalOH", "[tblInventory3]",
"Item
=" &
lstItem)


End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top