Add notes with command button (1 Viewer)

Emma35 · Jan 10, 2020

Hi All,
I have a data entry form where i add information about a supplier. Due to space restrictions, i need to add a command button to the form which will open a pop up form where i can add notes about that supplier. I have two tables like below

tbl_Suppliers
SupplierID Autonumber PK
SupplierName ShortText
PhoneNo Number
Address LongText

tblNotes
NotesID AutoNumber PK
SupplierID Number FK
Comments LongText

I've created a relationship between the two tables but i get a message when i click the command button telling me a related record is required in tbl_Suppliers ?

I'm confused

Ranman256 · Jan 10, 2020

make a form with 2 fields UNBOUND.
SupplierID
Note

click the button, open the form, send the Supplier ID to the form.
then user clicks save, run an append query.

Code:

sub btnAddNote_click()
  docmd.openform "frmAddNote"   ' pop up dialog here if needed
  forms!frmAddNote!txtID = forms!myMasterForm!txtSupplierID
end sub

sub btnSave_Click()
  sSql = "Insert into tblNotes (supplierID, Comments) Values (" & txtID & ",'" & txtNote 
& "')"
  docmd.runSql sSql
end sub

Emma35 · Jan 10, 2020

Thanks Ranman....i have your code running and the notes form opens ok but when i try to enter some notes and press save i get a INSERT INTO syntax error in this line of code highlighted in yellow

DoCmd.RunSQL sSql

Edit : Sorry, the code on the Save button is

Code:

Private Sub btnSave_Click()
sSql = "Insert into tbl_SupplierNotes (SupplierID, Comments) Values (" & SupID & ",'" & Notes & "')"

  DoCmd.RunSQL sSql
End Sub

Gasman · Jan 10, 2020

Debug.Print sSql and report back what that shows.
Are you sure it is a syntax error and not runtime error, as the parent record will need to exist.?

Emma35 · Jan 10, 2020

Nothing comes into the immediate window ?

Gasman · Jan 10, 2020

Emma35 said:
Nothing comes into the immediate window ?

You have to put the debug.print statement into your code and then comment it our when no longer needed.?

I would have thought you would also need a Dim statement for sSql ?
You should have Option Explicit at the top of every module?

Emma35 · Jan 10, 2020

That's what i did but nothing shows. I don't know about the other stuff it's miles over my head !

Gasman · Jan 10, 2020

Emma35 said:
That's what i did but nothing shows. I don't know about the other stuff it's miles over my head !

so your code should look like

Code:

Private Sub btnSave_Click()
Dim sSql as String
sSql = "Insert into tbl_SupplierNotes (SupplierID, Comments) Values (" & SupID & ",'" & Notes & "')"
    Debug.Print sSql

    DoCmd.RunSQL sSql
End Sub

I have just created a few lines in the Immediate window and your syntax looks OK to me

However you should get the output in the immediate window.

Is the event even being run?

Code:

sSql = "Insert into tbl_SupplierNotes (SupplierID, Comments) Values (" & SupID & ",'" & Notes & "')"
SupID=2
Notes="Test notes"
? ssql
Insert into tbl_SupplierNotes (SupplierID, Comments) Values (2,'Test notes')

Edit:
Prefix SupID and Notes with Me.

and try again, however you should get something displayed.
HTH

Emma35 · Jan 10, 2020

Ok tried that but still nothing in the immediate window. This is the code i used

Code:

Private Sub btnSave_Click()
sSql = "Insert into tbl_SupplierNotes (SupplierID, Comments) Values (" & Me.SupID & ",'" & Me.Notes & "')"

  Debug.Print sSql
  
  DoCmd.RunSQL sSql
  
    
End Sub

I've attached the error message i'm getting when trying to add notes

jdraw · Jan 10, 2020

Emma35,

Just a question re your requirement.

If you have/could have multiple comments per Supplier, wouldn't you want a CommentDate or some other attribute regarding comments for your use?

If we knew more about the "application" generally, it might streamline posting and communication.
Good luck with your project.

Gasman · Jan 10, 2020

Can you upload enough of the DB to replicate the issue?

Just zip the DB and attach in Advanced mode

Emma35 said:
Ok tried that but still nothing in the immediate window. This is the code i used

