Look up records or add new

[waldo7474]

I had learned Access a couple years ago, told my boss that I could do something with one of the databases that we have at work. I am having trouble remembering some of the things that I have done.

I am trying to do something that I thought would be simple. I want to have a user type in a part number, I then want the database to search for this part number. If it finds the part number I would like it to display the information based on that part number inside the form that I am using. If it doesn't find the record, I want it to create a new record based.

This seems pretty basic to me, but I can't find any examples that actually talk about it. If anyone could point me in the right direction it would be greatly appreciated.

 

[annsolomon]

I have a text box on a work order form where the user can enter a PO # to search on. I display a message if the PO isn't found but you could open the form in add mode instead. Here's the code behind the After Update event on my test box:

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[OrderNumber] = """ & [Text173] & """"

If rs.NoMatch Then
MsgBox "This PO # isn't in the database", vbOKOnly, "JSJ, Inc."
Text173 = [OrderNumber] 'Update the Data Entry PO # text box

End If

Me.Bookmark = rs.Bookmark

 

[waldo7474]

Thanks for your quick response Ann.

I have found that I need to put this ext box in my header. Correct? Once I have done this, I still get an error.

If I look for a PartNumber that is already there, it comes up with the error immediately. When it is a new PartNumber, I get your message box, after I click ok I get the error.

This is the error:
Run-time error '2465'
Microsoft Access can't find the field 'forms' referred to in your expression.

When I run the debugger, it highlights the line:
Me.Bookmark = Rs.Bookmark

What do I do? I appreciate any help. So far it is closer than I have been.

Thanks again!!

 

[annsolomon]

I'm assuming you replaced my field names with your own. Can you post your code here? Also, check to see that you have referenced the Microsoft DAO 3.6 Object Library.

Ann

 

[waldo7474]

That library was not checked. It works now. So thank you for all your help Ann.
Wally

 

[annsolomon]

I'm glad it worked out for you.
Ann