I need to ensure that the data entered into a field on a form is correct. The field contains an email address and I have a function to check if the format is correct.
If the format is incorrect I need the cursor to return to the field in that record. The form is continuous.
What event should I use?
I have a main form with several continuous subforms. Each subform consists of several listbox controls. I would like to require the user to select an item from the listbox before being allowed to move to the next record in the subform, and upon reaching the last record in that continuous subform, to require an entry there in order to move to the first record in the next cont. subform.
As an added bonus, it would be nice, though not necessary, to automatically jump the focus from one record to the next after data is entered. But my basic goal is to avoid skipping records.
Caveat: I cannot use the "required" option in the field to which the control is bound because that field has a default value previously entered using an append query. (The default value basically means "not yet entered" and is not one of the options in the listbox. I am using this because this field is a foreign key in the table, thus it must have a value in order to have a record with which to populate the subform.)
My apologies if this has been asked and answered elsewhere, but I've searched and, while I found a few related threads, they don't quite answer my question. Here they are for reference:
http://www.access-programmers.co.uk/forums/showthread.php?t=38364
http://www.access-programmers.co.uk/forums/showthread.php?t=69047
http://www.access-programmers.co.uk/forums/showthread.php?t=103176
Thanks for any help! Also, if you are going to recommend VB code, could you be specific about how and where I should use it? I'm not afraid of code, but I'm definitely new to it...
I've been trying to use some code I've found to prevent a user from entering a duplicate ID. I've tried this:
Dim Answer As Variant Answer = DLookup("[SAR_ID]", "Request", "[SAR_ID] = '" & Me.SAR_ID & "'")
If Not IsNull(Answer) Then MsgBox "Duplicate SAR ID Found" & vbCrLf & "Please enter again.", vbCritical + vbOKOnly + vbDefaultButton1, "Duplicate"
Cancel = True Me.SAR_ID.Undo
Else:
End If
but it displays the message even if I don't enter a duplicate.
I have a form and was wondering if it was possible to set it so that a user is not able to click the submit [Submitcmd] unless certain text boxes are filled (however SampleOuttxt needs to be able to remain blank).
View 5 Replies View RelatedHi,
Mainform and Subform are linked on field "barcode".
Barcode is the primary key for the table that Mainform is based on.
When user is entering a new record, I have the BeforeUpdate event of Mainform.barcode set to check to see if the barcode already exists in the table, and if so, to give the user some meaningful error message.
Works fine, except that the subform still updates to match the invalid, previously-used barcode. I want the subform to stay blank until the user has entered valid data in MainForm.
How do I accomplish this?
Thanks for any ideas.
In this db, the users select their name from a dropdown in an auto-open form, but with no password. I want to capture this value for use as the default value in a field on another form, but with the first form closed. I have seen the ENVIRON(Username) function referred to many times on here, but I need to capture the entered value, not the Windows or network login.
Is this possible without leaving the form open? I suppose I could just set its Visible to False when they enter their name, but I try not to have any more windows open than are necessary.
THANKS for your help!!
We are working on an Access (2007) database that is on a SharePoint Site (2007).
Currently the form is operational, but there is one last thing that would be nice to have.
The table is "Updated Headcount" which contains "EMP_ID" which are unique numbers stored as text.
In the event a new employee is entered in the system by another user on this site we would like to prevent any duplicate "EMPID"s from being entered and saved on the SharePoint, we would also like to alert the user and prevent the data from being saved.
All data is currently bound, so once the user makes a change it is made, no submit button is required.
We are running into some difficulties in doing a dlookup from the value entered and comparing to a column in the table.
TABLE - UPDATED HEADCOUNT
COLUMN in UPDATED HEADCOUNT - EMPID
FORM CELL user will input an EMPID - newEMPID
FORM CELL used for a dlookup to compare what user has entered to what is already in the table - duplicateEMPID
So below is what we are trying to do, we are sure there are a few commands missing....
=IIF(newEMPID=dlookup([UPDATED HEADCOUNT]![EMPID]), newEMPID, "Error, EMPID already exists")
Have read through all the threads related to this topic and can't find anything to help. Just want a message box to pop up after entry of first and last names in a form if the string already exists in the database. DLookup works wonderfully for validating on one field, but adding the second field is driving me crazy - Can I use DLookup to check more than one field? The following code gives me the error: "Run time error '2001' - You canceled the previous operation."
Private Sub LastName_AfterUpdate()
Dim x As Variant
x = DLookup("[txtLastName]", "[tblVolunteerInfo]", "[txtLast Name]= '" & Me![LastName] & "'" & " And " & "[txtFirstName] = " & Me![FirstName])
On Error GoTo CustID_Err
If Not IsNull(x) Then
Beep
MsgBox "This name already exists in the database. Please check that you are not entering a duplicate person before continuing.", vbOKOnly, "Duplicate Value"
Cancel = True
End If
CustID_Exit:
Exit Sub
CustID_Err:
MsgBox Error$
Resume CustID_Exit
End Sub
thanks,
lkwander
I am using a data entry form to enter new data into one of the tables.How can we prevent entry of duplicate records? Duplicacy shall be checked on the first two fields only.
Please help.
regards
bijon
I am trying to prevent a user entering duplicates of date and time in an appointments table for the same doctor..how do I do this?
View 3 Replies View RelatedI am looking at the public function routine, that validates the password entry. I want to know how i can make a message pop up with the specific error the user has made on entry.
Public Function ValidatePwd(varPassword As Variant) As Boolean
Dim blnValid As Boolean
Dim blnValidCriteria As Boolean
Dim intChar As Integer
blnValid = Len("" & varPassword) >= 4 And Len("" & varPassword) <= 12
[Code] ....
I have a query based on two other queries
If I create a form based on the fields in my query the form will not allow me to edit any fields - no matter what.
All fields are unique however I am stumped as to why I can't edit the data. Even adding in the second query immediately stops data entry even without linking.
Is there a way I can prevent wrapping in a memo field box?
I have a memo field with a list of email addresses and usernames however the email addresses often just wrap around the end of the memo box just creating a new line in the memo box rather than properly showing the email without the wrapping.
Even if the message scrolls out of the box, id rather that then wrap the text.
Also is there a way in which I can enable someone to open the "ZoomBox" whilst the text box is locked/disabled.
Ive had a recurring problem with one of my databases
ive got a form with a subtable on it, the subtable is very important as much of the DB's data is entered in there.
every once and a while one of the users will be doing data entry and on a certain field (a short text field if that makes any difference) theyll push enter instead of tab, to move on to the next field, enter creates a new line instead of moving to the next field, when a new line is created the user will either assume the field is blank and retype the data or ignore it entirely and move on
the trouble is one of my queries completely fails to work when there are multiple lines in this field (comes with a data type mismatch error)
is there a way to prevent the field from being able to contain multiple lines (like a property setting or some VB code?)
So now, I'm using the code below to prevent duplicate name entry and it is working great - EXCEPT when I enter a first or last name which contains a ' (ie, O'Tool, O'Malley, O'Hern)...anyone got any ideas for me on how to make this not happen?
The error I get is:
Run Time error '3075'
Syntax error (missing operator) in query expression '[Last Name]='O'Hern' And [First Name]='Lori'.
The code i'm using is:
Private Sub Last_Name_AfterUpdate()
'Check for duplicate first and last name using DCount
If DCount("*", "[Constituents]", "[Last Name]= '" & Me![Last Name] & "' And [First Name] = '" & Me![First Name] & "'") > 0 Then
Beep
MsgBox "This first and last name already exists in the database. Please check that you are not entering a duplicate constituent before continuing.", vbOKOnly, "Duplicate Value"
Cancel = True
End If
CustID_Exit:
Exit Sub
CustID_Err:
MsgBox Error$
Resume CustID_Exit
End Sub
I have recently started working for a new company and have inherited a nightmare of a system!
I have basic knowledge of Access and have been asked to stop duplicate entries under the same PO Number.
I have tried using DLookups by looking at other posts on the forum with no success.
The input form is called Purchase Order Entry, with the field called PO_No. The table is called Orders, with the field called Purchase Order Number.
I have tried to make the Purchase Order Number in the Orders table, the primary key but an error that it cannot contain a null value appears.
The Purchase orders may not be entered into the database in numerical order also.
I have a form, that once prompted, finds a certain record for the user. How do I prevent the user from being able to see any other records but that one, once the form is opened? The user needs to be able to edit the particular record they opened, without viewing or editing any other records in the database. Is there a way to hide the bar at the bottom of the form that allows users to manipulate through the different records?
View 6 Replies View Related1. I have a database (see attached) with three tables all of them with the same fields. The first three are numbers (InCo_No, Proto_No, Year_No). Each of these fields (numbers) can be the same in the other table(eg. Year_No), but the combination of the three cannot be.
How can I prevent the entry of a duplicate combination of these three fields?
2. I want to have a form to fill the three tables separately, depending the values in the other fields.
How can I do this?
Hi
with the help of this site, i have made big advances in learning and adapting code, in building my access FE on an SQL BE.
however, i have a problem, i cant find any information on in how to resolve.
i am using menus to navigate between forms, but i cannot synchronise the critical value between the forms.
so i am on a form with critical value, say hullID
i use a menu command to open another form, but i need the form to open with the same hullID as the critical value as the previous form which is about to close.
because i can go to the new form from any form, using the menu command, i cannot directly link the critical value,
is there a way to use the critical value from the current focus form, to the new opening form
many thanks in advance
Hey guys..
is there any way in which i can prevent the user operating a form from adding or deleting or editing any records ?
I have made an access database for doing inventory at work. We have an inventory of approx. 700 items that need to be counted each month. I have a subform that is in datasheet view, it has Inventory IDs, product descriptions, UOMs, and a field to enter quantities. On the parent form are pictures and extra descriptions linked to each item in the subform.
The Inventory IDs all start with a 3 letter alpha code for which class of item they are PLU for plumbing, FAS for fasteners, ELD for electrical device, etc. There are about 15 different classes of these inventory items. The rest of the InvIDs are 4 digits: (PLU5453 or ELD1123)
We are using tablets to do the inventory counts, so to search for an item we can swipe through the datasheet to find the InvIDs we are looking for and then enter the quantity. After our initial inventory my supervisor asked if I could add buttons to jump to the first item in a particular class of item such as PLU0001. He showed me his address book in his phone as an example, how he can push a P button to bring him to the first name that starts with P.
I considered tabs for each class, but he wants to navigate to other items by just swiping also. I tried to make a Macro that would use Go To Record but was unable to make that work.
Good morning! I'm building a database for use by four technicians in a call-center type operation. The call records are purely transactional in nature -- once the call has been logged, the record is closed. If the customer calls again, a new record is opened.
The supervisor doesn't want the techs to be able to go back and edit a record once it's entered -- the records are for quality-control purposes and he doesn't want them gaming the system by going back and changing things after the initial call is logged (let's not discuss the issue of employee psychology, motivation, Office Space, etc.). So I'm trying to figure out how to do that; they all have wheel mice, and I can currently see all the records as I scroll back and forward with my mouse wheel, so I'm assuming they'll be able to, as well.
Any help?
Thanks,
Andreas
I'm ready to "publish" my desktop db for end users, however I want to limit their ability to view the objects in the Navigation Pane. Essentially, the only way I want the user to be able to interface with my db is through the forms I have created.
View 1 Replies View RelatedI have a form and a subform for data entry.It is often the case that not all the data may have been entered and so a user might need to go to recordID 24 in 30 already entered main records.However, he needs to get to that record via a (unique) 1-1 field. How do I create the form so that as well as being able to go to the first, the last, the next, the previous record, I can go to a record of my choice.
View 5 Replies View RelatedFirst thing: I have a navigation form that leads through 5 subforms on my database. One is a data entry form, the rest are various informative forms with explanations, images and other text mainly. In this scenario, a user enters data on the first tab but doesn't finish it. The user clicks over to check up on some guidelines on the other tabs and then comes back to the first tab for find that all of the fields he entered reset and he lost everything.
Is there a way of preventing this from happening? Plan A would be for me to allow the user to switch around tabs without losing the data he or she entered into textboxes or dropdowns. Plan B would be for me to have a popup that appears when he tries to switch tabs that warns him of data loss and that he should save before moving tabs.
PS: One other thing: I have the standard Access wizard search button on my first tab that allows the user to search records. This works well, but I'd like for the search box to close immediately after a search turns up a matching record. This seems odd, but the users requested it because manually closing the search box after every search can add up to a lot of wasted time.
I have a form which consists of four (4) tabbed sheets. The first sheet is the main form and the other three sheets are the linked subforms.
Pressing the tab key on the keyboard navigates from field to field in each individual form; however, I would like that when I get to the last field of each form, pressing tab moves to the next sheet or the first field of the next subform.
The problem I am getting is that when I get to the last field of the main form and/or subforms, hitting the tab button creates a new record in the specific form, which I do not want. I want to just tab to the next subform.