Search vba Question

[ramez75]

I have the below vba that I use on my form event "On Click". The form is called "ActionPlan" and it works perfectly

Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click
    
' Define
    Dim strNumRef As String
    Dim strSearch As String
    
' Check if the field "txtSearch" on the form is empty/blank
    If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
        Me![txtSearch].SetFocus
    Exit Sub
    End If
                
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("Seq_Number")
    DoCmd.FindRecord Me!txtSearch
        
    Seq_Number.SetFocus
    strNumRef = Seq_Number.SetFocus.Text
    txtSearch.SetFocus
    strSearch = txtSearch.Text
        
    If strNumRef = strSearch Then
        MsgBox "Match Found For: " & strSearch, , "Congratulations!"
        txtSearch = ""
        txtSearch.SetFocus
        
        Else
          MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
            , "Invalid Search Criterion!"
            txtSearch.SetFocus
            DoCmd.GoToRecord , , acNewRec
    End If
    
Exit_cmdSearch_Click:
   Exit Sub
   
' Closes Form after being used
    DoCmd.close acForm, "frmSearch"
   
' Takecare of error
Err_cmdSearch_Click:
   'MsgBox Err.Description
   Resume Exit_cmdSearch_Click
    
End Sub

My question is as follows....I did not want the search command on the "ActionPlan" form. I created another form called "frmSearch" and under "On Click" event I embedded the above code. I just cant get it to work.

I want to type the Seq_Number in the "txtSearch" unbound field and click Search button and the result is

ActionPlan form opens up with the info populated in the fields related to the Seq_Number and ofcourse the frmSearch will close

Thanks for the help

 

[pbaldy]

Based on your description, I'd go this direction:

http://www.baldyweb.com/wherecondition.htm

 

[ramez75]

I will give it a try and see what I get..thanks

 

[ramez75]

pbaldy

This what I have

Private Sub cmdSearch_Click()
    If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
        Me![txtSearch].SetFocus
    Exit Sub
    End If
DoCmd.OpenForm "ActionPlan", , , "Seq_Number = '" & Me.txtSearch & "'"
 
'DoCmd.close acForm, "frmSearch"
 
End Sub

The problem it open the ActionPlan but blank, if I click the search button again on the frmSearch then ActionPlan form gets populated.

Is their a way I can get this done at one click. The problem is I want frmSearch to close when ActionPlan is opened so I cant click on Search button again

Thanks

 

[pbaldy]

Curious; I've never had to run the process twice to get it to work. The Seq_Number field is text? Is the process looking for a record that might not be saved yet on the first form?

 

[ramez75]

Seq_Number is a text field. Records are all stored already......For some reason when I click the search button on frmSearch form its just opening a blank record of ActionPlan form. Now if I Click the Search button again on the frmSearch form then ActionPlan form that is already opened get populated based on the Seq_Number.....

 

[ramez75]

An update the only way i could get it to work is to use the DoCmd.Openform twice

Private Sub cmdSearch_Click()
    If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
        Me![txtSearch].SetFocus
    Exit Sub
    End If
 
DoCmd.OpenForm "ActionPlan"
DoCmd.OpenForm "ActionPlan", , , "Seq_Number = '" & Me.txtSearch & "'"
 
DoCmd.close acForm, "frmSearch"
 
End Sub

The only criteria is what if the user enters in the search form a value that is wrong or doesnt exist.......I would like to give it a message saying "Please Check value"

On the Search function that i had embedded on the form i could make it work but since i am using another form as a search I just dont know the right command to use

Below is what I had and i was trying to modified it to work by using frmSeach

    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("Seq_Number")
    DoCmd.FindRecord Me!txtSearch
        
    Seq_Number.SetFocus
    strNumRef = Seq_Number.Text
    txtSearch.SetFocus
    strSearch = txtSearch.Text
        
    If strNumRef = strSearch Then
        MsgBox "Match Found For: " & strSearch, , "Congratulations!"
        txtSearch = ""
        txtSearch.SetFocus
        
        Else
          MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _
            , "Invalid Search Criterion!"
            txtSearch.SetFocus
            DoCmd.GoToRecord , , acNewRec
    End If

 

[SpentGeezer]

Greetings,

Use the seq_number as a filter to open the form.
dim strSQL
strSQL = "<tbl_name>.<fieldname> = seq_number"

docmd.close
DoCmd.OpenForm "<formName>", acNormal, , strSQL, acFormPropertySettings

Then check the record count.
c = Forms!<frm_name>.Recordset.recordCount
If not c > 0 Then
docmd.close
docmd.openform("<search form name>")
msgbox "Item not found, try again"
exit sub
end if

This causes the form to flick on and off if there is no item found. To avoid this you could create a query using the above criteria and check it's recordcount before closing and opening any forms. Then msgbox if 0 and exit sub.

