Message when combo used after first time

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

Guest

Hi.

Is it possible to get Access to recognise when a combo has been used more
than once, so that any selections made after the first one cause a message
box warning to appear? My combo causes a text field to be updated at the same
time as the combo selection is made and I want to remind the user about this.
The combo is on a subform set to continuous view and I'm using Access 2000 on
XP.

Thanks, JohnB
 
Create on the form decleration a variable that will hold the original value
of the combo
Dim MyComboVar as ...

On the Load event assign the value from the combo in the variable
MyComboVar = Me.ComboName

On the after update event of the combo, check if the value has been changed
since the form was loaded
If MyComboVar <> Me.ComboName then
msgbox "Combo been changed"
End If
 
Thanks for this and sorry for this late reply - our system was off the air
for a while.

I understand the second part of your suggestion but I don't understand the
first part - "Create on the form decleration a variable ......." Could you
please expand on that a bit for me - I don't know where I should put the code
and what I should put in place of the ... in "Dim MyComboVar as ... "

Thanks again for the help, JohnB
 
When you open the code under the form, the top line sais that

Option Compare database
Under this line declare the variable

Dim MyComboVar
 
Hi again and thanks for the reply. I think I'm still missing something here.
I've done as you say but nothing has changed - the combo behaves just as it
did before. Is there something else I should be adding to the "Dim
MyComboVar" line? Here is a copy of the code I've changed. Thanks again, JohnB

Code at the top of the forms code:

Option Compare Database
Dim MyComboVar


On Load Event code:

Private Sub Form_Load()
MyComboVar = Me.cboSchoolName
End Sub


After Update Event for the combo:

Private Sub cboSchoolName_AfterUpdate()
If MyComboVar <> Me.cboSchoolName Then
MsgBox "Combo been changed"
End If

Me!txtPlacementStartYear = Me.cboSchoolName.Column(3)
Me.cboMentor.Requery
Me.cboProfMentor.Requery
End Sub
 
If you copy the code I provided you with, and made changes to the names, its
not enough.
Open the form in design view, check the fields and form properties, do you
have it display there that the code should be called?
If not press on the button on the right and select code.
 
Hi. Hmmmm. Not sure if I know what you mean. I created an On Load Event for
the form and added the code you suggested. Then I added code to the already
existing After Update Event for the combo. Then I added the "Dim MyComboVar"
line at the top of the code window. So, yes, I'm used to creating Events and
these are all proper Events. O.K. I've probably done something wrong and just
can't can't see it. I'll have a play around some more tomorrow - perhaps by
intentionally using wrong names etc. to make sure the code is actually being
called. Any other suggestions would be welcome. Thanks again,JohnB
 
Use a code break in the "If MyComboVar <> Me.cboSchoolName Then" line (press
F9, and run the form, select a value from the combo, if the code stop there,
then step through the code using the F8 key, see what happen.
 
Hi again.

Well, I sort of knew I was doing something wrong. It turns out that, when I
was testing it, I was reselecting the same school in the combo box. Your code
works if I select a different school to the one already shown but not if I
select the same school. I would want the message to show in either case.
Another complication - the combo sits on a subform on a form that's on a
control on a Tab Form. So the On Load Event for the form only works once per
session so your code only works when a user selects a different school in the
combo for the first time after the Tab Form is opened.

Is there any way to solve both of these issues?

Thanks for sticking with me on this one and sorry for the confusion. JohnB
 
Or, you can use that
Option Compare Database
Dim MyComboVar


On Load Event code:

Private Sub Form_Load()
MyComboVar = Me.cboSchoolName
End Sub


After Update Event for the combo:

Private Sub cboSchoolName_AfterUpdate()
If MyComboVar <> Me.cboSchoolName Then
MsgBox "Combo been changed"
End If
MyComboVar = Me.cboSchoolName
 
I thought that there is no point giving a message when the same school has
been selected, If that what you want then to your code you can make few small
changes
Option Compare Database
Dim MyComboVar as Integer


On Load Event code:

Private Sub Form_Load()
MyComboVar = 0
End Sub


After Update Event for the combo:

Private Sub cboSchoolName_AfterUpdate()
If MyComboVar > 0 Then
MsgBox "Combo been changed"
else
MyComboVar = 1
End If
 
Hi. Very close now but not quite there yet.

When I use the code you suggest below, it doesn't work the first time I make
a selection in the combo when a schoolname is already there - either when I
select the same school or another. However, it does work perfectly the second
time I make a selection and every time after that.

When I use the suggestion in your last post, it does work the first and
subsequent times but not when I select the same school as is already in the
combo.

Any further thoughts? Thanks again, JohnB
 
Try ths:

Option Compare Database
Dim MyComboVar as Integer
Dim MyComboStr

On Load Event code:

Private Sub Form_Load()
MyComboVar = 0
MyComboStr = Me.ComboName
End Sub


After Update Event for the combo:

Private Sub cboSchoolName_AfterUpdate()
If MyComboVar > 0 or Not IsNull(MyComboStr) Then
MsgBox "Combo been changed"
else
MyComboVar = 1
End If
 
Hi again.

Sorry, but I won't be able to try out your latest suggestion until tomorrow
(Tuesday 4th Oct). I will reply ASAP. Thanks again for the help. JohnB
 
Hi again. Sorry it's taken so long to get back to you.

O.K. Still a little bit to go. Everything now works fine for an existing
record - when the same school, or a different school is selected in the
combo, the message appears. The only problem now is that when a new record is
created and the combo is used for the first time, the message appears. I
would like the message not to appear if the combo starts off blank. From what
I understand of your code, the message should not appear in this case. I've
tried using your After Update Event code as Before Update code instead but
the same thing happens.

Any further ideas? Thanks again, JohnB
 
Try and move this code
MyComboVar = 0
MyComboStr = Me.ComboName

To the on current event of the form
 
Success! That got it - it's now working perfectly. If the users select the
same or a different school in an already completed combo, they are given the
message. Otherwise, no message. Excellent.

Many thanks for all the help.

Regards, JohnB
 
Back
Top