using find in subform

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

Guest

Hi,
I'm using access2k3 and trying to develop inventory database. Here is the
problem; I have subform (supply requests subform) which includes combo box
(productID) that has product list (about 500 items), this list is generated
from products table. this all works just fine and user is able to pick from
the list and select product. What I would like to do is use "On Not in List"
and generate product listing which partialy contains what the user had typed
in combo box, something like when using query with " Like *productname* ". If
possible when user types for example beer and it is not on list, multiple
matches would come up ( bud light beer, busch beer, beer in keg) , which he
would be able to select one from that list if possible. If unable to do
selection at least I would like to be able to show those products.
Thank you in advance
 
Try opening a popup form in the NotInList event that has a list box
that gets its value from the combo box. Set the Row Source using an
SQL statement. Something like:

SELECT * FROM tblProducts WHERE [ProductName] Like "* " &
Forms!MyForm!MySubform.Form!MyCombo & " *"

Once you select the new value from your list box undo the value in
your combo box and insert your new value.

Hope this helps.
 
I have tried this but it is just not working. Once I have
implemented the code, combo box comes up blank.... I
really sure what is the problem. Here is the lowdown
Tables: Products, Inventory Transactions & Form1 (one with
combo box)
Forms: Supply Requests , subform Supply Requests Subform
source for subform is Inventory transactions. Field in
subform is called ProductID. It is modified version of
Microsoft supply database. I would be happy to send you a
copy to take a look at it.
Thank you
 
Her is the code in Supply Requests Subform
Private Sub ProductID_NotInList(NewData As String,
Response As Integer)
DoCmd.OpenForm "Form1"
End Sub

here is the code on combo box (Product) in form 1 in row
source:
Select * FROM Products WHERE [ProductName] Like "* " &
[forms]![supply requests]![supply requests subform].Form!
ProductID & " *"
Hope That helps
 
I need more info, is your fields in the Products table arranged like:

Field Name Data Type
ProductID Number or Text
ProductName Text
Quantity Number
Price Currency
etc...

If you enter a ProductID which is not found in the database then the
NotInList can be used. Your question may need a search form instead.
Something to query on different fields.
 
In Products table:
ProductID Autonumber
ProductName Text
UnitOfIssue Text
etc.
In Inventory Transactions table (this is source for the
subform)
TransactionID Autonumber
TransactionDate Date
ProductID Number (linked to Products table by:
SELECT DISTINCTROW Products.* FROM Products ORDER BY
Products.ProductName; I'm hiding first column ProductsID
Column and just showing ProductName second column)
UnitsRequested Number
LogNumber Number
etc.
The Supply Request form Record Source is from Supply
Requests table with following fields:
LogNumber Autonumber
Department Text
OfficeLocation Text
etc.
I'm linking form (Supply Requests) and subfrom (Supply
Requests Subform) via LogNumber, that works fine.
In subform which Record Source is Inventory Transactions
there are following fields:
TransactionDate txtbox
ProductID combo with following statement for row
source: SELECT DISTINCTROW Products.ProductID,
Products.ProductName, Products.UnitOfIssue AS [Unit Of
Issue], Products.Enable FROM Products WHERE
(((Products.Enable)=True)) ORDER BY Products.ProductName;
There are 3 columns but I'm showing only two (ProductName
and UnitOfIssue) the first column ProductID is set to 0"
width.
UnitsRequested txtbox
On Form1 I just have unbound combo with previously posted
code.
Hopefully you will figure our what is going on. Thank you
in advance.
 
Use this code in your first combo box:

Private Sub ProductID_NotInList(NewData As String, Response As
Integer)
On Error Resume Next
Dim NewCategory As Integer, SSQL as String
Beep
NewCategory = MsgBox("Product not found. Do you wish to search?",
36, "Data Not Found")
If NewCategory = 6 Then
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.OpenForm "form1"
SSQL = "Select * From tblProducts Where [ProductName] = *" &
NewData & "*;"
Forms![form1]![Product].RowSource = SSQL
Response = acDataErrContinue
Else
Response = acDataErrContinue
Me!ProductID = Null
Me!ProductID.SetFocus
MsgBox "Please select a product from the list.", , "Select
Product"
End If

