Question DLookup within an IIf()?

[jtundra05]

I have 2 Work Order forms that I am trying to pass data with in to. Form 1 is named SPR_frm where: 1. SPR No (ID# for relating) is set to auto-increment. 2. Problem location is entered from a drop down selection combo box control. 3. Problem description is entered in text control. Form 2 is named SCADA Field Report_frm where: 1. HPR No (ID# for relating) is set to auto-increment. 2. SPR No text control that upon input will reference to a specific SPR. 3. Problem Description text control. Here are my two issues: 1. I want to be able to input an SPR No on Form 2 and have it return the problem description from Form 1 onto problem description on Form 2. (I can DLookup that no problem) Here is the kicker; I also want to manually input text on Form 2 problem description if there is no SPR created yet. I tried to use an IIf() statement to validate if SPR is Null, then [Problem Desc], (from the Form 2 table data source) Else DLookup("[Problem Desc]","[SPR_tbl]","[SPR No]=Form![SPR No]"). Is there a way to disregard the DLookup if SPR is null and allow text entry into the text control? 2. If so, can I also make the Form 2 Location control (which is a drop down combo control) allow for manual entry if the SPR No control is Null? I hope i asked this question clearly. Thanks in advance.

 

[neileg]

If I understand you correctly (and I'm not sure of that!) I would do this differently. I would add an entry to the SPR table of zero (ie create an SPR of 0) with no text in the Description field. Use Nz() on the contents of the SPR control in your Lookup. If the SPR is null, Nz() will convert it to zero so you will always have a valid lookup but it will return an empty description. As for you combo, you can toggle the Limit to List property.

 

[jtundra05]

Thanks for your help neileg. After reading my post I couldn't really understand what I was asking for myself so let me try again.

I want Form2 [Problem Description] to lookup Form 1 [Problem Description]
if:
[SPR No] on Form 2 has an entry,
if not:
then I want to allow for manual entry of Form 2 [Problem Description].

Is this possible? I hope this makes my question clearer.

 

[Pat Hartman]

When do you want to populate the description? Is the description required?

You could use the AfterUpdate event of SprNo.

Me.ProblemDescription = DLookup(.....)

You should also check the description field in the form's BeforeUpdate event to ensure that it is populated:
If IsNull(Me.ProblemDescription) Then
Msgbox "Please enter a problem description.",vbokOnly
Me.ProblemDescription.SetFocus
Cancel = True
Exit Sub
End if

 

[NigelShaw]

to lookup Form 1 [Problem Description]
if:
[SPR No] on Form 2 has an entry,
if not:
then I want to allow for manual entry of Form 2 [Problem Description].

Is this possible? I hope this makes my question clearer.

Is the form 2 [Problem Description] a textbox that gets the value from form 1?

if so, could you do an OnLoad Event:

Private sub SCADA Field Report_frm_OnLoad()

Dim SPRNo As Integer

'get SPR No value from SPR_frm
SPRNo = Forms![SPR_frm]![SPR No].value

'check for zero. if the textbox has entry, get the problem description and put it into SCADA Field Report_frms ProblemDescription textbox
If SPRNo <> Null Then
Me.ProblemDescription = Forms![SCADA Field Report_frm]![ProblemDescription].value
Exit sub

Else

'otherwise goto the ProblemDescription textbox and manually enter.
Me.ProblemDescription.SetFocus

End If

End Sub


it might work.

nigel