Text Box - Control Source (1 Viewer)

JimmyHoffa · Dec 11, 2018

Good evening,

I have a form with the record source set to tblEmployeeTraining.

In tblEmployeeTraining, I have the following fields:
ID (primary key)
EmployeeID_fk
TrainingID_fk
TrainingDate (date)
TrainingScan (attachment)

I have a table with a list of trainings, named List_Trainings. In this table, I have the following fields:

ID (pk)
TrainingDescription
TrainingExpiryNumber
TrainingExpiryInterval
TrainingCanExpire (yes/no)

In my form, I want text to automatically appear for it to say either "Valid", "Expired" or "Not Applicable" next to the training description combo box.

The way I tried to do it was to add three text boxes one on top of another. I've used the following control source expression builder for each of the text boxes:

Code:

=IIf(([List_Trainings]![TrainingCanExpire] = -1) and (Now()<DateAdd("m",[List_Trainings]![TrainingExpiryNumber],[tblEmployeeTraining]![TrainingDate])),"Valid (" & DateAdd("m",[List_Trainings]![TrainingExpiryNumber],[tblEmployeeTraining]![TrainingDate]) & ")","")

Code:

=IIf(([List_Trainings]![TrainingCanExpire] = -1) and (Now()>DateAdd("m",[List_Trainings]![TrainingExpiryNumber],[tblEmployeeTraining]![TrainingDate])),"Expired (" & DateAdd("m",[List_Trainings]![TrainingExpiryNumber],[tblEmployeeTraining]![TrainingDate]) & ")","")

Code:

=IIf([List_Trainings]![TrainingCanExpire] = 0,"Not Applicable","")

All I get is #Name? as a result. I may be way off in what I'm trying to do and I'm looking for your help!

Thanks

June7 · Dec 12, 2018

Use Date() instead of Now().

Cannot directly reference table in expression. Can only reference fields that are included in the form's RecordSource. Options are:

1. include List_Trainings in the form RecordSource, this would be a query that joins tables, probably a (LEFT or RIGHT join, not INNER), do not allow edits of List_Trainings data

2. include the List_Training fields in a combobox for selecting training, then reference those columns in expression

3. DLookup()

isladogs · Dec 12, 2018

Welcome Jimmy

Agree with June's comment re Date rather than Now

Suggest you use a label lblTraining (only one label needed) & set its caption with code similar to this in e.g. Form_Current event

Code:

Dim blnCanExpire As Boolean

blnCanExpire=Nz(DLookup("TrainingCanExpire","List_Trainings"),0)

Select Case blnCanExpire

Case True
   If Date()<DateAdd("m",[List_Trainings]![TrainingExpiryNumber],[tblEmployeeTraining]![TrainingDate])) Then
       Me.lblTraining.Caption="Valid  (" & DateAdd("m",[List_Trainings]![TrainingExpiryNumber],[tblEmployeeTraining]![TrainingDate]) & ")"
  ElseIf Date()>DateAdd("m",[List_Trainings]![TrainingExpiryNumber],[tblEmployeeTraining]![TrainingDate])) Then
     Me.lblTraining.Caption="Expired (" & DateAdd("m",[List_Trainings]![TrainingExpiryNumber],[tblEmployeeTraining]![TrainingDate]) & ")"
  Else 'covers case where Date=DateAdd .....
      Me.lblTraining.Caption=""
  End If	

Case False
      Me.lblTraining.Caption="Not applicable"

End Select

You'll need to check I've understood what your code is meant to do

JimmyHoffa · Dec 12, 2018

Thanks for the suggestion both of you.

I'm running into a problem though. There were two extra parenthesis that I managed to find and remove. When the training cannot expire, it does display as "Not Applicable". However, when it can expire, I'm getting the following error:

"Microsoft Access can't find the field '|1' referred in your expression"

Code:

Private Sub Form_Current()
Dim blnCanExpire As Boolean

blnCanExpire = Nz(DLookup("TrainingCanExpire", "List_Trainings"), 0)

Select Case blnCanExpire

Case True
[COLOR="Red"]  If Date < DateAdd("m", [List_Trainings]![TrainingExpiryNumber], [tblEmployeeTraining]![TrainingDate]) Then[/COLOR]
       Me.lblTraining.Caption = "Valid  (" & DateAdd("m", [List_Trainings]![TrainingExpiryNumber], [tblEmployeeTraining]![TrainingDate]) & ")"
  ElseIf Date > DateAdd("m", [List_Trainings]![TrainingExpiryNumber], [tblEmployeeTraining]![TrainingDate]) Then
     Me.lblTraining.Caption = "Expired (" & DateAdd("m", [List_Trainings]![TrainingExpiryNumber], [tblEmployeeTraining]![TrainingDate]) & ")"
  Else 'covers case where Date=DateAdd .....
      Me.lblTraining.Caption = ""
  End If

Case False
      Me.lblTraining.Caption = "Not applicable"

End Select
End Sub

Galaxiom · Dec 12, 2018

JimmyHoffa said:
"Microsoft Access can't find the field '|1' referred in your expression"

The tablename is not part of the fieldname in the recordset. Use just the fieldname.

Text Box - Control Source (1 Viewer)

JimmyHoffa

New member

June7

AWF VIP

isladogs

MVP / VIP

JimmyHoffa

New member

Galaxiom

Super Moderator

Similar threads

Users who are viewing this thread