List Box Troubles

  • Thread starter Thread starter Roger Converse
  • Start date Start date
R

Roger Converse

I have a form that I am trying to create. Within this form, one of the
things you choose is a product ID. Once that is chosen, I would like to have
my listbox refreshed with all of the warehouses that the product is available
in.

I first attempted to create a temp table on the AfterUpdate event of my
ProdID label. I receive an error stating the previous temp table can't be
deleted because it is in use.

I would use the control source in properties, but I don't know how to "pass"
the prodID variable like that.

It has been a little while since I have worked in Access, but I think this
is doable. Any assistance would be greatly appreciated. I have been
struggling for a couple of hours now.

Thanks,
Roger
 
For your list box row source, create a query on whatever table contains the
product id and the warehouse id and filter it based on the control where you
enter the product id.
 
Hello,

Thanks for the response. I have never worked with filters. I am not sure I
know excatly what you are saying.

This is my query in the row source of my list box, which does not work,
because it never finds me.Prod_ID

SELECT Activeprods.Whseid, tblInvalid_WhseID.WhseID
FROM Activeprods LEFT JOIN tblInvalid_WhseID ON Activeprods.Whseid =
tblInvalid_WhseID.WhseID
GROUP BY Activeprods.Prodid, Activeprods.Whseid, tblInvalid_WhseID.WhseID
HAVING (((Activeprods.Prodid)= & me.Prod_ID & ) AND
((tblInvalid_WhseID.WhseID) Is Null));

I created a filter, but I am not sure how to apply it in this scenario.
Would it be possible for you to be a little more specific as to how to do
this? I had to save my filter as a query. It looks as such:

SELECT Activeprods.WhseID
FROM Activeprods
WHERE (((Activeprods.Prodid)=[me].[lbProd_ID]));

I am still at a disconnect as to how these play together.

Thank you,
Roger
 
You shuld not have to use a filter. The row source should do it all.
What is the data type of Prod_ID? As your query is written, it expects it
to be a numeric field.
--
Dave Hargis, Microsoft Access MVP


Roger Converse said:
Hello,

Thanks for the response. I have never worked with filters. I am not sure I
know excatly what you are saying.

This is my query in the row source of my list box, which does not work,
because it never finds me.Prod_ID

SELECT Activeprods.Whseid, tblInvalid_WhseID.WhseID
FROM Activeprods LEFT JOIN tblInvalid_WhseID ON Activeprods.Whseid =
tblInvalid_WhseID.WhseID
GROUP BY Activeprods.Prodid, Activeprods.Whseid, tblInvalid_WhseID.WhseID
HAVING (((Activeprods.Prodid)= & me.Prod_ID & ) AND
((tblInvalid_WhseID.WhseID) Is Null));

I created a filter, but I am not sure how to apply it in this scenario.
Would it be possible for you to be a little more specific as to how to do
this? I had to save my filter as a query. It looks as such:

SELECT Activeprods.WhseID
FROM Activeprods
WHERE (((Activeprods.Prodid)=[me].[lbProd_ID]));

I am still at a disconnect as to how these play together.

Thank you,
Roger


Klatuu said:
For your list box row source, create a query on whatever table contains the
product id and the warehouse id and filter it based on the control where you
enter the product id.
Click to expand...
Click to expand...
 
One thing about Access SQL I've found is that it has funny syntax that
seems to be at odds with the 'standard' SQL--in things like
Parenthesis "(" and the ' apostrope'. Play around with the SQL view
of the Query menu. You may be off by a parenthesis.

For example, this simple parametricised query did not work until I put
in a couple of extra parens:

SELECT Count(*) AS MyCount1
FROM AccountStocks
WHERE (((AccountStocks.AcctID)=[1]) AND
((AccountStocks.StockSymbol)=[2]));

Stuff like that.
As for struggling for hours, it's because VB in Access has a terrible
debugger and compiler/interpreter. For example, it allowed the below
to compile when it never should have:


'CORRECT SYNTAX:

If Not IsNull(Me.StockSymbol) Then
Str002G = Me.AcctID.Value
If Not IsNull(Me.AcctID) Then
Str001G = Me.StockSymbol.Value
End If
End If

replaces (WRONG SYNTAX--GIVES OBSCURE RUNTIME ERRORS--WASTED SEVERAL
HOURS YESTERDAY WITH THIS)

If (Me.StockSymbol.Text <> Null And Me.AcctID.Text <> Null ) Then

...

End If
 
No, it is a text.

Klatuu said:
You shuld not have to use a filter. The row source should do it all.
What is the data type of Prod_ID? As your query is written, it expects it
to be a numeric field.
--
Dave Hargis, Microsoft Access MVP


Roger Converse said:
Hello,

