Unbound form to add data only 2

[BrenoAguiar]

Any one that could save me on that:
I have a form (unbound) that have two text boxes. One is capturing the username and real time (=Current user & Now() )"usertime" and the other is called "addnotes". What I need is to combine the information typed in the "addnotes" text box and in the "usertime" text box and send that to my NOTES field in the UNITS Table. But that needs to be adding data only and not deleting the previous notes on that record.

Any help would be really appreciated!
Breno

 

[PHV]

Something like this ?
DoCmd.RunSQL "UPDATE Units SET Notes=Notes & '" & [usertime] & "' & '" & [addnotes] & "' WHERE ...."

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pkailas]

is this NOTES field part of the recordset of the UserName?

Or in otherwords is this a table containing these fields?

if so you'd have to use a select statement to return the current value and then add the new value to the existing one sorta like this.

sql = "Select * from yourtable where username = '" & username & "'"
rs.open sql, yourconnection
if not rs.eof then
   rs("notes") = rs("notes") & " " & yournewnotes
   rs("usertime") = now
   rs.update
end if
rs.close

 

[BrenoAguiar]

PHV and Pkailias,

I used what you gave me but this way:

DoCmd.RunSQL "UPDATE Units SET Notes=[usertime] & [addnotes] where [computer code] = " & Me!compcode

The "Computer code" is the Primary key in my UNITS table. The Compcode is the unbound text box where the user needs to type the Computer code that the NOTES will be added to. But I get an error msg "type mismatch in the query".

I do not have a USERNAME field in my UNITS table. I wanted just to capture that from my unbound form text box "usertime", plus the text box "addnotes" and combine that to the NOTES field in my UNITS table. The Cmd above worked fine until the WHERE condition was added.

thanks Guys

 

[PHV]

Replace this:
where [computer code] = " & Me!compcode
By this:
where [computer code] = [tt]'"[/tt] & Me!compcode & [tt]"'"[/tt]
The Cmd above worked fine
Really ? How the SQL engine knows the values of =[usertime] & [addnotes] and how are the previous values of Notes preserved ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[pkailas]

do what PHV suggests, but you still have one more issue. Your query will replace whatever is in the NOTES field with the new value you are sending. Try this.

DoCmd.RunSQL "UPDATE Units SET Notes = Notes " & [usertime] & [addnotes] where [computer code] = '" & Me!compcode & "'"

 

[BrenoAguiar]

Sorry, it worked fine to ADD the data, but would add stuff in ALL the records until I set up the WHERE condition, which, by the way, worked great now with what you sent me. Just one thing: Its DELETING the previous notes, everytime you add another. I really neede to Keep waht is in the record.
is that possible ?

here is the cmd:
DoCmd.RunSQL "UPDATE Units SET Notes=[usertime] & [addnotes] where [computer code] = '" & Me!compcode & "'"

thanks again
Breno

 

[pkailas]

did you see my previous post?

 

[PHV]

Combining my two post you already have the solution.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BrenoAguiar]

I used the code from you guys above and now I get a "Syntax error"

DoCmd.RunSQL "UPDATE Units SET Notes = Notes " & [usertime] & [addnotes] where [computer code] = '" & Me!compcode & "'"

?

 

[pkailas]

replace
DoCmd.RunSQL "UPDATE Units SET Notes = Notes " & [usertime] & [addnotes] where [computer code] = '" & Me!compcode & "'"

with
DoCmd.RunSQL "UPDATE Units SET Notes = Notes " & [usertime] & [addnotes] & " where [computer code] = '" & Me!compcode & "'

 

[PHV]

BrenoAguiar, reread carefully my first post.

 

[BrenoAguiar]

ok.. Now I got the "Syntax error (Missing operator) in query expression 'Notes ..........."

I think we are getting there.

 

[pkailas]

could you post more of your code. Are [usertime] and [addnotes] variables?

 

[BrenoAguiar]

It works now! but it has to be like that:

DoCmd.RunSQL "UPDATE Units SET Notes=[Notes] & [usertime] & [addnotes] where [computer code] = '" & Me!compcode & "'"

I just need to put a space in beetween them. Thaks a Looot Guys! really.

Breno Aguiar