Create a notes form linked to a specific record

[ali3n8]

Hello I need some assistance with creating a form/subform. The scenarios is I have a form on this form i want to create a button to open a page were notes can be entered and time stamped. On the notes page I would like to have the option that the notes will be timestamped and also would like a button on that page that says add notes which will add the notes field everytime it is click so new notes can be seperated and not all in one field. Ive been stumped on how to achieve this for two days and my searches have come up with nothing. This notes page should be tied to the record that it was entered for. Any help would be appreciated.

[Scott Price]

A general overview:

Create a table called tblNotes with about 4 fields. You will need a primary key NotesID field, a foreign key CustomerID etc field to relate this to your Customer/Record/Order/Whatever, a notes field and a date/time field.

Relate this table to the table containing your Customer/Record/Order/Whatever in the database relationships window.

Create a select query that looks up the notes for a certain record.

Create a form based on this query. It should have three visible controls and one invisible, corresponding to your foreign key, notes, and date/time fields and the invisible control to your NotesID field. Place a command button on the form under the Date/Time field, and in the On Click event, tell the database to insert Now() in the Date/Time field. You can name the command button what you like, but it should be meaningful and the name shouldn't be one of the Access/VBA reserved words (like Date, Now, etc. cmdDate or cmdNow are fine...) Caption it as you wish, something like Time Stamp would work.

Now in the main form you will need to decide how you want to call this frmNotes form. Any of the events can be used, but likely you'll want another command button that when clicked will use the DoCmd.OpenForm command in VBA to open your frmNotes to the desired note for the selected record/customer...

Regards,
Scott

[ali3n8]

A general overview:

Create a table called tblNotes with about 4 fields. You will need a primary key NotesID field, a foreign key CustomerID etc field to relate this to your Customer/Record/Order/Whatever, a notes field and a date/time field.

Relate this table to the table containing your Customer/Record/Order/Whatever in the database relationships window.

Create a select query that looks up the notes for a certain record.

Create a form based on this query. It should have three visible controls and one invisible, corresponding to your foreign key, notes, and date/time fields and the invisible control to your NotesID field. Place a command button on the form under the Date/Time field, and in the On Click event, tell the database to insert Now() in the Date/Time field. You can name the command button what you like, but it should be meaningful and the name shouldn't be one of the Access/VBA reserved words (like Date, Now, etc. cmdDate or cmdNow are fine...) Caption it as you wish, something like Time Stamp would work.

Now in the main form you will need to decide how you want to call this frmNotes form. Any of the events can be used, but likely you'll want another command button that when clicked will use the DoCmd.OpenForm command in VBA to open your frmNotes to the desired note for the selected record/customer...

Regards,
Scott

I attempted this method and have been somewhat unsuccessful. When I attempt to save the notes I get the error " You cannot add or change a record because a related record is required in table "tblcustomerinformation" How can I resolve this.

[Scott Price]

I attempted this method and have been somewhat unsuccessful. When I attempt to save the notes I get the error " You cannot add or change a record because a related record is required in table "tblcustomerinformation" How can I resolve this.

To clarify the issue, you have a one to many relationship between tblcustomerinformation and tblnotes. You need to structure the tables so that the linking field (customerID) is in the tblnotes and not in tblcustomerinformation.

If your query to populate your form that you enter notes into is built correctly, then you will not encounter this error as it will not be attempting to create a note record for which there is no customer.

Could you post your table meta data as well as the sql of your query that you are using to populate the notes form? Here is an example of how to post table MetaData :
Table Name=tblStudent

1. Field; Type; IndexInfo
2. StudentID; AutoNumber; PK
3. Family; String; FK
4. Name; String
5. University; String; FK
6. Mark; Numeric
7. LastAttendance; Date/Time

Thanks,
Regards,
Scott

[ali3n8]

Im a noob at this so if you could tell me how to extract my metadata information I would appreciate it. Thank you

