Adding a field automatically

[fishqqq]

This code below works perfectly if i remove the
"Forms![fCurrentUser]![USER CODE] & "'" . what i'm trying to do is have
the "user code" put beside the Now(). The user code field is in a
subform [fCurrentUser]. bust i still can't get this to work properly.

please help.

Private Sub Command232_Click()
Me.Notes__internal_tracking_ = Now() & Forms![fCurrentUser]![USER CODE]
& "'" & vbCrLf & Me.Notes__internal_tracking_
Me.Notes__internal_tracking_.SetFocus
Me.Notes__internal_tracking_.SelStart = 19
End Sub

 

[Klatuu]

You need to preface the name of a subform with the parent form name:
Forms![MainFormName]![fCurrentUser]![USER CODE]

 

[fishqqq]

thanks it worked perfectly

 

[fishqqq]

in the line below the cursor will be placed at the 19th space. is it
possible to instruct the cursor to go to the end , regardless if the
end is the 19th or 21st space? the now() function will change the
required position depending on the date and time etc...

thanks

Me.Notes__internal_tracking_.SelStart = 19

2. Klatuu
Jan 18, 1:12 pm show options

Newsgroups: microsoft.public.access.formscoding
From: Klatuu <[email protected]> - Find messages by this
author
Date: Wed, 18 Jan 2006 10:12:09 -0800
Local: Wed, Jan 18 2006 1:12 pm
Subject: RE: Adding a field automatically
Reply | Reply to Author | Forward | Print | Individual Message | Show
original | Report Abuse

You need to preface the name of a subform with the parent form name:
Forms![MainFormName]![fCurrentUser]![USER CODE]



- Hide quoted text -
- Show quoted text -

This code below works perfectly if i remove the
"Forms![fCurrentUser]![USER CODE] & "'" . what i'm trying to do is have
the "user code" put beside the Now(). The user code field is in a
subform [fCurrentUser]. bust i still can't get this to work properly.

please help.

Private Sub Command232_Click()
Me.Notes__internal_tracking_ = Now() & Forms![fCurrentUser]![USER CODE]
& "'" & vbCrLf & Me.Notes__internal_tracking_
Me.Notes__internal_tracking_.SetFocus
Me.Notes__internal_tracking_.SelStart = 19
End Sub

Reply

 

[fishqqq]

Can you tell me how i would add to the following code so that another
field [notes] would also be updated at the same time. I want the
[notes] field to say something similar to [Notes_internal_tracking] but
slightly different.

here's what i have so far...

- Tks

Private Sub Combo235_AfterUpdate()
Me.Notes__internal_tracking_ = Now() & " - " & Forms![Shipment Tracking
II]![fCurrentUser]![USER CODE] & " - " & "*** " & [Combo235] & " ***" &
" - " & "" & vbCrLf & Me.Notes__internal_tracking_

Me.Notes__internal_tracking_.SetFocus
Me.Notes__internal_tracking_.SelStart = 27

Me.Notes_ = Now() & " - " & Forms![Shipment Tracking
II]![fCurrentUser]![USER CODE] & " - " & "*** " & [Combo235] & " ***" &
" - " & "" & vbCrLf & Me.Notes_

Me.Notes_.SetFocus
Me.Notes_.SelStart = 27
End Sub





You need to preface the name of a subform with the parent form name:
Forms![MainFormName]![fCurrentUser]![USER CODE]

 

[John Vinson]

in the line below the cursor will be placed at the 19th space. is it
possible to instruct the cursor to go to the end , regardless if the
end is the 19th or 21st space? the now() function will change the
required position depending on the date and time etc...

thanks

Me.Notes__internal_tracking_.SelStart = 19

Use Len(Me!Notes__internal_tracking) + 1 instead of 19.

John W. Vinson[MVP]

 

[fishqqq]

Use Len(Me!Notes__internal_tracking) + 1 instead of 19.

John W. Vinson[MVP]


John, not sure what you mean. changed the line as you suggested and now
get a syntax error? Did i misunderstand you?

Private Sub Combo235_AfterUpdate()
Me.Notes__internal_tracking_ = Now() & " - " & Forms![Shipment Tracking
II]![fCurrentUser]![USER CODE] & " - " & "*** " & [Combo235] & " ***" &
" - " & "" & vbCrLf & Me.Notes__internal_tracking_

Me.Notes__internal_tracking_.SetFocus
Len(Me!Notes__internal_tracking_) + 1



DoCmd.RunMacro "Shipment Tracking.update status"

End Sub

 

[Douglas J. Steele]

I'm sure John meant:

Me.Notes__internal_tracking_.SelStart = Len(Me!Notes__internal_tracking) + 1

 

[fishqqq]

I tried what you suggested and i get a new error: stating that it can't
find the field 'Notes__internal_tracking' referred to in the
expression.

i notice that the __ is actually doubled in the expression in places
and _ not in others? not sure why but i tried playing around with that
without success. Does anything look obvious to you?
thanks


Private Sub Combo235_AfterUpdate()
Me.Notes__internal_tracking_ = Now() & " - " & Forms![Shipment Tracking
II]![fCurrentUser]![USER CODE] & " - " & "*** " & [Combo235] & " ***" &
" - " & "" & vbCrLf & Me.Notes__internal_tracking_

