Cancel Adding New Record If Condition Is True

Oct 11, 2007

Hi

I wrote code that should validate a field when entering a new record and then if a condition is true, that new record should be cancelled and not entered into the table.

I managed to partially achieve this by writing the code below, but the new record does not get cancelled because the table will still create a PK for that record and leave the rest of the fields empty. I am using an autonumber for the PK that's why the table creates it automatically What I want to achieve is to cancel the creation of a new record at once, I don't want even PK created for that new record.

I used the CancelUpdate because I thought it would cancel the record creation, but it did not! When I read about it it said that I need to use it with either Edit or AddNew, (which i don't understand why!) but it still does not work.

Private Sub PlotNum_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_msg
Dim db As DAO.Database, rs As DAO.Recordset
Dim n As Integer, i As Integer
Dim vPlotNum As Integer
Dim vPhaseID As Integer

vPhaseID = Forms![frmHouse].Form![PhaseID]
vPlotNum = Forms![frmHouse].[qryHouse2].Form![PlotNum]

Set db = CurrentDb
Set rs = db.OpenRecordset("tblHouse")
rs.MoveLast
n = rs.RecordCount
rs.MoveFirst
If n > 0 Then
For i = 1 To n
If rs![PhaseID] = vPhaseID Then
If rs![PlotNum] = vPlotNum Then
rs.Edit
rs.CancelUpdate
MsgBox "This plot number already exist in this particular phase." & vbCrLf & "Please choose a different Plot Number"
Forms![frmHouse].qryHouse2.Form![PlotNum].Text = ""
End If
End If
rs.MoveNext
Next i
End If
rs.Close
db.Close
Set db = Nothing
Set rs = Nothing

Exit_Err_msg:
Exit Sub

Err_msg:
MsgBox Err.Description
Resume Exit_Err_msg
End Sub


Any suggestions will be very much appreciated.
Thanks.
B

View Replies


ADVERTISEMENT

Cancel Adding New Record

Jun 15, 2007

am using VB to check for an id if it is a new record. i have a message that pops up.

If IsNull(Form_myform![ID].Value) Then
response = MsgBox("You have not entered your ID. Do you intend for this to be a record?", vbYesNoCancel, "id check")

but how do i get it to not enter the record into the table if no or cancel is clicked?

View 2 Replies View Related

Adding One Field Of New Record Automically According To The Condition

Feb 21, 2008

hi all,

I am doing one project using access. I 've made two forms. In one form, lets say, it contains two common buttons. Both buttons will load the same second form and will add new record to the same table. When I click first button, it will load second form with all fields blank and one field of record will autofill one value,lets say "a" to the table.That field shouldn't be appeared in the second form,just want to fill automatically. Then the other fields of new records will be filled by the user input from the second form.
Also, when I click second button, it will do similarly but only the autofill value will be different from the first one.
Does anyone know how to make it?
If don't understand what I am saying, I can explain it again.
Any help will be grateful for me.

View 5 Replies View Related

Cancel Record In Subform If Click Cancel On Parent Form Before Save

May 24, 2014

I have a form and a subform in it. I added New cancel button in the form so that the the user can cancel the record creation and no record will be inserted in the parent table.

But when details are entered in the subform (a datasheet) row records will be created in the subform table. what is the correct method or how to cancel these records if the user choose to click cancel button on the parent form.

View 5 Replies View Related

Open Query If Condition Is True

Jun 15, 2006

I have a database with 3 tables. Each table has an account number field however the tables are completely different as are the account numbers that populate them. To go with the tables are queries that will bring in desired information. What i would like is for the user to input an account number on a form and have some code in the background that will find the account number in the table it is in and open the corresponding query. Is this possible? Thanks for the help. I have been trying to write code with DLookup but can't seem to get it to do what I want.

View 7 Replies View Related

Auto Fill Entry If Condition Is True

Apr 19, 2005

I have a table

(Not real names)

AutonumberPK
RequestType (Lookup to TypeTable)
RequestNumber
RequestName

This table then Relates to a request detail table.

The question here,

There are two types or request (T1 and T2)

In my form to enter new Requests I will have a dropdown box to select the type of request.

The next field is the request number. If the type of request is T1 then the person entering the request will have a 6 digit number to enter. But if the request is T2 then I need to auto populate the field.

Is this possible?
Or should I just build two forms, one for each type.

Also what would be the best way to auto fill in a number that follow this pattern.

05040001
05 = year
04 = month
0001 = the number of the request for this month.

Every month starts over at 0001

View 2 Replies View Related

Queries :: Multiple Query Criteria IIF Statement - True Condition

Feb 24, 2015

I'm trying to have a single or multiple query criteria based on what the user checks on a form.

I can't get the True condition to work at all, I get no records. Here is what I'm using

IIf([Forms]![FrmAttendanceLogsRpt]![BlkFilter]=-1,[TempVars]![EID] Or 86,[TempVars]![EID])

If I just put
[TempVars]![EID] Or 86
in the Criteria it works just fine.

View 14 Replies View Related

Change Color Of Whole Line If Condition Yields True Of One Control

May 28, 2013

I know you can use a "Conditional format" to change a color of a control in a report. What I am trying to do is change the whole line if a condition yields "True" of one control. I did it once before, but oops I didn't save my work. I am using the format [Entity]![IT]. Where "Entity" is either the department or vendor, and "IT" is for the IT department. I want the control "Name" to change color if "Entity" is true if "IT" is the data.

View 3 Replies View Related

Form Field Validation - "Cancel = True" Problem

Feb 21, 2005

I'm calling this code on my Form's BeforeUpdate Event which I understand should only run if the form is dirty. In this example, the code should check to see if 4 fields ("txtField1", "txtField2", txtField3", "txtField4") have data present, if not highlight them yellow.

Function Msg()
Dim ctl As Control
Dim strFields As String
Dim strControl As String
Dim intCounter As Integer
Dim blnEmpty As Boolean

For Each ctl In Me.Controls
blnEmpty = False
If ctl.ControlType = acComboBox Or acTextBox Then
If ctl.Visible And Len(ctl.Tag) > 0 Then
Select Case ctl.Tag
Case "txtField1", "txtField2", txtField3", "txtField4"
If IsNull(ctl.Value) Then
blnEmpty = True
ctl.BackColor = vbYellow
Else
ctl.BackColor = vbWhite
End If
Case Else
If ctl.Value = 0 Then
blnEmpty = True
ctl.BackColor = vbYellow
Else
ctl.BackColor = vbWhite
End If
End Select
If blnEmpty Then _
strFields = strFields & ctl.Tag & vbCrLf
If blnEmpty Then _
If Len(strControl) = 0 Then _
strControl = ctl.Name
End If
End If
Next

If Len(strFields) > 0 Then
Cancel = True
MsgBox "You have not completed all data fields, " & _
"please enter data in the following fields:" & vbCrLf & strFields, _
vbExclamation, Me.Caption
Me(strControl).SetFocus
Me(strControl).BackColor = vbWhite
Exit Function
End If
End Function

However, it keeps falling over at:
Cancel = True saying "Compile Error: Variable Not Defined"

I want to be able to run this code from a "Close Form button" to ensure that either the record is not created unless the fields listed are filled in or the form closure is cancelled so that the user can complete the missing data.

I've seen varients of this code on a couple of Access forums but can't find a solution for the "Cancel = True" issue.

Am I doing something wrong?

View 2 Replies View Related

Adding Value To A Field When Condition Is Met

May 19, 2015

I need for a valued to be changed in a row after it checks for how many rows have another value, counting how many and that number making it the amount field.

Example:
Fields- Name sponsor amount

I want access to show how many people have been sponsored by Gabriel... So if 3 where sponsored by Gabriel show 3 to the amount field for Gabriel row! Any way to do this or an easier way?

I for it to check it every time a user is added incase the user added is sponsored by Gabriel add it automatically!

View 14 Replies View Related

Adding Multiple Searches In Where Condition

Oct 16, 2013

I currently have this for my where condition, and searches the form based on the clientID selected in the combo box.

The catch now is i have to add another search.

="[ClientID] =" & [TempVars]![ActiveControlValue]

now i have to also search [ProductID]

The combo Box is set up like this:

ClientID....ContactName....ProductName....ProductI D
Hidden.....Visible..............Visible........... ..Hidden

How can i add [ProductID] into the existing where condition to add an additional parameter?

View 4 Replies View Related

Forms :: Adding Additional Records On Condition

Jun 29, 2013

What I am trying to create is a Despatch database for our warehouse.

Records in table:
Date ()
Customer
Invoice #
Qty of parcels sent
Courier used
consignment number

In most cases we will send one invoice per consignment number (database works fine for this) But on occasion we may send multiple invoices. What I want to be able to do is have a list box to select the number of invoices, this will make available additional fields for Invoice # and Qty of parcels sent. The idea is to get away from keying in the other records for each invoice going to the same place.

View 3 Replies View Related

Cancel New Record Command

Jan 26, 2005

Folk,

I'm using the default command to add a new record in a table:

DoCmd.GoToRecord , , acNewRec

The question is: How I can cancel the new record inclusion on table? It's possible?

Thanx a lot,

Maikon

View 2 Replies View Related

Hit (cancel) But Record Still Creates?

Jul 31, 2015

When I launch my modal, I want the user to be able to 'cancel' without creating a new record. It's not doing that and creating extra 'junk' in my table. How do I prevent that?

Here's my code:
Private Sub Btn_Exception_SubModal_Click()
DoCmd.OpenForm "Frm_Exception_UpdateModal", acNormal, , acFormAdd
Forms! [Frm_Exception_UpdateModal]![clientnmbr].value = Me![clientnmbr].value
End Sub

View 6 Replies View Related

Queries :: Cancel Age Calculation Query For One Record

Mar 26, 2013

I have the following age calculation query:

Age: (Now()-[DOB])365

It works a treat! However, I do not want this to continue to calculate if the record has them as deceased - I want it to stop at their date of death.

I have a tick box that when selected indicates that this record has died, and a field where you can enter date of death.

Is there some way that via clicking this button, or by entering a date of death, I can stop the Age Query from calculating for just that relevant record, not all of them? If so, where to place the necessary VBA, etc?

View 8 Replies View Related

Cancel Button On Add New Record Form Not Working

Feb 5, 2014

I have a cancel button on an add new record form and its not working for some reason. When I press cancel it prompts if I really want to cancel and when I press yes it cancels the record creation BUT it adds a number to the recordID autonumber as if one has been created. Is there anyway to stop this? Here is my code

Option Compare Database
Private Sub Cancel_Click()
On Error GoTo Err_Cancel_Click
If Me.Dirty = True Then

[Code] .....

View 14 Replies View Related

Condition For New Record

Sep 9, 2006

What method(s) are available to detect when a user is at a new record? I really just want to change the .text property of a combo box when the user is creating a new record. If this sounds stupid, I can explain the specifics.

View 6 Replies View Related

Forms :: Open Form With Where Condition - No Record Found

Apr 16, 2013

I try this code and it's not working

no record found

Code:
Private Sub openForm_but_Click()
Dim whr As String
whr = "get_date = #" & Me.gDate & "#"
DoCmd.OpenForm "searchResult_form", acNormal, "", whr, acFormPropertySettings, acDialog
End Sub

View 7 Replies View Related

Forms :: Condition A Field To Locked On 1 Record Of Continuous Form Subfile

Jun 1, 2015

Access 2002 . Can I condition a field to 'locked' on just one record of a continuous form subfile, based on the contents of a 2nd field in same record?

View 14 Replies View Related

Forms :: Open A Form For Adding Child Record Related To Highlighted Record In Subform

Oct 2, 2013

Is it possible to open a form to add a child record related to the highlighted record in the subform?

View 2 Replies View Related

Adding New Record

Jan 5, 2006

Hi,

I've a quick question. In Access, I have a form that allows user to add new record into a table. Is there anyway of finding whether the new record has successfully been inserted or added?

So i think it may be wise to have a message to notify us of whether it's inserted successfully or not.

Hope this explain clear

Thank you very much in advance for your help

View 1 Replies View Related

Adding A Record

Feb 5, 2005

Can someone please tell me why I might be adding a record everytime I open my database. I have to forms that work from querys if I open the main form all is well , if I open the other oone first and then the main form I find a record has been added. Most annoying! :(

View 1 Replies View Related

Adding A New Record

Feb 23, 2005

This may seem basic but I need some help.

I have a table of vacinations and need to add a new record every time an animal is vacinated. I have created a form with all the fields in the table in it. But when I open it it takes me to the first record. I have had to add a button to the form to add a new record. This button then takes me to a blank form where i can input data. This is very messy and not very user friendly. Any suggestions on how i can tidy this up would be appreciated greatly.

View 3 Replies View Related

Adding A New Record

Jun 15, 2006

I have looked on here for hours and cannot find anything, perhaps someone can help.
I have just converted from 97 to 2003 and now I cannot add new records to my forms. I have checked that the allowAddtions property is set to Yes but the New record icon and menu option is grayed out. I have looked through all the form settings and I can't see what it might be.

View 3 Replies View Related

Adding A Record...

Oct 5, 2006

Hi,

I have a form that has a list of textboxes linked to table properties, and a subform linked to the same table. I used the command button wizard to create an add record command, but when i click on it it comes up with:

You cant go to the specified record
You may be at the end of a record set

i have checked the properties on the forms and querys and set all data entry to yes
What should i do?

Thanks for your time

,Leon

View 9 Replies View Related

Adding A New Record

Nov 8, 2006

I have a form that has a list box with events in it. On the same form I have two combo boxes, each with the same information; officials names. I added a subform to form1 and I want this form to store and display the information that is chosen(officials and events). How do I make this happen? It seems easy, but I just can't wrap my finger around it.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved