VBA Code to Append a Record not working

[PNGBill]

here is my latest..

Trying to append a record to tblLoanAppComm.
3 fields

strLoanMessage = "Cancel Loan Due to "          'Start tblLoanComm Message
    strLoanMessage = strLoanMessage & strReason     'Include Reason
    strAppMessage = "Cancel Application Due to "    'Start tblLoanAppComm Message
    strAppMessage = strAppMessage & strReason       'Include Reason
    
    'SQL to Append Message to tblLoanAppComm
    strSQL = "INSERT INTO tblLoanAppComm ( LoanAppID, CommNotes, Operator )"
    strSQL = strSQL & " SELECT AID, strAppMessage, strUserRole"
    
    CurrentDb.Execute strSQL, dbFailOnError           'Run SQL

error message is to "Too few parameters. Expected 3.

 

[SteveSchapel]

Bill,

What does "SELECT AID" mean? If you mean this to be a SELECT clause, then I think you are going to need a FROM clause? Or else where does the value of AID come from? Otherwise, if the value of AID comes from for example the value of a control on a form, then you are going to need to correctly reference it, and use a VALUES clause in the SQL.

In addition to the above comments, the syntax of the SQL is not correct, around the strAppMessage and strUserRole... you need to concatenate the commas into the string, enclosed in quotes etc. I (or somebody) can help with that if required, after we clarify what the SELECT AID refers to.

 

[PNGBill]

Here is my edited code

 strSQL = "INSERT INTO tblLoanAppComm (LoanAppID, CommNotes, Operator)"
    strSQL = strSQL & " VALUES ('" & AID & "', " & " strAppMessage " & ", strUserRole);"

Found a post that referred to values and changed select to values.

AID, strAppMessage & strUserRole are all defined earlier in the code.

I use AID in a earlier sql and this works so the value should be available to this part of the code.

Really confused by the " & '

 

[SteveSchapel]

Bill,

I think the syntax should be like this:

