Solved

Click a button add a note to a record

Posted on 2014-02-11
35
349 Views
Last Modified: 2014-02-19
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
Comment
Question by:lehi52
  • 21
  • 14
35 Comments
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39853027
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
 

Author Comment

by:lehi52
ID: 39853613
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
 

Author Comment

by:lehi52
ID: 39853822
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
 

Author Comment

by:lehi52
ID: 39853829
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
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39855350
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
 

Author Comment

by:lehi52
ID: 39855399
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
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39855627
If you insist.
3.accdb
0
 

Author Comment

by:lehi52
ID: 39856470
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
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39856567
Here you go.
4.accdb
0
 

Author Comment

by:lehi52
ID: 39856591
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
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39856655
It does by me.
0
 

Author Comment

by:lehi52
ID: 39856696
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
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39856795
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
 

Author Comment

by:lehi52
ID: 39856864
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
 
LVL 26

Accepted Solution

by:
MacroShadow earned 500 total points
ID: 39856907
Try this.
5.accdb
0
 

Author Comment

by:lehi52
ID: 39856911
same thing.  the contact ID does not fill in.  So it is adding the note but not assigning it to anyone.
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39856960
Works by me. Maybe try it on a different machine.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:lehi52
ID: 39856969
Ill try a restart or something kind of interesting.
0
 

Author Comment

by:lehi52
ID: 39856971
Why might it not be working on mine but it works on yours.
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39856996
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
 

Author Comment

by:lehi52
ID: 39857059
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
 

Author Comment

by:lehi52
ID: 39857187
We tried it on another machine and it does not work.
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39857249
Put a break point on the Sub Form_Load(), what is the value of Me.OpenArgs?
0
 

Author Comment

by:lehi52
ID: 39857308
what is a break point.  Sorry beginner.  Also you asked what is the value of the item you requested.  Im not sure.
0
 

Author Comment

by:lehi52
ID: 39857406
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
 

Author Comment

by:lehi52
ID: 39857674
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
 

Author Comment

by:lehi52
ID: 39857777
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
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39858195
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
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39858197
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
 

Author Comment

by:lehi52
ID: 39859182
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
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39861939
Easier way? No, I don't think so. This is the ultimate solution, sorry you couldn't get it to work.
0
 

Author Comment

by:lehi52
ID: 39868932
please delete this whole question we could not get it resolved.
0
 

Author Comment

by:lehi52
ID: 39869022
I've requested that this question be deleted for the following reason:

There was no answer to this question so you can delete.
0
 
LVL 26

Expert Comment

by:MacroShadow
ID: 39869023
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
 

Author Comment

by:lehi52
ID: 39869053
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now