Append Combobox Value to Table

[Dmak]

I have looked for hours trying to find a working solution for something that seems so simple.

I have a form called Enquiries
In the Enquiries form is a subform called enquiry notes
I have added an unbound textbox so users can add notes to enquiries
Notes are stored in a table called tblEnquiryNotes, along with UserID and bound by EnquiryID.

Essentially I'm after a comment system similar to facebook, type message, hit enter and it's there.

I'm struggling, please help

 

[Isskint]

This can be done by using some SQL and the DoCmd.RunSQL method. The SQL string would need to be an INSERT INTO.

EG "INSERT INTO

([Field1], [Field2], Field3) VALUES (Forms!YourForm.[Control1] , Forms!YourForm.[Control2] , Forms!YourForm.[Control3] );"

Text fields would need to be enclosed in single quotes '
Numeric fields are fin as is
Date fields would need to be enclosed in # markers

 

[Dmak]

This can be done by using some SQL and the DoCmd.RunSQL method. The SQL string would need to be an INSERT INTO.

EG " INSERT INTO

([Field1], [Field2], Field3) VALUES (Forms!YourForm.[Control1] , Forms!YourForm.[Control2] , Forms!YourForm.[Control3] );"

Text fields would need to be enclosed in single quotes '
Numeric fields are fin as is
Date fields would need to be enclosed in # markers

Thank you! I'm getting the error message as attached. I haven't yet learnt enough to know what this means. Any help much appreciated!

 

[Isskint]

Basically something is written wrong - either simple syntax or name of an object etc.
Can you post the code you have written for the OnExit event?

 

[Dmak]

Basically something is written wrong - either simple syntax or name of an object etc.
Can you post the code you have written for the OnExit event?

I've tried loads of things now, nothing works, very frustrating.

I've attached an example of what I want to achieve in my main project. It would be interesting to see how different people achieve the same result and also REALLY useful and much appreciated. I've removed my attempts at VBA for this.

Thank you!

 

[Dmak]

I must have refreshed this page a hundred times today. I would really appreciate help on the above. Thank you

 

[Isskint]

You had no code in your attached DB so i can not see where you were going wrong. This code will achieve what you are looking for

Dim strSQL As String

strSQL = "INSERT INTO tblChat ( TopicID, UserID, MsgDate, Message ) VALUES (" & Me.[TopicID] & ", " & Me.[UserID]
strSQL = strSQL & ", #" & Now() & "#, '" & Me.[tboAddMessage] & "'); "

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

Me.tboAddMessage = ""

Forms![frmTopics]![sfmChat].Form.Requery
Forms![frmTopics]![sfmChat].Form.Recordset.MoveLast

It goes in the AfterUpdate event of tboAddMessage

 

[Dmak]

That's brilliant, thank you, much appreciated

 

[Dmak]

Applied the code and it works great. I added a couple of lines "Recordset.MovePrevious" so more of the thread is shown. This causes an error on new threads but not a problem for my main project as the new comments will be delivered to the top, makes things easier. Not to the norm but fit for purpose.

The only other problem is with words containing apostrophes in the message which conflict with the code. Is there an easy workaround for this?

Thanks again

 

[Isskint]

The 'easy' way is to stop your users using it

add this to the code, it will replace ' with "" so you get Don't = Dont. if you want to replace it with another character (say -) then put that where the "" are.

Dim SngApos As String

SngApos = Chr(39)'ASCII for '

Me.[tboAddMessage]= Replace(Me.[tboAddMessage], e, "")

 

[Dmak]

Thanks again, I've tried getting that code addition to work but no luck. I'll figure it out though Think I'll upload the working chat db soon, after a few tweaks as it might be useful to other beginners like myself.