Cascade Not In List

C

CJ

Good Day Groupies!

I have 2 combos, Category Name and Sub Category Name

Category gets it's data from the Category table, no problem. It has an
AfterUpdate requery for SubCategory.

SubCategory has the following SQL:

SELECT DISTINCT tblSubCategory.strSubCategory, tblInventory.[Category Name]
FROM tblSubCategory LEFT JOIN tblInventory ON tblSubCategory.strSubCategory
= tblInventory.[Sub Category Name]
WHERE (((tblInventory.[Category Name])=[Forms]![frmInventory]![Category
Name]))
ORDER BY tblSubCategory.strSubCategory;

Things are working fine except when I want to add a new SubCategory to an
existing Category.
I have the "standard" not in list code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = strMsg & "Add " & NewData & " to the list?"

If MsgBox(strMsg, vbQuestion + vbYesNo, "Item Not Available") = vbNo
Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblSubCategory", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!strSubCategory = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

It seems to work fine if I add a new Category and new SubCategory.
However, if I have an existing Category and add a new Sub, I can not move
to the next record or I get an "item not in list" error, depending on how
I have the Limit To List set.

Any ideas?
 
D

Dale Fye

What are the fields in tblSubCategory? It would seem to me that you would
need both a Category (or Category_ID) as well as strSubCategory.

