How do use the inputbox to search through a table to find a record?

[b31luv]

I've been trying to figure out how to do this......

1) I have an inputbox asking for project number.

2) I want to use the information placed in the inputbox to search through "Table - Project List"....

3) If the project number is found then I open a form....

4) If no project number is found then I move on to the next portion of code and ask the person to enter project information.

I can get 1, 3, and 4 to work, however, 2 is where the problem is. How do use the inputbox to search through a table to find a record?

 

[Orion45]

I think you can do this using a recodset. Create a search button next to the text box with the following code to run in the on click event.

Dim qdfTemp As QueryDef
    Dim Projects As Recordset
    Dim ProjectNo As Long
    Dim dbs As Database
    Dim source As String
    Dim frm As Form
set frm = Forms("form you want to open")
ProjectNo = Me.txtProjectNo
source = "SELECT ProjectList.ProjectNo"
source = source + "FROM (ProjectList)"
source = source + "WHERE ((ProjectList.ProjectNo)=" & ProjectNo & ");"
Set dbs = CurrentDb
Set qdfTemp = dbs.CreateQueryDef("", source)
Set Projects = qdfTemp.OpenRecordset(dbOpenDynaset, dbSeeChanges)
If Projects.RecordSet > 0 Then
  'Open your form using, frm.RecordSource = source
Else
  'prompt the user to create a new project
end If
Projects.Close
Set Projects = Nothing 'close the recordset

hope this helps!

 

[Orion45]

Oops, just realized I made a mistake. The If statement should read

If Projects.RecordCount

 

[AutumnBlues]

Here's the idea you want; you'll need to tart it up a bit though.

--------
Sub Whatever()

Dim x as integer
Dim strSQL as string
Dim dB as Database
Dim Rec as Recordset

x = InputBox("Enter Number&quot
strSQL = "select count(ProjectNumber) from ProjectList where ProjectNumber = " & x & " "

Set db = CurrentDb
Set rec = db.OpenRecordset(strSQL, dbOpenDynaset)

With rec
if .recordcount > 0 then
DoCmd.OpenForm "YourForm"
Exit Sub
end if
End With

'other stuff

Exit Sub
End Sub
----------------

 

[b31luv]

Autumn, Exactly what I think I need right now but a few questions before we begin......

Why.....Dim strSQL as string? Can I also write this as DoCmd.RunSQL........?

Set rec = db.OpenRecordset(strSQL, dbOpenDynaset).....What is this saying?

Is this.....

strSQL = "select count(ProjectNumber) from ProjectList where ProjectNumber = " & x & " "

the same as

DCount("SelectComment", "[Query - Review Comment List]", "SelectComment = True")

Thanks for your assistance....

I'll answer after using both ways...

 

[b31luv]

Dim x As String
Dim TotalActive As Integer
x = InputBox("Enter Job Number&quot
TotalActive = DCount("ProjectNumber", "[Table - Project Info]", "ProjectNumber = '" & x & "' &quot
If TotalActive > 0 Then

This is what I'm using, and it works beautiful. Autumn, thanks you caused it to happen.....

 

[AutumnBlues]

Okay

The reason I use a string called strSQL is that I have a function set up that returns the result of a record-call (like I showed you above), I just pass it a string (called strSQL) and (with a bit of coaching) it returns me what I want.



You don't really need the strSQL part, you could just write...

Set rec = db.OpenRecordset("TYPE SQL HERE", dbOpenDynaset)

But it gets a bit untidy, and confusing if you have a variable in there too (which you do).



DoCmd.RunSQL is a diferent kettle of fish. That runs an action query. So if you want to delete everything in a table, you'd type...

DoCmd.RunSQL "delete * from myTable"

OR

Dim strSQL as string
strSQL = "delete * from myTable"
DoCmd.RunSQL strSQL



The DCount function is a bit different, but you could use it in your case. You might want to have a quick perusal at the help files for more info on this

 

[b31luv]

Dim clseSwitchboard As String
    Dim stDocName As String
    Dim sMsgReply As String
    Dim x As String
    Dim TotalActive As Integer
   
    x = InputBox("Enter Job Number")
    
        TotalActive = DCount("ProjectNumber", "[Table - Project Info]", "ProjectNumber = '" & x & "' ")
    
    If TotalActive > 0 Then

        Dim stDocName1 As String
        stDocName1 = "Form - Submittal Info"
        DoCmd.OpenForm stDocName1, acNormal, , , acFormAdd
        DoCmd.Maximize
        DoCmd.RunMacro "CloseSwitchboard"

    Else

        sMsgReply = MsgBox("Is this a new job", vbYesNo, "New Job")

        If sMsgReply = vbYes Then

            stDocName = "Form - Project Info"
            DoCmd.OpenForm stDocName, acNormal, , , acFormAdd
            DoCmd.Maximize
            clseSwitchboard = "Switchboard"
            DoCmd.Close acForm, clseSwitchboard

        Else

            Dim stDocName2 As String
            stDocName2 = "Form - Review Comments New"
            DoCmd.OpenForm stDocName2, acNormal
            DoCmd.Maximize
            DoCmd.RunMacro "CloseSwitchboard"

        End If

    End If

End Sub