Regarding Handling Combo Box Functionality (1 Viewer)

[pdesh3]

I am developing an application for a hospital which provides Outpatient billing information. I have created appropriate tables required. I have to develop a client server system where from multiple systems differnt people will enter data into the forms relted to the patients. The details of all the patients and their visits are stored in the MSAccess database and retrieved accordingly upon request and reports will be created.
I started creating forms and I have to create a combo box which provides following functionality:

---when you start typing the name, the drop down menu would appear offering options including

<new patient> : selecting this option would take you to the new patient registration form

a list of names in which the information you typed is contained in the last name of all patients in the database

a list of names in which the information you typed is contained in the first names of all patients

a list of names in which the Soundex of the letters you typed matches in the Soundex of either the first or last names


For example, you type in "Smith” and the drop menu looks something like:

<new patient>

Smith, John

Blackwell, Smithson

Smythe, Robert



Selecting on the drop down menu should return the auto number index of that patient in the "patients" table.

How to handle this kind of dynalic information as depending on the user typed text in combo box the patients with the same name if exist in database should be listed.

I am grouping this kind of functionality so that it will be easy to enter new patients data with minimum clicks.

It would be great if you can provide some suggestions on this .

Thank you,
Pdesh3.

 

[neileg]

You're asking a lot of Access. I would start by capturing the user input and then testing it on a button click. With a multiuser set up, dynamic, real time matching, especially based on Soundex matches, is going to be a real perfomance issue.

The native behaviour of a combo is that it will 'zoom' into values as you type, but only for exact matches (ignoring case). I'm pretty sure you're not going to be able to combine that with your other requests, so you'll have to build a function in VBA. Not impossible, but not elementary either.

I think you're going to run out of Access horsepower!

 

[pdesh3]

Regarding Handling Combo Box functionality

Thank you for the suggestion.

Here I made few changes.

I have created a form where one text box and two command buttons named Search and Clear. Under all these fields I am also including a subform which displays all the values stored in Patient table.
When I enter any word in the name text box and click enter then the values in sub form only display the names of the patient whose first name or last name matches with the given name. When button Clear is hit ( in future I am planning to make it cancel button , so that to cancel the operation ) textbox becomes empty.
I have written code for implementing this but finding problems when I hit the search button nothing is done. Can you please look at the code below and tell me the changes.

Option Compare Database
Option Explicit

Private Sub btnClear_Click()


Me.txtName = ""



End Sub

Private Sub btnSearch_Click()

Me.frmSubPatient.Form.RecordSource = "SELECT * FROM qryPatientDetails " & BuildFilter

Me.frmSubPatient.Requery
End Sub


Private Sub Form_Load()


btnClear_Click

End Sub

Private Function BuildFilter() As Variant
Dim varWhere As Variant


varWhere = Null

If Me.txtName > "" Then
varWhere = varWhere & "[FirstName] LIKE """ & Me.txtName & "*"" AND "
End If

If Me.txtName > "" Then
varWhere = varWhere & "[LastName] LIKE """ & Me.txtName & "*"" AND "
End If


If IsNull(varWhere) Then
varWhere = ""
Else
varWhere = "WHERE " & varWhere

' strip off last "AND" in the filter
If Right(varWhere, 5) = " AND " Then
varWhere = Left(varWhere, Len(varWhere) - 5)
End If
End If

BuildFilter = varWhere



End Function

I am also planning to display a pop up message when new patient name is entered in the text box. The pop message like " New patient !! do you want to enter the details?" when OK button is pressed new patient registration form should be displayed. Can we implement this?

Thank you,
Pdesh3

 

[Samrasr]

Help, Help, Help with Memo Box

I have a form which has a MEMO box. I want my users to type in the memo box, each time they type in the box, i want the box to expand/grow as they type or hit return. I have seen this on another database but cant figure the code as its another version.

Whats the best way of doing this and please explain in basic as im not good at computers.

At the moment my users have to use the scroll bar which i do not like.

Please see the Photos for more information.

Form is the database im working on now which has a scroller.
FORM 2 is the database i want the memo to look like

 

[neileg]

Pdesh3
I think you're better at coding than I am!
However, I don't understand why you have delcared BuildFilter as a function. I would have put this code into the button click event.
I think this code If Me.txtName > "" Then should read If Me.txtName <> "" Then.

Samrasr, you should start a new thread for your question.