Thanks for the response. I have never worked with filters. I am not sure I
know excatly what you are saying.

This is my query in the row source of my list box, which does not work,
because it never finds me.Prod_ID

SELECT Activeprods.Whseid, tblInvalid_WhseID.WhseID
FROM Activeprods LEFT JOIN tblInvalid_WhseID ON Activeprods.Whseid =
tblInvalid_WhseID.WhseID
GROUP BY Activeprods.Prodid, Activeprods.Whseid, tblInvalid_WhseID.WhseID
HAVING (((Activeprods.Prodid)= & me.Prod_ID & ) AND
((tblInvalid_WhseID.WhseID) Is Null));

I created a filter, but I am not sure how to apply it in this scenario.
Would it be possible for you to be a little more specific as to how to do
this? I had to save my filter as a query. It looks as such:

SELECT Activeprods.WhseID
FROM Activeprods
WHERE (((Activeprods.Prodid)=[me].[lbProd_ID]));

I am still at a disconnect as to how these play together.

Thank you,
Roger


Klatuu said:
For your list box row source, create a query on whatever table contains the
product id and the warehouse id and filter it based on the control where you
enter the product id.
--
Dave Hargis, Microsoft Access MVP


:

I have a form that I am trying to create. Within this form, one of the
things you choose is a product ID. Once that is chosen, I would like to have
my listbox refreshed with all of the warehouses that the product is available
in.

I first attempted to create a temp table on the AfterUpdate event of my
ProdID label. I receive an error stating the previous temp table can't be
deleted because it is in use.

I would use the control source in properties, but I don't know how to "pass"
the prodID variable like that.

It has been a little while since I have worked in Access, but I think this
is doable. Any assistance would be greatly appreciated. I have been
struggling for a couple of hours now.

Thanks,
Roger
Click to expand...
Click to expand...
Click to expand...
 
If it is a text field, then change this line:
HAVING (((Activeprods.Prodid)= & me.Prod_ID & ) AND
((tblInvalid_WhseID.WhseID) Is Null));

To:
HAVING (((Activeprods.Prodid)= & "'" & Forms!FormName.Prod_ID & "'" & ) AND
((tblInvalid_WhseID.WhseID) Is Null));

Note the replacement of Me. A query doesn't understand Me. You have to use
the syntax above.


--
Dave Hargis, Microsoft Access MVP


Roger Converse said:
No, it is a text.

Klatuu said:
You shuld not have to use a filter. The row source should do it all.
What is the data type of Prod_ID? As your query is written, it expects it
to be a numeric field.
--
Dave Hargis, Microsoft Access MVP


Roger Converse said:
Hello,

Thanks for the response. I have never worked with filters. I am not sure I
know excatly what you are saying.

This is my query in the row source of my list box, which does not work,
because it never finds me.Prod_ID

SELECT Activeprods.Whseid, tblInvalid_WhseID.WhseID
FROM Activeprods LEFT JOIN tblInvalid_WhseID ON Activeprods.Whseid =
tblInvalid_WhseID.WhseID
GROUP BY Activeprods.Prodid, Activeprods.Whseid, tblInvalid_WhseID.WhseID
HAVING (((Activeprods.Prodid)= & me.Prod_ID & ) AND
((tblInvalid_WhseID.WhseID) Is Null));

I created a filter, but I am not sure how to apply it in this scenario.
Would it be possible for you to be a little more specific as to how to do
this? I had to save my filter as a query. It looks as such:

SELECT Activeprods.WhseID
FROM Activeprods
WHERE (((Activeprods.Prodid)=[me].[lbProd_ID]));

I am still at a disconnect as to how these play together.

Thank you,
Roger


:

For your list box row source, create a query on whatever table contains the
product id and the warehouse id and filter it based on the control where you
enter the product id.
--
Dave Hargis, Microsoft Access MVP


:

I have a form that I am trying to create. Within this form, one of the
things you choose is a product ID. Once that is chosen, I would like to have
my listbox refreshed with all of the warehouses that the product is available
in.

I first attempted to create a temp table on the AfterUpdate event of my
ProdID label. I receive an error stating the previous temp table can't be
deleted because it is in use.

I would use the control source in properties, but I don't know how to "pass"
the prodID variable like that.

It has been a little while since I have worked in Access, but I think this
is doable. Any assistance would be greatly appreciated. I have been
struggling for a couple of hours now.

Thanks,
Roger
Click to expand...
Click to expand...
Click to expand...
 
In reality, I have never seen any version of SQL that is "standard". Each
has it's own issues. For the most part, they are all alike, but in some
cases, there is a difference. For example, the * is a wildcard in Jet SQL,
but in SQL Server it is %.