[COLOR=black]  [FONT=Courier New]strSQL [COLOR=#007700]= [/COLOR][/FONT][/COLOR][FONT=Courier New][COLOR=black][COLOR=#dd0000]"INSERT INTO tblLoanAppComm ( LoanAppID, CommNotes, Operator )" & _
         [/COLOR][COLOR=#dd0000]" VALUES ( '" [/COLOR][COLOR=#007700]& [/COLOR][COLOR=#0000bb]AID [/COLOR][COLOR=#007700]& [/COLOR][COLOR=#dd0000]"', '" [/COLOR][COLOR=#007700]& [/COLOR][COLOR=#dd0000]strAppMessage [/COLOR][COLOR=#007700]& [/COLOR][COLOR=#dd0000]"', '" & strUserRole & "' )"

[/COLOR][/COLOR][/FONT]

This assumes AID is text.

 

[PNGBill]

Doesn't like this at all

'SQL to Append Message to tblLoanAppComm
    strSQL = "INSERT INTO tblLoanAppComm (LoanAppID, CommNotes, Operator)" &_
            " VALUES ('" & AID & "', '" & strAppMessage & "', '" & strUserID & "' )"
    
    CurrentDb.Execute strSQL, dbFailOnError           'Run SQL

totally red.

AID is integer.

Changed strUserRole to strUserID as the wrong value was being picked up. Role rather then initials.

Tested the three variables and they all came up on a message box which i guess proves they are available to be appended.

 

[PNGBill]

On tblLoanAppComm LoanAppID is long integer, Operator is text and CommNotes is Memo.

Here is extracts from the code.

Dim stDocName, stLinkCriteria, strLoanMessage, strAppMessage, strReason, strSQL, strUserID As String
    Dim LID As Integer
    Dim AID As Integer

AID = Me.APLPK

strAppMessage = "Cancel Application Due to "    'Start tblLoanAppComm Message
    strAppMessage = strAppMessage & strReason       'Include Reason

strUserID = UCase(CurrentUser)

 

[PNGBill]

This gives to few parameters error 1 - the number has reduced from 3 to 2 and now to 1

'SQL to Append Message to tblLoanAppComm
    strSQL = "INSERT INTO tblLoanAppComm (LoanAppID, CommNotes, Operator)" & " VALUES ( AID, '" & strAppMessage & "', '" & strUserID & "' )"
    
    CurrentDb.Execute strSQL, dbFailOnError           'Run SQL

 

[vbaInet]

Notice the integer, AID, is not surrounded in single quotes as per Steve's last post:

strSQL = "INSERT INTO tblLoanAppComm ( LoanAppID, CommNotes, Operator )" & _
         " VALUES (" & AID & ", " & strAppMessage & ", '" & strUserRole & "';)"

The other thing is you shouldn't be doing this:

strAppMessage = "message"
strAppMessage = strAppMessage & "message continued"

Unless when necessary, in your case it isn't. It's just a continuous string and should be like this:
strAppMessage = "message" & "message continued"

 

[PNGBill]

This works!!!

Thanks Steve and VBI.

Used VBI's advice but had to drop the end;

'SQL to Append Message to tblLoanAppComm
    strSQL = "INSERT INTO tblLoanAppComm (LoanAppID, CommNotes, Operator)" & " VALUES (" & AID & ", '" & strAppMessage & "', '" & strUserID & "')"
    
    CurrentDb.Execute strSQL, dbFailOnError           'Run SQL

Really appreciate your patience.

I won't leave my VBA book in Raglan (NZ) next time

 

[vbaInet]

You can still write it like Steve wrote it, as long as you close the string at the end of each line.

What the underscore at the end of a string means is that the next line should be regarded as part of the current line (but just on a different line).

 

[PNGBill]

Thanks again. and have changed the message string to one line each instead of two lines each.

Just one little bit of a "scratch" on the screen and the code won't work.

 

[vbaInet]

I actually prefer multiple lines, unless you've got a 32" widescreen lcd? It's only for legibility and readability really.

 

[PNGBill]

19" and two monitors.

Will try and make them two lines but just soaking up the pleasure for now before I mess it up.

 

[vbaInet]

Nice!

I'm sure Steve will be pleased to see you writing two lines

 

[SteveSchapel]

Nice!

I'm sure Steve will be pleased to see you writing two lines

LOL! Presentation is everything!

Actually, Bill, at one stage back in the thread, one of the problems was that you missed out the required space between the & and the _ line continuation character. Put it down to a scratch on the screen if you want to, but ...

 

[PNGBill]

Here is the final working two line version of the code

 'SQL to Append Message to tblLoanAppComm
    strSQL = "INSERT INTO tblLoanAppComm (LoanAppID, Operator, CommNotes)" & _
            " VALUES (" & AID & ", '" & strUserID & "', '" & strAppMessage & "')"
    
    CurrentDb.Execute strSQL, dbFailOnError           'Run SQL

Thanks for your assistance Steve and VBI.

One error I made in the two line version was to type &_ instead of &^_ ie space between.

The old basic code writing where you use a triangle to identify a space no longer seems important and it really is hard to identify spaces some times.

The complete code allowed me to delete 7 queries, one macro and avoid a temp table.
Of course the 7 queries may have been over the top but still, now it is all in the Form.

 

[SteveSchapel]

Bill,

Great to hear that you have got it sorted now.

 

[SteveSchapel]

By the way, Bill, since we are fine-tuning here, there is another line in your code that caught my eye:

Dim stDocName, stLinkCriteria, strLoanMessage, strAppMessage, strReason, strSQL, strUserID As String

Do you realise that this will dimension strUserID as a String variable, and all the other variables as Variant? Is that what you intended? If not, you need to explicitly assign the type to each variable in your Dim statement.

 

[vbaInet]

By the way, Bill, since we are fine-tuning here, there is another line in your code that caught my eye:

Dim stDocName, stLinkCriteria, strLoanMessage, strAppMessage, strReason, strSQL, strUserID As String

Do you realise that this will dimension strUserID as a String variable, and all the other variables as Variant? Is that what you intended? If not, you need to explicitly assign the type to each variable in your Dim statement.

That does get alot of people. I wonder why that wasn't ever properly explained.

 

[PNGBill]

By the way, Bill, since we are fine-tuning here, there is another line in your code that caught my eye:

Dim stDocName, stLinkCriteria, strLoanMessage, strAppMessage, strReason, strSQL, strUserID As String

Do you realise that this will dimension strUserID as a String variable, and all the other variables as Variant? Is that what you intended? If not, you need to explicitly assign the type to each variable in your Dim statement.

Is this better?

Dim stDocName As String
    Dim stLinkCriteria As String
    Dim strLoanMessage As String
    Dim strAppMessage As String
    Dim strReason As String
    Dim strSQL As String
    Dim strUserID As String
    Dim strMesg As String
    Dim LID As Integer
    Dim AID As Integer

I guess this is the price of copying what is done rather then learning