Cheers.

 

[ramez75]

SpentGeezer,

Let me make sure I am understanding this correctly. So in the form "Data" tab Filter = Seq_Number (I already have this)

Then copy the code u have and put it in the "Click" event of the second form that i will be using to search the first form.

I will try it it and c what happens

 

[ramez75]

What command I can use so that if txtSearch in frmSearch not equal to Seq_Number in frmActionPlan then frmActionPlan will not open up....Below is what i tried but doesnt work.

 If Me.txtSearch = Forms!frmActionPlan!Seq_Number Then
        DoCmd.OpenForm "frmActionPlan", , , "Seq_Number = '" & Me.txtSearch & "'"
 Else
        MsgBox "Match Not Found For: " & txtSearch & " - Please Try Again.", , "Invalid Search Criterion!"
        Me![txtSearch].SetFocus
    Exit Sub
 End If

 

[pbaldy]

The form isn't open (I assume) at the point the code runs, so you can't really test that way. You could use a DCount() with the same criteria to see if any records exist with that number.

 

[ramez75]

U r right the form is closed at that point........How do I use the DCount if i may ask? U mean like below

If Me.txtSearch = DCount(Forms!frmActionPlan!Seq_Number) Then
DoCmd.OpenForm "frmActionPlan", , , "Seq_Number = '" & Me.txtSearch & "'"
Else
MsgBox "Match Not Found For: " & txtSearch & " - Please Try Again.", , "Invalid Search Criterion!"
Me![txtSearch].SetFocus
Exit Sub
End If

 

[pbaldy]

Again, you can't reference the form that is to be opened, since it's not open yet. You count whether any records with that Seq_number exist in the underlying table, using the same criteria you're using to open the other form. Info on proper syntax:

http://www.mvps.org/access/general/gen0018.htm

 

[ramez75]

Below is what i have so far..........I just need to loop it across the table what i have below only will work for the first record

Private Sub cmdSearch_Click()
 
Dim varX As Variant
       
    If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
        Me![txtSearch].SetFocus
    Exit Sub
    End If
 
    varX = DLookup("Seq_Number", "ActionPlan", "id =  id")
        
If (Me![txtSearch]) = varX Then
DoCmd.OpenForm "ActionPlan", , , "Seq_Number = '" & Me.txtSearch & "'"
Else
DoCmd.OpenForm "frmSearch"
MsgBox "Match Not Found For: " & txtSearch & " - Please Try Again.", , "Invalid Search Criterion!"
Me![txtSearch].SetFocus
End If         
DoCmd.close acForm, "frmSearch"
End Sub

I tried a Do loop around the above code but i could get it to work, what am I doing wrong

 

[ramez75]

Below is the do loop i tried but for some reason it doesnt loop across the records.. Am I using the wrong command.

Private Sub cmdSearch_Click()
Dim rs As DAO.Recordset
Dim db As Database
Dim varX As Variant
Set db = CurrentDb
Set rs = db.OpenRecordset("ActionPlan", dbOpenDynaset)
    If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
        MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
        Me![txtSearch].SetFocus
    Exit Sub
    End If
If rs.RecordCount > 0 Then
rs.MoveFirst
varX = DLookup("Seq_Number", "ActionPlan", "id =  id")
 Do Until rs.EOF
 If (Me![txtSearch]) = varX Then
 DoCmd.OpenForm "ActionPlan", , , "Seq_Number = '" & Me.txtSearch & "'"
 DoCmd.close acForm, "frmSearch"
 Exit Do
 Else
 MsgBox "Match Not Found For: " & txtSearch & " - Please Try Again.", , "Invalid Search Criterion!"
 Me![txtSearch] = Null
 Me![txtSearch].SetFocus
 End If
 
 rs.MoveNext
 varX = DLookup("Seq_Number", "ActionPlan", "id =  id")
 MsgBox "is" & varX
 
Loop
End If
rs.close
Exit_cmdSearch:
        Set rs = Nothing
        Set db = Nothing
        Exit Sub
 
End Sub

 

[pbaldy]

Why are you looping? If I have the objects right:

If DCount("*", "ActionPlan", "Seq_Number = '" & Me.txtSearch & "'") > 0 Then
  'you have a match, open the form with that wherecondition
Else
  'no match, warn the user
End If

 

[ramez75]

Why are you looping? If I have the objects right:

If DCount("*", "ActionPlan", "Seq_Number = '" & Me.txtSearch & "'") > 0 Then
  'you have a match, open the form with that wherecondition
Else
  'no match, warn the user
End If

will the Dcount go through all the records in ActionPlan. Let me try this

 

[ramez75]

Perfect thats exactly what I wanted... Thank you so much.....

 

[pbaldy]

No problem.