• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 479
  • Last Modified:

Dlookup from inputbox - if exists, open form

Rather than using a form based on a query with criteria, could I use something like this:

Dim myJobRef As Variant
    myJobRef = DLookup("[T-cardnumber]", "[tblMain]")
    InputBox "Enter Job Number", "Job Number Required", myJobRef

    If IsNull(myJobRef) Then
 
    MsgBox "Job not there"
   
        Else

        DoCmd.OpenForm "frmMain", acNormal, , "[T-cardnumber] = " & CLng(myJobRef)

    End If
   
End Sub

That asks the user to enter a job number.  If that job number exists in tblMain it will open a form called frmMain and display that record.  If the record does not exist in tblMain the user will get a messagebox "does not exist".

Thanks
Lapchien
0
Lapchien
Asked:
Lapchien
  • 3
  • 3
  • 2
1 Solution
 
Bat17Commented:
Yes you can, You might need something other than dlookup to get the default jobRef though, possibly DMax To get the last Job ID and then Dlookup to check they have given a valid ID

Dim myJobRef As Variant
    myJobRef = DMax("[T-cardnumber]", "[tblMain]")
    myJobRef = InputBox("Enter Job Number", "Job Number Required", myJobRef)
   
    If IsNull(DLookup("[T-cardnumber]", "[tblMain]", "[T-cardnumber] =" & CLng(myJobRef))) Then
        MsgBox "Job not there"
    Else
        DoCmd.OpenForm "frmMain", acNormal, , "[T-cardnumber] = " & CLng(myJobRef)
    End If
End Sub

0
 
tbsgadiCommented:
Hi Lapchen
Why not use a combo box on a form.
The combo box will query the T-cardnumber from tblMain & then the user will only be able to choose an existing Job
0
 
LapchienDirectorAuthor Commented:
Yeah, that's what I want (i think!)!  If the user clicks Cancel on the input box I get an error though - can I trap this?
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
tbsgadiCommented:
You don't use an input box you use a form..
If you want you can use the same form as the data.
You can use the combo box wizard to help.
0
 
LapchienDirectorAuthor Commented:
Yes, I see that.  I have amended the forms query to use a new parameter based on whatever is entered in the combo box.  But how do I then open the frmMain with the corect record?
0
 
Bat17Commented:
Just check for an empty string before doing the lookup

Dim myJobRef As Variant
    myJobRef = DMax("[ID]", "[capital]")
    myJobRef = InputBox("Enter Job Number", "Job Number Required", myJobRef)
    If myJobRef = "" Then Exit Sub
    If IsNull(DLookup("[id]", "[capital]", "[id] =" & CLng(myJobRef))) Then
        MsgBox "Job not there"
    Else
        DoCmd.OpenForm "frmMain", acNormal, , "[T-cardnumber] = " & CLng(myJobRef)
    End If
End Sub

Peter
0
 
Bat17Commented:
oops

Dim myJobRef As Variant
    myJobRef = DMax("[T-cardnumber]", "[tblMain]")
    myJobRef = InputBox("Enter Job Number", "Job Number Required", myJobRef)
    If myJobRef = "" Then Exit Sub
    If IsNull(DLookup("[T-cardnumber]", "[tblMain]", "[T-cardnumber] =" & CLng(myJobRef))) Then
        MsgBox "Job not there"
    Else
        DoCmd.OpenForm "frmMain", acNormal, , "[T-cardnumber] = " & CLng(myJobRef)
    End If
End Sub

Peter
0
 
tbsgadiCommented:
If your'e using a separate form just use DoCmd.OpenForm "frmMain" & hide the present form.
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now