Help Please - Search box code

[Walshie1987]

Hi, I'm wanting to create a simple db so when i enter a document number, if the document number is in the record a box will say something like "found" then go to the record. If the record isn't found then i want a box the say something like "record not found, add now?" then open an input form.

I've managed to come up with this (see below) but when the record is there the box says record found, then opens the "add new record" box, and if i enter a number not in the db then it just goes to the first record and then brings the "add new record" box up. I wondered if anyone could help,

txtSearch = Search box
ReqNumber = Document Number
frmInput = Input Form (add new form)
Main = Name of Table

Here's the code:

Private Sub cmdSearch_Click()
Dim ReqNumberRef As String
Dim txtSearch As String

'Check Search Box for Blank Value

If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please Enter Req Number!", vbOKOnly, "No Req Number"
Me![txtSearch].SetFocus
Exit Sub
End If
'---------------------------------------------------------------

'Searches for record

DoCmd.ShowAllRecords
DoCmd.GoToControl ("ReqNumber")
DoCmd.FindRecord Me!txtSearch

ReqNumber.SetFocus
txtSearch = ReqNumber.Text
ReqNumber.SetFocus

'If Req Number found goes to record

If ReqNumber = txtSearch Then
MsgBox "Req Found: " & txtSearch, , "Congratulations!"
ReqNumber.SetFocus
txtSearch = ""
'If Req Number not found goes to input sheet

If Not ReqNumber = txtSearch Then
UserSelection = MsgBox("Req Not Found" & Chr(10) & "Add Req", vbYesNo)
Select Case UserSelection
Case 6
DoCmd.OpenForm "frmInput"
Case 7
End Select
End If
End If
Me![txtSearch] = Null
End Sub

Thanks alot

 

[Remou]

Here are a few comments:

Private Sub cmdSearch_Click()
 'It is not a good idea to have a variable with the same name as
 'a control.
   ' Dim ReqNumberRef As String
   ' Dim txtSearch As String
    
'Check Search Box for Blank Value

    'If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
    'neater to say
    If Trim(Me![txtSearch] & " ") = "" Then
        MsgBox "Please Enter Req Number!", vbOKOnly, "No Req Number"
        Me![txtSearch].SetFocus
    Exit Sub
End If
'---------------------------------------------------------------
        
'Searches for record
        
    DoCmd.ShowAllRecords
    DoCmd.GoToControl ("ReqNumber")
    DoCmd.FindRecord Me!txtSearch
        
    ReqNumber.SetFocus
    
    'Leave out this bit or ReqNumber will always
    'be equal to txtSearch and the next bit won't work.
    'txtSearch = ReqNumber.Text
    'ReqNumber.SetFocus
         
'If Req Number found goes to record

    If ReqNumber = Me.txtSearch Then
        MsgBox "Req Found: " & txtSearch, , "Congratulations!"
        ReqNumber.SetFocus
        txtSearch = ""
    'You need an Else here, not an If
'If Req Number not found goes to input sheet
    Else
    'If Not ReqNumber = txtSearch Then
        UserSelection = MsgBox("Req Not Found" & Chr(10) & "Add Req", vbYesNo)
        Select Case UserSelection
        Case 6
        DoCmd.OpenForm "frmInput"
        Case 7
        End Select
    End If

Me![txtSearch] = Null
End Sub

 

[Walshie1987]

Thanks alot for that, as you probably guessed im a bit of a noob at vba, been using access for quite a while but have now decided to give code a go.

Is there any chance you could write the above exactly how you would if you were to put it into a form.

Thanks alot I appreciate it!

 

[Remou]

You can put the above in a form in place of your existing code.

 

[Walshie1987]

Thanks alot, one last thing, When i enter a document number that is already in the database i still get the "add new req" box instead of the "record found" box

any ideas how to solve this?

Thanks again

 

[Remou]

Is there any other code on your form, or have you changed the code above to match other things on your form? You can try creating a small test form with just three controls:
[tt]txtSearch : Unbound
ReqNumber : Bound to ReqNumber in a table
cmdSearch : Code as above.[/tt]

 

[Walshie1987]

Thanks!!!!!
Made the sheet again and now it works fine

Can actually get db finished now

 

[Walshie1987]

Got Problems again, i entered the code and it works fine on the test sheet, but when i put it into my new sheet i get the same problem!

Any ideas??

 

[Remou]

It seems that there may be a problem with the control names. Try cutting and pasting from the new sheet to the test sheet until running the code on the test sheet falls over. You should then have the name of the control that is causing the problem.

It is probably best to put Me. (me dot) before each ReqNumber in the code, first. I suppose you do not have any other code on the new sheet and that it is single form, not continuous form or datasheet?

 

[Walshie1987]

Yeah its a single form, i think i will have to go throught the code line by line like you said and try and find whats wrong with it

Thanks alot for your help

 

[Remou]

I do not think it is the code, unless you have added additional code. I think it is a control. The test form works, which shows the code works. It only fails on the new form, which seems to say that you have, for example, an unbound control with the same name as a field, or such like. Therefore I suggest cutting and pasting controls from the new form to the test form. You can select and paste them all at once, then test if the code works, if it does not, start deleting controls until you reach a point where the code works again. Alternatively, look for conflicting controls, oh, and make sure that everything compiles.