Validating Primary Key Before Opening A New Form
Jan 4, 2006
Folks,
i have two forms with a command button on the first which opens the other...
is there a way of getting this button to validate the primary key (i.e. make sure its not a duplicate) before it trys to open the next form??
i've tried to call the next record function (as the button seems not allow the next record to continue) but all this has done is delete the record and open the new form rather than stopping the action...
any help much appreciated
cheers
si
View Replies
ADVERTISEMENT
Mar 23, 2006
Hi all
Im currently trying to set validation rules for a form using VBA, does anyone know of a good method of doing this as i'm a bit stuck:confused:
View 1 Replies
View Related
Oct 11, 2006
I have a form which allows an individual to add a cylinder record, i would like to validate the form in the sense that if the cylinder has already been added into the table it cannot be added again. So once the cylindernumber has been typed within the text field and the add button is clicked, the cylinder number typed is checked against the table if it has already been added a meesage should appear not allowing the user to enter a clyinder that is already added. I ahve tried this with the following code (the coding in red was already generated by adding a command button "add" to the form. The message i am recieving when the cylinder number in the textbox matches the cylinder number within the table is "you cant go to the specific record". I think my coding must be in the wrong order and IDEAS!!!!!
MyCode:
Private Sub Command39_Click()
On Error GoTo Err_Command39_Click
DoCmd.GoToRecord , , acNewRec
Exit_Command39_Click:
Exit Sub
Err_Command39_Click:
MsgBox Err.Description
Resume Exit_Command39_Click
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim StrSQl As String
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * From tbl_CylinderMaster Where [Cylinder Serial Number] = '" & Me![Cylinder Serial Number] & "'")
If Me![Cylinder Serial Number] = [Cylinder Serial Number] Then
MsgBox ("This Cylinder Number has already been added please add another")
End If
End Sub
View 2 Replies
View Related
Jun 12, 2006
Hi,
I've got a close button on my form as follows:
Private Sub exitForm_Click()
DoCmd.Close
End Sub
In the beforeupdate event of the form I have have following code to validate the forms fields:
Private Sub Form_BeforeUpdate(Cancel As Integer)
'Place an asterisk (*) in the Tag Property of the text boxes you wish to validate.
'Then in the BeforeUpdate Event of the form, copy/paste the following:
Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control
nl = vbNewLine & vbNewLine
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If ctl.Tag = "*" And Trim(ctl & "") = "" Then
msg = "Data Required for '" & ctl.Name & "' field!" & nl & _
"You can't save this record until this data is provided!" & nl & _
"Enter the data and try again . . . "
Style = vbCritical + vbOKOnly
Title = "Required Data..."
MsgBox msg, Style, Title
ctl.SetFocus
Cancel = True
Exit For
End If
End If
Next
End Sub
This validation works fine up to a point. If I try to close the form and a required field is empty, I get a message box asking me to fill in the data. The problem is that when I click ok to the message, it shuts down the form, so I have to re-enter all the data again. My question is how do I prevent the form from closing after clicking ok on the message box?
appreciate your help:)
View 1 Replies
View Related
Jan 24, 2015
I try to validate the input in a form combobox. In my table it works okay with a validation rule
validation rule: Like "[A][B]"
validation text: The input should be two numbers with A or B with two numbers Example: 01A01 or 21B43
But when i go to my form i can still input other letters than A or B. It will not save but there is no warning that the input is wrong.
View 4 Replies
View Related
Apr 7, 2014
I have a Form opening from Access Options. I would like to close this Form using the Timer. The following is the code I have used but it is not working.
Private Sub Cover_Page_Form_Load()
OpenTimer = Timer
End Sub
Private Sub Cover_Page_Form_Timer()
If (Timer - OpenTime) = 5 Then DoCmd.Close acForm, "Cover_Page_Form", acSaveYes
End Sub
Next question. If I can get this to work can I then use a DoCmd to open new Form within the code above or do I need a new process.
View 5 Replies
View Related
Apr 12, 2005
Hi there
I hope you can help me
I am building an access database that has a section that is acting like an enrolment form. I have seperate tables that represent seperate entities but would all be part of the same enrolment page. For example, when i have a new person i open the databse where i have one form for entering the persons name and address etc, then ideally i want to click on a button to save that form/table and go to another (to input details on their goods, then another for another purpose)
What i am having difficulty is finding out how to do this. I am an amateur with Access and although i have used VB before im by no means an expert.
I set up my form with a control button which id like the user to be able to click which would then save all of the name/address information and move onto the next screen to input that persons goods. This should be based on the persons ID number, so if my next new customer has an autoID of 332, after adding their personal details and clicking the next button the goods form is opened with 'customerID of 332' already present.
It would also be good if there was a way of doing this that didnt look like seperate forms opening but one continuous flow ... if thats possible?
How do i do this?
If anybody knows, and can tell me as simply as possible i will be eternally greatful
Thanks
:)
View 4 Replies
View Related
Apr 17, 2014
I have a form where I would like to validate (restrict) choices in subsequent fields.
Example
Combobox choices are Air, Fire, Water, Earth, Space
In the form I have 4 fields = Material1, Material2, Material3, Material4. (all in same record on my table)
Basically I need the validation to not allow duplicates across the 4 fields.
something like....
[Material1] <> [Material2] or [Material3]or [Material4]
I am not sure of the proper syntax for the validation field in the Form Properties. Or if I am even putting it in the right place.
View 3 Replies
View Related
Apr 14, 2015
I have been tasked with creating a tool to analyse mobile phone bill data and present the analysis, and our recommendation, to a customers. Being new to Access (other than basic tuition) this has been a slow uphill task, which is finally nearing completion, however there is a problem which I have not yet been able to overcome.
The requirement is for the DB to open first on a splash screen (lets call it Form A) with fancy picture where our customer is selected from a combo box, the customer is then telephoned, a linked computer screen is established and our staff then click "Go" to proceed to a second form (Form B) showing an account overview and more details.
The problem I have is when "Go" is clicked, the second form loads via on click event, and even populates the correct customer in its combo box. Unfortunately that is as far as it gets - the combo does not look up the information. The customer needs to be selected again for the subforms and subreports to load with the customer overview. To clarify, form B just sits there blank until the customer is re-selcted from the combo box in form B.
View 9 Replies
View Related
May 3, 2006
I have a form that creates quotes for my company by inputting inventory items in a continuous subform. I need to be able to check and see if the item already exists in the inventory or if it is a new one that has been manually entered, and have this toggle a bound yes/no field that I will use to control whether or not some fields on the form are locked. Here is the code I have been using, but it doesn't seem to work at all.
Private Sub Combo14_AfterUpdate()
Dim itemcheck As Integer
itemcheck = DCount("[Item]", "tbItems", "[Item] = " & Chr(34) & Me![Item] & Chr(34))
If itemcheck = "0" Then
Me![Locked] = False
Else
Me![Locked] = True
End If
End Sub
I would appreciate anyone's feedback on this.
View 2 Replies
View Related
Jul 19, 2007
All I really need some help.
I have two tables:
Table 1
Table 2
There is a one to many relationship here.
One in table 1 and many in table 2.
When I add in a record in table 1, I need a validation script/key that won't allow adding a record to table 1 without a corresponding record in table two.
Please help. I'm really stuck. I am doing this through a form. I tried messing around with the "requirements" but had no luck.
View 5 Replies
View Related
Apr 2, 2008
I need to validate a surname field which obviously can only consist of letters and occasionally spaces and/or hyphens. (i.e. van Driel or Johnson-Crooks)I managed to create a validation rule which would allow me to have space in the surname field:Is Null Or Not Like "*[!((a-z) or ( ))]*"but when I tried to allow hyphens into the field - I used these codes - but none of them worked:Is Null Or Not Like "*[!((a-z) or ( ) or (-))]*"Is Null Or Not Like "*[!(a-z)]*" Or Not Like (" ") Or Not Like ("-")Is Null Or Not Like "*[!(a-z)]*" Or Not Like "*[!( )]*" Or Not Like "*[!(-)]*"Is Null Or Not Like "*[!(a-z)]*" Or Like "*[!( )]*" Or Like "*[!(-)]*"Is Null Or Not Like "*[!(a-z)]*" Or Not Like "*[( )]*" Or Not Like "*[(-)]*"Is Null or Not Like "*[!(a-z)]*" or (in ("-"," "))Is Null Or Not Like "*[!(a-z)]*" And (Not In ("*[0-9]*"))Is Null Or Not Like "*[!(a-z)]*" And (Not In ("*[0-9]*")) And (In ("-"," "))Is Null Or Not Like "*[!(a-z)]*" Or Like ( ) Or (-)Please can smeone help me.James
View 3 Replies
View Related
Nov 15, 2006
Hello all.
I'm having a problem with a query. I am pulling information from a table that holds demographic info plus some company info. the data in question is the deptno of an employee. This employee also has a jobcode associated with it. I have another table with a list of a few jobcodes.
The data I will retrieve is fname, lname and deptno. If a person is in detno 210 then I want to check his jobcode in the jobcode table. If it is in the table then I want it to return 215 for a deptno. If not then it will return 210.
Does anyone know how this query would look like?
Thanks in advance.
View 1 Replies
View Related
May 10, 2005
I have taken over a database that has be modified by a number of people over time. I have a forw with tab controls and a some of the fields are critical to be completed before you should be able to progress to the next tab. There are currently validation controls on the controls on the first tab, but if you only complete the first of these controls it is possible to move on to the second tab. The third tab is a subform and and if you try and select this tab, the validation text appears, but you can still switch to that tab which I want to prevent.
Finally, I have deleted one of the controls which was a critical field for completeion. I had assumed the validation was based on the validation on the control and would therefore be deleted but I still get a message saying complete this. I can't work out where there miight be code to make this happen? I'm not sure if anyone can help me with this without seeing the database, but I just wondered if anyone had any ideas what to even look for to help me understand this.
Sorry if all this is a bit vague, but would really grateful if anyone can help me at all.
View 1 Replies
View Related
Oct 25, 2005
Question I am looking at some annoying Access database for a charity and got stuck on trying to make something work that they believe used to
Two Tables
Venues
pK Venue ID
Venue Name
Venue Capacity
Booking
pK
Venue
Name
Addr1
Addr2
Addr3
Ect
They have a query call capacity check which does the following
Count Occurrences of Venues then takes this away from venue capacity
Essentially they have a standard access form of the Booking table with a drop down box and want it to only validate if seats are still free at a venue eg if the current count for venue is less than the venue capacity.
Any ideas ?
View 2 Replies
View Related
May 4, 2006
I have a NEW INVOICE button, and the code behind says
DoCmd.GoToRecord , , acNewRec
Now if a user clicks on this buttons 3 times, three blank records get inserted in the table. THis is because I am generating teh invoice number myself, and the invoice_date is defaulted to current date (DATE())
The script written in the Sub Form_BeforeUpdate event to validate if the invoice is empty, if the total is ZERO etc. is all skipped, and I get blank records in the table. I even tried to place breakpoints in the before updates script, but it does not even come inside.
What is the right way to trap data errors, blank records in this situation?
Please help.
Thanks,
Vinai
View 3 Replies
View Related
Jul 26, 2006
Hey,
I've been looking at this website (http://learning.north.londonmet.ac.uk/ib212/week7/validation.htm) and i'm trying to find an input mask that lets a user enter in an I.P. address like the IP settings in Network Connection > Properties > TCP/IP > Properties.
E.g. a user enters 19216802. I want this converted to 192.168.0.2 without the user having to enter any '.'
So far this is the closest i've gotten to validating the field
0##.0##.0##.0## whereas 0 means something has to be entered and the # are optional.
Thanks,
Barret
View 3 Replies
View Related
Jul 28, 2006
Hey,
In a form I am trying to complete, I want the user to be able to enter in an IP address like so
192168<user hits space bar>0<user hits space bar>1
Would convert to - 192.168.0.1
Is there an input mask which allows this to happen?
Thanks.
View 2 Replies
View Related
Nov 29, 2006
HiBeen trying To Find A Way Of Validating A Postcode.But Getting No Where Being They Can Be Like CT1 1QX, CT11 2DT, CT11 12DT Act.Best I've done is to make sure there's a space and it starts and ends with a Letter.Any Help Advice Would be much AppreciatedMick
View 2 Replies
View Related
Jun 20, 2007
Hi there, just a quick question really.
I have got a start date and an end date field in a table both in Date/Time data type. Can i validate the end date so that it cannot be longer than 5 days after the start date, that has been entered. Or is this impossible to do in a table?
Thanks for any help
View 10 Replies
View Related
Mar 25, 2008
Hi
I have a large file where I am trying to clean up the data.
One of the fields is the email address.
I want to check if the email address has something followed by an @ symbol followed by something else. If it does not, then I want to replace it with a null value.
Can you help?
I thought that I could use the wildcard features such as <>*@* but this does not appear to be working.
Noel
View 6 Replies
View Related
Mar 25, 2008
Hi
I am trying to work with a large table of customer data.
I know that there is a large number of invalid values.
I would like to run a make table query that would check the phone number against a table of known invalid numbers (e.g. 1234567, 11111111, 99999999, etc).
If the number exists on the invalid table, then I would like to replace it with a null value.
Regards
Noel
View 1 Replies
View Related
Sep 10, 2006
I'm trying to figure out how to validate a field to make sure the users are not entering any blank spaces into the field. I've tried setting the mask up as "aaaaaa" but it still lets the user enter a blank space.
I've also tried building an event procedure to run on exit that reads:
Private Sub Barcode_Exit(Cancel As Integer)
Dim LPos As Integer
Dim LChar As String
Dim LValid_Values As String
'Start at first character in Barcode
LPos = 1
'Set up values that are considered to be alphanumeric
LValid_Values = " abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWX YZ+-.0123456789"
'Test each character in Barcode
While LPos <= Len(pValue)
'Single character in Barcode
LChar = Mid(pValue, LPos, 1)
'If character is not alphanumeric, return FALSE
If InStr(LValid_Values, LChar) = 0 Then
AlphaNumeric = False
Exit Function
End If
'Increment counter
LPos = LPos + 1
Wend
'Value is alphanumeric, return TRUE
AlphaNumeric = True
If AlphaNumberic = False Then
MsgBox "Barcode cannot contain blank spaces, only letters and numbers.", vbExclamation, "Barcode Field Value"
Else
End Sub
****
However the message box does not come up and the form will not then let you get out of that field.
Any ideas? I've been checking on this field by running a query and then manually correcting the errors but I would rather that the DB check and force correction at the time the data is entered.
Any and all help will be greatly appreciated.
Charis
View 5 Replies
View Related
Oct 27, 2004
I have two tables in an Access Database- One is the chart of accounts, and the other is the actual entries, with the common field being ID. Chart of accounts is set up something like this-
ID Description
1000 Customer Revenue
2000 Office Expenses
2100 Paper
That kind of thing. Anyway, I want to set up the ID field in the entries table so that it is basically a pull down box that only allows you to enter one of the IDs listed in the Chart of Accounts. Any way to do this? I'd really hate to have to write a quick separate program to do this.
View 6 Replies
View Related
Mar 12, 2005
Here is the code I have in a module. When I put the correct dates in, the message box appears telling me that the date is incorrect, but I have checked and the closing date appears to be later that the date for completion.
When I accept the error message I get a runtime error 2115 saying that the database engine is being prevented form saving the data to the table.
Can anyone offer some help as to my problem here?
The code:
Private Sub txtbx_Date_Closed_BeforeUpdate(Cancel As Integer)
'Declaring the variables
Dim Completed As String
Dim Closed As String
'Assigning values to the variables
Completed = "txtbx_Date_Completed"
Closed = "txtbx_Date Closed"
'Checking to see if there is no completed date
If IsNull(Completed) Then
MsgBox "The action must be completed prior to being closed"
Me![txtbx_Date_Closed] = Null
Exit Sub
'Validating the closing date
ElseIf Closed < Completed Then
MsgBox "The closing date cannot be before completion date"
Me![txtbx_Date Closed] = Null
Exit Sub
End If
End Sub
View 3 Replies
View Related
Oct 15, 2006
How can I check if the user is entering numeric values?
I can do it in the code, i.e. the lost focus event to the text box but I want to make a check in the "validation rule property" of the text box...!! How is it possible??
Thanks
View 1 Replies
View Related