[Scott Price]

Im a noob at this so if you could tell me how to extract my metadata information I would appreciate it. Thank you

No need to extract, just type in the structure of your table using the format I mentioned above.

Regards,
Scott

[ali3n8]

Ok thank you

This is my table called tblcustomerinformation. I use these fields on my main form.

Field Name - Data Type
Customer ID - Autonumber
File Status - Text
File Status Reason - Text
Contact Number - Text
First - Text
Last - Text
Spouse - Text
Street - Text
State - Text
City - Text
Zip Code - Text
Apartment - Number
CCDebt - Currency
Amount of Cards - Number
Minimum Payment - Currency
Minimum Saving Guaruntee - Currency
Finance Rate - text
Total - Currency
Card Type - Text
Credit Card Number - text
Expiration Date - Date/Time
CVC Number - Text
CC Approval# - Text
Available - Currency
Bank Phone - text
Bank Name - text
Verified - Text
Verified By - Text
Verification - Number
Date - Text
Reason for not verifiying - memo
Consultant - text
Closer - text
Opener - Text
Notes - Memo
Followup - Date/Time

This is the table I created for notes- I use these fields on another form called notes.

Customer ID - Autonumber
Notes ID- Number
Date Notes Entered - Date/Time
Notes 1 - Memo
Notes 2 - Memo
Notes 3- Memo
Notes 4-memo
First - Text
Last - Text
Contact Number - Text

I hope this helps thank you

[Scott Price]

This is the table I created for notes- I use these fields on another form called notes.

Customer ID - Autonumber
Notes ID- Number
Date Notes Entered - Date/Time
Notes 1 - Memo
Notes 2 - Memo
Notes 3- Memo
Notes 4-memo
First - Text
Last - Text
Contact Number - Text

I hope this helps thank you

In your Notes table, the CustomerID field looks like the primary key (PK) rather than the foreign key (FK) You need the NotesID to be the PK autonumber and the CustomerID to be FK number data type. You then go into the table relationships diagram and relate the two CustomerID fields: PK in the CustomerInfo table and FK in the Notes table.

You should remove the Notes field from your CustomerInfo table since that just duplicates what you are doing with the Notes table.

I also am wondering why you have 4 note fields in your Notes table? Do you really have that much text to type into each note record that you need 4 memo fields? That's over 260,000 characters, or a small novel for each note record!

You probably should consider removing the First and Last fields from tblnotes as well, since that duplicates data unnecessarily.

The concept of a one to many relationship like you have between tblcustomer and tblnotes is that you only should need one notes field. Then when you want to add a new note for the same contact, the only duplicate data is the CustomerID field that identifies the unique record in tblnotes with the unique record in tblcustomer. The new note becomes a new record in tblnotes.

Now when you create your query to lookup each note from tblnotes that is associated with a particular customer in tblcustomer it will look something similar to this:

SELECT tblnotes.noteID, tblnotes.note, tblcustomer.customerid, tblcustomer.first, tblcustomer.last FROM tblnotes, tblcustomer INNER JOIN tblnotes ON tblnotes.customerid=tblcustomer.customerid WHERE tblcustomer.customerid=1

Does this help explain what is going on a little better?

Regards,
Scott

[ali3n8]

How do I create a foreign key. I also put the multiple fields for notes incase an update needs to be on that paticular record. I want to keep each update in a seperate field.

Thank you

[Neekos]

i know this doesnt help your current problem, but i noticed you have your "Apartment" field as a number type. You may want to change that to a text as many apartments are alphanumeric. (ex. - Apt 4B).

[Scott Price]

How do I create a foreign key. I also put the multiple fields for notes incase an update needs to be on that paticular record. I want to keep each update in a seperate field.

Thank you

The foreign key is not something you 'create', it's the term used to refer to a primary key of one table that is in a 'foreign' table. I'm assuming you are familiar with the process of using the relationship diagram to drag a key field from one table and drop it over the key field of another table, thereby relating the two tables?