Code:

Private Sub btnSave_Click() sSql = "Insert into tbl_SupplierNotes (SupplierID, Comments) Values (" & Me.SupID & ",'" & Me.Notes & "')" Debug.Print sSql DoCmd.RunSQL sSql End Sub

I've attached the error message i'm getting when trying to add notes

Emma35 · Jan 10, 2020

Ok thanks guys....i'm just leaving now but i;ll upload something on Mon morning (away for the weekend).

Emma35 · Jan 13, 2020

Hi again....here's a stripped down version. The form containing the command button (Notes) is SupplierSub. The form which opens to allow the notes to be added is frm_SupplierNotes

Thanks for having a look.

jdraw · Jan 13, 2020

Emma,

You seem to be implementing "bits of code" that are suggested by posters/responders. All well and good if they meet your needs. I'm still not very clear on what you are trying to do.
I see a mix of vba and embedded macros and a form in dialog mode. This may be spot on to your requirement.
It would be helpful if you described in a step by step manner, a few sample records for the Supplier and the Notes associated.
This is not intended as a comment against advice being given, it's sole purpose is to make sure we are all on the same page addressing your goal.

Good luck.

Emma35 · Jan 13, 2020

Ok....when i'm adding information about a supplier i want to be able to click the 'Notes' button to bring up the form frm_SupplierNotes so as i can add other details about that supplier. I don't need dates or anything, just another area where i can add additional comments about that supplier

jdraw · Jan 13, 2020

What is the purpose of aliasing all the fields to Expr#?

Code:

SELECT tbl_Suppliers.SupplierID AS Expr1
	,tbl_Suppliers.IngID AS Expr2
	,tbl_Suppliers.MainContact AS Expr3
	,tbl_Suppliers.AltContact AS Expr4
	,tbl_Suppliers.MainPhone AS Expr5
	,tbl_Suppliers.AltPhone AS Expr6
	,tbl_Suppliers.MainEmail AS Expr7
	,tbl_Suppliers.AltEmail AS Expr8
	,tbl_Suppliers.VendorID AS Expr9
	,tbl_Suppliers.Supplier AS Expr10
FROM tbl_Suppliers;

Gasman · Jan 13, 2020

You need to be consistent in your names.
The first form has SuppliedID and the second SupID, but you were using SupplierID ?

Was

Code:

Private Sub btnAddNote_click()

  DoCmd.OpenForm "frm_SupplierNotes"   ' pop up dialog here if needed
  Forms!frm_SupplierNotes!SupplierID = Me.SupplierID
End Sub

Now

Code:

Private Sub btnAddNote_click()

  DoCmd.OpenForm "frm_SupplierNotes"   ' pop up dialog here if needed
  Forms!frm_SupplierNotes!SupID = Me.SupplierID
End Sub

So SupID was never set for the Insert statement.

Not sure why you are using all those Exprn fileds and not the data.

Consider
How you I get to the notes already created?
Am I always going to want to add a note here.?
Show the ID/key field whilst creating, then shrink/make invisible.

HTH

Emma35 · Jan 14, 2020

I'm sorry that was my mistake. The form SuppliersSub is actually a subform which might be the reason for the Expr thing. I accidentally deleted the main form before uploading the database. I'll change SupID back to SupplierID

Emma35 · Jan 15, 2020

Ok changed the name back to SupplierID. When i go to add a note, i click Save and i get the 'You are about to append (1) record message'. I then click ok and get the error message attached.

Gasman · Jan 15, 2020

It works for me, though I am using existing Suppliers?

Post what you have for this event

Code:

Private Sub btnAddNote_click()

  DoCmd.OpenForm "frm_SupplierNotes"   ' pop up dialog here if needed
  Forms!frm_SupplierNotes!SupID = Me.SupplierID
End Sub

Add notes with command button (1 Viewer)

Registered User.

Well-known member

Registered User.

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Registered User.

Enthusiastic Amateur

Registered User.

Attachments

Super Moderator

Enthusiastic Amateur

Registered User.

Registered User.

Attachments

Super Moderator

Registered User.

Super Moderator

Enthusiastic Amateur

Registered User.

Registered User.

Attachments

Enthusiastic Amateur

Similar threads

Users who are viewing this thread