Value in text box searched in table

[Fonzie]

I have a workorder form with a 'part number' text box. What I need to happen is if the part number entered is not in the inventory table, the inventory form is opened so the user can enter that information into the table. If possible I would like not to use a combo box or list box. Any ideas anyone?

 

[Katerine]

There are two possibilities that come immediately to mind (both of which involve coding)

It would probably be easier for the user to have the Part Number in a combo (drop-down) box, rather than a text box. Have it look up 'part number' in the inventory table (and make sure the bound column property is set to the right column). Set the "Limit To List" property of the combo box to "True".

Then, have the following code in your combo box's "NotInList" event:

--- Begin Code ---
if msgbox (etc etc) = vbYes Then
docmd.openform formname,,,,,acDialog,NewData

Response=acDataErrAdded
combo box.Requery
end if
--- End Code ---

In the "Add Part Number" form, put the following code in the form's "Open" event:

--- Begin Code ---
partnumber textbox.Value = Me.OpenArgs
--- End Code ---

This should work for a starting point.. it may need a little tweaking.

OR

If you really want to keep the textbox control, instead of a combo box, you could have the textbox's "BeforeUpdate" event try to find the part number in the inventory table each time the textbox is updated, and display the prompt if it's not found. Please let me know if you'd like me to type out this option as well.

Please let me know if you need any clarification on anything

 

[Fonzie]

Thank You Katerine

The second option you listed sounds like what I am looking for. However, I am not so great with code yet, if you could please type that out, that would be great.

Thanks again

 

[Katerine]

OK

--- Begin Code ---
txtPartNumber_BeforeUpdate (Cancel As Integer)

dim dbs as database, rstInventory as recordset
dim strCriteria as string

' Note: this assumes the Inventory Table is
' in the same database, and it's called "tblInventory"
set dbs=currentdb
set rstInventory = dbs.OpenRecordset ("tblInventory", dbOpenDynaset)

if not isnull (txtPartNumber.value) then
' This assumes the Part Number field in the
' Inventory table is called "Part Number"
strCriteria = "[Part Number] = " & txtPartNumber.value

rstInventory.findfirst strCriteria
' see if the part number entered is not found
if rstInventory.NoMatch then
if msgbox ("Part number does not exist in " & _
"Inventory Table. Add?",vbYesNo) = vbYes then

open form.. I've got to go, so I'm rushing this..sorry..
end if
end if

.... (error handling)
End sub
--- End Code ---

Again, hope that helps! If you run into any problems, please let me know.

 

[Fonzie]

Tried the code, but coming up with errors. Are there any variables listed above that I need to change? Like I said before, I'm not very good with the code.

 

[Katerine]

Hi Fonzie,
Sorry about the errors.. as I said, I made a large number of assumptions when I wrote that post, so it would need to be modified for your use.

Here is the completed code, now that I'm not in a rush.. This code assumes that:
- The inventory table is in the same database as the form
- The inventory table is called "tblInventory"
- The Part Number textbox is called "txtPartNumber"
- The Part Number field in the inventory table is called "Part Number"
- The "Add Part Number to Inventory" form is called "frmInventoryAdd"

--- Begin Code ---
txtPartNumber_BeforeUpdate (Cancel As Integer)
on error goto Err_txtPartNumber_BeforeUpdate

dim dbs as database, rstInventory as recordset
dim strCriteria as string

set dbs=currentdb
set rstInventory = dbs.OpenRecordset ("tblInventory", dbOpenDynaset)

if not isnull (txtPartNumber.value) then
strCriteria = "[Part Number] = " & txtPartNumber.value

rstInventory.findfirst strCriteria
' see if the part number entered is not found
if rstInventory.NoMatch then
if msgbox ("Part number does not exist in " & _
"Inventory Table. Add?",vbYesNo) = vbYes then
docmd.openform "frmInventoryAdd", , , , , _
acDialog, txtPartNumber.Value
end if
end if

Exit_txtPartNumber_BeforeUpdate:
rstInventory.close
set dbs=nothing
Exit Sub

Err_txtPartNumber_BeforeUpdate:
msgbox Err.Number,,"txtPartNumber_BeforeUpdate: " & _
err.Description
resume exit_txtPartNumber_BeforeUpdate
End sub
--- End Code ---

If, after you've made any modifications necessary to use with your database, you're still running into errors with this code, please let me know:
- Whether the program compiled ok (I typed it in this post, not in Access, so it may have some typos)
- If it's a run-time error, what does the error message say
- What is the error number (set to display in the error message caption in run-time errors)
- What line does it seems to be having trouble with
.. and I'll try to figure it out!

 

[Fonzie]

I entered your code and made the necessary changes. When I enter a part number, whether it is in the inventory table or not, I get the following error:

Compile error:
User-defined type not defined.

and 'dbs As database' is high-lighted.

 

[Katerine]

Ohhhh..

Ok. I think I see the problem. When I wrote the code above, I was assuming that you were working from within Access 97. Were you, instead, coding in some other program (besides Access), such as Word?

If that is the case, then you need to do the following to get the code to work:

In the code window, select "References" from the "Tools" menu. Make sure "Microsoft DAO 3.51 Object Library" is checked (it isn't by default in non-Access programs).

Make the following changes to the code:
- change "rstInventory as recordset" to "rstInventory as DAO.recordset"
- change "set dbs=currentdb" to "set dbs = OpenDatabase(Name:="C:\MSOffice\Access\Samples\Northwind.mdb&quot
(change the database name and path to the database with the inventory table).

Another possibility is that you're working in Access 2000, and it's not a valid data type in Access 2000? I really don't know.. I've never worked with 2000.

Please let me know if I'm totally off-base here.. that's the only explanation I can think of for it not recognizing "database" as a valid data type. Unless that library somehow got unchecked in Access..

 

[Fonzie]

Yes, I am working with Access 2000.

 

[Katerine]

Ok. I apologize for all the trouble.. I've never even seen Access 2000, either at home or at work, so I can't really comment on what the error might be. Or even if there's an easier way in Access 2000, without going into code at all.

Anybody? :-(