Modules & VBA :: Lock Certain Fields On Form Based On Value In A Particular Field
Aug 4, 2015
I'm trying to lock certain fields on my form based on the value in a particular field. It seems to work the first time but applies itself to all other records in the database from then on!
The starting point is a command button that sends an email and makes Me.DPLLock = 1. The corresponding table entry is formatted as a number.
The code is...
Private Sub Form_Current()
If Me.DPLLock = 1 Then
Me.OR_Name.Locked = True
Me.OR_Sales_Order.Locked = True
Me.OR_WO_No.Locked = True
Me.OR_Qty.Locked = True
I'm making up a stock system for the independent jewellers I work for. Each stock pattern needs an individual code which can quickly be recognised read as tickets are often taken off the jewellery and can be mixed up easily. To this end we categorise based on two criteria:
1. Material 2. Type
At the moment these are as follows:
MATERIAL
IDMaterial 0Base and Miscellaneous 1Silver 2Gold 3Palladium 4Platinum 5Pewter
TYPE
IDStock Type 0Previously Owned 1Ring 2Pendant 3Chain 4Necklace 5Bracelet or Bangle 6Earrings 7Brooch 8Gents 9Gift, Clock or Miscellaneous
So if I had three pairs of silver earrings I want the first to be No. 161, the next 162, the next 163. If I had three Gold Bracelets I'd want them No. 251, 252, 253 and so on...Material and type are both selected form separate Comboboxes on a form.
I am new to MS Access, and am not sure if what I want to do is even possible. It is my understanding that comparing subsequent cells within a field in a database cannot be done- so I thought I'd see if there is a way to go around it.
I have a dataset for pedestrian activity, with over 3 million rows and 40 columns - too big for excel to handle.
I need to sort the entire dataset by 2 fields, following which I need to search down the field containing my pedestrian ids (numbered 1, 2, 3... till approx 10000), and when my ped id changes from one to the next, I need to check the value in a field showing the ped location, and if that matches with the ped's previous location, I need to copy out a cell corresponding to the previous cell's time stamp. If it doesn't match, I need to copy out another time stamp from another field.
I have a field within a form that needs to be modified based off of someone's access level and I have written that part but not sure how to keep others from modifying the field.
Code: Private Sub Qty_Rcvd_Click() If ap_GetUserName() = "Danny_Davis" Or ap_GetUserName() = "christopher_ayers" Or ap_GetUserName() = "Tena_McCrackin" Then GoTo 10 Else MsgBox "You are not authorized to view this form" End If 10 End Sub
Right now this code will bring up the msgbox for someone not authorized but once they hit "ok" the msgbox goes away and they can still change the field. What can I type in here to lock down the field for someone not authorized?
I have a Customers table and an Orders table. My Customers table is a bit different than the norm because I couldn't figure out any other way to do this.
My client's customers are either businesses (companies) with a contact person, or individual customers. So, in the Customers table, I have the following fields (there are others but do not apply to my problem):
Customer ID Company Contact Customer
So, if it is a company the data entry person would enter the company name and the full name (EX: Doe, John) in the contact field, but if it is an individual customer, then they would leave Company and Contact blank and enter just the Customer name (Ex: Doe, Jane).
So, in the Orders table, I have combo boxes for look ups for the fields Company and Customer.
That works okay, but I would really like for the Customer ID to post into a text box once the Company or Customer has been selected. In other words, the input person would enter the name, then it would show that Customer's ID #.
Using Access 2010. Fairly new to automation and macros.I have two tables (tblProductReceived and tblBins) and a form (frmProductReceived).A field in tblBins corresponds to a location the bin is at (named BinLocationID) and the tblProductReceived table tracks product that a specific bin has received.
What I need is for the tblProductReceived field PRLocationID ([tblProductReceived].[PRLocationID]) to be automatically populated with where the bin is at ([tblBins].[BinLocationID]) when selecting a specific bin in the form (frmProductReceived).
I am wanting to grey out/disable a field on a form based on entries in two other fields being the same. One of these fields that contains the data to be referenced in on a sub-form.The form is called 'Payment_Information-Form', the field to be greyed out/disabled is called 'SUF A Paid?'
The fields that are being referenced are: 'Set up fee in s (SUF) Recrt Restriction A <=' which is on the main form, and 'SUF_Letter' which is on a sub-form (within the main form). The sub-form is called 'Costings_11_Previously_Paid_SUF subform'.If 'Set up fee in s (SUF) Recrt Restriction A <=' equals 'SUF_Letter' then grey out.I tried the following formula in both the On Current and After Update sections but it hasn't work.
=IIf([Forms]![Payment_Information-Form]![Costings_11_Previously_Paid_SUF subform].[Form]![SUF_Letter]=[Set up fee in s (SUF) Recrt Restriction A <=],[SUF A Paid?].[Visible]=True,[SUF A Paid?].[Visible]=False)
hi, i would like to be able to prevent a user from being able to change a field (lock a field) in a main form if a field in a subform is complete. (the field in the subform is named: "new_weekly_base" if this is complete then i would like the field: "weekly base" to be locked on the main form. is this possible?, please help.
the main form is named: "SCREEN-MAIN" the subform is named: "SCREEN-SUBFORM" (the main form has a button on it which loads the subform.)
ive tried the below code but it doesnt work, any help would be excellent.
------------------------------------------------------- Private Sub Form_BeforeUpdate(Cancel As Integer) If [NEW_WEEKLY_BASE] >= 0 Then
With Me.WEEKLY_BASE .Visible = True .Enabled = False End With End If End Sub ------------------------------------------------
I'm trying to lock a checkbox when the forms open and depending if one has role as admin, it should unlock it.When I add the below code in the on open form I get error:
Is it possible to lock a field in a form after the user has populated the field? So for example, I have two date fields and I want them to be locked after the user has entered values on those fields. I want to avoid the user to come back in the future and change those fields. However I would like the database administrator to be able to change those dates when needed. How do I do this?
I have created a form with 3 subforms on. i was just wondering is it possible to display/ hide these subforms based on a Yes/No field in the form. as the subforms would only be valid if the field is ticked as yes.
I would like to be able to lock a form's field temporarily until certain information has been added to other fields. Once the required fields are complete, I want to be able to allow the field to be updated.
Here is a simplified version of the table fields (only including relevant fields):
Basically, I don't want the user to be able to update the CurrentEmployee field in a new record until the LastName and FirstName fields have been completed.
The reason I want to prevent this is that when CurrentEmployee field is updated, the record is then assigned a ContactID number (PK autoID field) which means that my subform is assigned an ID# and the result is messy if the user decides to abort the creation of the record.
I have a table with routes that tell me which carrier to use. This is based on from what state to what state it is traveling as well as the service lever (next day, standard, etc.)
How would I code for the value in the carrier field to populate based on the state and service level values entered.
Should I write a query to get the results? Or should it be code in an event on a particular field?
Whichever way works, any ideas on how to get started?
I have a form with 2 option buttons on it one with year 12 on it and one with year 13. I have a simple form with drop down menus and text boxes allocated for year 12s and 13s. I want all of the year 13 text boxes and drop down lists to be locked if year 12 is selected and I want all of the year 12 text boxes and drop down lists to be locked if year 13 is selected. I do not want to over complicate the form by hiding parts of it or having a new form open depending on the option button selected.
I want to lock down and unlock the full menu based on a password..
For instance upon load of the database I would like it stripped down when a general user logs in... but if a admin logins in then they get all menus...
so based on the user type will dictate the menus visible.. cane this be achieved'''''''''''';
I have a table that is used for project progress. One of the fields is named "Completed". I would like to know if it's possible to auto fill the data in the "completed" field based on three other fields within the same table.
ex: If field 1 = Yes, and field 2 = Yes, and filed 3 = Yes, then field "completed" = Yes
I'm open for any ideas, I'm new to access and am not sure how to get this started.
I am an amature with databases and a bit rusty too. Can anyone help me with this problem?
basically I want to automatically enter a unique asset code for one table that is based on product and manuafacturer's codes from other tables
e.g. for audio visual eqipment
The first table would be a manufacturers table with the manufacturers code as the primary key e.g. JVC, HTC (hitachi), SHP (sharp) etc
The second table would be a product table with the product code as the primary key e.g. AMP, DVD, LCD (lcd tv) etc.
( i would also have a customer table and an asset repair table)
In my asset table I would like to enter an asset code as the primary key of the type JVC-DVD-001, JVC-DVD-002, HTC-LCD-001 etc by looking up codes from the other tables and then adding the last number. (Ideally once the manufacturer's code has been selected only the product codes for products made by that manufacturer would be displayed). (Also it would be ideal if referential integrity could be set up so that the asset code components can only contain valid manufacturer and product keys.)
The asset code cannot be a compound key as I want to have a repair records table in which the asset code is entered as a single field with referential integrity to the asset field in the asset table.
When entering repairs I would like the lookup field to display only the assets belonging to a particular customer.
i am the biggest of all newbies when it comes to access and understand only the graphical parts of the process (no SQL knowledge etc.).
how would i go about auto filling a certain field based on what's in another field? for example, i have a field called TimeZone that i want filled with either West, East, Central, or Mountain based on another field for State. can i build a query to automatically take care of any empty fields for TimeZone that haven't already been filled out by me manually?
if i need to use SQL, that's fine too as long as the directions are clear and precise as i have no prior knowledge.