How to invoke error meesage

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that i enter records with a lot of fields. I start with a
StyleNo and the color which has referential integrity. If I entered a style
w/ a color that is not entered in the referenced table, a error message comes
out, but only after you have finished entering that record. How can be
alerted of the error as soon as I entered the style and color (primary index
,1st and 2nd field) This way I can stop entering. What I did is on the second
field I entered a requery comand on the after update event. This is okay, if
the style and color was not enter - an errod dialog
box appears, if I re-enter a color that is actually in the table, it
automaticall reindex
and it goes wherever it goes, it does not stay as the last record being
entered.
Is there a better way of making the rror mesage appear and not lose focus or
place. TIA for any input.
 
Albert,

Here's an idea: use an event of the second control, like Before Update
or After Update, to run some simple code to DCount the number of records
in the table that match both; if 0, which means illegal combination,
display the error message you want.

HTH,
Nikos
 
Nikos,
Sounds like an idea that would work. However I am not yet an advanced user
of Access. I an not exactly a beginner, but I still have so much to learn.
Would you be
so kind to give me a coding example that I can use as a guide.
Many Thanks,

Albert
 
Albert,

It would actually be easier and safer to attempt the real thing, if you
gave me the names of the controls, table and fields involved.

Nikos
 
Hi Nikos,
Was out of the office for a while. I hope you still follow the thread.
Okay. The form that I am filling up the data.. StyleColorEntryForm
the Ist field is StyleNo (Numeric). It has referential integritiy with
field StyleNumber(Numeric) in Products Table.

What I want to happen is, after I type a style no in the StyleColorEntryForm
that has not been entered yet in the Products table, an alert or error message
should pop up, so I don't continue up to the end.

Thanks ,

Albert
 
Albert,

Am I right to assume the controls on the form have the same name as the
table fields they are bound to? I will for the sake of the exercise.

Put this code in the Before Update event of the StyleNo control:

Dim vCount As Integer
Dim vMsg As String
'Dim vNewStyleNo As Long
vCount = DCount("*", "Products", "StyleNo = " & Me.StyleNo)
If vCount = 0 Then
vMsg = "Style No:" & Chr(13) & & Chr(13) Me.StyleNo
vMsg = vMsg & Chr(13) & Chr(13) & "does not exist."
MsgBox vMsg, vbCritical, "Error!"
'vNewStyleNo = Me.StyleNo
Me.StyleNo = Null
Me.StyleNo.SetFocus
'DoCmd.OpenForm "frmProductEntry", , , , acFormAdd
'Forms!frmProductEntry.StyleNo = vNewStyleNo
End If

Alternatively, you could use a combo box instead of a text box for
StyleNo, make its rowsource the StyleNo field in Products, set its Limit
To List property to Yes, and utilise the Not In List event to fire code
to open the Products entry form and copy the StyleNo value entered.
You could do the same in the former approach as well (uncomment the
commented lines, and replace the form name with the actual one); the
added merit of the latter is that you can have two columns display in
the combo, StyleNo and description, so it makes it easier to look for a
particular style number, rather than having to remember it and type it in.

HTH,
Nikos
 
Nikos,

Many Thanks,

Albert

Nikos Yannacopoulos said:
Albert,

Am I right to assume the controls on the form have the same name as the
table fields they are bound to? I will for the sake of the exercise.

Put this code in the Before Update event of the StyleNo control:

Dim vCount As Integer
Dim vMsg As String
'Dim vNewStyleNo As Long
vCount = DCount("*", "Products", "StyleNo = " & Me.StyleNo)
If vCount = 0 Then
vMsg = "Style No:" & Chr(13) & & Chr(13) Me.StyleNo
vMsg = vMsg & Chr(13) & Chr(13) & "does not exist."
MsgBox vMsg, vbCritical, "Error!"
'vNewStyleNo = Me.StyleNo
Me.StyleNo = Null
Me.StyleNo.SetFocus
'DoCmd.OpenForm "frmProductEntry", , , , acFormAdd
'Forms!frmProductEntry.StyleNo = vNewStyleNo
End If

Alternatively, you could use a combo box instead of a text box for
StyleNo, make its rowsource the StyleNo field in Products, set its Limit
To List property to Yes, and utilise the Not In List event to fire code
to open the Products entry form and copy the StyleNo value entered.
You could do the same in the former approach as well (uncomment the
commented lines, and replace the form name with the actual one); the
added merit of the latter is that you can have two columns display in
the combo, StyleNo and description, so it makes it easier to look for a
particular style number, rather than having to remember it and type it in.

HTH,
Nikos
Click to expand...
 
Back
Top