In the case of updates, you should have a third table tblNoteUpdates. The problem with putting updates in your notes table is that you cannot foresee how many updates that could be added, so limiting them to 3 is going to cause you problems down the road when you want to add a fourth update, and can't because the table only has three update fields. It is compounded when someone else is trying to use the database and doesn't understand why they can't enter more than one update!

A third table will allow you to create as many updates as you want for as many notes as you want. It will work in exactly the same relation to the tblnotes, as the tblnotes works in relation to the tblcustomers.

So your tblnotes should be like this:

tblNotes
NoteID AutoNumber PK
CustomerID Number FK
Notes Memo
NoteDate Date/Time Default value =Now()

tblNoteUpdates
NoteUpdate AutoNumber PK
NoteID Number FK
Update Memo
UpdateDate Date/Time Default value =Now()

With this setup, you enter your updated text in the tblNoteUpdate, the original NoteID in it's field in the tblNoteUpdates, and hey presto! The two are related by the One to Many relationship that you have defined between the two tables.

Using the default value =Now() function automatically timestamps any record that is entered in either tblNotes or tblNoteUpdates.

Don't hesitate to ask any question about this that you don't understand!

Neekos has a good point also about the Apartment field. Thanks!

Regards,
Scott

[ali3n8]

Thank you for the help I understood excatly what you said. Now could you clear up what you mention about a query earlier in the post?

Thank you

[Scott Price]

You're quite welcome, and glad you followed my logic :-)

About the query: Because you are basing your Notes form on information drawn from more than one table, the normal way to do this is by creating a select query that first collects the information you want to see; and then basing your form on the query instead of a table.

A query, as you likely know, is just a representation of data that functions as a 'virtual' table.

So on your Notes form, you will want to see what customer you are writing notes about, the note you are writing and the date being attached. Your query needs to include these fields, which then are bound to specific controls on your form.

To create the query, double click on the create query in design view icon in your database window. Right-click, then choose Show Table. Double click on both the tblcustomer and the tblnotes. Then double click on each field you will want on your notes form: NoteID, CustomerID, First, Last, Note, NoteDate.

In the criteria grid under your CustomerID you will reference the customer you wish to view/create a note for, likely by referencing the control of your main customer form. On the main form you should have something like a txtcustomerID textbox. Then in the criteria grid you will enter this: =Forms![MainFormName]!txtcustomerID. Remember to change the names in this example to those you are using in your database!

Now when you open the Notes form by clicking on a command button on your main form, it will open with only the notes that belong to the customer you are viewing on the main form.

Regards,
Scott

[ali3n8]

I also took your reccomendation about creating another form for updatenote. I have also Include another form tied to the main one called additional credit cards. I used the same method as i did with the notes form to tie them all together to one record. Your info for the query is clear I understand it but when i create this query which table do I want to pull my fields from?

Im thinking I pull them from each table that I want to query information from...Just a thought

[Scott Price]

I also took your reccomendation about creating another form for updatenote. I have also Include another form tied to the main one called additional credit cards. I used the same method as i did with the notes form to tie them all together to one record. Your info for the query is clear I understand it but when i create this query which table do I want to pull my fields from?

Im thinking I pull them from each table that I want to query information from...Just a thought

Nice lightbulb :-)

Yes, for your Notes form you will pull from tblcustomer AND tblNotes.

Regards,
Scott

[ali3n8]

So Im getting to the end of this I just created a test entry in the db and i still get the error "You cannot add or change record because a related record is required in the table 'tblcustomerinformation'. If you like I can email you a copy of what I am creating.

[ali3n8]

Im curious is it possible I am getting this error because I am not saving the record before I enter the notes?

[Scott Price]

So Im getting to the end of this I just created a test entry in the db and i still get the error "You cannot add or change record because a related record is required in the table 'tblcustomerinformation'. If you like I can email you a copy of what I am creating.

