Search for existing records in a form

[dan_mouse]

I am trying to set up a form for entering data. The first field in the form is the project number. When entering a New Record, I would like the current view to automatically jump to that record if that project number already exists. IF not, it allows you to continue entering the rest of the fields.

I have tried having two forms, one that only allows the entering of the project number, then it opens a new form with the condition of the project number being equal to that of the entered number. This sort of works except that the form that opens will not allow data to be modified. It also creates a new record in the table instead letting you modify the existing one. Ugg.

There MUST be a simple way to do this.

Any help will be greatly appreciated.

P.S. I am not very skilled in the macro language, I usually use the expression builder.

Thanks,
Dan

 

[Elana]

There is an easy way to do this. Create a combobox using the combobox wizard. One of the options the wizard gives you is "Find a record on a form based on the value selected in my combo box."

Follow the steps and you're there.



[This message has been edited by Elana (edited 04-30-2002).]

 

[Jonathan Kok]

OK, Elena's idea will find a record based on what you type, but it will not create a new record if that's what you want to do.

What I would suggest is this:

1st, set your form's "Allow Additions" property to 'No' (you'll see why in a minute). Also, LOCK the 'projectnumber' field. Add a command button that will call up a new form that you'll create below and close the current form at the same time. You can use the command button wizard to call up the new form. Once you've done that, go to the code (by clicking on the '...' next to the 'On Click' event of the button) and add the following line under the 'DoCmd.OpenForm' line:

DoCmd.Close acForm, "yourformname"

You can't create the command button until you've made the new form, so you might want to do that first.

2nd, create a NEW form that will be your Search/Add New form. This will contain only one thing: A combobox containing the list of jobnumbers.
Create the combobox using the wizard to look up the values of your projectnumbers in your table. Set the 'Limit to List' property to 'Yes'.
Under the 'Not In List' event of the combobox, create the following code using the Code Editor by clicking on the '...' next to the 'On Not In List' event for the combobox (this will add a new record to your table if the projectnumber isn't already taken):

Dim db
Dim rs
Dim msg

If NewData = "" Then Exit Sub
msg = "'" & NewData & "'" & " is not in a current Project Number. Do you wish to add?" 'This will prompt your user to create the new record
If MsgBox(msg, vbYesNo) = vbYes Then
Set db = CurrentDb()
Set rs = db.openrecordset("yourtablename")
With rs
.AddNew
.projectnumberfieldname = NewData
.Update
End With
Response = DATA_ERRADDED
Else
MsgBox "Please Try Again"
Response = DATA_ERRCONTINUE
End If

OK, that will create the new record. Now, you want to call up the form and find the record. To do this, you have to create an 'After Update' event for the combobox (again, by clicking on the '...' next to the 'AfterUpdate' event of the combobox). Here's the code:

DoCmd.OpenForm "yourformname" 'This will open the form
[Forms]![form1]![projectnumberfieldname].SetFocus 'This sets focus on the form
DoCmd.FindRecord comboboxname, acEntire, , acSearchAll, , acAll 'This finds the record
DoCmd.Close acForm, "search/addnewformname" 'this closes the search/addnew form

That should work, it did for me. Good luck!

EDIT: anything bolded above you have to change to reflect your database field, form, and table names as indicated. Or, it's a comment, in which case, it has a ' before it.

[This message has been edited by Jonathan Kok (edited 05-01-2002).]

 

[dan_mouse]

First, thank you to Elana and Jonathan Kok.

But I am still having some troubles.
I think that it would work except that I get a "The table is already opened exclusively by another user interface and cannot be manipulated programmatically." error.
This occurs when I try to have the form open another form. Even if I set the command button to close the current form first (this works) it will not open the second form (I get the error message).
I tried to set the database properties to:
Default Open mode = Shared and
Default Record Locking = No locks
in the Advanced Properties, but no luck.
I thought that by setting the first form's properties to Allow Additions = NO would fix this problem, but it did not.

Thanks again for your help so far.

-Dan

 

[dan_mouse]

Thanks for the help, I got it working now.

-Dan