Help: Best way to go

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have three text boxes with OTltc;PTltc; and STltc with identical code
Small sample of code: Same for PT and ST text boxes
Private Sub Otltc_Exit(Cancel As Integer)
If ([Unit] = "fr1e") And ([Otltc] > "0") Then [Ottherapist].Value = "KS"
If ([Unit] = "fr1w") And ([Otltc] > "0") Then [Ottherapist].Value = "LH"
If ([Unit] = "fr2e") And ([Otltc] > "0") Then [Ottherapist].Value = "MW"

The {ltc} text boxes are numeric. Therapist text boxes are initials!!!

I do have a table (UnitAssignment) listing Unit, OT, PT and ST.
The above code works, but, if I change initials in UnitAssignment table I
need to go in form and change over 50 initials.

There's got to be a better way to do this and I'm just not sure how.
 
Could you add a column to the UnitAssignment table that holds the fr1e, fr1w,
fr2e values? Do these map one-to-one to the OtherRapist values? If you could
do this, everything would be table-based. You could replace your multiple
lines of code with a single DLookup call.

Barry
 
The UnitAssignment has three fields: Unit (fr1e, etc); OT(KS, etc); and
PT(RD, etc). How would I write the DLookup code?
 
It would look something like this:
OTTherapist = DLookup("OT", "UnitAssignment", "[Unit] = '" & Me.Unit & "'")

Look for DLookup in Access help to better understand how this works.
Barry

dar said:
The UnitAssignment has three fields: Unit (fr1e, etc); OT(KS, etc); and
PT(RD, etc). How would I write the DLookup code?
dar said:
I have three text boxes with OTltc;PTltc; and STltc with identical code
Small sample of code: Same for PT and ST text boxes
Private Sub Otltc_Exit(Cancel As Integer)
If ([Unit] = "fr1e") And ([Otltc] > "0") Then [Ottherapist].Value = "KS"
If ([Unit] = "fr1w") And ([Otltc] > "0") Then [Ottherapist].Value = "LH"
If ([Unit] = "fr2e") And ([Otltc] > "0") Then [Ottherapist].Value = "MW"

The {ltc} text boxes are numeric. Therapist text boxes are initials!!!

I do have a table (UnitAssignment) listing Unit, OT, PT and ST.
The above code works, but, if I change initials in UnitAssignment table I
need to go in form and change over 50 initials.

There's got to be a better way to do this and I'm just not sure how.
Click to expand...
Click to expand...
 
Thanks for responding: Here is my code in an unbound text box.
=DLookUp("OTtherapist","UnitAssignment","[Unit] = ' " & Me.Unit & " ' ")
I get a #Name? error in text box. I've check spelling and everything seems
okay.
The text box is on a form where the info is saved to a table.
Once this is solved, I'll need to know how to activate the text box IF
another text box is greater than 0.
Thanks again.

Barry Gilbert said:
It would look something like this:
OTTherapist = DLookup("OT", "UnitAssignment", "[Unit] = '" & Me.Unit & "'")

Look for DLookup in Access help to better understand how this works.
Barry

dar said:
The UnitAssignment has three fields: Unit (fr1e, etc); OT(KS, etc); and
PT(RD, etc). How would I write the DLookup code?
dar said:
I have three text boxes with OTltc;PTltc; and STltc with identical code
Small sample of code: Same for PT and ST text boxes
Private Sub Otltc_Exit(Cancel As Integer)
If ([Unit] = "fr1e") And ([Otltc] > "0") Then [Ottherapist].Value = "KS"
If ([Unit] = "fr1w") And ([Otltc] > "0") Then [Ottherapist].Value = "LH"
If ([Unit] = "fr2e") And ([Otltc] > "0") Then [Ottherapist].Value = "MW"

The {ltc} text boxes are numeric. Therapist text boxes are initials!!!

I do have a table (UnitAssignment) listing Unit, OT, PT and ST.
The above code works, but, if I change initials in UnitAssignment table I
need to go in form and change over 50 initials.

There's got to be a better way to do this and I'm just not sure how.
Click to expand...
Click to expand...
Click to expand...
 
I'm not sure if this is the problem, but it looks like you have spaces
between the single quotes and double quotes.

Also, is the spelling of your table and column names correct? DLookup works
like this:
DLookup(ColumnName, TableName, WhereClause)

Barry

dar said:
Thanks for responding: Here is my code in an unbound text box.
=DLookUp("OTtherapist","UnitAssignment","[Unit] = ' " & Me.Unit & " ' ")
I get a #Name? error in text box. I've check spelling and everything seems
okay.
The text box is on a form where the info is saved to a table.
Once this is solved, I'll need to know how to activate the text box IF
another text box is greater than 0.
Thanks again.

Barry Gilbert said:
It would look something like this:
OTTherapist = DLookup("OT", "UnitAssignment", "[Unit] = '" & Me.Unit & "'")

Look for DLookup in Access help to better understand how this works.
Barry

dar said:
The UnitAssignment has three fields: Unit (fr1e, etc); OT(KS, etc); and
PT(RD, etc). How would I write the DLookup code?
:

I have three text boxes with OTltc;PTltc; and STltc with identical code
Small sample of code: Same for PT and ST text boxes
Private Sub Otltc_Exit(Cancel As Integer)
If ([Unit] = "fr1e") And ([Otltc] > "0") Then [Ottherapist].Value = "KS"
If ([Unit] = "fr1w") And ([Otltc] > "0") Then [Ottherapist].Value = "LH"
If ([Unit] = "fr2e") And ([Otltc] > "0") Then [Ottherapist].Value = "MW"

The {ltc} text boxes are numeric. Therapist text boxes are initials!!!

I do have a table (UnitAssignment) listing Unit, OT, PT and ST.
The above code works, but, if I change initials in UnitAssignment table I
need to go in form and change over 50 initials.

There's got to be a better way to do this and I'm just not sure how.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top