What is the nature of the relationship between tblcustomerinformation and tblnotes? Did you specify referential integrity? If so, un-do it.

Regards,
Scott

[ali3n8]

Worked like a charm. Thank you so much I appreciate the help. This has by far been one of the most helpful forums that I have been a member of. Thank you

[Scott Price]

Worked like a charm. Thank you so much I appreciate the help. This has by far been one of the most helpful forums that I have been a member of. Thank you

Glad it worked for you, and you're welcome!

Regards,
Scott

[ali3n8]

Thanks for all the help Scott, If you could assist me with the query on this I would appreciate it I havent been so succesful with it. I have a search form which uses a query called qrycustomerinformation. The form looks up the following fields:

[First]
[Last]
[Verification]
[ContactNumber]
[Street]
[City]
[State]
[Zip]

When I use this search form it pulls the record just fine. Just not the notes associated with it. So when what im asking do I add the fields from frmnotes to the qrycustomerinformation so that when I use my search function it will also pull the associated notes with it?

[Scott Price]

Thanks for all the help Scott, If you could assist me with the query on this I would appreciate it I havent been so succesful with it. I have a search form which uses a query called qrycustomerinformation. The form looks up the following fields:

[First]
[Last]
[Verification]
[ContactNumber]
[Street]
[City]
[State]
[Zip]

When I use this search form it pulls the record just fine. Just not the notes associated with it. So when what im asking do I add the fields from frmnotes to the qrycustomerinformation so that when I use my search function it will also pull the associated notes with it?

That would be the general idea, yes. Could you post your SQL code for the qrycustomerinformation? In query design view, right-click on the top blue bar of the design view window, choose SQL view, copy and paste all the code into teh reply window here. When you've done that, select it all, wrap it in the code tags by clicking the # button on the top of this reply window, and in the first code tag, manually edit it to read just like this: [code=sql]

Thanks!

Regards,
Scott

[ali3n8]

That would be the general idea, yes. Could you post your SQL code for the qrycustomerinformation? In query design view, right-click on the top blue bar of the design view window, choose SQL view, copy and paste all the code into teh reply window here. When you've done that, select it all, wrap it in the code tags by clicking the # button on the top of this reply window, and in the first code tag, manually edit it to read just like this: [code=sql]

Thanks!

Regards,
Scott

