Force Update Into Unbounded Comments Field
Oct 24, 2005
Hopefully there's someone who can help me with this!!! I've had several unsuccesfull attempts and don't know what else to try...
We're trying to force users to make entries in a field called txtComments in the frmQuestionnaire form if they provide negative responses and to stay on that record until the comments are changed. Inside the form is an option group (fraResponses) which contains options (OptA, OptB..etc.) valued from 1 through 7, based on a likert 7 scale. We're only using a likert 6 scale, so the respondents will only see 2-7 on the form. If they options B-D (valued 2-4), they are considered negative, so we want to make sure they enter a comment.
I tried to enter the italicized code under the form on BeforeUpdate, AfterUpdate, CmdUp (click to next question) and CmdDown (click to previous question), with frmQuestionnaire but nothing happens:
There is a module called clsUser which contains the following and where I tried to insert the code:
Set myForm = Forms!frmQuestionnaire
' ================================================== ==
' First, set the value of the variables
' ================================================== ==
lngQuestion = arrQ(lngArray, 0)
lngSession = GetCustomInfo("TestSession")
lngUser = UserID
lngBillet = BilletID
strComment = Nz(myForm.txtComment, "")
strComment = "None"
'If user selects negative responses
If Form_frmQuestionnaire.fraResponses.OptionValue = 2 Or 3 Or 4 And strComment = "None" Then
MsgBox "Please explain the problems you encountered with the system which " & _
"caused you to select an unfavorable response."
Form_frmQuestionnaire.txtComment.SetFocus
End If
lngResponse = myForm.fraResponses
When I place the code there, the dialog box appears for all values and goes to the next record. This is what appears for the CmdUp (click to next question):
If cUser.blnDirty = False And Me.fraResponses = 153 (this is default value for the entire option group) And cUser.blnNew = True Then
cUser.blnDirty = True
End If
cUser.CaptureAnswer
If cUser.lngArray < cUser.UBound_ArrQ() Then
cUser.lngArray = cUser.lngArray + 1
Else
cUser.lngArray = cUser.UBound_ArrQ()
End If
cUser.FillQuestions
cUser.blnDirty = False
I've tried to enter the line of code with the form and the module, but no luck.
I also want it to remain on the same record until the field is changed. The form is unbound and has the following:
Public Sub FillQuestions()
'************************************************* ****
' Purpose: Scroll through the questions
'
' Assumptions: N/A
'
' Effects: N/A
'
' Inputs:
' None
'
' Returns:
' None
'************************************************* ****
Dim lngRG As Long
Set myForm = Forms!frmQuestionnaire
myForm.txtQuestion = arrQ(lngArray, 1)
lngRG = arrQ(lngArray, 2)
myForm.prgProgress.Value = lngArray + 1
myForm.lblProgText.Caption = "Question " & (lngArray + 1) & " of " & lngQuestions
myForm.lblTS.Caption = "Test Session: " & arrQ(lngArray, 3)
myForm.lblQID.Caption = "Question ID: " & arrQ(lngArray, 0)
GetResponseSet lngRG
FillAnswers
If lngArray = 0 Then
myForm.txtComment.SetFocus
myForm.cmdDown.Enabled = False
Else
myForm.cmdDown.Enabled = True
End If
If lngArray >= UBound(arrQ) Then
myForm.txtComment.SetFocus
myForm.cmdUp.Enabled = False
Else
myForm.cmdUp.Enabled = True
End If
End Sub
Public Sub FillAnswers()
'************************************************* ****
' Purpose: Fill out the user's previous responses
'
' Assumptions: N/A
'
' Effects: N/A
'
' Inputs:
' None
'
' Returns:
' None
'************************************************* ****
Dim strSQL As String
Dim recAnswer As New ADODB.Recordset
Set myForm = Forms!frmQuestionnaire
strSQL = "SELECT datResponse.reDatQuestionID, datResponse.reDatRespondentID, "
strSQL = strSQL & "datResponse.reDatResponseSetID, datResponse.reComment "
strSQL = strSQL & "FROM datResponse "
strSQL = strSQL & "WHERE datResponse.reDatQuestionID=" & arrQ(lngArray, 0)
strSQL = strSQL & "AND datResponse.reDatRespondentID=" & UserID
recAnswer.Open strSQL, CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
If Not recAnswer.EOF Then
myForm.fraResponses = recAnswer!reDatResponseSetID
myForm.txtComment = recAnswer!reComment
blnNew = False
Else
If myForm.fraResponses <> 152 Then
myForm.fraResponses = 153
myForm.txtComment = ""
blnNew = True
End If
End If
recAnswer.Close
Set recAnswer = Nothing
End Sub
Thanks in advance for your help!!
View Replies
ADVERTISEMENT
Aug 1, 2013
How can I force a field in a form to be updated before the record is saved / changed?
For instance I have a form with information on it and I want to ensure that any time the form is updated the user fills in a section providing the date and by who it has been updated by. I dont want the record to save unless that information has been filled out, and I also want it to take you to the field if you press save and it hasnt been filled out along with an error message.
To try and be a bit clearer. At current I have a Save and New button. This saves the form if dirty and opens a new record.
I want to add in the step that if record has been changed and FieldA has not just been updated then go to fieldA (Showing a message box). If FieldA has just been updated then save record and open new as normal.
My current save & new button properties are as follows (in Macro Editor)
On error Go To Next
If [form].[dirty]
RunMenuCommand Command SaveRecord
End If
If [MacroError].[Number}< >0
Message =[MacroError].[Description]
Beep Yes
Type None
Stop macro
On Error
Go To Fail
Go To Record
Record New
Go To Control
Control name Resort Code
View 3 Replies
View Related
Jul 24, 2006
Can I force a control to update (ie run its after update event code) from a global module.
Thanks.
View 2 Replies
View Related
Aug 20, 2013
I need to save a user's machine name when they comment on a record.
I have a form that has a comments field, and a separate area that shows the historical entries from comments.
I also have an invisible field populated by "=fOSMachineName()" that shows the users machine name. Getting this running was tough on my 64 bit machine, but it now works!)
I wish to have the machine name included in the comments field every time the "Save Record and Close" button (which runs a macro) is clicked.
Right now, when someone adds comments, it shows a date and time stamp (which I still want to keep) but I want to also capture the machine name at the same time.
View 11 Replies
View Related
May 5, 2015
Having some trouble with a memo field. For each record, the notes field is present on the Form.... I added an unbound text box (txtMemoAdd) and a command button (Add New Note). When the button is selected, it adds the note to the Read Only Notes Field and adds a timestamp using the following code:
Me.Notes = Me.Notes & vbCrLf & Now () & VbCrLf & Me.txtMemoAdd
Me.txtMemoAdd = ""
The note is added to the bottom, and I was wondering if there was a way to make the new note go to the TOP of the field (Descending Order rather than Ascending).
View 5 Replies
View Related
Jan 15, 2015
I have one memo per change (user input)...I need to have the comment the user inputs to copy into other records that were queried. I tried insert. My coding does one of the records or all of the records ..I need it to copy just to the queried records ...and I have a query but no filter set.
View 4 Replies
View Related
Jun 7, 2007
Hi,
I have a DB where I want text entry of the primary key to adhere to a certain format.
I'm already using a mask of >LL000000 to force two capital letter and 6 numbers.
Is there any way I can force extra restrictions, by making for example the first 3 characters to have to be AB1, thus making every entry follow format:
AB1<number><number><number><number><number>
(FYI: Access 2003)
View 1 Replies
View Related
Jul 10, 2013
Not = " " And InStr(1,"seat_person"," ") <> 0
I have a field into which I wish to place a person's name and I want to ensure that the field has a space in it but is not a blank field,
so
all blank - illegal
"Bob" - Illegal
"Bob Smith" - legal
the edit at the top is my attempt to code it but it isn't working.
View 13 Replies
View Related
Jan 21, 2005
I've created an unbounded form because I have user who accidently delete a field without realizing it and ask how come it did that. SO i've created and unbounded form for them to review and save with a command button, but I'm having a problem using the vbaunbound.zip file that i've downloaded from sbaxter. I created the update using DAO and I keep getting "An Error Occurred. The System doesn't know which action to take" I edited all fields that were in the example to point to the correct fields and I Keep getting this message and then the cmdBox gray's out...Any input would be greatly appreciated AGAIN...
Thanks...
Eddie...
View 14 Replies
View Related
Apr 4, 2006
Will some be able to assist me throught this please? I've been tasked (AGAIN ) to add a command feature to a form in access to do an add new record. What the user would like to do is be able to do a search and make a change by adding the existing as new record. For example survey number 3738 is open and they want to make a change by making inspector a different name and then click add and boom it's a new record. I've done this in the past but I cant' rmember how i did it before...
YOUR help is truly appreciated...
View 4 Replies
View Related
May 2, 2014
Is there any way to force a field value to be unique and of a set length, but with exceptions?
Let me explain... I have a text field in my table called "employee_number" and this value is always one of the following:
NULL
an 8-digit number
the word "External"
What I want to do is to force that field to either be NULL, the word "External", or a unique 8-digit number.
Is this possible? Obviously I can't set the source field in SQL to accept unique values only but I wondered if there was any way around it at form level?
View 8 Replies
View Related
Jan 29, 2006
hi guys, i have created 1 combo box and 1 text field in my form. For my combo box, I managed to connect it to my table. But when i run the form, i can press the combo button BUT cannot select the name displayed. Worst of all, i cant even type anything on the 2 boxes. is there any properties that I got change?
View 2 Replies
View Related
Jun 19, 2005
Hi guys. I made a unbounded form that has option to search for customerno .
Whenever I try type a customerno and click on the search button I get the following error.
I be happy if some expert help fix this error. Thanks
Error
Run -time error '2109':
There is no field named 'customerno' in the current record
http://i5.photobucket.com/albums/y180/method007/searcherror.jpg
=====>searcherror pic of the form
Code:Option Compare DatabaseOption ExplicitSub clearTextBoxes()''clearing the tow texboxes txtCompanyName and txtCustomerIdMe.customerNumber.Value = ""Me.customerName.Value = ""End SubSub getReadyForAnAddOperation()Me.cmdSave__.Enabled = TrueMe.cmdSave__.SetFocusMe.cmdCancel.Enabled = TrueMe.cmdAdd__.Enabled = FalseMe.cmdEdit.Enabled = FalseMe.cmdDelete.Enabled = FalseEnd SubSub stateOnLoad()Me.customerName.SetFocusMe.cmdCancel.Enabled = TrueMe.cmdSave__.Enabled = FalseMe.cmdEdit.Enabled = TrueMe.cmdAdd__.Enabled = True'''disableing the cancel and save button on load'''Me.cmdCancel.Enabled = False'''Me.cmdSave__.Enabled = FalseEnd Sub'declaring subrotineSub FillFeilds()Me.customerNumber = myRS.Fields("customerno")Me.customerName = myRS.Fields("customername")End SubPrivate Sub cmdAdd___Click()clearTextBoxesgetReadyForAnAddOperationpbAddingARecord = True'''changing the value of this boolean variablemyRS.AddNewEnd SubPrivate Sub cmdCancel_Click()FillFeildsstateOnLoadEnd SubPrivate Sub cmdDelete_Click()Dim x As Variantx = MsgBox(" You are abut to delete " & Me.customerName & " from this table - proceed ? ", vbOKCancel)If x = 1 ThenWith myRS.Delete.MoveFirstFillFeildsstateOnLoadEnd WithEnd IfEnd SubPrivate Sub cmdEdit_Click()''clearTextBoxesgetReadyForAnAddOperationpbEditingARecord = True'''changing the value of this boolean variableEnd SubPrivate Sub cmdMoveFirst_Click()myRS.MoveFirstFillFeildsEnd SubPrivate Sub cmdMoveLast_Click()myRS.MoveLastFillFeildsEnd SubPrivate Sub cmdMoveNext_Click()If pbAddingARecord = True Or pbEditingARecord = True ThenMsgBox ("Please save or cancel changes first ")Exit SubEnd IfmyRS.MoveNextIf myRS.EOF ThenMsgBox ("Last Record")myRS.MovePreviousEnd IfFillFeildsEnd SubPrivate Sub cmdMovePreviouse_Click()myRS.MovePreviousIf myRS.BOF ThenMsgBox (" First record")myRS.MoveNextEnd IfFillFeildsEnd SubPrivate Sub cmdSave___Click()If pbAddingARecord = True ThenmyRS.AddNewEnd IfIf pbEditingARecord = True ThenmyRS.EditEnd If'''inserting the value of textboxes to the table fields.feeding the date to record setmyRS.Fields("customerno").Value = Me.customerNumber.ValuemyRS.Fields("customername").Value = Me.customerName.Value'''calling update method. it comittes the changesmyRS.UpdatepbAddingARecord = FalsepbEditingARecord = FalsestateOnLoadEnd SubPrivate Sub cmdSearch_Click() Dim strStudentRef As String Dim strSearch As String 'Check txtSearch for Null value or Nill Entry first. If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!" Me![txtSearch].SetFocus Exit SubEnd If'--------------------------------------------------------------- 'Performs the search using value entered into txtSearch'and evaluates this against values in customerno DoCmd.ShowAllRecords DoCmd.GoToControl ("customerno") DoCmd.FindRecord Me!txtSearch '''Me.customerno.SetFocus customerNumber.SetFocus strStudentRef = customerNumber.Text txtSearch.SetFocus strSearch = txtSearch.Text 'If matching record found sets focus in customerNumber and shows msgbox'and clears search control If strStudentRef = strSearch Then MsgBox "Match Found For: " & strSearch, , "Congratulations!" customerNumber.SetFocus txtSearch = "" 'If value not found sets focus back to txtSearch and shows msgbox Else MsgBox "Match Not Found For: " & strSearch & " - Please Try Again.", _ , "Invalid Search Criterion!" txtSearch.SetFocus End IfEnd SubPrivate Sub Form_Load()Set db = CurrentDb()'''Set myRS = db.OpenRecordset("select * from customer")Set myRS = db.OpenRecordset("customer", dbOpenTable)''' need to learn how to add index to customer table'''myRS.Index = ("Company Name")'calling subroutinestateOnLoadFillFeildsEnd SubPrivate Sub lblFindIt_Click()With myRSSelect Case Me.lblFindIt.CaptionCase "Company Name"Me.lblFindIt.Caption = " First Name "''' this indexcontactfirstname should already exist in the table''' you can also create index trough code on tables. this method''' not good in multi user evironment . best way to create indexes and''' refere them trough code.Index = "CotactFirstName"Case "First Name"Me.lblFindIt.Caption = " Last Name ".Index = "CotactLastName"Case "Last Name "Me.lblFindIt.Caption = "Company Name ".Index = "CompanyName"End SelectEnd WithEndEnd SubPrivate Sub textFindIt_Change()Dim strSeek As VariantDim posInmyRS As Variant''' feeding it the value from text boxstrSeek = Me![textFindIt].TextWith myRSposInmyRS = myRS.Bookmark.Seek ">=", strSeekIf .NoMatch = True ThenmyRS.Bookmark = posInmyRSExit SubEnd IfFillFeilds End With End Sub
View 4 Replies
View Related
Nov 1, 2007
Hi Alli,
I have a database that has a comments field. The database has a status field: Plan Test and Run,
in the comments field the user adds comments, i'm trying to updated the comments with the status, for example; the user added some comments like. "The test will begin next week" I want the comments feild to automatically get updated when the user changes the status for example to Run, I want the "run status to be added to end of the comments line" can somebody help? Thanks.
View 3 Replies
View Related
Dec 17, 2005
I would like to insert comments into my sql code (select query > sql view window).
I tried with usual sequences such as /* ... */ but it doesn't work.
Does anyone knows the right sequence ?
Thanks.
View 6 Replies
View Related
Apr 12, 2006
How can I add comments in an SQL Query mode? ",',?, ; None of these work with text following them.
View 1 Replies
View Related
Aug 3, 2006
On our site we have option where users can add coments to articles. Lately some people have been misbehaving. I wish to add a field in .mdb file and call it "approved". If the field contains "1" then it's ok to show the message, if the field has nothing in it comment will not be shown. How do I implement this into below code. I wish to leave posting as is, just not showing not yet approved comments.
Thank you in advance. (comments are shown in <td>" & rs("Comment") & "</td> part)
Here is the code:
' show past comments
If Not rs.EOF Then
s = s & " <tr bgcolor=""" & TitleBGColor & """>" & vbCrLf
s = s & " <td colspan=""3""> Comments:</td>" & vbCrLf
s = s & " </tr>" & vbCrLf
While not rs.EOF
s = s & " <tr class=""" & TitleClass & """>" & vbCrLf
s = s & " <td> </td>" & vbCrLf
s = s & " <td>From:</td>" & vbCrLf
s = s & " <td>" & rs("Name") & " (" & rs("DateAdded") & ")</td>" & vbCrLf
s = s & " </tr>" & vbCrLf
s = s & " <tr>" & vbCrLf
s = s & " <tr class=""" & TitleClass & """>" & vbCrLf
s = s & " <td> </td>" & vbCrLf
s = s & " <td>Comment:</td>" & vbCrLf
s = s & " <td>" & rs("Comment") & "</td>" & vbCrLf
s = s & " </tr>" & vbCrLf
s = s & " <tr>" & vbCrLf
s = s & " <td> </td>" & vbCrLf
s = s & " <td> </td>" & vbCrLf
s = s & " <td> </td>" & vbCrLf
s = s & " </tr>" & vbCrLf
rs.MoveNext
Wend
End If
View 1 Replies
View Related
Aug 22, 2006
Hey,
In the form I am creating, I have checkboxes. Some of these checkboxes, when ticked, need additional comments entered into a comments box.
Only when the box is ticked, the comments box should appear/become enabled.
Any pointers in the general direction I should help would be greatly appreciated.
Thanks.
View 6 Replies
View Related
May 20, 2005
We have several users that access the same database. Is there a way to make it so that the users can add comments to the "coments" field on the form, but not remove any comments once saved?
View 1 Replies
View Related
Aug 23, 2005
I have a comments on my form. I have contacts with them having from 1 to many customers accts. I am trying to come up with a way to update the comments for a contact that adds those comments to all their customers records...but not to those that have been closed.
How would I add these comments to the comments on the form without overwriting the current comments.
Comments - Memo
Contact - Text
It's quite a pain, copying and pasting from record to record.
Thanks
View 1 Replies
View Related
Feb 6, 2006
Help please!!!!
Firstly, I would apologise as I am not overly familiar with the correct terminology!
I need to create a "space" on my form whereby I can add comments etc which will exceed 255 characters: these comments will be specific to each record. I have tried having an "unbound" text box in design view but this makes the comments the same on each record.
I know this is probably "as clear as mud" but if anyone can help I would be really grateful.
Thanks,
Emma
View 3 Replies
View Related
Sep 7, 2014
I am developing a new tool which needs to show the history of updates to the users, i.e.:
An observation is entered into a field on the MAIN FORM by user 1.
User 2 reviews the observation on the MAIN FORM and adds a separate comment in another field on the same form.
When the record is saved, the updates are NOT written to the Main Form Table but to the UPDATES TABLE for audit purposes.
I am also using current date/ time as a timestamp, which is protected and needs to be carried over to the UPDATES TABLE Every time a new comment is added in the MAIN FORM, I need it to be written to the UPDATES TABLE as a new record.
What I need to be able to show is:
Each comment added, as a list of readable records, in a table within the main form AND Each time the main form is refreshed/ opened the separate comment field is blank to enable each new update to be applied.
View 4 Replies
View Related
Nov 21, 2014
I'm trying to make a text box for comments that grows as the user types to fit the comment perfectly.
The comments can be anywhere from 2 words to a paragraph long.
Is this even possible? If it is, is the best place to do it on a form?
I've made a text box and tried the "Can Grow" property, but that doesn't increase the size as you type.
I'm wondering maybe if there's a VBA "After Update" Code that will readjust the textbox to fit the code?
View 1 Replies
View Related
Nov 12, 2012
how to change existing comments on a report?
View 1 Replies
View Related
Mar 26, 2014
How to extract the comment system from the Contacts Web Database template found in Access 2010? I can upload it if you like.
I would like to use it in a standard desktop database to track notes and comments in my records.
I am able to convert it from web to standard, but it still appears to use data macros or something, and my knowledge on macros is limited.
I just want to keep a history of my notes for each record, with the most recent always listed, and the comment system in the Contacts Web Database template seems perfect for that. I just don't know how to implement it into my desktop database.
View 1 Replies
View Related
Sep 18, 2013
We have created a database where data entry happens on the first form. So far all is working well on this form except users complain there is not enough space for comments. I set up a column for comments, but it is limited to 255 characters. What/How else can I set this up to provide a lot more room?
View 1 Replies
View Related