Search Field (1 Viewer)

[chuckster]

Instead of using a find button can I have a text box on a form that when you type in a value it automatically takes you to the record with that value.

The field I want to do this for is the Primary key therefore no duplicates.

Thanks

 

[Jack Cowley]

Create a combo box on the form using the Wizard. On the first screen select the third item, "Find a record....". When the user select or types in the value that they want the appropriate record will be displayed on the form.

 

[chuckster]

Cheers m8.

 

[jabrum]

Does this have to be a combo box? Can it not be a true text box?

 

[Jack Cowley]

A text box does not have the same 'abilities' as the combo box. You could create a 'user defined' function to do what the combo box does already I suppose...

 

[jabrum]

I'm on a mission to find out the following:

On a form, if the patientID is entered (primary key) and the record already exists, instead of getting the standard MS ACCESS error message about not being able to create duplicate records... have a message bx appear stating that a record currently exists with the ID number, and then on click of OK, display that record.

I know this question or one similar is often brought up, I've researched the archives, but haven't found what I understand to be a solution.

Thank you for your help

 

[Jack Cowley]

This is a very quick and crude bit of code that does, I think, what you want;

If Me.NewRecord Then
If Not IsNull(DLookup("PrimaryID", "TableName", "[PrimaryID] = " & Me.PrimaryID)) Then
MsgBox "This ID already exists"

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[PrimaryID] = " & Me![PrimaryID]
Me.Undo
Me.Bookmark = rs.Bookmark

End If
End If

Forgot to tell you that this code would go in the After Update event of of the Primary key field....
[This message has been edited by Jack Cowley (edited 06-02-2001).]

[This message has been edited by Jack Cowley (edited 06-02-2001).]

 

[jabrum]

Jack,

I appreciate your reply. The code had a compile error. I'm guessing that part of the problem lies in the fact that my primary key is named "MRN Number" It was only after I had completely built the db did I learn that it wasn't a good idea to have spaces in your field names. At any rate, the primary key is "MRN Number" and resides in the "Demographics" table. The intended search field on the form is called "MRN Number" as well.

My guess is, that becuase I've included a space in the field name, the code needs to be modified. Any further help would be appreciated.

Thank you

[This message has been edited by jabrum (edited 06-02-2001).]

 

[chuckster]

I'm glad to see that my question generated some interest. Would using an underscore _ help.
e.g.
MRN_Number.

 

[jabrum]

Chuckster,

Not sure if that is a statement or a question. Either way, I wouldn't have a clue.

 

[Jack Cowley]

I guess that I would need to see your code to see if I can figure out what is wrong. If you get a compile error VB usually stops on the line that has the error in it. That should give you a clue as to what the trouble may be. And Chuckster is suggesting the you put the underscore where the space is between MRN and Number, i.e. MRN_Number. If MRN Number is enclosed in quotes ("MRN Number") or square brackets ([MRN Number]) the space should not be the problem....


[This message has been edited by Jack Cowley (edited 06-03-2001).]

 

[jabrum]

Jack,

Maybe a key piece if information as well. The search field is on a child form called "Filtered Data". The parent form is called "Demographics1".

here is the code so far,

Private Sub MRN_Number_AfterUpdate()
If Me.NewRecord Then
If Not IsNull(DLookup("MRN_Number", "Demographics", "MRN_Number = " & Me.MRN_Number)) Then
MsgBox "A record already exists with this ID"
Dim rs As Object
Set rs = Me.RecordsetClone
rs.FindFirst "MRN_Number = " & Me.MRN_Number
Me.Undo
Me.Bookmark = rs.Bookmark
End If
End If

It's hanging on the "Me.Bookmark" line

hope this helps
again, your help is greatly appreciated

[This message has been edited by jabrum (edited 06-04-2001).]

[This message has been edited by jabrum (edited 06-04-2001).]

 

[jabrum]

Jack,

Just reviving this thread to see if maybe I could get this resolved. Any further assistance in this would be greatly appreciated.

Thank you again.
Jon

 

[MarionD]

Have sent you an Example DB - hope it helps a bit

Marion

 

[cargobay 69]

Here, try this:

http://www.geocities.com/airwalker08/Search4Stuff.htm


jabrum,
I know how to do the check that you are looking for. I have built a database that does just that but it will take some time to make a small working example like the one at the above link. Check back later, maybe I'll get one together.

CB69 - DH

[This message has been edited by cargobay 69 (edited 06-07-2001).]