Me.Notes__internal_tracking_.SetFocus
Me.Notes__internal_tracking_.SelStart =
Len(Me!Notes__internal_tracking) + 1



DoCmd.RunMacro "Shipment Tracking.update status"

End Sub

 

[John Vinson]

i notice that the __ is actually doubled in the expression in places
and _ not in others? not sure why but i tried playing around with that
without success. Does anything look obvious to you?
thanks

I was copying your message... I thought!... and may have done so
incorrectly. The fieldname in the expression should be the fieldname
in the table, whatever it is.

Just FWIW, I find excessive underscores confusing; I prefer to use
"camel case", e.g. NotesInternalTracking. But if it works for you,
fine - just use the same fieldname consistantly wherever you use it.

John W. Vinson[MVP]

 

[fishqqq]

I was copying your message... I thought!... and may have done so
incorrectly. The fieldname in the expression should be the fieldname
in the table, whatever it is....

John, i still get the same error:can't find the field Notes internal
tracking...
as you can see the fieldname is correct. Could there be another problem
?
Tks - Steve

Private Sub Combo235_AfterUpdate()
Me.Notes__internal_tracking_ = Now() & " - " & Forms![Shipment Tracking
II]![fCurrentUser]![USER CODE] & " - " & "*** " & [Combo235] & " ***" &
" - " & "" & vbCrLf & Me.Notes__internal_tracking_

Me.Notes__internal_tracking_.SetFocus
'Me.Notes__internal_tracking_.SelStart = 27
Me.Notes__internal_tracking_.SelStart =
Len(Me!Notes__internal_tracking) + 1


DoCmd.RunMacro "Shipment Tracking.update status"

End Sub

 

[John Vinson]

John, i still get the same error:can't find the field Notes internal
tracking...

Then there must not be a field of that name in your table.

Does your table fieldname have the underscores, or is the field
actually named

Notes internal tracking

? If so, you must leave OUT the underscores, and instead enclose the
fieldname in square brackets:

Private Sub Combo235_AfterUpdate()
Me![Notes internal tracking] = Now() & " - " & _
Forms![Shipment Tracking II]![fCurrentUser]![USER CODE] & _
" - " & "*** " & Me![Combo235] & " ***" & _
" - " & vbCrLf & Me![Notes internal tracking]

Note: I changed [Combo235] to Me![Combo235] and removed some redundant
code (there's no need to concatenate "", that's an empty string and
adds nothing to the result).

I'm a bit concerned at what you're doing: it looks like this Notes
field is redundantly storing a bunch of redundant data, and that it
will keep getting bigger and bigger every time you select the combo
box. Are you perhaps using a Memo field where you should instead be
using a Subform based on a related table, with fields for the date,
the user code, and whatever field Combo235 represents?

John W. Vinson[MVP]

 

[fishqqq]

I'm a bit concerned at what you're doing: it looks like this Notes
field is redundantly storing a bunch of redundant data, and that it
will keep getting bigger and bigger every time you select the combo
box. Are you perhaps using a Memo field where you should instead be
using a Subform based on a related table, with fields for the date,
the user code, and whatever field Combo235 represents?




John the form IS a MEMO field (so is Notes internal tracking)
what happens is the internal tracking notes are capturing the user's id
and the Notes memo field is what the customer will see (all the data
execpt the user's id)
what this is is a tracking feature for a shipping company. so we can
email our customers and let them know the status of their shipments (as
well as track other misc internal notes on the shipments)

Does the memo field cause a problem???
you see now why we need two memo fields (internal and customer
viewing).

tks

 

[John Vinson]

I'm a bit concerned at what you're doing: it looks like this Notes
field is redundantly storing a bunch of redundant data, and that it
will keep getting bigger and bigger every time you select the combo
box. Are you perhaps using a Memo field where you should instead be
using a Subform based on a related table, with fields for the date,
the user code, and whatever field Combo235 represents?




John the form IS a MEMO field (so is Notes internal tracking)

No. The FORM is not a Field, nor is the Form a Table.

The Form is a *TOOL* - a window which lets you intract with data in a
table. Don't mix levels, even in your mind - it will lead to using the
program incorrectly because of your incorrect mental model!

what happens is the internal tracking notes are capturing the user's id
and the Notes memo field is what the customer will see (all the data
execpt the user's id)
what this is is a tracking feature for a shipping company. so we can
email our customers and let them know the status of their shipments (as
well as track other misc internal notes on the shipments)

Does the memo field cause a problem???
you see now why we need two memo fields (internal and customer
viewing).

If - as it appears - you are capturing multiple notes, each date and
time stamped, I would suggest that you in fact should NOT use a Memo
field.

Instead, use a Notes table related one to many to the shipment table.
It would be joined by the unique ShipmentID; there'd be a Date/Time
field defaulting to Now() to automatically capture the time that the
note was entered; and a Text or Memo field to enter *that individual
note*. The Notes table could also have a yes/no field indicating
whether the customer should see the note, or not.

You can easily generate an Email based on a Report showing *multiple
records* from this table - all the notes for that user.

Storing multiple ID's, dates, times, and notes in a single field is
NOT good design. Fields should be "atomic" - have only ONE value. Just
because you can store multiple disparate kinds of information and
multiple rows of text in a memo field does not mean that it's a good
idea!!!


John W. Vinson[MVP]