filter on two fields

[medic133]

I have a form that users can input instructor information such as first name, last name, address, etc. The first two fields that are entered are the lastname and firstname. After entering data in the firstname field I would like to filter the information based on the two fields to display data in the form if a record matching the two fields has already been entered. If there is no such record, I would like the user to be able to continue to enter new data as if no filter was run. Can I accomplish this? I've tried using the ApplyFilter method and Me.Filter with no success. Since this form is based on a table rather than a query, I don't see how I can use a query to display the data. This is the first time I've tried using filters and I think I need some major help. Thanks in advance!

 

[KenReay]

Hi

What you are trying to achive is not intrinsicly difficult, I am a little puzzled as to how you are doing this, is your form bound?, are teh two name fields bound?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[medic133]

The form is bound to a table and the two fields on the form are bound to two fields within the table.

 

[KenReay]

Hi

I suspected as much.

Surely if the two 'search' fields are bound when you type into them it updates the data within the table?

Basically you need two unbound fields and a button to instigate a search which results in finding and displaying an existing record or creating a new one (presumably after asking for confirmation, to allow for typos).

Another way to do this is to use a combo box (or boxes) to act as a seach or look up mechanism, you can achive the new record bit by having a <New> option in the drop down list.

How much knowledge do you have an how much explanation do you need?

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[medic133]

Thanks for sticking with me! Yes, when you enter data into the fields the table is updated. Again, my goal is to fill in the rest of the form with data from the table after the two name fields are updated if there is a matching record. Otherwise, I would like for the user to be able to continue to the third field to enter data since the lack of records matching the first two fields indicates this is actually a new record. Maybe that's my problem. I want the db to understand that, by no record matching the &quot;criteria&quot; in the first two fields, this is a new record. If there is a record matching the criteria, then display the data in the existing form. Perhaps you can't do that?
Regarding how much explanation I need, well, probably lots. I'm fairly comfortable writing code if I understand what functions to use. Any further help would be greatly appreciated!! Thanks!!

 

[medic133]

I think I got it!! I got this from another thread. This is my code:

'Apply filter to determine if record already exists.
docmd.applyfilter , &quot;fieldname1=[forms]![formname]![fieldname1] And fieldname2=[forms]![formname]![fieldname2]&quot;

This fills in the rest of the form with the information if the record already exists and allows the user to continue on as if nothing happened if there is no matching record. It also allows the user to edit the existing record if they so desire.

This did, however, create one problem. It places a record into the table to which the form is bound with only the first two fields containing data. This was a useless record so I labeled the record by placing an unbound textbox on the form which is populated by the following code using a select query to check and see if a matching record exists in the first place. Then, if the unbound textbox is populated, I run a delete query which contains certain criteria that the bogus record will have. All in all, in the afterupdate function of fieldname2, I have the following code:

'Run select query to see if matching record exists.
Dim varX as Integer
Docmd.openquery &quot;selectqueryname&quot;
varX=Nz(DCount(&quot;selectqueryfieldname&quot;,&quot;selectqueryname&quot,0)
If varX <> 0 Then
Unboundtextboxname=1
End If
Docmd.Close acQuery, &quot;selectqueryname&quot;,acSaveNo

'Apply filter.
Docmd.ApplyFilter , &quot;{code as above}&quot;
'Move focus to next field in tab order due to
'focus remaining in fieldname2.
fieldname3.Setfocus

'Run delete query if unboundtextboxname=1.
If unboundtextboxname=1 Then
DoCmd.OpenQuery &quot;deletequeryname&quot;
Docmd.close acQuery, &quot;deletequeryname&quot;, acSaveYes
End If

It all actually works. My only question with this now lies with the confirmation following running the delete query. Access prompts the user as to whether to delete the records twice. I know I can turn this feature off through Tools/Options/Edit/Confirmation on the menu bar, but can I turn it on and off with code? I believe this feature must be set on each computer and multiple computers will use this db. (Because of my ignorance, I don't know if the db will be placed on a server or what, if that matters)

Thanks for all of your help and I hope you might have a solution for my last problem!!

 

[KenReay]

Hi

To answer the last part first

See the before delete confirm event

Response = acDataErrContinue

will suppress the standard are you sure dialog

Now to the rest - pleased you have a solution which works - you could also consider using an unbound combo box with a drop down list of names, I have a FAQ on this in the forms forum under Combo Boxes - Using a combo box to navigate

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK