Input Box

[hdgirl]

I have an input box that allows the user to input an autonumber generated against a record in the table and that will then open the corresponding form for that record but if the user enters a record number that doesn't exist it then opens a blank form, i would like it to just give a message to say "Sorry that record number does not exist". I've thought of using an If statement but am not sure how i would say IF FieldName = ("Does not exist&quot THen MsgBox etc....
Any ideas
CJB

 

[Norris68]

If IsNull(DLookup("FieldName","TableName","FieldValue=" & TheDataYouEntered)) Then
MsgBox "Invalid Value"
Else
'Open The Form
End If

If you are searching on a text value make sure you include quotes in the DLookup function i.e. Final parameter is ... ,"FieldValue='" & TheDataYouEntered & "'&quot

 

[ajorge]

I had the same situation and I resolved it with this code.


Dim rst As New Recordset
Dim strSQL As String
Dim cnn As Connection
Dim Year As Integer

Set cnn = CurrentProject.Connection

Year = InputBox("Please Enter the Year.", "Approved Projects", 0) '****** Approved Project is the source ******

strSQL = "SELECT [Setup Master].RecordID, [Setup Master].[ID number], [Setup Master].[Approved project], [Setup Master].[Analyst ID], " & _
"[Setup Master].AgencyName, [Setup Master].Project, [Setup Master].Funding, [Setup Master].[CDBG Funds], [Setup Master].[ESG Funds], [Setup Master].[HOME Funds], " & _
"[Setup Master].[HOPWA Funds], [Setup Master].[Other Funds], [Setup Master].[Report Year], [Setup Master].HUDCode, [REPORT YEAR].[start-finish], Projects.[Project Description] " & _
"FROM ([Setup Master] INNER JOIN [REPORT YEAR] ON [Setup Master].[Report Year] = [REPORT YEAR].year) INNER JOIN Projects ON [Setup Master].Project = Projects.Number " & _
"WHERE((([Setup Master].[Approved project])=True) AND (([Setup Master].[Analyst ID])=CurrentUser()) AND (([Setup Master].[Report Year])=" & Year & &quot) " & _
"ORDER BY [Setup Master].RecordID;"
rst.Open strSQL, cnn, adOpenDynamic, adLockPessimistic, adCmdText

If rst.EOF = True And rst.BOF = True Then
MsgBox "There are no project approved for the year selected", vbOKOnly, "IDIS"
DoCmd.Close
DoCmd.OpenForm "Idis Main Menu", acNormal
Else
DoCmd.OpenForm "Current Projects", acNormal, strSQL
End If

rst.Close
cnn.Close
Set cnn = Nothing
Set rst = Nothing


End Sub


Hopefully this will help good luck.