Either that, or you would need to add the subCategory value to tblInventory
(based on the SQL query you indicated above.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.
 
C

CJ

Hi Dale:

tblSubCategory just has the field strSubCategory. It is basically just a
lookup table.

I do have the subCategory value in the SQL and in tblInventory

<snip>
FROM tblSubCategory LEFT JOIN tblInventory
ON tblSubCategory.strSubCategory = tblInventory.[Sub Category Name]
<snip>


....I apologize for the two different SubCategory fields names.
I adopted this database and the first developer was untidy......
--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
Dale Fye said:
What are the fields in tblSubCategory? It would seem to me that you would
need both a Category (or Category_ID) as well as strSubCategory.

Either that, or you would need to add the subCategory value to
tblInventory
(based on the SQL query you indicated above.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



CJ said:
Good Day Groupies!

I have 2 combos, Category Name and Sub Category Name

Category gets it's data from the Category table, no problem. It has an
AfterUpdate requery for SubCategory.

SubCategory has the following SQL:

SELECT DISTINCT tblSubCategory.strSubCategory, tblInventory.[Category
Name]
FROM tblSubCategory LEFT JOIN tblInventory ON
tblSubCategory.strSubCategory
= tblInventory.[Sub Category Name]
WHERE (((tblInventory.[Category Name])=[Forms]![frmInventory]![Category
Name]))
ORDER BY tblSubCategory.strSubCategory;

Things are working fine except when I want to add a new SubCategory to an
existing Category.
I have the "standard" not in list code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = strMsg & "Add " & NewData & " to the list?"

If MsgBox(strMsg, vbQuestion + vbYesNo, "Item Not Available") = vbNo
Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblSubCategory", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!strSubCategory = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

It seems to work fine if I add a new Category and new SubCategory.
However, if I have an existing Category and add a new Sub, I can not move
to the next record or I get an "item not in list" error, depending on how
I have the Limit To List set.

Any ideas?
Click to expand...
Click to expand...
 
D

Dale Fye

Understood, but you added the new subCategory value to tbl_Sub_Category, but
I don't see any code where you add it to tblInventory. If that value is not
in tbl_Inventory, then because of the LEFT JOIN, the tblInventory.[Category
Name] will be NULL, and will not match the value in the first combo box.

You are going to have to add both the category (from the first combo) and
the subCategory (from the Not In List event) in tblInventory, as well as in
tblSubCategory.

HTH
Dale

CJ said:
Hi Dale:

tblSubCategory just has the field strSubCategory. It is basically just a
lookup table.

I do have the subCategory value in the SQL and in tblInventory

<snip>
FROM tblSubCategory LEFT JOIN tblInventory
ON tblSubCategory.strSubCategory = tblInventory.[Sub Category Name]
<snip>


...I apologize for the two different SubCategory fields names.
I adopted this database and the first developer was untidy......
--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
Dale Fye said:
What are the fields in tblSubCategory? It would seem to me that you
would
need both a Category (or Category_ID) as well as strSubCategory.

Either that, or you would need to add the subCategory value to
tblInventory
(based on the SQL query you indicated above.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



CJ said:
Good Day Groupies!

I have 2 combos, Category Name and Sub Category Name

Category gets it's data from the Category table, no problem. It has an
AfterUpdate requery for SubCategory.

SubCategory has the following SQL:

SELECT DISTINCT tblSubCategory.strSubCategory, tblInventory.[Category
Name]
FROM tblSubCategory LEFT JOIN tblInventory ON
tblSubCategory.strSubCategory
= tblInventory.[Sub Category Name]
WHERE (((tblInventory.[Category Name])=[Forms]![frmInventory]![Category
Name]))
ORDER BY tblSubCategory.strSubCategory;

Things are working fine except when I want to add a new SubCategory to
an
existing Category.
I have the "standard" not in list code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = strMsg & "Add " & NewData & " to the list?"

If MsgBox(strMsg, vbQuestion + vbYesNo, "Item Not Available") = vbNo
Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblSubCategory", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!strSubCategory = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

It seems to work fine if I add a new Category and new SubCategory.
However, if I have an existing Category and add a new Sub, I can not
move
to the next record or I get an "item not in list" error, depending on
how
I have the Limit To List set.

Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
D

Dale Fye

Sure,

What columns are in your tblInventory?

--
Dale

email address is invalid
Please reply to newsgroup only.



CJ said:
OK, I understand.

Would you be able to help me out with the code? I thought I had
looked after the Left Join requirements by adding the item via
the Not In List code?

I don't know what else I need to do?
--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
Dale Fye said:
Understood, but you added the new subCategory value to tbl_Sub_Category,
but I don't see any code where you add it to tblInventory. If that value
is not in tbl_Inventory, then because of the LEFT JOIN, the
tblInventory.[Category Name] will be NULL, and will not match the value in
the first combo box.

You are going to have to add both the category (from the first combo) and
the subCategory (from the Not In List event) in tblInventory, as well as
in tblSubCategory.

HTH
Dale

CJ said:
Hi Dale:

tblSubCategory just has the field strSubCategory. It is basically just a
lookup table.

I do have the subCategory value in the SQL and in tblInventory

<snip>
FROM tblSubCategory LEFT JOIN tblInventory
ON tblSubCategory.strSubCategory = tblInventory.[Sub Category Name]
<snip>


...I apologize for the two different SubCategory fields names.
I adopted this database and the first developer was untidy......
--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
What are the fields in tblSubCategory? It would seem to me that you
would
need both a Category (or Category_ID) as well as strSubCategory.

Either that, or you would need to add the subCategory value to
tblInventory
(based on the SQL query you indicated above.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

Good Day Groupies!

I have 2 combos, Category Name and Sub Category Name

Category gets it's data from the Category table, no problem. It has an
AfterUpdate requery for SubCategory.

SubCategory has the following SQL:

SELECT DISTINCT tblSubCategory.strSubCategory, tblInventory.[Category
Name]
FROM tblSubCategory LEFT JOIN tblInventory ON
tblSubCategory.strSubCategory
= tblInventory.[Sub Category Name]
WHERE (((tblInventory.[Category Name])=[Forms]![frmInventory]![Category
Name]))
ORDER BY tblSubCategory.strSubCategory;

Things are working fine except when I want to add a new SubCategory to
an
existing Category.
I have the "standard" not in list code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = strMsg & "Add " & NewData & " to the list?"

If MsgBox(strMsg, vbQuestion + vbYesNo, "Item Not Available") =
vbNo
Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblSubCategory", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!strSubCategory = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

It seems to work fine if I add a new Category and new SubCategory.
However, if I have an existing Category and add a new Sub, I can not
move
to the next record or I get an "item not in list" error, depending on
how
I have the Limit To List set.

Any ideas?
Click to expand...
Click to expand...
Click to expand...
 
D

Dale Fye

CJ,

Is the form you are working on the main form for entering or editing
tblInventory?

The problem with this configuration is that you cannot add the
category/sub_category to tblInventory, because that is the record you are
working on.

I would normally have a table (tbl_Categories), which contains fields:
Cat_ID, Parent_Cat, Cat_Name. Then, my Inventory table would have fields for
Cat_ID and Sub_Cat_ID. What this would give me the ability to do is add a
new category or sub_category to tblCategory, and use that table as the Row
Source for your two combo boxes that whose control source would be Cat_ID and
Sub_Cat_ID from tblInventory.

With this configuration, your query for the subCategory combo box would look
like:

SELECT Cat_ID, Cat_Name
FROM tbl_Categories
WHERE Parent_ID = Forms!yourFormName.cbo_Cat_ID

Without too many changes to your database, I think you should modify the
structure of tbl_Sub_Category so that it contains both the category and
subCategory values. Then, modify your code so that it saves the category
value as well as the subCategory value in tblSubCategories.
Then, modify the queries for the combo boxes so that they reference
tblSubCategories (similar to my query above).

Dale
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



CJ said:
tblInventory has

SKU Number (PK)
Year
Make
Model
Category Name (text)
Sub Category Name (text)
among other fields........

--
Thanks Dale!!
CJ

Dale Fye said:
Sure,

What columns are in your tblInventory?

--
Dale

email address is invalid
Please reply to newsgroup only.



CJ said:
OK, I understand.

Would you be able to help me out with the code? I thought I had
looked after the Left Join requirements by adding the item via
the Not In List code?

I don't know what else I need to do?
--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
Understood, but you added the new subCategory value to
tbl_Sub_Category,
but I don't see any code where you add it to tblInventory. If that
value
is not in tbl_Inventory, then because of the LEFT JOIN, the
tblInventory.[Category Name] will be NULL, and will not match the value
in
the first combo box.

You are going to have to add both the category (from the first combo)
and
the subCategory (from the Not In List event) in tblInventory, as well
as
in tblSubCategory.

HTH
Dale

Hi Dale:

tblSubCategory just has the field strSubCategory. It is basically just
a
lookup table.

I do have the subCategory value in the SQL and in tblInventory

<snip>
FROM tblSubCategory LEFT JOIN tblInventory
ON tblSubCategory.strSubCategory = tblInventory.[Sub Category Name]
<snip>


...I apologize for the two different SubCategory fields names.
I adopted this database and the first developer was untidy......
--
Thanks for taking the time!

CJ
---------------------------------------------------------
Know thyself, know thy limits....know thy newsgroups!
What are the fields in tblSubCategory? It would seem to me that you
would
need both a Category (or Category_ID) as well as strSubCategory.

Either that, or you would need to add the subCategory value to
tblInventory
(based on the SQL query you indicated above.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



:

Good Day Groupies!

I have 2 combos, Category Name and Sub Category Name

Category gets it's data from the Category table, no problem. It has
an
AfterUpdate requery for SubCategory.

SubCategory has the following SQL:

SELECT DISTINCT tblSubCategory.strSubCategory,
tblInventory.[Category
Name]
FROM tblSubCategory LEFT JOIN tblInventory ON
tblSubCategory.strSubCategory
= tblInventory.[Sub Category Name]
WHERE (((tblInventory.[Category
Name])=[Forms]![frmInventory]![Category
Name]))
ORDER BY tblSubCategory.strSubCategory;

Things are working fine except when I want to add a new SubCategory
to
an
existing Category.
I have the "standard" not in list code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strMsg As String

strMsg = strMsg & "Add " & NewData & " to the list?"

If MsgBox(strMsg, vbQuestion + vbYesNo, "Item Not Available") =
vbNo
Then
Response = acDataErrContinue
Else
Set db = CurrentDb
Set rs = db.OpenRecordset("tblSubCategory", dbOpenDynaset)
On Error Resume Next
rs.AddNew
rs!strSubCategory = NewData
rs.Update

If Err Then
MsgBox "An error occurred. Please try again."
Response = acDataErrContinue
Else
Response = acDataErrAdded
End If

End If

It seems to work fine if I add a new Category and new SubCategory.
However, if I have an existing Category and add a new Sub, I can not
move
to the next record or I get an "item not in list" error, depending
on
how
I have the Limit To List set.

Any ideas?
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

NotInList not firing ? 2
Not In List 2 values 5
Help With Code Please 5
Not in list requery issue 4
Not in list not working 1
Category and Subcategory 4
Implementing public VBA function in Access 2003, passing multiple parameters 5
Not in list warning 2

Top