As to the compiler issue. The compiler only checks for syntactical
problems. To the compiler, the incorrect line of code is correctly
structured. But, since you are dealing with Null, it will fail at run time.
--
Dave Hargis, Microsoft Access MVP


raylopez99 said:
One thing about Access SQL I've found is that it has funny syntax that
seems to be at odds with the 'standard' SQL--in things like
Parenthesis "(" and the ' apostrope'. Play around with the SQL view
of the Query menu. You may be off by a parenthesis.

For example, this simple parametricised query did not work until I put
in a couple of extra parens:

SELECT Count(*) AS MyCount1
FROM AccountStocks
WHERE (((AccountStocks.AcctID)=[1]) AND
((AccountStocks.StockSymbol)=[2]));

Stuff like that.
As for struggling for hours, it's because VB in Access has a terrible
debugger and compiler/interpreter. For example, it allowed the below
to compile when it never should have:


'CORRECT SYNTAX:

If Not IsNull(Me.StockSymbol) Then
Str002G = Me.AcctID.Value
If Not IsNull(Me.AcctID) Then
Str001G = Me.StockSymbol.Value
End If
End If

replaces (WRONG SYNTAX--GIVES OBSCURE RUNTIME ERRORS--WASTED SEVERAL
HOURS YESTERDAY WITH THIS)

If (Me.StockSymbol.Text <> Null And Me.AcctID.Text <> Null ) Then

...

End If
Click to expand...
 
As for struggling for hours, it's because VB in Access has a terrible
debugger and compiler/interpreter. For example, it allowed the below
to compile when it never should have:
[...]
(WRONG SYNTAX--GIVES OBSCURE RUNTIME ERRORS--WASTED
SEVERAL HOURS YESTERDAY WITH THIS)

If (Me.StockSymbol.Text <> Null And Me.AcctID.Text <> Null ) Then

...

End If
Click to expand...

You keep posting this complaint, but you are mistaken. There is absolutely
nothing wrong with that syntax. It's the semantics that are wrong: the
Text property is always a string and so can never be Null, and the
documented syntax of comparisons to Null makes it clear that "x <> Null"
will always yield a Null result. You may argue, if you wish, that the
compiler should vet the semantics of your code, though that is not the
normal job of a compiler. However, to say that the compiler should flag the
above statement as a syntax error is wrong. The syntax is correct.
 
I changed it to:

HAVING (((Activeprods.Prodid)= & "'" & Forms!frmMinTrhesh_DataEntry.Prod_ID
& "'" & ) AND
((tblInvalid_WhseID.WhseID) Is Null));

I am receiving the following error:

syntax error (missing operator) in query exppression
(((Activeprods.Prodid)= & "'" & Forms!frmMinTrhesh_DataEntry.Prod_ID & "'"
& ) AND
((tblInvalid_WhseID.WhseID) Is Null));

I have tried changing the ""'s about ten times and can't quite seem to get it.

Any ideas?

Klatuu said:
In reality, I have never seen any version of SQL that is "standard". Each
has it's own issues. For the most part, they are all alike, but in some
cases, there is a difference. For example, the * is a wildcard in Jet SQL,
but in SQL Server it is %.

As to the compiler issue. The compiler only checks for syntactical
problems. To the compiler, the incorrect line of code is correctly
structured. But, since you are dealing with Null, it will fail at run time.
--
Dave Hargis, Microsoft Access MVP


raylopez99 said:
It has been a little while since I have worked in Access, but I think this
is doable. Any assistance would be greatly appreciated. I have been
struggling for a couple of hours now.
Click to expand...


One thing about Access SQL I've found is that it has funny syntax that
seems to be at odds with the 'standard' SQL--in things like
Parenthesis "(" and the ' apostrope'. Play around with the SQL view
of the Query menu. You may be off by a parenthesis.

For example, this simple parametricised query did not work until I put
in a couple of extra parens:

SELECT Count(*) AS MyCount1
FROM AccountStocks
WHERE (((AccountStocks.AcctID)=[1]) AND
((AccountStocks.StockSymbol)=[2]));

Stuff like that.
As for struggling for hours, it's because VB in Access has a terrible
debugger and compiler/interpreter. For example, it allowed the below
to compile when it never should have:


'CORRECT SYNTAX:

If Not IsNull(Me.StockSymbol) Then
Str002G = Me.AcctID.Value
If Not IsNull(Me.AcctID) Then
Str001G = Me.StockSymbol.Value
End If
End If

replaces (WRONG SYNTAX--GIVES OBSCURE RUNTIME ERRORS--WASTED SEVERAL
HOURS YESTERDAY WITH THIS)

If (Me.StockSymbol.Text <> Null And Me.AcctID.Text <> Null ) Then

...

End If
Click to expand...
Click to expand...
 
Hello,