End Sub

The undo line will vary with your version of Access.
In form1 I suggest using a list box instead of a combo box. This way
you can double click your new entry, while viewing the entire list.
 
OK, I have inserted this code and still not displaying
anything in Form1's listbox. I have changed below
statement from tblProducts to Products since that is the
name my table. Also the listbox in Form1 is called
Product but what do I use for the row source??? i tried
using this statement;
Select * FROM Products WHERE [ProductName] Like "* " &
[forms]![supply requests]![supply requests subform].Form!
ProductID & " *"
And still not displaying anything in listbox. I guess I'm
just too stupid to figure this out myself...
-----Original Message-----
Use this code in your first combo box:

Private Sub ProductID_NotInList(NewData As String, Response As
Integer)
On Error Resume Next
Dim NewCategory As Integer, SSQL as String
Beep
NewCategory = MsgBox("Product not found. Do you wish to search?",
36, "Data Not Found")
If NewCategory = 6 Then
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.OpenForm "form1"
SSQL = "Select * From tblProducts Where [ProductName] = *" &
NewData & "*;"
Forms![form1]![Product].RowSource = SSQL
Response = acDataErrContinue
Else
Response = acDataErrContinue
Me!ProductID = Null
Me!ProductID.SetFocus
MsgBox "Please select a product from the list.", , "Select
Product"
End If

End Sub

The undo line will vary with your version of Access.
In form1 I suggest using a list box instead of a combo box. This way
you can double click your new entry, while viewing the entire list.


In Products table:
ProductID Autonumber
ProductName Text
UnitOfIssue Text
etc.
In Inventory Transactions table (this is source for the
subform)
TransactionID Autonumber
TransactionDate Date
ProductID Number (linked to Products table by:
SELECT DISTINCTROW Products.* FROM Products ORDER BY
Products.ProductName; I'm hiding first column ProductsID
Column and just showing ProductName second column)
UnitsRequested Number
LogNumber Number
etc.
The Supply Request form Record Source is from Supply
Requests table with following fields:
LogNumber Autonumber
Department Text
OfficeLocation Text
etc.
I'm linking form (Supply Requests) and subfrom (Supply
Requests Subform) via LogNumber, that works fine.
In subform which Record Source is Inventory Transactions
there are following fields:
TransactionDate txtbox
ProductID combo with following statement for row
source: SELECT DISTINCTROW Products.ProductID,
Products.ProductName, Products.UnitOfIssue AS [Unit Of
Issue], Products.Enable FROM Products WHERE
(((Products.Enable)=True)) ORDER BY Products.ProductName;
There are 3 columns but I'm showing only two (ProductName
and UnitOfIssue) the first column ProductID is set to 0"
width.
UnitsRequested txtbox
On Form1 I just have unbound combo with previously posted
code.
Hopefully you will figure our what is going on. Thank you
in advance.
Click to expand...

.
Click to expand...
 
Take out the space in "* " and " *"

OK, I have inserted this code and still not displaying
anything in Form1's listbox. I have changed below
statement from tblProducts to Products since that is the
name my table. Also the listbox in Form1 is called
Product but what do I use for the row source??? i tried
using this statement;
Select * FROM Products WHERE [ProductName] Like "* " &
[forms]![supply requests]![supply requests subform].Form!
ProductID & " *"
And still not displaying anything in listbox. I guess I'm
just too stupid to figure this out myself...
-----Original Message-----
Use this code in your first combo box:

