Event after update of last record in subform

  • Thread starter Thread starter Robin S.
  • Start date Start date
R

Robin S.

I have a subform which lists specifications for a product. The user
enters the specification, hits enter, and the code below concentenates
the name of the spec, the value, and the prefix/suffix as required.
Multiple specs are strung together into a single memo field.

Everything works fine until the last record on the subform. The event
fires, but (I assume) because the last record does not lose the focus,
the value of the last spec is not updated in the concentenated memo
field.

If the code is run after focus is moved off the last spec, the spec
updates as it should.

Any thoughts on how to do this simply? I can't detect when enter is
used after updating the last record (as opposed to using enter on the
second last record, moving to the last record, and then firing the
enter event - which is what seems to happen naturally).

Thoughts? Code below sig line.

Regards,

Robin



Private Sub UpdateConcSpecs()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim varConcat As Variant

'Concentenated specification proceedure

Set db = CurrentDb
strSQL = "SELECT AutoProductNumber, ConcValue FROM
qryProClassJoinDetails WHERE AutoProductNumber = " &
Eval("Forms.frmProductSpecifications.AutoProductNumber")
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
varConcat = Null
'Make sure records exist
With rs
If .RecordCount <> 0 Then
'start concatenating records
Do While Not rs.EOF
varConcat = varConcat & rs("ConcValue") & "<br> "
.MoveNext
Loop
End If
End With

Forms!frmProductSpecifications.ConcSpecDescription = varConcat

Exit_UpdateConcSpec:
Set rs = Nothing: Set db = Nothing
Exit Sub

Err_UpdateConcSpecs:
Resume Exit_UpdateConcSpec
 
I'm not sure I can envision a way to have Access "know" when <Enter> means
"I'm all done entering specs", vs. "I'm done with THIS spec, start me a new
one".

Another approach might be to force the user to move elsewhere, either to a
new "spec" row, or some place else. You could do this by setting the
"Cycle" property to "Current Record". That way, the user would control when
the spec was finished, rather than simply hitting <Enter>.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I'm not sure I can envision a way to have Access "know" when <Enter> means
"I'm all done entering specs", vs. "I'm done with THIS spec, start me a new
one".
Click to expand...

Jeff,

My bad. I didn't claify. The subform does not allow new entries. All
of the records were created when the product was created. They just
sit there with a field blank for each spec until the user enters the
value.

I just wanted to be able to fire an event when the user hits [Enter]
after editing the last record. Ideally, I could move the focus to the
first record (thus moving off the last record, and allowing the
concentenating code to work correctly).

My major issue is that the [Enter] event for both the second last
record and the last record seems to occure interchangeably. I can't
tell if the user was already on the last record, or if they were on
the second last record.

I was comparing the Me.CurrentRecord and
RecordsetClone.AbsolutePosition (after .MoveLast) to detect which
record has focus.

Another approach might be to force the user to move elsewhere, either to a
new "spec" row, or some place else. You could do this by setting the
"Cycle" property to "Current Record". That way, the user would control when
the spec was finished, rather than simply hitting <Enter>.
Click to expand...

I want to keep the keystrokes to an absolute minimum. We'll be
entering thousands of products and every keystroke is important.

Thanks very much for your time.

Regards,

Robin
 
Robin,

Can't say that I would encourage this train of thought. I never like to use
a memo field to store data that should logically be in some other form of
details table (in this case tbl_SomethingSpecDetails).

However, if you are set on using this technique, I would add a command
button to the subforms footer that says something like "Post records", which
posts your records and then clears out the Specs subform. This way, the user
can post the Specs at any time, not just after he has made changes to the
"last" record on the subform.

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


Robin S. said:
I'm not sure I can envision a way to have Access "know" when <Enter> means
"I'm all done entering specs", vs. "I'm done with THIS spec, start me a new
one".
Click to expand...

Jeff,

My bad. I didn't claify. The subform does not allow new entries. All
of the records were created when the product was created. They just
sit there with a field blank for each spec until the user enters the
value.

I just wanted to be able to fire an event when the user hits [Enter]
after editing the last record. Ideally, I could move the focus to the
first record (thus moving off the last record, and allowing the
concentenating code to work correctly).

My major issue is that the [Enter] event for both the second last
record and the last record seems to occure interchangeably. I can't
tell if the user was already on the last record, or if they were on
the second last record.

I was comparing the Me.CurrentRecord and
RecordsetClone.AbsolutePosition (after .MoveLast) to detect which
record has focus.

Another approach might be to force the user to move elsewhere, either to a
new "spec" row, or some place else. You could do this by setting the
"Cycle" property to "Current Record". That way, the user would control when
the spec was finished, rather than simply hitting <Enter>.
Click to expand...

I want to keep the keystrokes to an absolute minimum. We'll be
entering thousands of products and every keystroke is important.

Thanks very much for your time.