I am getting an error now saying that query cannot be used as record source.

HAVING (((tblInvalid_WhseID.WhseID) Is Null) And ((Activeprods.Prodid) like
'" & Forms!MinThresh_DataEntry.Prod_ID & "'));

I am pulling my hait out over this.

Thanks,
Roger

Klatuu said:
If it is a text field, then change this line:
HAVING (((Activeprods.Prodid)= & me.Prod_ID & ) AND
((tblInvalid_WhseID.WhseID) Is Null));

To:
HAVING (((Activeprods.Prodid)= & "'" & Forms!FormName.Prod_ID & "'" & ) AND
((tblInvalid_WhseID.WhseID) Is Null));

Note the replacement of Me. A query doesn't understand Me. You have to use
the syntax above.


--
Dave Hargis, Microsoft Access MVP


Roger Converse said:
No, it is a text.

Klatuu said:
You shuld not have to use a filter. The row source should do it all.
What is the data type of Prod_ID? As your query is written, it expects it
to be a numeric field.
--
Dave Hargis, Microsoft Access MVP


:

Hello,

Thanks for the response. I have never worked with filters. I am not sure I
know excatly what you are saying.

This is my query in the row source of my list box, which does not work,
because it never finds me.Prod_ID

SELECT Activeprods.Whseid, tblInvalid_WhseID.WhseID
FROM Activeprods LEFT JOIN tblInvalid_WhseID ON Activeprods.Whseid =
tblInvalid_WhseID.WhseID
GROUP BY Activeprods.Prodid, Activeprods.Whseid, tblInvalid_WhseID.WhseID
HAVING (((Activeprods.Prodid)= & me.Prod_ID & ) AND
((tblInvalid_WhseID.WhseID) Is Null));

I created a filter, but I am not sure how to apply it in this scenario.
Would it be possible for you to be a little more specific as to how to do
this? I had to save my filter as a query. It looks as such:

SELECT Activeprods.WhseID
FROM Activeprods
WHERE (((Activeprods.Prodid)=[me].[lbProd_ID]));

I am still at a disconnect as to how these play together.

Thank you,
Roger


:

For your list box row source, create a query on whatever table contains the
product id and the warehouse id and filter it based on the control where you
enter the product id.
--
Dave Hargis, Microsoft Access MVP


:

I have a form that I am trying to create. Within this form, one of the
things you choose is a product ID. Once that is chosen, I would like to have
my listbox refreshed with all of the warehouses that the product is available
in.

I first attempted to create a temp table on the AfterUpdate event of my
ProdID label. I receive an error stating the previous temp table can't be
deleted because it is in use.

I would use the control source in properties, but I don't know how to "pass"
the prodID variable like that.

It has been a little while since I have worked in Access, but I think this
is doable. Any assistance would be greatly appreciated. I have been
struggling for a couple of hours now.

Thanks,
Roger
Click to expand...
Click to expand...
Click to expand...
 
Well, here is the solution that I was able to get to work.

strProdID = Me.Prod_ID.Value

strSQL = "SELECT Activeprods.Whseid FROM Activeprods LEFT JOIN
tblInvalid_WhseID ON" _
& " Activeprods.Whseid = tblInvalid_WhseID.WhseID WHERE
(((tblInvalid_WhseID.WhseID) Is Null)" _
& " AND ((Activeprods.Prodid)=""" &
[Forms]![frmMinThresh_DataEntry]![Prod_ID] & """));"

Me!lbWhseID.RowSourceType = "Table/Query"
Me!lbWhseID.RowSource = strSQL
Me!lbWhseID.Requery

Thanks Everyone!
Roger

Dirk Goldgar said:
As for struggling for hours, it's because VB in Access has a terrible
debugger and compiler/interpreter. For example, it allowed the below
to compile when it never should have:
[...]
(WRONG SYNTAX--GIVES OBSCURE RUNTIME ERRORS--WASTED
SEVERAL HOURS YESTERDAY WITH THIS)

If (Me.StockSymbol.Text <> Null And Me.AcctID.Text <> Null ) Then

...

End If
Click to expand...

You keep posting this complaint, but you are mistaken. There is absolutely
nothing wrong with that syntax. It's the semantics that are wrong: the
Text property is always a string and so can never be Null, and the
documented syntax of comparisons to Null makes it clear that "x <> Null"
will always yield a Null result. You may argue, if you wish, that the
compiler should vet the semantics of your code, though that is not the
normal job of a compiler. However, to say that the compiler should flag the
above statement as a syntax error is wrong. The syntax is correct.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Me!lbWhseID.RowSource = strSQL
Me!lbWhseID.Requery
Click to expand...

You shouldn't need the Requery. Setting the list box's RowSource property
forces it to requery automatically.
 
Back
Top