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.
How can I adjust my required field so that you cannot simply enter a space bar character to circumvent the requirement?
Here is part of the code:
If IsNull([txtRequiredReason]) Then
MsgBox "The Reason field is required"
DoCmd.GoToControl "txtRequiredReason"
Else
I write Access 2010 apps in VBA that require no knowledge of Microsoft Access on the part of the user. My forms contain all the necessary controls to use the program without relying on any of the Microsoft Access toolbars. I call this "de-Microsofting" the app. When I prepare the .accdb program to create a .accde app, I uncheck every checkbox (Options/Current Database) except "Compact on Close". This works well, but it also hides the Windows taskbar at the bottom of the screen. Some of my users need to be able to jump from my app to others (e.g., Excel) and back without having to exit my app. How can I force the Windows taxkbar to always be on top using VBA or, possibly, some setting?
View 4 Replies View RelatedI have an application that is using SQL Express as a backend and syncing to a SQL database through SQL web synchronization. This all works great and my clients have my custom application to interface with their local database and do work and it syncs back to the corporate database and I've got it scheduled to run in Windows every minute. What I would like to do is have a button or on event set of code that forces that sync pairing to run and I'm not exactly sure how to do that.
View 3 Replies View RelatedI have an issue with my subform, that when opened inside the main form, I want the form to be opened on its last record (which works) and then I want the subform to be opened on the first record regarding that record ID coming from from the main form. The relationship is many to one, coming from the form to the subform. I have tried several code on many events on the subform so it can goes to the first record, but it sometimes goes to the first, othe times ot goes to the third, i don't know why. I have tried the following:
Private Sub Form_Load()
DoCmd.GoToRecord , , acGoTo, 1
En Sub
or
DoCmd.GoToRecord , idsPreguntas, acFirst 'idsPreguntas is the control name of the record
or
DoCmd.GoToRecord , , acFirst
I have about 4 combo boxes with a list value of dates in them, combo1 is the 'main control'. When a date from combo1 is chosen and a button is clicked, the other 3 combo boxes receive a default value that closely matches combo1. In fact the default value each of the 3 combo boxes receive exists in that combo box. For example, if I gave combo2 default value "2013/01/01", it actually has that date in its list. What I want to do is trigger a OnChange even after combo2,3,4 receive their values. Other things happen after the OnChage trigger is fired.
View 2 Replies View RelatedI have a database stored out on a shared drive at my company that multiple users connect to via a front end application that they save on their own hard drives. Recently I have noticed that the back end database keeps getting opened exclusively which prevents other users from being able to access the back-end database. For the life of me I cannot figure out what about the front end opens the back end exclusively and cannot find any plausible solutions I thought I could develop something that could force close a back-end database or disconnect all users from it?
View 3 Replies View RelatedHi,
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)
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!!
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
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?
I have a button that when clicked moves a piece of data to a subform. I have put the whole sequence below. The trouble I am having is :
- The event will not happen until the form is saved. I think this is because fkTaskID is a new record (auto) number which hasn't generated yet.
- If I force the form to save it does work but then I get an error on the INSERT command when not all required fields of the form are complete (see sample in second part below).
Is there a way to save the record and maintain the integrity of the form input - and still have this code work?
BUTTON CODE
==========
Private Sub BTNAddReasonRw_Click()
Dim dIndex As Long
DoCmd.SetWarnings False
For dIndex = 0 To Me.LISTReworkReasonsUnselected.ListCount - 1
[Code] ......
Hi!
Is it possible for me to alias the fldGroup to Function Group?
How do you alias a field name to a name that contain a space?
Please advise.
Aijun.
I have several fields that I fill in on a form and they all need to add up in the last field. The first way that I tried this worked good for the initial data entry on the form. However if I need to edit one of the fields in the form then it resets the TotalCost field and I have to enter all of the numbers again. Is there some way that I can make this work? Or is this an Access quirk I will have to live with?
I tried to do this two different ways.
Here is the code for the first method:
Option Compare Database
Dim C1 As Long 'For Total Cost
Dim C2 As Long 'For Total Cost
Dim C3 As Long 'For Total Cost
Dim C4 As Long 'For Total Cost
[Code] .....
This way works great the first time that I tried it, I had to re-enter info in all the fields if I wanted to change one.
Here is my second method:
Which didn't work, i received an error message:
"The expression After Update you entered as the event property setting produced the following error: Invalid outside procedure."
And then nothing changes.
Code:
Option Compare Database
Dim C1 As Long 'For Total Cost
Dim C2 As Long 'For Total Cost
Dim C3 As Long 'For Total Cost
Dim C4 As Long 'For Total Cost
[Code] ....
So I know this second method is incorrect.
Hi!
I have a field that has to be 20 Characters long. Most of the time data for this field is less than 20 characters, so the remaining has to be filled with spaces to make it 20. e.g. if there is a record xyz1234567, then the rest of the record should have 10 spaces like xyz1234567ssssssssss, meaning if i click on this record then the cursor should not blink after 7 but after the spaces. Hope I am making sense.
Thanks for any input.
Is there a way in a masked date field(mm/dd/yyyy) that when clicking anywhere in the field that the cursor can be at the beginning of the field? For instance if I were to click in the position of the dd can I get the cursor to be at the first m.
Thanks for any help.
T
Code:
strSQL = ("INSERT INTO " & strTable & " Select c.OrderNumber, c.Item, c.RepId, p.ProductNbr, p.Name, [tbl_LBP_Sales Location Num].[Rep Region Code] FROM CalculateTotal c, dbo_PartNew p, [tbl_LBP_Sales Location Num] WHERE ([Structure] like '*" & u & "*') AND ([ProductNbr] = '" & txtPartNumber & "')")
CurrentDb.Execute strSQL
It keeps giving me this error and I don't know why?...
I tried replacing the brackets around Rep Region Code with quotation marks as well as these things `` but kept getting the same error...
How can I replace a '0' to a blank space and '1' to 'X' in 'Text Box' in a report ? The 'Data Type' is 'Yes/No' in the table.
View 6 Replies View RelatedIn my Dbase, I have a table with a text field. Field name is Country
There are over 5000 records in the table and many of these in the Country field start with a space. I would like to get rid of this space as to ensure that all text starts with a character, rather than a space.
How can I achieve this.
I have a query that I export to a text file with fixed width settings.
For one particular field that had only been one character and 5 spaces I used PGM: " " & [ProgramCode] for my field. The recipient of the export file requires that this field be 6 spaces long and leading spaces fill in any unused field.
Now, one of the records for this field will be 2 character so the way it is now will cut off the leading character. How can I write this to add leading spaces until it reaches 6?
i want that when i press the space key on my keyboard oi will exit from the form. i searched on the interne t but i couldn't find the proper one. I don't know exactly what i have to write in vba.
View 2 Replies View RelatedI am having an issue with some imports. As you will be able to see from the attached Db file, some fields import into MS Access with a leading space in front.
The problem is, Access doesn't seem to see it. For example, (in Query_1) I try to find the leading space with a Left and a Mid function using: " ", "", and Null... but none of these criteria match.
However if you use Left(string, 1)... the empty space will post. So my question is: How can I programmatically eliminate these leading spaces when I can't state if there is a leading space or not?
Take a look...
We have a spreadsheet that i upload onto a table, i use on of the fields to build a file path but if there is a special character it causes problems,i need to replace the below with spaces if they are in my field called path
< (less than)
> (greater than)
: (colon)
" (double quote)
/ (forward slash)
(backslash)
| (vertical bar or pipe)
? (question mark)
* (asterisk)
. (full stop)
, (commer)
how i can do this in some sort of update query.
I want to be but to save a space in a field on a table but every time I do it appears to be a NULL and therefore fails a validation rule that I wish to preserve.
View 14 Replies View RelatedI use several fields to capture a name: LName, FName, MI and Suffix.
In a report I combine the fields.
=[LName] & " " & [Suffix] & ", " & [FName] & " " & [MI]
This works fine if there is a suffix; however, without a suffix I have an extra space.
What is the "work around" to eliminate the space.
I like to store SQL code as a memo field in a table. I then use a form to collect selection criteria from the user, and this is used to create a 'WHERE' clause which is then appended to the stored SQL. This has always worked, but in one recent case the SQL failed. On examination I discovered that although the SQL is correctly stored in the memo field, when Access retrieves the code it inserts a space character in the middle of one of the words, thereby of course creating an invalid SQL string. It's easy to work around it, but why this happens? A bug in Access?
View 1 Replies View Related