Modules & VBA :: Prevent Deletion Or Changes To Records That Meet Certain Conditions
Jun 14, 2013
Been experimenting on some code to prevent deletion or changes to records that meet certain conditions.
The problem I am having is the code is contradicting each other preventing some of the records to be locked down.
Code:
Private Sub Form_Current()
'Code 1 - Prevent edits if 21 day period has lapsed
If TestDate2(Me.txtStartedHidden) > 0 Then
MsgBox "Editing NOT allowed - 21 day time period has been reached"
Me.txtBasic.Locked = True
[Code] ....
Rules are:
Edits allowed if invoice date not reached
Edits allowed if within 21 day but NOT if invoice date is reached
Edits prevented if invoiced date reached and if records still within 21 days
Edits prevented if invoiced date reached & records past 21 days
View Replies
ADVERTISEMENT
Mar 24, 2007
I have a database with several forms, tables, queries etc. and it works fine. One of the users of the database accidentally deleted a record which was displayed by a query. I have selected AllowDelections = No in all object properties. Despite this, the record got deleted. Is there a way to prevent such deletions? Kindly help.
View 1 Replies
View Related
May 17, 2013
In a simple Access 2003 database that has two table. A contacts table and a Report table.
I would like to be able to prevent the deletion of any contact that is still being used in the Reports table. The problem is that they are in a backend of a linked database. So the enforce referential integrity doesn't work. Plus I don't think I would like it anyway as it seems kind of dangerous to the reports table.
Is this possible to do what I need through the use of form coding?
View 14 Replies
View Related
Jun 3, 2014
I have a table called estimates and a field in that report is called RFA and this is a checkbox. How can i open a report i created (called EReport) for all rhe records in this table that have the RFA field checked (therefore true).
View 2 Replies
View Related
Nov 24, 2014
I have some code which creates a record in an external csv file to provide information to upload a vehicle to the website. It works fine with the exception of creating duplicates when, for example an option to amend a record is given to the user before continuing to a new record. The option has to be in place in case there is an error in the specifics of what is important to be recorded in the database and under such circumstances the form code allows the user to tab through the form and correct any errors, but this creates duplicate entries in the csv file as the user has to pass through the 'SavePrintVehPurch_Exit' button a second time after corrections are made.
I am wondering if there is a way of altering the following code to effectively look look to see if the csv record exists and if so correct any changed field information rather than create a whole new record or create a new record where the record does not exist - The record in the csv file is determined by the 'Me.VP_VehRegMark.Value '.
Private Sub SavePrintVehPurch_Exit(Cancel As Integer)
Dim fileText As String
Dim fileName As String
Dim fileNumber As String
fileNumber = FreeFile
[Code] ....
I am wondering about introducing the something like the following ( but can't work out how to clarify comparison of the fields)
If Len(filename(fileText)) = ? Then
Do not change entry
Else
Overwrite the new detail
View 2 Replies
View Related
Dec 17, 2013
I have a main form with 3 subforms. Each subform is identical except for the value of the filter property. The filter is for the same field, but with a different value for each subform. So, for example, the first subform has a filter of:
Code:
[WBS Element]="DEF" And [Period]=Forms!frm_ProjectFinancials!Period
while the second subform has a filter of:
Code:
[WBS Element]="PPE" And [Period]=Forms!frm_ProjectFinancials!Period
and the third subform has a filter of:
Code:
[WBS Element]="EXP" And [Period]=Forms!frm_ProjectFinancials!Period
The recordset for each subform results in a single record with numeric values in each field or no records at all. When the resulting recordset is empty (no records), the bound text fields on the subform display as blank. I want these fields to display 0 instead of blank so I can use them in other calculated fields. Functions such as Nz or IsNumeric do not work since there are no records and the values are neither null nor numeric.
How I can display zeroes in the bound fields when no records exist that meet the filter criteria? Or is there a way that I can dummy a resulting recordset to have all zero values when there would otherwise be no records?
View 9 Replies
View Related
Aug 10, 2015
I have the following code
SELECT Count(T_STUDENTS.studentsID) AS res1yes, T_COACHING.COACH1res, T_COACHING.COACH1port, T_COACHING.COACHworkgroup
FROM T_STUDENTS, T_COACHING
WHERE (((T_STUDENTS.res_vrijstelling)=False) And ((T_STUDENTS.stud_year)="1") And ((T_STUDENTS.stud_coach)=forms!F_cboCOACHING!cboCOACHING))
GROUP BY T_COACHING.COACH1res, T_COACHING.COACH1port, T_COACHING.COACHworkgroup;
It runs fine, untill the moment the count is 0. At that point it breaks the form. How can i tell access to deal with this? Somehow i need to replace the count result with 0 if there are no records that meet the criteria.
View 8 Replies
View Related
Mar 22, 2008
I have two tables which are connected with one to many relation, here are these tables: tblCars PK. intCarID txtModelName intManufacturerID intModelPrice tblManufacturerPK. intManufacturerID txtName txtAdress txtTelephoneNow, how do I achieve that when I delete a Car from the database, the Manufacturer is not deleted, because as you know one Manufacturer can create several model of cars, but when I delete Manufacturer all the cars from that Manufacturer are deleted? I know I need to enable cascade Delete but when I do that in each case when I delete a record from one database its relational record from the other table is deleted. How do I acomplish this?
View 3 Replies
View Related
Mar 1, 2015
I'm having trouble executing a SQL command in VB... I want it to find the the value of the input box in TBL-Purchases and Delete all related values. Here's my code.... I get an error on the line I've highlighted in green...
Private Sub Command31_Click()
Dim Message, Title, Default, MyValue1, MyValue2
Title = "Sell Stocks"
Default = ""
MyValue1 = InputBox("Which stock ticker name would you like to sell?")
MyValue1 = UCase(MyValue1)
[Code] .....
View 9 Replies
View Related
Aug 19, 2013
I'm using this stardard piece of code on a button that deletes a record from a simple continuous contact form.
Code:
If MsgBox("Do You Want To Delete This Record?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete?") = vbYes Then
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
End If
If the form is called "CONTACTS" with a field called "Contact_Name", what code do I use to have the relevant Contact_Name appear in the MsgBox to be sure that I'm deleting the correct record ...... something like this .....
Do You Want To Delete The Record Called Paul Quinn ?
View 2 Replies
View Related
Jul 16, 2015
We need to reset the autonumber after the last record is deleted.
For example:
2006
2007
2008
if 2008 gets deleted, we want the autonumber to reset back to 2008 rather than move to 2009.
Is this possible? This is the code we are using but cannot get the string to recognize the variable. If the variable is replaced with a number, it works, but it defeats the purpose of adding the plus 1 to the last autonumber.
Code:
Private Sub Command0_Click()
Dim RLMax As Integer
Dim Statement As String
RLMax = DMax("[id]", "Table1")
RLMax = RLMax + 1
strSQL = "Alter table table1 Alter Column Id Autoincrement(RLMax,1)"
DoCmd.RunSQL strSQL
End Sub
I realize we don't want to depend on the autonumber for anything other than a row identifier, but the table is setup that it is important for the rows to be sequential if the last record is deleted. Only if the last record is deleted.
View 12 Replies
View Related
Jul 8, 2006
Hello. Can this be done?
I am creating a mini scheduling program, and I have one table that handles all requests (tblRequests), then a second table is the final schedule (tblFinalSchedule). On the table for the requests, I have a field called "locked" that is of type yes/no. The purpose of this field is for a meeting time that is of uptmost importance and that I don't want the program to manipulate it in any way.
Is there a way to program in VBA so that when a button on a separate form is clicked, those records that when the yes/no field is checked (set to true), are copied into the appropriate fields from tblRequests into tblFinalSchedule?
View 1 Replies
View Related
Jun 8, 2014
I want to count the records with multi iif conditions. I am able to get one condition right but don't know how to put more than 1.
Eg: =Count(IIf([Step #]=1 And [Type of Work]=1,0))
But I am not able to get if I want step#1, step#2 and step#3 all together with type of work = 1
View 5 Replies
View Related
Nov 6, 2013
how to do multiple conditions, Nested....I have 6 conditions that i like to check,
Code:
if (condition-1) And
(condition-2) And
(condition-3) And
'[code]....
View 8 Replies
View Related
Sep 10, 2014
I'm trying to create an If/Then statement that needs to match to multiple values. My initial thoughts were to create a list but that doesn't seem to work the way I thought it would.
For example:
If Me.Field = 1,2,5, or 8 Then
Do 'X'
Else IF Me.Field = 3,4,6 or 7 THen
Do 'Y'
Else Do 'Z'
I haven't been able to get the syntax to work right. I'd rather not have to do 'Me.Field = 1 or Me.Field = 2, etc.' but if that's the only way to have it work right, then I guess it'll have to do.
View 6 Replies
View Related
Oct 11, 2013
I have a Customer Issue form that writes the following into one table named Table1: Date, Customer Name, Ticket number, Agent, Issue and Comments. This is very simple.
All of this is filled in from a form that has links to 3 other tables for drop downs; Customer Name, Agents, Issues. This is working perfectly.
What I want to be able to do is generate a report based on a date range for a particular agent. Say 9/1/2013 - 9/30/2013 for John Doe.
For the report portion, on the form I have to combo boxes, one for start date, one for end date. I also have a drop down for selecting the agent. When I click a button on the form named Report, It will generate a preview of the report.
Here is the problem. I can get this to generate a report based either on the date range, which gives me all of the agents, or by agent, which gives me all of the dates. I can't get it to do both.
Here is some code that I have on the Report button:
DoCmd.OpenReport "AIReport3", acViewPreview, , "[Agent]=" & Me.Agent
This is the code that will let me choose the agent, but gives me all dates.
If I change this code to this:
DoCmd.OpenReport "AIReport3", acViewPreview, , "[DateRptd] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"
It will display all issues in the date range, but gives me all agents.
I was thinking I should be able to combine them with an AND or an & to get it to use both the agent and date fields, but I can't get this to work.
Something like: DoCmd.OpenReport "AIReport3", acViewPreview, , "[Agent]=" & Me.Agent And "[DateRptd] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"
or
DoCmd.OpenReport "AIReport3", acViewPreview, , "[Agent]=" & Me.Agent & "[DateRptd] Between #" & Me.cboFrom & "# And #" & Me.cboTo & "#"
View 3 Replies
View Related
Sep 8, 2014
I am trying to get a Case Statement to evaluate multiple conditions. Example: below when I get diagnosis code 20400 and the age_at_diagnosis is 40 the code is basically ignoring the second condition of the Case "And rs![Age_At_Diag] < 18". How do I get the code to recognize both conditions?
Code:
Private Sub cmd_Update_Conditional_Codes_Click()
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
[code]...
View 1 Replies
View Related
Jul 15, 2014
I’d like to repeat the last not = 0 record under certain conditions, in a table or in a query.What I have:
Calendar_All Dates
Calendar dateMyValue
7/6/20140.00
7/7/2014108.94
7/8/2014107.71
[code]...
Basically, if Calendar date > Date(), if Calendar Date not Saturday or Sunday, weekday(Calendar date<>1 and <>7), AND Calendar Date not in (Holidays table).Then repeat the last not = 0 value of MyValue. I thought of changing the default value but the value is already 0, while default is null + I need to set the default value under certain conditions.
View 14 Replies
View Related
Jul 3, 2014
I have a module that is in Excel that is connecting to a back end database. I am trying to pull data from a table that meets a number of conditions. With the following statement, it is not returning any records,even though I know there are records that meet all of the conditions. I suspect the problem is with the last condition. In that condition I am trying to say that pull in records where it has been at least 14 days since the last review.
Code:
strSQL = "SELECT tblsojrol_oc.* FROM tblsojrol_oc WHERE tblsojrol_oc.[Status] = 'Pending' AND tblsojrol_oc.[1st Review Date] IS NOT NULL AND " _
& "tblsojrol_oc.[3rd Review Date] IS NULL AND (DateADD(Day,14,tblsojrol_oc.[2nd Review Date])) >= #" & dt & "#;"
objRs.Open strSQL, objConn, adLockReadOnly
View 14 Replies
View Related
May 27, 2014
I have a table in Access that I have a form saving new records to. Before this save occurs, I would like Access to check if the account number already exists and if the account does exist if it is outstanding. If both of those conditions are met I would like a message box to display and cancel the save as it is a duplicate. I can't seem to get it to work though.
I was thinking to use a filtered recordset based on one of the conditions and then perform a find on that recordset to see if it is null.
Code:
dim acct as long
dim rstfiltered as DAO.Recordset
Set rstfiltered = CurrentDb.OpenRecordset("SELECT * FROM tblclstrack WHERE [Request Status] <> 'Completed'")
acct = Me.cd_number.Value
[code]...
View 9 Replies
View Related
Dec 13, 2005
What is the best way to prevent widowed records? By widow record I mean records in the parent table that do not have a related record in the child table.
I have a tblOrder that is linked to a tblProductOrder by the OrderID field. I don't want the user to be able to create a record in tblOrder without adding some products to the order, which will be stored in tblProductOrder.
Thanks for the help.
View 5 Replies
View Related
Jan 13, 2007
i am using a textbox in the main form to transfer the Yr/Mth into the table. (refer to qp.zip)
now my problem is, how do i prevent duplicate records into the table?
e.g: currently whenever i scroll ard the months control in the form, i will get duplicate Mth/Yr into the table. how do i prevent that from happening?
i only want to have updated data inside the Mth/Yr table instead of duplicated Mth/Yr
Public Sub PutInMonthlyRecords()
Dim sql As String
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim f As Form
Dim MthYr As String
Set f = Forms!frmQpi
MthYr = f("txtMthYr")
sql = "SELECT * FROM [tblQpiMonthly] WHERE (([tblQpiMonthly].[Input MthYr] = 'MthYr' ));"
Set Db = CurrentDb()
Set rs = Db.OpenRecordset(sql)
If rs.RecordCount = 0 Then
If (f!txtTotalPF) = 0 Then
Exit Sub
End If
rs.AddNew
rs![Input MthYr] = f("txtMthYr")
rs![Monthly TotalPF] = f("txtTotalPF")
rs![Monthly Rejection] = f("txtTotalAvoid")
rs![Monthly TotalAvoid] = f("txtRejection")
rs.Update
Else
If (f!txtTotalPF) = 0 Then
rs.Delete
Exit Sub
End If
rs.Delete
rs.AddNew
rs![Input MthYr] = f("txtMthYr")
rs![Monthly TotalPF] = f("txtTotalPF")
rs![Monthly Rejection] = f("txtTotalAvoid")
rs![Monthly TotalAvoid] = f("txtRejection")
rs.Update
End If
Db.Close
End Sub
View 1 Replies
View Related
Apr 7, 2008
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 Related
Apr 17, 2008
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
View 6 Replies
View Related
May 8, 2005
Hey guys..
is there any way in which i can prevent the user operating a form from adding or deleting or editing any records ?
View 10 Replies
View Related
Sep 5, 2006
Hello, I HAVE searched the forums for many days but I could not find a solution. Maybe I'm not sure what to look for...
The MAINFORM_1(single form view) is based on a Query and has a cbo named 'cbo_client'. This cbo is for a field named [client_id]. The AfterUpdate [event] of 'cbo_client' does a requery on a field in the the following SubForm:
In the SUBFORM_1(datasheet) (based on another Query) is another cbo named 'cbo_subclient', with a select statement of 'Select (all the needed fields) FROM tbl_subclient WHERE ((([tbl_subclient].[subclient_id])=[Forms]![mainform_1]![cbo_client])).
Everything (incl. the REQUERY) works fine, and all the tables gets updated with the records the way it should. No problems.
But this is where i get stuck...: Once the user has entered data in the SUBFORM_1 datasheet, and then >changes the 'cbo_client'< on the MAINFORM_1, it requery the subform like it should, BUT the previously entered data in the subform is already written to the table!
So, I need a way to prevent the data in the subform from beiing stored if the user changes the cbo on the mainform (for a second time). OR prevent the cbo on the mainform from beiing changed after the subform is completed.
I hope you understand what I'm trying to do...
Please, just a point in the right direction and i'll battle it out from there.
Thank you.
View 5 Replies
View Related
