Preventing New Record When Searched Item Is Not Found -Ms Access

K

Kassimu

Joined
May 24, 2008
Messages
6
Reaction score
0
I have an unbound form "SearchForm" for search purposes with one text box "txtSearch" and one command button. This form is used to search the "Products" table and if search item is found it is displayed in the "ProductsParticulars" form which is bound to the Products table. Ususally the user fills in the product ID in the text box on SearchForm and then click the search button. I am using the following code for the search button.

Dim strSearch As String
strSearch = "([ID] = """& Me.txtSearch & """)"
DoCmd.OpenForm "ProductsParticulars"
Forms!ProductsParticulars.Filter = strSearch
Forms!ProductsParticulars.FilterOn = True

The thing is; this code works fine as long as the searched item is found, if no product with the matching ID, the form opens in add record mode. I do not like this because it adds empty records in my database.

I wan a code which will prevent addition of new records when the searched item is not found, that is whenever access fails to get the searched record error message should be displayed. Anyone with the helping hand? Your assistance will be highly appreciated
 
I am new to tinkering with Access. Far from an expert. What your code does is exactly what I want to do. I am in the process of building a database in my office. We have soldiers making deposits into a savings program. I have a table of every soldier who has made at least one deposit. This table has their SSN, Last Name, First Name for it's fields. I named it Accounts. Another table lists all deposits made by all soldiers. It's fields are SSN, Last Name, First Name, Rank, Section (department), Date of Deposit, Amount of Deposit. I named it Deposits. I inserted Deposits as a subdatasheet of Accounts. I can expand each record in Accounts to find all deposits by that SSN. I made a form for users to do the same thing. The form is mainly based on the Accounts table and has a subform based on the Deposits table. I am wanting for my users to be able to do a search, by SSN, of the main form. If the SSN is found, I want them to be able to enter a new deposit for that SSN. If it is not found, that means they have a soldier making their first deposit. In that case, I want my users to be able to add the soldier as a new record to the Accounts table, and enter a new deposit for that soldier. I am asked for statistics on this program every week. So, if my users mess up and add empty records to my tables, I will delete those empty records on a weekly basis. Just perfect for me. So, thank you for posting your question and your code. It will help me out.
 
Back
Top