Hi, I have a group of typists who type for different hospitals. So

ThornPCC · Feb 14, 2000

Hi, 
 
I have a group of typists who type for different hospitals. Some typists work for more than one hospital. 
When our payroll clerk enters the number of reports typed by each typist I want to be able either limit the combo box of hospitals to that specific typist, or list all the hospitals. 
 
I have 3 main tables, Billing, Hospitals, & Typists. 
Here are the general layouts of each. 
 
Table Fields 
 
Typists TL Number (unique index), Name (Last,First) 
 
Hospitals Hospital (unique index), TLs (multiple typists) 
 
Billing Date, TL Number, Name, Hospital, Pages, Studies 
 
I'm thinking of putting a yes/no field for limit to list. That way we can set the typists that we want to be limited to the list. 
 
The part I'm stuck on is the logice behind the form for this. Any suggestions will be helpful. 
 
Thanks 
 
Phil <a href=mailto:Thorn@full-moon.com>Thorn@full-moon.com</a> <a href= > </a> Most of us go through life not knowing what we want, but feeling damned sure that this isn't it.

DougP · Feb 14, 2000

If you want to limit a list based on a field on your form than you need a relationship between the Hospitals and Typists. 
First NEVER put both First name and Last name in the same field. 
Second put a Combo box on your form that's "Row source" will limit the list. 
So if you are keying something in Text1 then the SQL statement looks like this 
SELECT Hospital.ID, Hospital.Name FROM Hospital WHERE (((Hospital.ID)=[Forms]![Form1]![Text1])); 
 
Next put this code in Text1's After Update event 
Private Sub Text1_AfterUpdate() 
Combo5.Requery '< This refreshes the Combo box 
End Sub 
 
So when you finish typing in something in Text1 then Combo5 will limit the list. 
Now I may not have the fields you need in the correct place, but this is the general Idea. 
 
 DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a>

ThornPCC · Feb 15, 2000

Doug, 
 
That's along the lines of what I need. I have to have the Both names in the same field as this is a legacy database converted to Access. I may eventually get around to changing the fields, but that is another project. 
 
What I need to do is search for one of multiple TL#'s that are in one field. example; 
Hospital = "Abington Memorial" 
TL ="100, 103, 189" 
 
I need to find the "103" and limit the Hospital Combo baox to Hospitals with "103" in the TL field. 
I could try to run a query to create a table with multiple hospitals and one tl in each record. That might make it easier, but give me a larger database. 
 
The database is already 2.2 megs and it's only the middle of Feb. 
 
Thanks Phil <a href=mailto:Thorn@full-moon.com>Thorn@full-moon.com</a> <a href= > </a> Most of us go through life not knowing what we want, but feeling damned sure that this isn't it.

DougP · Feb 16, 2000

Well to find a string within a string use "INSTR" function 
 
What criteria are you using to know which hospital to pick out of the list. In Other words 
If Hospital = "Abington Memorial" then how do you know you want 103 out of TL ="100, 103, 189" 
Is "Abington Memorial" in fact TL "103"? 
 
Thats where you put the INSTR function. 
here is an example not sure it its exactly what you want. 
 
Value = INSTR(1,MyTextBox,"103&quot

the your SQL satement in the Combo box uses Value in it 
 
SELECT Hospital.ID, Hospital.Name FROM Hospital WHERE (((Hospital.ID)= " & Value & &quot

);" 
 
Something like that.... 
 
 
 
 
 DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a>

elizabeth · Feb 16, 2000

You're asking for trouble if you create functionality for a database before you normalize it. Not only do you need to make the fields atomic (break down name and break down typists), the Hospital table structure is incorrect. The hospital table should have just hospital ID and related items. To relate Hospitals to Typists, you need an additional table HospitalTypists with one Hospital ID and one TypistID per record.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Hi, I have a group of typists who type for different hospitals. So

ThornPCC

Programmer

DougP

MIS

ThornPCC

Programmer

DougP

MIS

elizabeth

IS-IT--Management

Similar threads

Part and Inventory Search

Sponsor