Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 369
  • Last Modified:

Click a button add a note to a record

First of all, everyone has been really helpful.  In my database I want to be able to add a note to a record from the lead list tab.  I need to put the note in the person's record that I click on or right click on.  However would be easiest I would like to do it.  

On the lead list tab is a list of prospects.   I need the ability to add a note to that persons record from this page.  Then that note will show up on the notes table, assigned to that person.  I don't know the best way to do this.  Maybe a right click event to add a note or something like that.  

I am a real beginner so any help would be great.  We are using this database for my work to keep track of our calls and marketing.
1.accdb
0
lehi52
Asked:
lehi52
  • 21
  • 14
1 Solution
 
MacroShadowCommented:
Instead of creating a separate table for comments it should be a field in the CG Info table (actually that table should be split, but that's a different issue), and a field on your lead list tab.
0
 
lehi52Author Commented:
Macroshadow.   Im not sure that would work by putting the notes int he CG info table.  Each record could have 5 or 6 lines of notes tagged with a different date and time.  ANy ideas on how to add a notes from that first main page.
2.accdb
0
 
lehi52Author Commented:
So I think I know the initial parts of how we can do it.  We can add an after update event, so that when we double click a box in the record it will open a box which will add a note to that persons record.  

The code to do that I am not sure about.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
lehi52Author Commented:
Maybe there is a better way,  I am open.  It seems cluncky to add a button that shows up next to each person's record.  maybe highlighting a record on the lead list tab and hitting f9 to add a note would work well too.
0
 
MacroShadowCommented:
I don't see what the problem is. When you double click on a policy name in the Lead List tab you are transferred to the General tab, there on the right side of the screen you can enter as many notes as you want.
0
 
lehi52Author Commented:
That is true.   But it is a small thing.  Lets say someone is making calls from the lead list screen and wants to add notes without going to the next screen.  Its small but in these types of small its all the small things that make a big difference. In reality you are correct.  My people probably would not know the difference if they never saw it the other way.  On the other hand I thought it would be a cool feature.  If its not possible that is fine.
0
 
MacroShadowCommented:
If you insist.
3.accdb
0
 
lehi52Author Commented:
Oh great.  You are awesome.  I have one quick change.  I hestitate to ask this.  How would you click the button and have it default to the person who the button is next to, and have it default to adding a new note rather than scrolling through and viewing all the notes.
0
 
MacroShadowCommented:
Here you go.
4.accdb
0
 
lehi52Author Commented:
Thanks again.  There is still an issue when I click the add note button, It does not default to the person that the button is next to.  And now the double click even on that line throws an error.
0
 
MacroShadowCommented:
It does by me.
0
 
lehi52Author Commented:
I tried it, and when I double click the name next to the button it throws an error. and the button does not work right.
0
 
MacroShadowCommented:
when I double click the name next to the button it throws an error.

Sorry, didn't intend to make a change there, use your original code for that line.
DoCmd.SearchForRecord acDataForm, "Contact Form", acFirst, "[ContactID] = " & Nz(Me.ContactID, 0)

Open in new window



and the button does not work right.

Once again, it does by me, look at the id field and you'll see that it matches the id of the policy.
0
 
lehi52Author Commented:
Thanks,  I got the Double click working again.  When I click the button to add a note it does not fill in the Contact ID,  So it adds the note but its not associated with anyone.
0
 
MacroShadowCommented:
Try this.
5.accdb
0
 
lehi52Author Commented:
same thing.  the contact ID does not fill in.  So it is adding the note but not assigning it to anyone.
0
 
MacroShadowCommented:
Works by me. Maybe try it on a different machine.
0
 
lehi52Author Commented:
Ill try a restart or something kind of interesting.
0
 
lehi52Author Commented:
Why might it not be working on mine but it works on yours.
0
 
MacroShadowCommented:
Let me explain how the code works, then perhaps you will be able to troubleshoot.

This is the code on the click event of the cmdAddNote button.
Private Sub cmdAddNote_Click()
    Dim id As Integer
    id = DLookup("ContactID", "CG Info", "[Policy Name] = '" & Me.[policy name] & "'")
    DoCmd.OpenForm "frmNotes", acNormal, , "[ContactID] = " & id, , , id
End Sub

Open in new window

Dim id As Integer
Declaring a variable to hold the id of the currently selected Policy Name.

id = DLookup("ContactID", "CG Info", "[Policy Name] = '" & Me.[policy name] & "'")
Populating the variable.

DoCmd.OpenForm "frmNotes", acNormal, , "[ContactID] = " & id, , , id
Open the form filtered to the ContactID who matches the id variable.
The last argument (OpenArgs) is the id variable (which holds the id of the currently selected Policy Name).

This code is on the load event of the frmNotes form:
Private Sub Form_Load()
    Me.ContactID = Me.OpenArgs
End Sub

Open in new window

Here we populate the ContactID textbox with the OpenArgs string (which holds the id of the currently selected Policy Name).
0
 
lehi52Author Commented:
I can type the Contactid into the field and it assigns the notes to that record.  But it does not pull automatically.   What might be the issue.  I tried restarting my machine.  that did not fix it.
0
 
lehi52Author Commented:
We tried it on another machine and it does not work.
0
 
MacroShadowCommented:
Put a break point on the Sub Form_Load(), what is the value of Me.OpenArgs?
0
 
lehi52Author Commented:
what is a break point.  Sorry beginner.  Also you asked what is the value of the item you requested.  Im not sure.
0
 
lehi52Author Commented:
I researched it a bit.   I went into the code for the frmNotes clicked on the line
Me.ContactID = Me.OpenArgs

Open in new window


and hit f9.  That changed the color to red.  I saved and it still didnt fill in the contact Id.
0
 
lehi52Author Commented:
When I open the frmNotes by itself in Layout view I get this message.

"The expression on load you entered as the event property setting produced  the following error:  A problem occurred while microsoft access was communicating with the OLE server or activeX control.
0
 
lehi52Author Commented:
Here is a quick update.  I went to the VBA code and compiled it.  Ran the button and it filled in the ID automatically.  But when I try to run it a second time the ID number does not fill in.
0
 
MacroShadowCommented:
The form frmNotes is not intended to be opened independently, if you do want to be able to open in independently change the code in the on load event to:
Me.ContactID = Nz(Me.OpenArgs, "")

Put a break point on the last line of Private Sub Form_Load() (End Sub). The break point stops the code execution when the break point is reached. Once the break point is reached you will be transferred to the Visual Basic Editor (VBE) and the line will be highlighted.
When that happens type in the immediate window:
?Me.OpenArgs

Open in new window

That will tell you what the value of OpenArgs is.

To insert a break point there are several options:
1. Put the cursor at the line you want (in this case End Sub) and hit F9.
2. Click on the side of the VBE main window.
3. From the debug menu (in the VBE), Toggle Breakpoint

To show the immediate window there are several options:
1. From the View menu (in the VBE), Immediate Window
2. Keyboard shortcut, ctrl+g
0
 
MacroShadowCommented:
But when I try to run it a second time the ID number does not fill in.
It will only fill it in when frmNotes first opens, after adding the note the form must be closed.
0
 
lehi52Author Commented:
I think Im going to just shelve the adding the notes.  I appreciate your help.  Unless there was an easier way to do this.
0
 
MacroShadowCommented:
Easier way? No, I don't think so. This is the ultimate solution, sorry you couldn't get it to work.
0
 
lehi52Author Commented:
please delete this whole question we could not get it resolved.
0
 
lehi52Author Commented:
I've requested that this question be deleted for the following reason:

There was no answer to this question so you can delete.
0
 
MacroShadowCommented:
An answer was given to the question. The answer was tested and works, for some reason the OP couldn't get it to work.
I'm not sure what the EE policy is in such a case, as a working solution was provided and explained and can easily be confirmed by a moderator. If EE's policy is is to delete such a question, I raise no objection, otherwise I think the points are due.
0
 
lehi52Author Commented:
By the way, I am good keep it in here.  He did a lot of help.  Maybe mark it resolved. Ill do that.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 21
  • 14
Tackle projects and never again get stuck behind a technical roadblock.
Join Now