Solved Memo Enteries (1 Viewer)

StuartG

Registered User.
Local time
Today, 07:55
Joined
Sep 12, 2018
Messages
125
Hi all,
A form was designed for me by a member of our Forum and i've been requested to add a column but the results are not showing and i cannot work out why and hope someone could help.

The issue is the OrderProcessor is not showing in the memo log box when the user saves the comment.

When a user wants to add a comment they select per the below:

1623405898513.png




User entry view: I added "OrderProcessor"
1623405319228.png



Table:

1623405412289.png


Form:
1623405498417.png


Code for new comment:
1623405602684.png
 
Last edited:

Minty

AWF VIP
Local time
Today, 07:55
Joined
Jul 26, 2013
Messages
8,760
You would need to add the field and the combo box contents to your INSERT SQL Code.

(... , OrderProcessor) " & _
Values (... , '" & Me.YourComboControlName & "' ; " )
 

StuartG

Registered User.
Local time
Today, 07:55
Joined
Sep 12, 2018
Messages
125
You would need to add the field and the combo box contents to your INSERT SQL Code.

(... , OrderProcessor) " & _
Values (... , '" & Me.YourComboControlName & "' ; " )
Thanks for the reply.
How should I write it though, should i just slot it into values.

Private Sub cmdSave_Click()

If Nz(Me.Comments, "") = "" Then
MsgBox "You have not entered a comment", vbCritical, "Nothing to save"
Me.Comments.SetFocus
Exit Sub
Else
Call SpellChecker(Me.Comments)
End If

' append new comment to tblMemoFieldVH
CurrentDb.Execute "INSERT INTO tblMemoFieldVH (IssuesID, MemoField, MemoFieldDateTime)" & _
" VALUES (" & Me.ID & ", '" & Me.Comments & "', #" & Format(Now(), "mm/dd/yyyy hh:nn:ss") & "#);"

DoCmd.Close acForm, Me.Name

' refresh comments subform
Forms!frmUpdateForm.fsubMemoFieldVH.Form.Requery

End Sub
 

StuartG

Registered User.
Local time
Today, 07:55
Joined
Sep 12, 2018
Messages
125
Thanks for the reply.
How should I write it though, should i just slot it into values.

Private Sub cmdSave_Click()

If Nz(Me.Comments, "") = "" Then
MsgBox "You have not entered a comment", vbCritical, "Nothing to save"
Me.Comments.SetFocus
Exit Sub
Else
Call SpellChecker(Me.Comments)
End If

' append new comment to tblMemoFieldVH
CurrentDb.Execute "INSERT INTO tblMemoFieldVH (IssuesID, MemoField, MemoFieldDateTime)" & _
" VALUES (" & Me.ID & ", '" & Me.Comments & "', #" & Format(Now(), "mm/dd/yyyy hh:nn:ss") & "#);"

DoCmd.Close acForm, Me.Name

' refresh comments subform
Forms!frmUpdateForm.fsubMemoFieldVH.Form.Requery

End Sub

I've updated the line, but i now get a
1623408499554.png

Private Sub cmdSave_Click()

If Nz(Me.Comments, "") = "" Then
MsgBox "You have not entered a comment", vbCritical, "Nothing to save"
Me.Comments.SetFocus
Exit Sub
Else
Call SpellChecker(Me.Comments)
End If

' append new comment to tblMemoFieldVH
CurrentDb.Execute "INSERT INTO tblMemoFieldVH (IssuesID, MemoField, MemoFieldDateTime, OrderProcessor)" & _
" VALUES (" & Me.ID & ", '" & Me.Comments & "', #" & Format(Now(), "mm/dd/yyyy hh:nn:ss") & "#);" & Me.OrderProcessor & ";"

DoCmd.Close acForm, Me.Name

' refresh comments subform
Forms!frmUpdateForm.fsubMemoFieldVH.Form.Requery

End sub
 

Minty

AWF VIP
Local time
Today, 07:55
Joined
Jul 26, 2013
Messages
8,760
You are very close - This should work.

Code:
CurrentDb.Execute "INSERT INTO tblMemoFieldVH (IssuesID, MemoField, MemoFieldDateTime, OrderProcessor)" & _
" VALUES (" & Me.id & ", '" & Me.Comments & "', #" & Format(Now(), "mm/dd/yyyy hh:nn:ss") & "#,  '" & Me.OrderProcessor & "' );"

To assist you further in future I would assign the SQL to a string then debug.print it out in the immediate window;
Code:
Private Sub cmdSave_Click()
   
    Dim strSql As String
   
    If Nz(Me.Comments, "") = "" Then
        MsgBox "You have not entered a comment", vbCritical, "Nothing to save"
        Me.Comments.SetFocus
        Exit Sub
    Else
        Call SpellChecker(Me.Comments)
    End If

    ' append new comment to tblMemoFieldVH
   
    strSql = "INSERT INTO tblMemoFieldVH (IssuesID, MemoField, MemoFieldDateTime, OrderProcessor)" & _
        " VALUES (" & Me.id & ", '" & Me.Comments & "', #" & Format(Now(), "mm/dd/yyyy hh:nn:ss") & "# , '" & Me.OrderProcessor & "' );"
   
    Debug.Print strSql
   
    CurrentDb.Execute strSql
    DoCmd.Close acForm, Me.Name

    ' refresh comments subform
    Forms!frmUpdateForm.fsubMemoFieldVH.Form.Requery

End Sub

This lets you "See" the actual values and structures being passed to Access to execute.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:55
Joined
May 7, 2009
Messages
13,796
Code:
'' append new comment to tblMemoFieldVH
'CurrentDb.Execute "INSERT INTO tblMemoFieldVH (IssuesID, MemoField, MemoFieldDateTime, OrderProcessor)" & _
'" VALUES (" & Me.ID & ", '" & Me.Comments & "', #" & Format(Now(), "mm/dd/yyyy hh:nn:ss") & "#);" & Me.OrderProcessor & ";"

With CurrentDb.CreateQueryDef("", "INSERT INTO tblMemoFieldVH (IssuesID, MemoField, MemoFieldDateTime, OrderProcessor) " & _
            "SELECT p1, p2, p3, p4;")
        .Parameters("p1") = Me.ID
        .Parameters("p2") = Me.Comments
        .Parameters("p3") = Now()
        .Parameters("p4") = Me.OrderProcessor
        .Execute
End With
 

StuartG

Registered User.
Local time
Today, 07:55
Joined
Sep 12, 2018
Messages
125
You are very close - This should work.

Code:
CurrentDb.Execute "INSERT INTO tblMemoFieldVH (IssuesID, MemoField, MemoFieldDateTime, OrderProcessor)" & _
" VALUES (" & Me.id & ", '" & Me.Comments & "', #" & Format(Now(), "mm/dd/yyyy hh:nn:ss") & "#,  '" & Me.OrderProcessor & "' );"

To assist you further in future I would assign the SQL to a string then debug.print it out in the immediate window;
Code:
Private Sub cmdSave_Click()
 
    Dim strSql As String
 
    If Nz(Me.Comments, "") = "" Then
        MsgBox "You have not entered a comment", vbCritical, "Nothing to save"
        Me.Comments.SetFocus
        Exit Sub
    Else
        Call SpellChecker(Me.Comments)
    End If

    ' append new comment to tblMemoFieldVH
 
    strSql = "INSERT INTO tblMemoFieldVH (IssuesID, MemoField, MemoFieldDateTime, OrderProcessor)" & _
        " VALUES (" & Me.id & ", '" & Me.Comments & "', #" & Format(Now(), "mm/dd/yyyy hh:nn:ss") & "# , '" & Me.OrderProcessor & "' );"
 
    Debug.Print strSql
 
    CurrentDb.Execute strSql
    DoCmd.Close acForm, Me.Name

    ' refresh comments subform
    Forms!frmUpdateForm.fsubMemoFieldVH.Form.Requery

End Sub

This lets you "See" the actual values and structures being passed to Access to execute.
Thank you Minty. I've done this and it worked :)
Once last question..

If i wanted the form to check whether the OrderProcesser field had been completed prior to saving how would I build this into the code.
I'd want it to read.
"Please complete OrderProcessor before saving"

I've used inbuilt macros before but this entire form has been written in code which i am not ofay with.
 

Minty

AWF VIP
Local time
Today, 07:55
Joined
Jul 26, 2013
Messages
8,760
Something like this in your code before you commit to entering the record.
Code:
If Len(Me.OrderProcessor & "") < 1 Then
        MsgBox "Please complete OrderProcessor before saving", vbCritical, "Missing Processor"
        Me.Processor.SetFocus
        Exit Sub
    End If
 

StuartG

Registered User.
Local time
Today, 07:55
Joined
Sep 12, 2018
Messages
125
Something like this in your code before you commit to entering the record.
Code:
If Len(Me.OrderProcessor & "") < 1 Then
        MsgBox "Please complete OrderProcessor before saving", vbCritical, "Missing Processor"
        Me.Processor.SetFocus
        Exit Sub
    End If

It does not seem to like the first line?
I've provided the full line of code for reference.

Code:
Private Sub cmdSave_Click()
    
    Dim strSql As String
   
    If Nz(Me.Comments, "") = "" Then
        MsgBox "You have not entered a comment", vbCritical, "Nothing to save"
        Me.Comments.SetFocus
        Exit Sub
    Else
        Call SpellChecker(Me.Comments)
    End If

    ' append new comment to tblMemoFieldVH
   
    strSql = "INSERT INTO tblMemoFieldVH (IssuesID, MemoField, MemoFieldDateTime, OrderProcessor)" & _
        " VALUES (" & Me.ID & ", '" & Me.Comments & "', #" & Format(Now(), "mm/dd/yyyy hh:nn:ss") & "# , '" & Me.OrderProcessor & "' );"
   
    Debug.Print strSql
   
    CurrentDb.Execute strSql
    DoCmd.Close acForm, Me.Name
    
    If Len(Me.OrderProcessor & "") < 1 Then
        MsgBox "Please complete OrderProcessor before saving", vbCritical, "Missing Processor"
        Me.OrderProcessor.SetFocus
        Exit Sub
    End If

 
' refresh comments subform
  Forms!frmUpdateForm.fsubMemoFieldVH.Form.Requery

End Sub

[ATTACH type="full"]92298[/ATTACH]
 

Attachments

  • 1623416545840.png
    1623416545840.png
    6.8 KB · Views: 22

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 14:55
Joined
May 7, 2009
Messages
13,796
you already closed your form?
 

StuartG

Registered User.
Local time
Today, 07:55
Joined
Sep 12, 2018
Messages
125
It does not seem to like the first line?
I've provided the full line of code for reference.

Code:
Private Sub cmdSave_Click()
   
    Dim strSql As String
  
    If Nz(Me.Comments, "") = "" Then
        MsgBox "You have not entered a comment", vbCritical, "Nothing to save"
        Me.Comments.SetFocus
        Exit Sub
    Else
        Call SpellChecker(Me.Comments)
    End If

    ' append new comment to tblMemoFieldVH
  
    strSql = "INSERT INTO tblMemoFieldVH (IssuesID, MemoField, MemoFieldDateTime, OrderProcessor)" & _
        " VALUES (" & Me.ID & ", '" & Me.Comments & "', #" & Format(Now(), "mm/dd/yyyy hh:nn:ss") & "# , '" & Me.OrderProcessor & "' );"
  
    Debug.Print strSql
  
    CurrentDb.Execute strSql
    DoCmd.Close acForm, Me.Name
   
    If Len(Me.OrderProcessor & "") < 1 Then
        MsgBox "Please complete OrderProcessor before saving", vbCritical, "Missing Processor"
        Me.OrderProcessor.SetFocus
        Exit Sub
    End If


' refresh comments subform
  Forms!frmUpdateForm.fsubMemoFieldVH.Form.Requery

End Sub

[ATTACH type="full"]92298[/ATTACH]
you already closed your form?

I clicked the "Save comment" and it does the spell check and then comes up with the error message, it is not alerting the user they have not completed the "OrderProcesser" field just throws up the error.

could it be that the If Len code is in the wrong place?
 

Minty

AWF VIP
Local time
Today, 07:55
Joined
Jul 26, 2013
Messages
8,760
You need to move the code to before your insert;

Code:
Private Sub cmdSave_Click()
    
    Dim strSql As String
  
    If Nz(Me.Comments, "") = "" Then
        MsgBox "You have not entered a comment", vbCritical, "Nothing to save"
        Me.Comments.SetFocus
        Exit Sub
    Else
        Call SpellChecker(Me.Comments)
    End If

 
    If Len(Me.OrderProcessor & "") < 1 Then
        MsgBox "Please complete OrderProcessor before saving", vbCritical, "Missing Processor"
        Me.OrderProcessor.SetFocus
        Exit Sub
    End If
  
   ' append new comment to tblMemoFieldVH
  
    strSql = "INSERT INTO tblMemoFieldVH (IssuesID, MemoField, MemoFieldDateTime, OrderProcessor)" & _
        " VALUES (" & Me.ID & ", '" & Me.Comments & "', #" & Format(Now(), "mm/dd/yyyy hh:nn:ss") & "# , '" & Me.OrderProcessor & "' );"
  
    Debug.Print strSql
  
    CurrentDb.Execute strSql
    DoCmd.Close acForm, Me.Name
 

 
' refresh comments subform
  Forms!frmUpdateForm.fsubMemoFieldVH.Form.Requery

End Sub
 

StuartG

Registered User.
Local time
Today, 07:55
Joined
Sep 12, 2018
Messages
125
You need to move the code to before your insert;

Code:
Private Sub cmdSave_Click()
   
    Dim strSql As String
 
    If Nz(Me.Comments, "") = "" Then
        MsgBox "You have not entered a comment", vbCritical, "Nothing to save"
        Me.Comments.SetFocus
        Exit Sub
    Else
        Call SpellChecker(Me.Comments)
    End If


    If Len(Me.OrderProcessor & "") < 1 Then
        MsgBox "Please complete OrderProcessor before saving", vbCritical, "Missing Processor"
        Me.OrderProcessor.SetFocus
        Exit Sub
    End If
 
   ' append new comment to tblMemoFieldVH
 
    strSql = "INSERT INTO tblMemoFieldVH (IssuesID, MemoField, MemoFieldDateTime, OrderProcessor)" & _
        " VALUES (" & Me.ID & ", '" & Me.Comments & "', #" & Format(Now(), "mm/dd/yyyy hh:nn:ss") & "# , '" & Me.OrderProcessor & "' );"
 
    Debug.Print strSql
 
    CurrentDb.Execute strSql
    DoCmd.Close acForm, Me.Name



' refresh comments subform
  Forms!frmUpdateForm.fsubMemoFieldVH.Form.Requery

End Sub

Excellent, this worked perfectly :)
Thanks Minty!
 

Users who are viewing this thread

Top Bottom