Create a notes form linked to a specific record

[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:

[CODE=sql]SELECT tblnotes.[Notes ID], tblnotes.[Notes 1], tblcustomerinfo rmation.Custome rID, tblcustomerinfo rmation.First, tblcustomerinfo rmation.Last
FROM tblcustomerinfo rmation INNER JOIN tblnotes ON tblcustomerinfo rmation.Custome rID = tblnotes.Custom erID;[/CODE]

[Scott Price]

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

[CODE=sql]SELECT tblnotes.[Notes ID], tblnotes.[Notes 1], tblcustomerinfo rmation.Custome rID, tblcustomerinfo rmation.First, tblcustomerinfo rmation.Last
FROM tblcustomerinfo rmation INNER JOIN tblnotes ON tblcustomerinfo rmation.Custome rID = tblnotes.Custom erID;[/CODE]

Almost there! Try this:
[CODE=sql]
SELECT tblnotes.[Notes ID], tblnotes.[Notes 1], tblnotes.Custom erID FROM tblnotes WHERE tblnotes.Custom erID = Forms![YourCustomerFor mName].[YourCustomerIDC ontrol][/CODE]

Regards,
Scott

[ali3n8]

[QUOTE=Scott Price]Almost there! Try this:
[CODE=sql]
SELECT tblnotes.[Notes ID], tblnotes.[Notes 1], tblnotes.Custom erID FROM tblnotes WHERE tblnotes.Custom erID = Forms![YourCustomerFor mName].[YourCustomerIDC ontrol][/CODE]

Regards,
Scott[/QUOTE

Here is what my code looks like now:
[CODE=sql]SELECT tblnotes.[Notes ID], tblnotes.[Notes 1], tblnotes.Custom erID
FROM tblnotes
WHERE (((tblnotes.Cus tomerID)=Forms! frmcustomerinfo rmation.txtcust omeridl));[/CODE]

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,phon e 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 frmcustomerinfo rmation. 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 frmcustomerinfo rmation 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:
[CODE=sql]
SELECT tblnotes.[Notes ID], tblnotes.[Notes 1], tblnotes.Custom erID FROM tblnotes WHERE tblnotes.Custom erID = Forms![YourCustomerFor mName].[YourCustomerIDC ontrol][/CODE]

Regards,
Scott[/QUOTE

Here is what my code looks like now:
[CODE=sql]SELECT tblnotes.[Notes ID], tblnotes.[Notes 1], tblnotes.Custom erID
FROM tblnotes
WHERE (((tblnotes.Cus tomerID)=Forms! frmcustomerinfo rmation.txtcust omeridl));[/CODE]

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,phon e 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 frmcustomerinfo rmation. 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 frmcustomerinfo rmation 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 frmcustomerinfo rmation.

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: [CODE=vb]Me.[YourCustomerIDC ontrolName] = Forms!frmcustom erinformation.t xtcustomerid
[/CODE]

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

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 frmcustomerinfo rmation.

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: [CODE=vb]Me.[YourCustomerIDC ontrolName] = Forms!frmcustom erinformation.t xtcustomerid
[/CODE]

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

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