Prompted Entry Not Found On Open 1

[smdemo]

I looked for a bit on the boards but could not find anything. I have a form attached to a query and when the form opens it promts for a contract number (from the query). What currently happens if the contract number is not found is a blank record appears in the form. I would like the form to cancel from opening and disply a popup to show that the contract number was not found. Can anyone help with this?

Thanks
Steve

 

[jordanking]

How is the form opened?
I would suggest taking the query parameter out and controlling the SQL string myself.

Put an open button on another form with a unbound textbox that the user can enter a contract number into. Then you can build the query string through code to test for a record and open the form or throw a pop up based on if a record exists.

On the click event of the button, get the value of the text box, if not empty, and then build your sql query. You need to understand how to open a recordset through VBA code?

.....
I'd rather be surfing

 

[smdemo]

hi jordan,

The form is opened through a switchboard I have. When a button is clicked to open the form a dialog box opens and asks for a contract number (we only want that record to show). The dialog box is attached to the query which in turn opens the form.

Thanks for your suggestion, I just got it. I was having issues with the message box too when I tried to make it vbInformation or vbOKOnly message but a plain message will work just fine.



Private Sub Form_Open(Cancel As Integer)

If Me.Recordset.RecordCount = 0 Then
MsgBox "Contract number not found."
DoCmd.CancelEvent
End If

End Sub

 

[TheAceMan1]

How are ya smdemo . . .

Post the [blue]RecordSource[/blue] of the opened form!

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[ProgramError]

Take a look at thread702-1447912 it may give you some ideas.

Ian Mayor (UK)
Program Error
Always make your words sweet and nice. Because you never know when you may have to eat them.

 

[jordanking]

chnage you code to the click event of the switchboard with a contract number text box (txtContract)


try using something like this on the click event of a cmdButton. Adjust form names etc..

Private Sub Button_Click(Cancel As Integer)

Dim rs as adodb.recordset
set rs = new adodb.recordset
dim sql as string

    If Nz(Me.txtContract, 0) < 0 Then
        sql = "SELECT * FROM tableName WHERE intConractNum = " & Me.txtContractNum
        rs.Open sql, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
        If rs.RecordCount > 0 Then
            DoCmd.OpenForm "formName"
            Forms!FormName.RecordSource = sql
        Else
            MsgBox "Contract number not found."
            Exit Sub
        End If
    End If

.....
I'd rather be surfing

 

[smdemo]

Hi guys,

That is exactly what I was looking for, I just needed to see something similar to work it into what I needed.

Thanks a million