I think I'm going to shoot myself over this notes box

[Guest]

I've asked this question in the new users forum and got some responses but it
didn't help my situation. Hopefully I can get an answer here. Here we go...

I'm creating a database to keep track of content for a website. There is a
lot of activity so I would like to have a notes box with a time/date stamp
which will allow me to write notes. There is a possibility other people will
be using this database so I want to make it as user-friendly as possible.

Here is the structure of my tables including the fields...

-Page: PageID(Primary Key-AutoNumber), Page Name (Text), Last Modification
Date (Date/Time), Reviewing Frequency (Text), URL (Hyperlink)

-Notes: Note ID (Primary Key-AutoNumber), Page ID (Foreign Key from Page
table), Notes (Memo), NoteDate (Date/Time)

I've created a parent form using fields from the Page table. This was ok.

I don't know what to do past this.

I would like a box where people can type a note and click on a button to
insert the note in a second box. This second box would display all of the
notes with a date/time stamp in chronological order starting from the most
recent one. I would like it so that the data in the second box never changes
(read-only). People are only allowed to insert the new note at the click of a
button. I would like that the Date/Time Stamp appear in red and there be a
marker seperating the date from the text...maybe a colon or something. In
addition, I would like it so that each note is seperated by one or two
spaces. Here is an example of what I would like:

Oct-17-2006 3:00PM: This is my second note.

Oct-16-2006 4:50PM: This is my first note.

That is exactly how I want the note to appear except the date would be in
red and the text in black.

I've read about this subject and tried several of them but I always
encountered problems. I feel comfortable using Access but when it comes to
macros, events codes...I'm a complete newby.

If you are going to write instructions, please number them. I'm sort of like
Ricky on Trailer Park Boys...I follow directions better with a numbered
list...LOL!!!

Someone please, please help me. I would appreciate this so much. You can
e-mail me at (e-mail address removed) or answer here.

I'm using version 2003.

 

[Phillip Windell]

The form would be based on only the Notes table. It doesn't sound like any
data is being altered in the Pages Table.

Also a few other things:

1. Do use spaces in object names
2. Prefix field names with "fld"
3. Prefix table names with "tbl"

tblPage
fldPageID(Primary Key-AutoNumber)
fldPageName (Text)
fldLastModificationDate (Date/Time)
fldReviewingFrequency (Text)
fldURL (Hyperlink)

tblNotes
fldNoteID (Primary Key-AutoNumber)
fldPageID (Foreign Key from Page table)
fldNotes (Memo)
fldNoteDate (Date/Time)

When opening the Form (what you call a Notes box) you would have to know the
PageID you are dealing with and then base the opening of the form on a query
like this one:

SELECT
fldNoteID,
fldPageID,
fldNotes,
fldNoteDate,
FROM
tblNotes
WHERE
tblNotes.fldPageID = <whatever>

For specifics you will have to ask others. I have general database
knowledge and general (and weak) scripting knowledge but I am not really an
"Access Guy" when it comes to the Access GUI.

 

[Guest]

To show the list of notes you can either use a separate form of a subform
within your Pages parent form which you've created. A subform is easier to
work with as you then don't have to synchronize the two separate forms. The
steps would be:

1. Create a form in continuous form view based on a sorted query on the
Notes table:

SELECT *
FROM Notes
ORDER BY NoteDate DESC;

2. Add text box controls to the form bound to the NoteDate and Notes
fields, the former on the left, the latter on the right.

3. Set the properties of the NoteDate text box as follows:

ForeColor: 255
Locked: Yes
Enabled: No
Format: mmm-d-yyyy hh:nnAM/PM

This will show the date/time in red and the user will be prevented from
accessing the control.

4. Add this form as a subform to your parent form which you can do by
dragging it from the database window and dropping it onto your parent form in
design view. To save space you might consider adding the parent form's
controls to one page of a tab control and the subform to a second page of the
same tab control. Name the subform control sfrNotes.

5. Set the LinkMasterFields and LinkChildFields properties of the subform
control on the parent form to PageID.

7. Add a command button to the parent form to open a dialogue form. For
the button's Clicjk event procedure use the following code:

DoCmd.OpenForm "frmNotesDlg", _
WindowMode:=acDialog, _
OpenArgs:=Me.Name
' requery subform when code execution resumes
' after frmNotesDlg is closed
Me.sfrNotes.Requery

