Automatically Add New Memo Field

[Luke]

Ok I understand, is it possible to copy/paste the same VBA code from the form into the VBA code of the report? Or would that not work. Thanks.

[TheShabz]

Possible as far as being able to Ctrl+A > Ctrl+C > Ctrl+V, yea, it's possible. However, it won't do you any good. You would be making it from from scratch.

[Luke]

Hey Ryan, I've created a new database based exactly on the "Luke.mdb" you sent me a couple weeks ago. I really like the Reviews form/subform and the ease of being able to add new notes to certain entities. I'm having a major problem though. I need to be able to add a new Entity in another form so that will show up in the Reviews form&subform you created . When I try to add a new record in the Reviews form, I get a "microsoft visual basic run time error '3075' syntax error missing operator in query expression 'EntityID='.". I then have the option to "End" or "Debug". Do you know why this is happening? Also, I have relationships between the 3 tables (just like you created) so if I try to add a new Entity in another form, it will show up in my Entity table but not my Reviews table which makes no sense. Thanks.

-Luke

[TheShabz]

I would have to see it. If you hit "debug" it'll highlight the line of code in vba that is causing the error. Whatever it is, is looking for EntityID and is not finding it.

[Luke]

This is the line it highlights when I click debug:

Me.txtLastReview = DMax("ReviewDate", "tblReview", "EntityID = " & Me.cmbEntity)

[TheShabz]

does cmbEntity exist on the form and is it populated?

[Luke]

cmbEntity exits on the form but I don't know what you mean by "is it populated?"

[Luke]

This is the VBA code you sent me (I made each cmbEntity bold)

Option Compare Database
Option Explicit

Private Sub cmbEntity_AfterUpdate()

'create a string that will hold review cycle for DateAdd
Dim strReviewCycle As String
Dim strFrequency As String

'using DLookup function and qryEntityReviewCycle, find Review Cycle
Me.txtReviewCycle = DLookup("CycleDesc", "qryEntityReviewCycle")

'find what Cycle phase to use
Select Case Me.txtReviewCycle
Case "Monthly"
strReviewCycle = "m"
strFrequency = "1"
Case "Quarterly"
strReviewCycle = "m"
strFrequency = "4"
Case "Yearly"
strReviewCycle = "yyyy"
strFrequency = "1"
End Select

'using Dmax function find the date of the last review for entity selected in combobox
Me.txtLastReview = DMax("ReviewDate", "tblReview", "EntityID = " & Me.cmbEntity)

'using DateAdd function find the date of the next review for entity selected in combobox
Me.txtNextReview = DateAdd(strReviewCycle, strFrequency, Me.txtLastReview)

'update Subform
Me.subForm.Requery

End Sub

Private Sub cmdSubmit_Click()

'create a string that will hold the SQL to be run
Dim strSQL As String

'fill string with INSERT INTO syntax, referencing form controls
strSQL = "INSERT INTO tblReview (ReviewNote, ReviewDate, EntityID) " & _
"VALUES (""" & Me.txtReview & """, #" & Me.txtReviewDate & "#, " & Me.cmbEntity & ")"

'turn off notifications to run query without prompts
DoCmd.SetWarnings False

'Run generated SQL to update tblReview
DoCmd.RunSQL (strSQL)

'turn warnings back on
DoCmd.SetWarnings True

Me.subForm.Requery

End Sub

[TheShabz]

populated means the combobox contains a value. If you left it blank, it's not gonna work. Also, since you created a new db for it all, make sure all the objects involved are spelled the same and case sensitive. If your table has the field name EntityId instead of EntityID it will also fail. Check to make sure EVERYTHING is spelled exactly the same.

[Luke]

Everything is spelled correctly, but I think I was mistaken when I said "cmbEntity" exits on the form. It exists in the VBA code of the form but it is not displayed on the form itself. It's still showing "Me.txtLastReview = DMax("ReviewDate", "tblReview", "EntityID = " & Me.cmbEntity)" as the error.

Thanks.

[TheShabz]

Well, what that Dmax is doing is essentially telling Access to find biggest ReviewDate in tblReview where EntityID = whatever is in cmbEntity. Now, if cmbEntity doesn't exist, it's not going to be able to do it's thing, which is why it's prompting you for a value for EntityID. You need to put it in for the process to work.

[RecruiterScott]

I am trying to complete a database for a client in Access where when they add a note in the memo field and when they are done a new memo field to appear. I did this over 7 years ago and for the life of me I can't remember how to do this. Access knowledge has been overrun with cobwebs. Can someone refresh my memory?

http://olivelane.org/note.png

[TheShabz]

You want a new field to show up in a table after one is populated? For example:

tblWidget
WidgetID
WidgetName
Note1

After entering Note1 you want a new field called Note2?

[RecruiterScott]

Thank is correct. Like in the photo link...person enters note, clicks save or whatever is the best way to do it and a new memo field comes up.

http://olivelane.org/note.png

[TheShabz]

I asked how your table structure is because I wouldn't add a new memo field. Lets say, for example, this is to track customer service calls. I would have 3 tables involved. 1 for the person taking the call (employee), one for the customer (customer) and one for the notes (accountNotes) that creates a many-to-many relationship between customer and account.

I would then have one textbox for notes on my form. a button would confirm the note, add it to my accountNotes table, then clear out the textbox to be reused. Under the memo, I would have a subform of previous notes displayed with the most recent on top, that would requery after every update.

Not sure how well you can do this if your table structure merely adds a new field to a table for notes, instead of using a more normalized and more easily queriable structure.