Regards,

Robin
Click to expand...
 
Robin

I'm with Dale on what seems to be the notion of "stuffing" a lot of items
into a single memo field. Why not use a one-to-many relationship and store
each item in its own record?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Dale Fye said:
Robin,

Can't say that I would encourage this train of thought. I never like to
use
a memo field to store data that should logically be in some other form of
details table (in this case tbl_SomethingSpecDetails).

However, if you are set on using this technique, I would add a command
button to the subforms footer that says something like "Post records",
which
posts your records and then clears out the Specs subform. This way, the
user
can post the Specs at any time, not just after he has made changes to the
"last" record on the subform.

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


Robin S. said:
I'm not sure I can envision a way to have Access "know" when <Enter>
means
"I'm all done entering specs", vs. "I'm done with THIS spec, start me a
new
one".
Click to expand...

Jeff,

My bad. I didn't claify. The subform does not allow new entries. All
of the records were created when the product was created. They just
sit there with a field blank for each spec until the user enters the
value.

I just wanted to be able to fire an event when the user hits [Enter]
after editing the last record. Ideally, I could move the focus to the
first record (thus moving off the last record, and allowing the
concentenating code to work correctly).

My major issue is that the [Enter] event for both the second last
record and the last record seems to occure interchangeably. I can't
tell if the user was already on the last record, or if they were on
the second last record.

I was comparing the Me.CurrentRecord and
RecordsetClone.AbsolutePosition (after .MoveLast) to detect which
record has focus.

Another approach might be to force the user to move elsewhere, either
to a
new "spec" row, or some place else. You could do this by setting the
"Cycle" property to "Current Record". That way, the user would control
when
the spec was finished, rather than simply hitting <Enter>.
Click to expand...

I want to keep the keystrokes to an absolute minimum. We'll be
entering thousands of products and every keystroke is important.

Thanks very much for your time.

Regards,

Robin
Click to expand...
Click to expand...
 
Robin,

Just to go another step in this discussion. I have been known to "display"
information in a textbox similiar to what you are describing, despite the
fact that it is stored in a details table.

For example, a client enters information into a two line textbox on a
continuous form for each observation that they make, but when I display the
main record, I have a large textbox (locked) which concatenates all of the
text from the separate records into a single "document" which is easier to
read than the continuous form.

If they want to edit the text, they click a button and the textbox is
replaced with the continuous form.

Dale


--
Email address is not valid.
Please reply to newsgroup only.


Jeff Boyce said:
Robin

I'm with Dale on what seems to be the notion of "stuffing" a lot of items
into a single memo field. Why not use a one-to-many relationship and store
each item in its own record?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Dale Fye said:
Robin,

Can't say that I would encourage this train of thought. I never like to
use
a memo field to store data that should logically be in some other form of
details table (in this case tbl_SomethingSpecDetails).

However, if you are set on using this technique, I would add a command
button to the subforms footer that says something like "Post records",
which
posts your records and then clears out the Specs subform. This way, the
user
can post the Specs at any time, not just after he has made changes to the
"last" record on the subform.

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


Robin S. said:
I'm not sure I can envision a way to have Access "know" when <Enter>
means
"I'm all done entering specs", vs. "I'm done with THIS spec, start me a
new
one".

Jeff,

My bad. I didn't claify. The subform does not allow new entries. All
of the records were created when the product was created. They just
sit there with a field blank for each spec until the user enters the
value.

I just wanted to be able to fire an event when the user hits [Enter]
after editing the last record. Ideally, I could move the focus to the
first record (thus moving off the last record, and allowing the
concentenating code to work correctly).

My major issue is that the [Enter] event for both the second last
record and the last record seems to occure interchangeably. I can't
tell if the user was already on the last record, or if they were on
the second last record.

I was comparing the Me.CurrentRecord and
RecordsetClone.AbsolutePosition (after .MoveLast) to detect which
record has focus.



Another approach might be to force the user to move elsewhere, either
to a
new "spec" row, or some place else. You could do this by setting the
"Cycle" property to "Current Record". That way, the user would control
when
the spec was finished, rather than simply hitting <Enter>.


I want to keep the keystrokes to an absolute minimum. We'll be
entering thousands of products and every keystroke is important.

Thanks very much for your time.

Regards,

Robin
Click to expand...
Click to expand...
Click to expand...
 
Robin

I'm with Dale on what seems to be the notion of "stuffing" a lot of items
into a single memo field. Why not use a one-to-many relationship and store
each item in its own record?
Click to expand...

Jeff and Dale,

You're certainly right, and each specification is actually stored
exactly as you've suggested.

The problem is that I have to output a file for web shopping cart
software, and I don't have control over how that database works. These
specs are concentencated into one "Description" field.

I like the idea of running the code just before the form is closed.
That'll do just fine.

Thanks for your help.

Regards,

Robin
 
Back
Top