Customizing message box control

[svbl]

Hello. I have a text search form. When I enter in a value (text value) that pertains to a certain control on my form and press search, if such a record exists, the information in the controls will change accordingly to my searched text. It's a search control that only relates to that form.

The problem is, I'd like to NOT have the other controls visible until I press search AND ONLY BE VISIBLE if there is a record for that search. If there is no record for that search, the controls would NOT pop up and instead a popup message box would say something like, Record does not exist, would you like to create a new record for this person? yes, no command buttons.

I tried the putting this code in the after update of the control where you'd type in the value you want to search

If Me.txtSearch2 = True Then
        Me.FirstName.Visible = True
    Else
        Me.FirstName.Visible = False
    End If

After pressing search, if there is no record for that person I searched for, a msg box appears saying, no such record exist. I'd press ok and immediately the control, FirstName, (which I wanted to stay hidden until a record DOES exist) appears obtaining some other name (the name of the FIRST person I entered into my database).

help anyone?

Also, how would I create a multiple criteria search control?

such as I want to search for Name AND Date of Birth.

Thanks in advance for any help.

 

[Tim L]

Hi svbl,

This presumes that you are using a filter to display the records which meet your search criteria.

The following code will detect if there are records to display, display a text box informing the user of this and give them the options.

[COLOR="blue"]Private Sub[/COLOR] Form_Current()

    [COLOR="blue"]If [/COLOR]Me.RecordsetClone.RecordCount < 1 [COLOR="blue"]Then[/COLOR]
            [COLOR="SeaGreen"]' No records: code to hide the controls[/COLOR]

            [COLOR="blue"]If [/COLOR]MsgBox("There are no records to display." & vbCrLf & vbCrLf & _
                      "Do you wish to create a new record?", vbYesNo, "No records") _
                      = vbYes [COLOR="blue"]Then[/COLOR]
                [COLOR="seagreen"]' code if they answer Yes, i.e: to add a new record[/COLOR]

            [COLOR="blue"]Else[/COLOR]
                [COLOR="seagreen"]' code if they answer No[/COLOR]

 [COLOR="blue"]           End If

        Else[/COLOR]
            [COLOR="SeaGreen"]' Records: code to show the controls[/COLOR]
[COLOR="Blue"]        End If

End Sub[/COLOR]

You need to fill in the four areas of code, according to what you want to do.

With regards to hiding and showing the controls, I'd probably write a small sub, which requires a boolean operator (True/False) to do this. You can then call the same sub to hide/show the controls as required.

[COLOR="blue"]Private Sub [/COLOR]showTheControls(blnHideShow [COLOR="Blue"]As Boolean[/COLOR])
[COLOR="SeaGreen"]    ' To hide the controls pass False to the sub
    ' e.g: CALL showTheControls(False)
    ' To show the control pass True to the sub
    ' e.g: CALL showTheControls(True)
    
    'list each of the controls to be hidden/shown
    ' e.g: Me.controlName.Visible = blnHideShow[/COLOR]

[COLOR="blue"]End Sub[/COLOR]

With regards to the multiple criteria, I would use a query that checks against the required fields. My initial thoughts would be to use the Record Source for the form, and add the two search boxes to the criteria.

You could have each of the search boxes Requery the main form in their After Update event, so the filtering would be applied as you go, rather than together. Or use the 'search' button to do the Requery as you may currently be doing.

It would be a good idea to have 'cancel search' (remove) filter button. Get it to empty the search criteria boxes and requery the form. You could hide this button until there is something to filter: in the code for your search button, or in the After Update of the search criteria text boxes, put a .Visible=True line for the remove filter button.

In your code to add a new record you should probably remove the filter as well.

'I'd press ok and immediately the control, FirstName, (which I wanted to stay hidden until a record DOES exist) appears obtaining some other name (the name of the FIRST person I entered into my database).'

If you do not want to show all of the controls at once, using the subroutine method I demonstrate, then you will need to make the first control visible in the 'add a new record' section of the first bit of code, above. You will then need to make each subsequent control visible as data is entered. I've done this myself, in order to guide users through a fairly complicated form. It can get quite tricky, especially handling 'cancel' or 'undo' actions.

As for the 'obtaining....' bit, I really don't understand what you mean here. If you meant the value from the search text box all that you need to do, in the code for add a new record, is make the value of the destination text box = to the value in the search box,

Me.txtDestinationBox.Value = Me.txtSearchCriteriaBox.Value

HTH

Tim