Private Sub ProductID_NotInList(NewData As String, Response As
Integer)
On Error Resume Next
Dim NewCategory As Integer, SSQL as String
Beep
NewCategory = MsgBox("Product not found. Do you wish to search?",
36, "Data Not Found")
If NewCategory = 6 Then
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
DoCmd.OpenForm "form1"
SSQL = "Select * From tblProducts Where [ProductName] = *" &
NewData & "*;"
Forms![form1]![Product].RowSource = SSQL
Response = acDataErrContinue
Else
Response = acDataErrContinue
Me!ProductID = Null
Me!ProductID.SetFocus
MsgBox "Please select a product from the list.", , "Select
Product"
End If

End Sub

The undo line will vary with your version of Access.
In form1 I suggest using a list box instead of a combo box. This way
you can double click your new entry, while viewing the entire list.


In Products table:
ProductID Autonumber
ProductName Text
UnitOfIssue Text
etc.
In Inventory Transactions table (this is source for the
subform)
TransactionID Autonumber
TransactionDate Date
ProductID Number (linked to Products table by:
SELECT DISTINCTROW Products.* FROM Products ORDER BY
Products.ProductName; I'm hiding first column ProductsID
Column and just showing ProductName second column)
UnitsRequested Number
LogNumber Number
etc.
The Supply Request form Record Source is from Supply
Requests table with following fields:
LogNumber Autonumber
Department Text
OfficeLocation Text
etc.
I'm linking form (Supply Requests) and subfrom (Supply
Requests Subform) via LogNumber, that works fine.
In subform which Record Source is Inventory Transactions
there are following fields:
TransactionDate txtbox
ProductID combo with following statement for row
source: SELECT DISTINCTROW Products.ProductID,
Products.ProductName, Products.UnitOfIssue AS [Unit Of
Issue], Products.Enable FROM Products WHERE
(((Products.Enable)=True)) ORDER BY Products.ProductName;
There are 3 columns but I'm showing only two (ProductName
and UnitOfIssue) the first column ProductID is set to 0"
width.
UnitsRequested txtbox
On Form1 I just have unbound combo with previously posted
code.
Hopefully you will figure our what is going on. Thank you
in advance.
Click to expand...

.
Click to expand...
Click to expand...
 
OK, I got it !!!
I couldn't do it the way you just told me for some
reason, but I used this procedure; I created query
(Products Query) with this code;
SELECT Products.ProductID, Products.ProductName,
Products.UnitOfIssue, Products.Enable
FROM Products
WHERE (((Products.ProductName) Like "*" & [forms]!
[Search]![product] & "*"));
based on this I created form called Search I have unbound
txtbox called Product (which will display keyword) and
listbox (displaying possible matches) List5 with row
source looking like this:
SELECT [Products Query].ProductID, [Products
Query].ProductName, [Products Query].UnitOfIssue,
[Products Query].Enable FROM [Products Query] WHERE
((([Products Query].Enable)=True));
listbox also has this code on double-click:
Forms![supply requests]![supply requests subform]!
ProductID = Me!List5.Value
docmd.close
this will insert found value and close form. On Supply
Requests Subform I got this code for ProductID not on
list:
On Error Resume Next
Dim NewCategory As Integer, SSQL As String
Beep
NewCategory = MsgBox("Product not found. Do you wish
to search?", 36, "Data Not Found")
If NewCategory = 6 Then
DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, ,
acMenuVer70
DoCmd.OpenForm "Search"
SSQL = "Select Products.ProductName From Products
Where [ProductName] = *" & NewData & "*;"
Forms![Search]![Product] = NewData
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, ,
acMenuVer70
Response = acDataErrContinue
Else
Response = acDataErrContinue
Me!ProductID = Null
Me!ProductID.SetFocus
MsgBox "Please select a product from the
list.", , "Select Product """
End If
and it is soo cool, I was blow how easy is it to select
product from listing. i just want to thank you for
helping me, as you can see I used bunch of your code just
tweaked it little bit. I was stuck on this for a while
and it feels good to get it working just as I imagined it.
Thank you & talk to you later
 
Back
Top