VBA Access - Input Box

[charlie442]

Hi
I have an input box which directs users to a Form (Frm_Risk_Profile) to update a Case ID specified in the input box. I only want this to happen if the case ID is present in Tbl_Profiling_Cases. If it is not then the user should be redirected to the Main Switchboard form (Frm_Main_Switchboard). I have tried to accomplish this using a dlookup of the case ID in Tbl_Profiling_Cases embedded in an IF statement. However the criteria referencing the Temp variable is causing problems.
Any help greatly appreciated. The code I have written is as follows:

Private Sub OpenRiskProfileForm_Click()
'This function allows the user to open the risk profile form to a particular case ID.

Dim stSql As String
Dim Temp$

DoCmd.Close acForm, Me.Name

'InputBox prompts user to enter Case ID for the form they wish to open.
Temp = InputBox("Enter Case ID", "Search for Case ID", "Case ID")

If StrPtr(Temp) = 0 Then 'redirect to main switchboard if cancel is pressed on switchboard
DoCmd.OpenForm "Frm_Main_Switchboard"
Else
If DLookup("Case_ID", "Tbl_Profiling_Cases", strTemp) Is Null Then 'redirect to main switchboard if case id is null
MsgBox "Case_ID Not Opened!"
DoCmd.OpenForm "Frm_Main_Switchboard"
Else
If DLookup("Case_ID", "Tbl_Profiling_Cases", strTemp) Is Not Null Then 'open form to record that matches the Case ID entered by user.
DoCmd.OpenForm "Frm_Risk_Profile", acNormal, , "[Case_ID]='" & stInput & "'", acFormEdit
End If
End If
End If

End Sub

 

[Galaxiom]

I think this might be what you are looking for:

DLookUp("Case_ID", "Tbl_Profiling_Cases", "Case_ID ='" & strTemp & "'")

 

[namliam]

1) what is strTemp?
2) what is is supposed to search?
the 3rd value of a dlookup is supposed to look simular tothe where clause in the Openform command.

 

[Galaxiom]

Also there is no need to repeat the DLookup.
If it Is Null then it is also Not Not Null. The Else suffices and will save a lot of time.

BTW. The DLookup can be used without the Null test as it can feign Boolean output in this circumstance. In the case of no record being found this expression returns False.

If DLookUp("expression", "domain", "where")

Please also post your code in a code box with proper indentation to make it more readable.

 

[charlie442]

Thanks GlaxiomAtHome

This is giving me a Run-time Error '424':
Object required

Any ideas?

 

[DCrake]

If StrPtr(Temp) = 0 Then 'redirect to main switchboard if cancel is pressed on switchboard

DoCmd.OpenForm "Frm_Main_Switchboard"
Else
If DLookup("Case_ID", "Tbl_Profiling_Cases", strTemp) Is Null Then 'redirect to main switchboard if case id is null
MsgBox "Case_ID Not Opened!"
DoCmd.OpenForm "Frm_Main_Switchboard"
Else
If DLookup("Case_ID", "Tbl_Profiling_Cases", strTemp) Is Not Null Then 'open form to record that matches the Case ID entered by user.
DoCmd.OpenForm "Frm_Risk_Profile", acNormal, , "[Case_ID]='" & stInput & "'", acFormEdit
End If
End If
End If

Should read

Dim Answer As string

If Temp = "" Or Temp = 0 Then 'redirect to main switchboard if cancel is pressed on switchboard
      DoCmd.OpenForm "Frm_Main_Switchboard"
Else
      Answer = Nz(DLookup("Case_ID", "Tbl_Profiling_Cases", "[Case_ID]= '" & Temp & "'"),0)

     If Answer = 0 Then
          MsgBox "Case_ID Not Opened!"
          DoCmd.OpenForm "Frm_Main_Switchboard"
     Else
          DoCmd.OpenForm "Frm_Risk_Profile", acNormal, , "[Case_ID]='" & Temp & "'", acFormEdit
     End If
End If

 

[charlie442]

Thanks DCrake

However the line:

If Temp = "" Or Temp = 0 Then 'redirect to main switchboard if cancel is pressed on switchboard

is giving a type-mismatch (error 13)

 

[DCrake]

This will because you defined Temp as a variant

Dim Temp$

Should be

Dim Temp As String

If Temp = "" or Temp = "0" Then

 

[charlie442]

Thanks

This has worked for the top section of code. The only problem I am having now is that when I enter an existing case ID I am getting a data mismatch error on the following line of code:

If Answer = 0 Then

The case ID takes the form of Year-Reference for example 2011-1111111111

But Answer has been Dim'd as String so not to sure what is the problem

 

[charlie442]

Hi

Never mind. I have figured this last bit out. Many thanks for your assistance.