Seting Focus to Text Box after MsgBox displayed?

[Draug]

Greetings,

On a certain form, I have a text that a user can enter a code into. In the after_Update function for that box, the code entered is used in a query, of which, the result populates a name field for the code entered. If no name is returned by the query, then the code entered is invalid.

When an invalid code has been entered, so no name can be retrieved, I would like to alert the user with a message box, and set the focus back to the code field so the user can key in a corrected code.

There seems to be something about popping up the message box that prevents the focus from being set to the code text field.

How can I do this???

My code currently is as follows:
'do query:
'SELECT NAME
'FROM POOL_INFO
'WHERE POOL_INFO.CODE = Me.txtPoolCode.Value

If RS.RecordCount > 1 Then
Me.txtPoolName.Value = RS!NAME
Else
Me.txtPoolName.Value = ""
MsgBox ("A valid Pool Name could not be retrieved using the Pool Code entered. Please ensure that the Pool Code entered is correct.&quot
Me.txtPoolCode.SetFocus
End If


Thank you,
Draug

 

[aggieval]

I don't know if this will help, but I had a similar problem and ended up working around it by putting code in the next field that got the focus (in the on Got Focus Event) that is the previous field was null (I had set field value to null if invalid) the to set focus back to the field.

Good Luck
Tracy

 

[Draug]

Tracy, yes, I could do that. But, is there not a more elegant way to do it?

I mean, validating data, alerting the user to an error, and then setting the cursor to the place that needs correction is a simple (in concept) thing to do. Can Access provide us no way of doing such a thing?

Thanks,
Jason

 

[Junior1544]

I know at the Table level, I use validation rules, and Validation text... the rule will cause an error if i try to put in data that doesn't fit with the rule, and the text is the error that get's produced...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[gol4]

Its best to use the before update event then you can cancel the event

heres example of code

Private Sub ROOMNum_BeforeUpdate(Cancel As Integer)
Dim strsql As String
Dim rst As Recordset
strsql = "select * from tblloc where '" & Me!ROOMNum & "' between tblloc.locrmstart and tblloc.locrmend"
Set rst = CurrentDb.OpenRecordset(strsql)
If rst.EOF Then
MsgBox "Room don't exist"
Me.Undo
DoCmd.CancelEvent
Else
rst.MoveFirst
Me!ROOMLOC = rst!loccode
rst.Close
Set rst = Nothing
End If
End Sub

 

[jebry]

Hi!

Do this in the before update event procedure of the txtPoolId text box:

Dim rst As DAO.Recordset
Dim sql As String

sql = "Select Name From Pool_Info Where Pool_Info.Code = '" & txtPoolId.Text "'"

Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset)

If rst.BOF = True and rst.EOF = True Then
MsgBox ("A valid Pool Name could not be retrieved using the Pool Code entered. Please ensure that the Pool Code entered is correct.&quot
txtPoolId.Undo
Cancel = -1
Else
txtPoolName = rst!Name
End If

Set rst = Nothing

hth
Jeff Bridgham
bridgham@purdue.edu