SELECT tblcustomerinformation.CustomerID, tblcustomerinformation.[File Status], tblcustomerinformation.[File status Reason], tblcustomerinformation.[Contact Number], tblcustomerinformation.First, tblcustomerinformation.Last, tblcustomerinformation.Spouse, tblcustomerinformation.Street, tblcustomerinformation.State, tblcustomerinformation.City, tblcustomerinformation.[Zip Code], tblcustomerinformation.Apartment, tblcustomerinformation.[CC Debt], tblcustomerinformation.[Amount of Cards], tblcustomerinformation.[Minimum Payment], tblcustomerinformation.[Minimum Saving Guaruntee], tblcustomerinformation.[Finance Rate], tblcustomerinformation.Total, tblcustomerinformation.[Card Type], tblcustomerinformation.[Credit Card Number], tblcustomerinformation.[Expiration Date], tblcustomerinformation.[CVC Number], tblcustomerinformation.[CC Aproval#], tblcustomerinformation.Available, tblcustomerinformation.[Bank Phone], tblcustomerinformation.[Bank Name], tblcustomerinformation.Verified, tblcustomerinformation.[Verified By], tblcustomerinformation.Verification, tblcustomerinformation.[Order Entered], tblcustomerinformation.[Reason for not verifiying], tblcustomerinformation.Consultant
FROM tblcustomerinformation;

Here you go thank you. I attempted already to create the query and it stopped my search form from working. So I reverted back to my original setup and thats what this code is for.

Thank you

[Scott Price]

Let's see, how and where do you want to show the notes for the particular contact that you are searching for? Is it on the same search form? Or are you opening a separate form to view all the information?

Regards,
Scott

[ali3n8]

Here is how my search function currently work. My frmsearch list its results in a subform. I have it set to were when I click on the field [first] it will open the record and populate it into the form that the information was collected on. On this form I have a button labeled add/view notes. So i would like to see this work using my current method which is search record, click on first name to open record in form, when i click add view notes I would like the notes corresponding with the record I have already loaded in the form to appear. And on a side note do you see anything wrong with this code

Me!txtdatefrom = CDate("01/" & Month(Date) & "/" & Year(Date))
Me!txtDateTo = DateAdd("d", -1, DateAdd("m", 1, Me!txtdatefrom))

Im trying to populate the first day of the month and the last day of the month based on todays date and this seems not work.

I have whole new respect for you software guys. Im a hardware guy myself and this is quite bit more involved. Thanks for the help.

[Scott Price]

OK, on problem one here, if you take a look at post #8 I wrote this SQL query in:

SELECT tblnotes.noteID, tblnotes.note, tblcustomer.customerid, tblcustomer.first, tblcustomer.last FROM tblnotes, tblcustomer INNER JOIN tblnotes ON tblnotes.customerid=tblcustomer.customerid WHERE tblcustomer.customerid=1

All you will need to do is check carefully the spelling of the table names and field names, and then in the WHERE criteria, point to the CustomerID control on the search form or customer form. That will look like this: tblnotes.customerID=Forms![SearchFormName]![CustomerIDControlName]. This will become the base query for your Add/View Notes form. Now when you click the button to open this form, the underlying query will filter down to the CustomerID that you feed it from the search form or customer information form.

Let me take a look at the other question and get back to you.

Regards,
Scott

[ali3n8]

What do you mean by "[customeridcontrolname]"? Im assuming the field name for my customerid? To be honest im not really understand this portion. I can make simple queries but this seems a bit out of my scope. I hate to ask this but if you could spell this out to me it would be much easier. And also I dont use the customerid on my search form on firs,last,zip,state,contact and verification.

Thank you

[Scott Price]

This will return the last day of the current month:

DateSerial(Year(Date),Month(Date)+1,1-1)

in this format: 8/31/2007

This will return the first day of the current month:

DateSerial(Year(Date),Month(Date),1)

in this format: 8/1/2007

It looks almost like you are trying to use the day/month/year format? So you would want it to look like 31/8/2007 and 1/8/2007?

If so you'll need to go one step further:

Format(DateSerial(Year(Date),Month(Date)+1,1-1),"d/m/yyyy")

and

Format(DateSerial(Year(Date),Month(Date),1),"d/m/yyyy")

respectively give the last day of month and first day of month for the current month in the day/month/year format.

To look up values between these two dates in a query, try

BETWEEN #Format(DateSerial(Year(Date),Month(Date)+1,1-1),"d/m/yyyy")# AND #Format(DateSerial(Year(Date),Month(Date),1),"d/m/yyyy")#

Regards,
Scott

[Scott Price]

What do you mean by "[customeridcontrolname]"? Im assuming the field name for my customerid? To be honest im not really understand this portion. I can make simple queries but this seems a bit out of my scope. I hate to ask this but if you could spell this out to me it would be much easier. And also I dont use the customerid on my search form on firs,last,zip,state,contact and verification.

Thank you

On your form, each control (i.e. text box, combo box, list box, command button, etc.) is named. You want to refer to the name of the control that contains the customerid value. In form design view, right click on the control, choose properties, on the All tab go to the very top and see what it says under the Name property. This is what you will enter in the query for [customeridcontrolname]. It should like this example: Forms!CustomerSearch!txtCustomerID

And yes, you want the customerid to show up on the form that contains customer information. It really is quite simple to do this: in your query design view that populates the search form, just double click on the customerid of your table, save your query, then go to the form design view and add a text box with the source set to customerid, save the changes and test.

Regards,
Scott

[ali3n8]

Ok kew i understand that, Here is whats happening everytime I add to the query my search function no longer works here is the sql info for my query what do I need to add to it? Currently this query is associated with tblcustomerinformation.

SELECT tblcustomerinformation.CustomerID, tblcustomerinformation.[File Status], tblcustomerinformation.[File status Reason], tblcustomerinformation.[Contact Number], tblcustomerinformation.First, tblcustomerinformation.Last, tblcustomerinformation.Spouse, tblcustomerinformation.Street, tblcustomerinformation.State, tblcustomerinformation.City, tblcustomerinformation.[Zip Code], tblcustomerinformation.Apartment, tblcustomerinformation.[CC Debt], tblcustomerinformation.[Amount of Cards], tblcustomerinformation.[Minimum Payment], tblcustomerinformation.[Minimum Saving Guaruntee], tblcustomerinformation.[Finance Rate], tblcustomerinformation.Total, tblcustomerinformation.[Card Type], tblcustomerinformation.[Credit Card Number], tblcustomerinformation.[Expiration Date], tblcustomerinformation.[CVC Number], tblcustomerinformation.[CC Aproval#], tblcustomerinformation.Available, tblcustomerinformation.[Bank Phone], tblcustomerinformation.[Bank Name], tblcustomerinformation.Verified, tblcustomerinformation.[Verified By], tblcustomerinformation.Verification, tblcustomerinformation.[Order Entered], tblcustomerinformation.[Reason for not verifiying], tblcustomerinformation.Consultant
FROM tblcustomerinformation;

This is also the query that my search form uses. My table for notes is called tblnotes. I sure this is simple its just me making it complicated.

Im going to give shot at what your saying right now i have the qrycustomerinformation open in design view is this what you asking me to do:

Field - customerid
Table - tblnotes
Sort
Show - checked
Criteria =Forms![SearchFormName]![CustomerID]

Also my search form consist of two forms the main form which have the fields to type the text you are searching and a subform which displays the results.

[Scott Price]

Create a separate query to base your frmnotes on, the separate query will be called qryNotes, and will have the sql I gave you above.

The search query as you have it is fine... if it works, don't fix it!

My preference is hardware, too :-) Just had to learn the software part out of sheer self-defense.

Regards,
Scott

[ali3n8]

I created the sep. query and here is the sqlview is this what I want:

SELECT tblnotes.[Notes ID], tblnotes.[Notes 1], tblcustomerinformation.CustomerID, tblcustomerinformation.First, tblcustomerinformation.Last
FROM tblcustomerinformation INNER JOIN tblnotes ON tblcustomerinformation.CustomerID = tblnotes.CustomerID;

[Scott Price]

I created the sep. query and here is the sqlview is this what I want:

SELECT tblnotes.[Notes ID], tblnotes.[Notes 1], tblcustomerinformation.CustomerID, tblcustomerinformation.First, tblcustomerinformation.Last
FROM tblcustomerinformation INNER JOIN tblnotes ON tblcustomerinformation.CustomerID = tblnotes.CustomerID;

Almost there! Try this:

SELECT tblnotes.[Notes ID], tblnotes.[Notes 1], tblnotes.CustomerID FROM tblnotes WHERE tblnotes.CustomerID = Forms![YourCustomerFormName].[YourCustomerIDControl]

Regards,
Scott

[ali3n8]

[quote=Scott Price]Almost there! Try this:

SELECT tblnotes.[Notes ID], tblnotes.[Notes 1], tblnotes.CustomerID FROM tblnotes WHERE tblnotes.CustomerID = Forms![YourCustomerFormName].[YourCustomerIDControl]

Regards,
Scott[/QUOTE

Here is what my code looks like now:

SELECT tblnotes.[Notes ID], tblnotes.[Notes 1], tblnotes.CustomerID
FROM tblnotes
WHERE (((tblnotes.CustomerID)=Forms!frmcustomerinformation.txtcustomeridl));

Now that im looking at it i see extra characters that yours does not have. But access added these characters itself. I created a test entry and did my search i can pull my records as always but still no notes. Is it possible I could be messing this up. On my notes form I have my first,last,phone fields pointing to the main form as the control source so that this information auto populates for you when you pull up the notes form.

Also when I look at my tbl called tblnotes in the customerid field it show the number 0. It doesnt seem like frmnotes is saving the customerid from the frmcustomerinformation. I checked and I do have the relationship configured and I do not have enforce referential integrity checked. Im wondering if I edited the control source for my customer id on my notes frm to point to frmcustomerinformation would this resolve the issue. Im thinking the reason the notes are not being pulled is beacuse the customer id is not appearing on the notes form. Could this also be caused by me making the customerid field on frmnotes not visible.

[Scott Price]

[quote=ali3n8]

Almost there! Try this:

SELECT tblnotes.[Notes ID], tblnotes.[Notes 1], tblnotes.CustomerID FROM tblnotes WHERE tblnotes.CustomerID = Forms![YourCustomerFormName].[YourCustomerIDControl]

Regards,
Scott[/QUOTE

Here is what my code looks like now:

SELECT tblnotes.[Notes ID], tblnotes.[Notes 1], tblnotes.CustomerID
FROM tblnotes
WHERE (((tblnotes.CustomerID)=Forms!frmcustomerinformation.txtcustomeridl));

Now that im looking at it i see extra characters that yours does not have. But access added these characters itself. I created a test entry and did my search i can pull my records as always but still no notes. Is it possible I could be messing this up. On my notes form I have my first,last,phone fields pointing to the main form as the control source so that this information auto populates for you when you pull up the notes form.

Also when I look at my tbl called tblnotes in the customerid field it show the number 0. It doesnt seem like frmnotes is saving the customerid from the frmcustomerinformation. I checked and I do have the relationship configured and I do not have enforce referential integrity checked. Im wondering if I edited the control source for my customer id on my notes frm to point to frmcustomerinformation would this resolve the issue. Im thinking the reason the notes are not being pulled is beacuse the customer id is not appearing on the notes form. Could this also be caused by me making the customerid field on frmnotes not visible.

No to the customerid field on frmnotes being invisible. That just means you can't see it, but the database still can.

When you create a new note by clicking the Add/View button, you need to populate the customerid field on your frmnotes from the customerid on your frmcustomerinformation.

To do so go into your frmnote properties box (double click on the little square in the upper left corner of the form design view window). On the Events tab, click on the ellipsis (...) to the right of the On Open event. Choose Code Builder. Put this code in:

Me.[YourCustomerIDControlName] = Forms!frmcustomerinformation.txtcustomerid
 

This will make the new note that is created have the same customerid as the customer you are viewing on the form: frmcustomerinformation.

Regards,
Scott

[ali3n8]

[quote=Scott Price]

No to the customerid field on frmnotes being invisible. That just means you can't see it, but the database still can.

When you create a new note by clicking the Add/View button, you need to populate the customerid field on your frmnotes from the customerid on your frmcustomerinformation.

To do so go into your frmnote properties box (double click on the little square in the upper left corner of the form design view window). On the Events tab, click on the ellipsis (...) to the right of the On Open event. Choose Code Builder. Put this code in:

Me.[YourCustomerIDControlName] = Forms!frmcustomerinformation.txtcustomerid
 

This will make the new note that is created have the same customerid as the customer you are viewing on the form: frmcustomerinformation.

Regards,
Scott

Thank you for the help but some how my db got currupted so now i have to rebuild the portions I was not able to recover. I will use our conversation as guide once I begin rebuilding. Thanks for all the help I appreciate it.

[Scott Price]

Sorry to hear about the corruption :-( Glad this was of some help to you.

Regards,
Scott