8. Create the frmNotesDlg unbound form. This simply need a single deep
text box, txtNotes and a button to close the form. St its properties to not
show the navigation buttons or record selectors and set its BorderStyle
property to Dialog.

9. Set the text box's EnterKeyBehavior property to New Line in Field.

10. In the dialogue form's Close event procedure put the following code:

Dim cmd As ADODB.Command
Dim strSQL As String

If Len(Me.txtNotes & "") > 0 Then

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

' insert new row into Notes table
strSQL = "INSERT INTO Notes(NoteDate, PageID, Notes) " & _
"VALUES(#" & Format(Now(),"mm/dd/yyyy hh:nn:ss") & "#," & _
Forms(Me.OpenArgs).PageID & ",""" & Me.txtNote & """)"
cmd.CommandText = strSQL
cmd.Execute

End If

Set cmd = Nothing

11. In the Click event of the button to close the form put:

DoCmd.Close acForm, Me.Name

Ken Sheridan
Stafford, England

 

[Guest]

1. Do use spaces in object names
2. Prefix field names with "fld"
3. Prefix table names with "tbl"

I'd strongly endorse the first, but equally strongly question the last two.
The usual convention of using proper case plural or collective nouns and
lower case singular nouns for column names makes for more readable SQL.
Using 'tags' only interferes with the semantics. Joe Celko, whose views on
this have frequently been expressed in his usual forthright manner, once drew
attention in one of the old CIS forums to some psychometric research which
had been done on this at a university in the USA. Unfortunately I didn't
note the details at the time, and have not found any reference to it in his
books.

In any case, tables have columns, not fields. It was old style sequential
file systems which had fields. Similarly tables have rows not records. On
these differences Joe does have a lot to say in his books. For brief but
authoritative discussions I'd recommend a look at his 'Data and Databases:
Concepts in Practice'.

Ken Sheridan
Stafford, England

 

[Guest]

This is what I understood from your answer:

*******************************************************
1. Create a form in continuous form view based on a sorted query on the
Notes table:

SELECT *
FROM Notes
ORDER BY NoteDate DESC;

** I created the query. Here is what it looks like:
Columm 1 Columm 2
Field: Notes.* Field: NoteDate
Tables: Notes Table: Notes
Sort: Descencing
Criteria: ORDER BY NoteDate DESC;

I saved the query under the name Add Notes.

*******************************************************
2. Add text box controls to the form bound to the NoteDate and Notes
fields, the former on the left, the latter on the right.

**Done!

*******************************************************
3. Set the properties of the NoteDate text box as follows:

ForeColor: 255
Locked: Yes
Enabled: No
Format: mmm-d-yyyy hh:nnAM/PM

This will show the date/time in red and the user will be prevented from
accessing the control.

**Done! The form is saved as Comments.

********************************************************

4. Add this form as a subform to your parent form which you can do by
dragging it from the database window and dropping it onto your parent form in
design view. To save space you might consider adding the parent form's
controls to one page of a tab control and the subform to a second page of the
same tab control. Name the subform control sfrNotes.

**I did all of the above except I did not use this Tab control since I don’t
know what that is. I just added the form as a subform. However, I did not
know where to name the subform control sfrNotes because there is no property
field named control.

********************************************************
5. Set the LinkMasterFields and LinkChildFields properties of the subform
control on the parent form to PageID.

**Done!

********************************************************
7. Add a command button to the parent form to open a dialogue form. For
the button's Clicjk event procedure use the following code:

DoCmd.OpenForm "frmNotesDlg", _
WindowMode:=acDialog, _
OpenArgs:=Me.Name
' requery subform when code execution resumes
' after frmNotesDlg is closed
Me.sfrNotes.Requery

**I created the command button and name it Insert Notes. I copied this code
into the OnClick field of the command button.

********************************************************
8. Create the frmNotesDlg unbound form. This simply need a single deep
text box, txtNotes and a button to close the form. St its properties to not
show the navigation buttons or record selectors and set its BorderStyle
property to Dialog.

**I created a new unbound form named frmNotsDlg. I added the text box and
command button to exit the form. The command button is name OK. I used the
wizard to get this command button. However, I don’t know what you mean by
txtNotes so I didn’t do it…

********************************************************
9. Set the text box's EnterKeyBehavior property to New Line in Field.

**Done!

********************************************************
10. In the dialogue form's Close event procedure put the following code:

Dim cmd As ADODB.Command
Dim strSQL As String

If Len(Me.txtNotes & "") > 0 Then

Set cmd = New ADODB.Command
cmd.ActiveConnection = CurrentProject.Connection
cmd.CommandType = adCmdText

' insert new row into Notes table
strSQL = "INSERT INTO Notes(NoteDate, PageID, Notes) " & _
"VALUES(#" & Format(Now(),"mm/dd/yyyy hh:nn:ss") & "#," & _
Forms(Me.OpenArgs).PageID & ",""" & Me.txtNote & """)"
cmd.CommandText = strSQL
cmd.Execute

End If

Set cmd = Nothing

**I copied this entire code in the frmNotsDlg form’s OnClose field.

********************************************************
11. In the Click event of the button to close the form put:

DoCmd.Close acForm, Me.Name

**I copied and pasted this code into the OnClick field of the command button
OK in frmNotsDlg.

********************************************************

I’m encountering problems from what I did:

1)When I test the parent form and click on the Insert Notes command button,
I get this error: Microsoft Office Access can’t find the macro ‘DoCmd.’

2)So I went and test the frmNotsDlg form to see if it works. Nope! I get a
similar message: Microsoft Office Access can’t find the macro ‘DoCmd’ when I
type in a note and click OK.

Please help!!!

I’m sorry this is so longer but I would really like to have this working. I
really appreciate your help.

Thanks,
Nicole D.

 

[Guest]

Nicole:

Lets take the problem areas one by one:

1. Your query is not quite right. It should be:

Columm 1 Columm 2
Field: Notes.* Field: NoteDate
Tables: Notes Table: Notes
Sort: Descending
Show: Unchecked

You don't need anything in the criteria row. If you switch to SQL view
afater setting it up in design view it should look something like the SQL I
sent you, though Access will have inserted the table name and some brackets
and parentheses, which aren't actually necessary, but do no harm.

4. A subform 'control' is the control on the parent form which houses the
subform (the subform is the underlying form object you created). If you've
created the subform by dragging and dropping it onto the parent form I think
it gives it the same name as the underlying table; if you add it from the
toolbox and then assign a form to it as its SourceObject property it calls it
something like Child1. With the main form in design view, if you select the
subform control you can then change its Name property in its properties sheet.

7. Did you copy the code straight into the On Click event property in the
button's properties sheet? If so that's not right. The code goes in the
Click 'event procedure'. To do this select the On Click event property in
the properties sheet, click on the 'build' button; that's the one on the
right with 3 dots. Select Code Builder in the dialogue. When you then OK
out of the dialogue the VBA window will open at the Click event property with
the first and last lines already in place. You paste the code as new lines
between these lines.

8. txNotes is the name of the text box you added to the form. In design
view select the text box and change its Name property in the properties
sheet. Its always a good idea to change the name of controls you add to a
form or report to something meaningful as soon as you've added them, rather
than just accepting the default name Access gives them. A word of warning,
though. Don't change the name of a control after you've entered code for one
of its event procedures. This will break the link between the control and
the code, so you'd have to recrearte the event procedure again.

10. Again, if you entered this directly in the properties sheet you need to
enter it in the event procedure using the steps described in 7 above.

11. Same again.

The reason I suspect you've entered the code directly into the properties
sheet is that this would explain the errors. You can put the name of a macro
or a VBA function in the properties sheet as an event property, but not the
code itself.

Good luck,

Ken Sheridan
Stafford, England

 

[Guest]

Ken,

I did what you said and my form is working better. However, I'm still
encountering problems. I don't know why.

1) To get the insert notes command button to work on the parent form, I had
to remove the following line of code you provided me in step 7:

Me.sfrNotes.Requery.

With the original code, when I clicked the insert command button, a box came
up and said: Compile error: Method or data member not found. When I click ok
the sfrNotes is highlighted in black. I just deleted the entire line and the
command buttons now works. ????

************************************************************

2) I get the same error message with the OK button in the frmNotesDlg box.
But the text VB highlights is .txtNote. I copied and pasted the code you
provided me with in step10 into VB.

************************************************************

Proplem #1: I think the problem with #1 is that I cannot name the
Subform/Subreport in the parent form sfrNotes. When I click on the Name
property, Access gives the default name Child94. The Source Object is
sfrNotes. So I changed the Names property to sfrNotes and closed the box, but
when I reopen it, the Name property is set back to Child94. I don’t know why
this is happening. I clicked and dragged the form from the database window to
the parent form like you said but Access still gave it this default name
Child94.

Problem #2: I don’t know why this issue is coming up. I named the textbox
txtNotes. I built this code in the OK button’s OnClick event: DoCmd.Close
acForm, Me.Name. I also added the code you provided me in step 10 in the
form’s OnClose event.

I really appreciate your help Ken. I hope I'm not being a bother.

Thanks,
Nicole

 

[Guest]

Nicole:

You are quite correct about the error on the Me.sfrNotes.Requery line being
due to the fact that you haven't been able to rename the subform control.
You need to save the main form for the change in name to stick. If you
really find you can't change it then change the line in the code to
Me.Child94.Requery so the code and the control's Name property match. If you
leave this line out the subform won't include the new record you add via the
dialogue form when you return to the main form from the dialogue.

As regards the second problem it looks as though that's simply because
you've called the text box txtNotes (plural) and the code refers to txtNote
(singular). They must be exactly the same.

Let me know how you get on. I'm leaving for Italy on Saturday morning so
will be incommunicado for a week. I'd rather not leave you in limbo with any
uncorrected bugs.

Ken Sheridan
Stafford, England

 

[Guest]

I figured out problem #2. It was the name..I wrote txtNotes instead of
txtNote. However, a new problem arises when I click OK: Run-time error
'2465': Application-defined or object-defined error. I click on Debug and the
following text in VB is highlighted:

strSQL = "INSERT INTO Notes(NoteDate, PageID, Notes) " & _
"VALUES(#" & Format(Now(), "mm/dd/yyyy hh:nn:ss") & "#," & _
Forms(Me.OpenArgs).PageID & ",""" & Me.txtNote & """)"

I'm so so so close!!!!

 

[Guest]

It's working. The problem was with PageID. I had it spelled Page ID. I've
been trying to figure this out for 3 weeks now. I bow down to thee!!!!!!!!

Thank you so much for your help.

I hope you have fun in Italy. This weekend I'm get to look forward to some
snow. The city I live in is probably colder than Moscow and St. Petersburg in
Russia...I'm in Canada. I'm wearing a winter jacket already. At least I have
the knowledge that this problem is solved!!!!!

Again, you're help is very appreciated.

Nicole

 

[Guest]

Nicole:

That's good news!

You are probably not that much further north than I am in fact; if I headed
due west I'd eventually hit Canada. The sea keeps us warm, however, so our
climate is much milder than our latitude would suggest. Hopefully in a few
days I'll be enjoying some Mediterranean sunshine!

Ken Sheridan
Stafford, England

 

[Larry Linson]

I'd strongly endorse the first,

I'd strongly disagree with the first -- that is, do NOT use spaces in object
names, because if you do, you'll have to clutter your database/code with
brackets to contain the names.

but equally strongly question the last two.
The usual convention of using proper case
plural or collective nouns and lower case
singular nouns for column names makes
for more readable SQL. Using 'tags' only
interferes with the semantics.

A few Access "authorities" do not agreee, but in the Access world, there is
an overwhelming concensus for use of the Reddick naming convention (which,
last I remember, did not really recommend the use of a prefix for Fields).
There are several "levels" that can be used, and many of us do not find that
such a convention "interferes with the semantics."

Joe Celko, . . . drew attention . . . to some psychometric
research which

I wouldn't debate Joe Celko's standing (and particularly not in this
newsgroup), except to say that he is not the be-all and end-all of "things
relational" and is not a particularly good reference for Access (which he
seems to think should be something other than what it is).

In any case, tables have columns, not fields. It was old
style sequential file systems which had fields. Similarly
tables have rows not records.

This is simply not factual. In Access, "Record" is more often used, but
"Row" is used interchangeably; "Field" is more often used, but "Column" is
used interchangeably.

There is nothing "old style" about sequential files -- they are perfectly
up-to-date, and probably the most used file format of all. That's something
like styling the English alphabet as "old-style" as though there were a
better replacement for it in writing the English language -- it's been
around a long time, but is current, accurate, and useful.

On these differences Joe does have a lot to say in his
books. For brief but authoritative discussions I'd
recommend a look at his 'Data and Databases:
Concepts in Practice'.

As I said, Celko is not a good reference for practices or facts about
Access. In fact, in his definition of relational database, the late Ed
Codd, Father of Relational Database, did not use either "tables," "rows,"
and "columns," nor "records" and "fields." I'd venture to guess that Ed
Codd and his associate Chris Date have more universal acceptance as the
"authorities of relational database" than anyone else.

But, Access occupies its own little niche, and despite the desire of some
experts to force every database to fit their own idea of what databases
should be, became the most widely-used, accepted, and popular relational
database of all. All this, even though its terminology and dialect of SQL
are unique.

Steve Roman and Rebecca Riordan have written books about relational database
design that, while not Access-specific, are more Access-applicable than the
ones you recommend.

Larry Linson
Microsoft Access MVP

 

[Tony Toews]

1. Do use spaces in object names

As Larry already stated don't.

2. Prefix field names with "fld"
3. Prefix table names with "tbl"

Don't do items 2 and 3 either. There is no need. Furthermore if you
have a lot of objects in your list it is very handy to hit the first
letter of the object and move down to it. For example P for
Purchasing. This also clutters up Intellisense and so on.

Tony's Table and Field Naming Conventions
http://www.granite.ab.ca/access/tablefieldnaming.htm

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[dbahooker]

Tony;

do you think that people listen to you?

you preach the good word of MDB I would rather eat shit than ever
listen to a single word that comes out of your mouth

****ing script kid; lose the training wheels

-Aaron

 

[Phillip Windell]

I'd strongly disagree with the first -- that is, do NOT use spaces in
object

That was a typo,...sorry,...I meant "Do use NOT spaces in object names"

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com

The views expressed are my own (as annoying as they are), and not those of
my employer or anyone else associated with me.
-----------------------------------------------------

 

[Phillip Windell]

Don't do items 2 and 3 either. There is no need. Furthermore if you
have a lot of objects in your list it is very handy to hit the first
letter of the object and move down to it. For example P for
Purchasing. This also clutters up Intellisense and so on.

I know there are other views of that. But it is a matter of opinion. You are
thinking of the Intellisense in the GUI, I am thinking of the idea of
staring at raw code with your eyeballs and being able to understand what
kind of object you are looking at by the prefix.

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com

The views expressed are my own (as annoying as they are), and not those of
my employer or anyone else associated with me.
-----------------------------------------------------

 

[Phillip Windell]

I thought it would obviously be seen as a "typo" since nothing in my example
had spaces in it.

 

[Rick Brandt]

I know there are other views of that. But it is a matter of opinion.
You are thinking of the Intellisense in the GUI, I am thinking of the
idea of staring at raw code with your eyeballs and being able to
understand what kind of object you are looking at by the prefix.

I don't use any of the common prefixes that many VB/Access developers favor. I
have never once been working on code and had a problem because I didn't know the
information that such prefixes supposedly provide. They are discouraged in just
about every other development enviroment and MS has discouraged them in its move
to dot-net.

 

[Phillip Windell]

I don't use any of the common prefixes that many VB/Access developers
favor. I have never once been working on code and had a problem because I
didn't know the information that such prefixes supposedly provide.

Yea, I suppose, but then you may have wrote the code. It might be different
if you were trying to figure out and troublshoot someone else's work. The
only development environment that I was personally aware of was an
electrical power company (Illinois Power) and they demanded those prefixes
be followed. I assume it is still that way, I don't know.

They are discouraged in just about every other development enviroment and
MS has discouraged them in its move to dot-net.

I wasn't aware of any of that.
But actually I am Networking Guy who sometimes uses MDB files in ASP Classic
web sites, so I don't consider myself a true developer. So when I respond to
posted questions (what few I do) I try to stay in the boundaries of what I
am familiar with. Anyway, with most of what I do with Access being ASP
Classic I would be struggling without the prefixes,..I use no IDE, no GUI,
no Intellesense,..just me and Notepad and if I am lucky a color coded editor
with very limited Intellesense.

My MVP is with ISA Server, but sometimes I wonder off my leash,..plus I have
a friend I know personally that had been a regular in this group.

--
Phillip Windell [MCP, MVP, CCNA]
www.wandtv.com

The views expressed are my own (as annoying as they are), and not those of
my employer or anyone else associated with me.
-----------------------------------------------------

 

[Rick Brandt]

Yea, I suppose, but then you may have wrote the code. It might be
different if you were trying to figure out and troublshoot someone
else's work. The only development environment that I was personally
aware of was an electrical power company (Illinois Power) and they
demanded those prefixes be followed. I assume it is still that way,
I don't know.

Well, I'm not religious about it. If I were in a group environment and the boss
or group consensus favored their use then I would use them. I just don't buy
into all of the productivity advantages that they are supposed to provide.