Hi Nick. Many thanks for all the work you are putting
into
this. I did as you said and created one row/record in the
table with value 0 in field StartYearChanged. When I
select a
school in the combo, the message apears as normal but
when nI
click on OK, I get a "syntax error, missing operator"
message
and when I debug, the following line is highlighted in
yelow:
DoCmd.RunSQL "UPDATE tblCountOfStartYearChanged SET
StartYearChanged = " & lngMaxCount & "WHERE
StartYearChanged
-1"
I had to correct a few line wrap errors that had been
introduced when I copied your code in, so maybe it's my
fault. The above line is all on one line.
Any ideas. Tnaks again, JohnB
:
John,
Try this, untested remember and you'll probably need to
create a row manually to begin with.
Private Sub SchoolName_AfterUpdate()
Dim lngMaxCount as Long
If Not IsNull(txtPlacementStartYear) _
And Me!txtPlacementStartYear <
DLookup("[PlacementStartYear]",
"[tblPlacementStartYear]") _
Then
MsgBox "Warning! When you click OK the Placement
Start
Year will be changed." & vbCrLf & _
"Take a note of it now and reset it manually
afterwards."
' Tables.tblCountOfStartYearChanged.[StartYearChanged] =
[StartYearChanged] + 1
'Get max existing
lngMaxCount =
DMax("StartYearChanged","tblCountOfStartYearChanged")
lngMaxCount = lngMaxCount + 1 'Increment
DoCmd.SetWarnings False 'Turn off warnings
'Now update row
DoCmd.RunSQL "UPDATE tblCountOfStartYearChanged SET
StartYearChanged = " & lngMaxCount & "WHERE
StartYearChanged
-1"
DoCmd.SetWarnings True 'Turn warnings back on
End If
Me!txtPlacementStartYear = Me.SchoolName.Column(3)
If Me.SchoolName.Column(4) = "Teaching Partnership" Then
Me.Status = "Supervisor"
Else
Me.Status = "Link Tutor"
End If
End Sub
Good luck
--
Nick Coe (UK)
http://www.alphacos.co.uk/ AccHelp + pAnimal
http://www.mrcomputersltd.com/ Repairs Upgrades
In
JohnB typed:
Thanks Nick.
"Do you need to know what that value is in different
sessions,
that is between shutting the app and re-opening it?"
Not sure what you mean by this. If you mean do I want
the
count to remain in place when all Users have exit'd
Access
then, yes I do. I want a totals figure that
accumulates.
All I
want to do is to be able to go into the database from
time
to
time to see how many times Users have caused a certain
After
Update Event to trigger. I just want to do this as
simply
as
possible, both to implement and to see.
Perhaps I should explain. Users use a certain main form
to
create subform records. On the subform, they use a
combo
to
select a School name. 'After Update' Event code ensures
that,
when they select a school, a seperate field is filled
with
a
PlacementStartYear value. What we have found is that
Users
have been going into old subform records and
reselecting
the
School name, which has the effect of changing the
PlacementStartYear field content to the current value.
I
have
now put a check in the code, so that if they do this in
an
old
record, a message appears warning them that they are
about
to
change the PlacementStartYear field value and to
remember
to
change it back.
I just thought it would be useful to create some sort
of
counter that increments by one each time they get to
stage
where the message is presented. Not something they
necessarily
have to see - just for my own use initially. For info,
here is
the code, plus my useless attempt commented out in the
middle.
Does this help?
Thanks again for the help. JohnB
Private Sub SchoolName_AfterUpdate()
If Not IsNull(txtPlacementStartYear) And
Me!txtPlacementStartYear <
DLookup("[PlacementStartYear]",
"[tblPlacementStartYear]") Then
MsgBox "Warning! When you click OK the Placement Start
Year
will be changed. Take a note of it now and reset it
manually
afterwards." '
Tables.tblCountOfStartYearChanged.[StartYearChanged] =
[StartYearChanged] + 1
End If
Me!txtPlacementStartYear = Me.SchoolName.Column(3)
If Me.SchoolName.Column(4) = "Teaching Partnership"
Then
Me.Status = "Supervisor"
Else
Me.Status = "Link Tutor"
End If
End Sub
:
To do that you could use an update query instead of
the
append. Include Doug's comment.
But why bother with a table and the overhead
associated
with
managing rows? Do you need to know what that value is
in
different sessions, that is between shutting the app
and
re-opening it?
If not, then store it in a global variable and write a
function to increment it each time.
--
Nick Coe (UK)
http://www.alphacos.co.uk/ AccHelp + pAnimal
http://www.mrcomputersltd.com/ Repairs Upgrades
In
JohnB typed:
Thanks Nick.
Not for the first time, what I think is a simple
question
turns out to be more complicated. I wasn't really
thinking
that I should create a new record each time a User
triggers
the Event Code - or that it mattered what value was
already in
field StartYearChanged. I just want to start off with
zero
in
field StartYearChanged and add one to the figure
already
there
each time the User triggers the Event. Just one
record
in
tblCountOfStartYearChanged and just one field in the
record
(StartYearChanged) whose value increments by one each
time. Is
it not easy to do that?
Thanks again, JohnB
:
You need to know what the largest existing count in
the
table is and then add one to it. Also the code you
have
there won't create a new record in your
tblCountOfStartYearChanged.
Bare bones air code:
Dim lngCount, lngMaxCount as Long
lngMaxCount =
DMax("StartYearChanged","tblCountOfStartYearChanged")
'Gets
max existing
lngMaxCount = lngMaxCount + 1 'Increment
DoCmd.SetWarnings False 'Turn off warnings
'Now append the new record
DoCmd.RunSQL "INSERT INTO tblCountOfStartYearChanged
(StartYearChanged) VALUES " & lngMaxCount & ")"
DoCmd.SetWarnings True 'Turn warnings back on
You may need to muck about with the syntax
especially
the
SQL, I can't remember if I've concatenated the value
of
lngMaxCount into the SQL correctly.
To get the SQL right you could create an append
query
using
the query designer, check that it works and then go
to
SQL
view and copy and paste it into your code.
--
Nick Coe (UK)
http://www.alphacos.co.uk/ AccHelp + pAnimal
http://www.mrcomputersltd.com/ Repairs Upgrades
In
JohnB typed:
Hi.
I want to set up a simple counter, so that I can
check
on
