In a table I have 3columns with the primary key of MemberID. Of the columns in question, one is StudentID and the other is StaffID. What i want to do is that if a use enters the information in MemberID, he will not be able to input in the field in StaffID and vice versa.
View RepliesHello,
I have two tables which contain the following fields (simplified);
Table 1:
ID
Startdate
Enddate
Table 2:
ID
Unitdate
Treatment
The tables are related (one-to-many) through the ID field.
In operation, the user first enters the Startdate and an Enddate for a patients' hospital stay in Table 1. Following this, the user enters the Startdate again in the Unitdate field in Table 2 and selects a Treatment from a drop-down list. After this, the user enters the following in the Unitdate field and selects a treatment. This is repeated in consecutive order until the Enddate is reached.
The end result looks like this;
Table 1:
ID Startdate Enddate
123456 01/01/2005 04/01/2005
Table 2:
ID Unitdate Treatment
123456 01/01/2005 Ventilation
123456 02/01/2005 Ventilation
123456 03/01/2005 Haemofiltration
123456 04/01/2005 Ventilation
Does anyone know if there is any way to restrict the Unitdate field in Table 2 to only allow dates to be entered that are between the Startdate and Enddate fields in Table 1? Ideally, I would like the Unitdate to populate automatically with the dates, starting with the Startdate value and ending with the Enddate value and filling in all the intermediate dates. Also I would like to make sure that there is no way of having duplicate dates in the Unitdate field.
If anyone can help, I would be extremely grateful.
Best wishes
Russell
Hello everyone,
I'm working on a data-entry form and would like to restrict the data that I enter on certain fields to alphabetic characters only i.e. a -z, A - Z and Spaces.
Is there any way I can acomplish this??
Many thanks in advance.
Will
Please see attachments.
POST.zip (384.0 KB)
database.zip (58.8 KB)
I have a database that contains 13,772 records and each record contains 34 fields. The database is used for historical research purposes. As I am constantly changing the content in one or more fields I would like to be able to have a field that would show the date of any changes that are made to any of the fields in any given record. Additionally I would like to be able to run a query, lets say every six months and extract all the updated records.
View 6 Replies View Related I have made a program that can identify a entry in a field (ID) and change the quantity of a field. However, if can only identify numbers. I have the code as follows:
Code:
Option Compare Database
Sub AddToInventory()
Dim tb As DAO.Recordset
Dim I As Integer
I = 1
Do Until I = 2
On Error GoTo Canceled
[code]...
The "where ID2 = " only works with numbers. Is there anyway I can make this work with Strings? Basically, Find the string in a column and update the quantity.
i am currently developing a database what i need to do is to restrict user to view selected record and change them. i have build a login form in which i have two areas as a user name in combobox and a password text box. i have another form in which i have a combo box named area what i need to do is to limit the area combo based on the selection of the user login form previously. for example if a user select LAS VEGAS in user combo and enters its password after clicking the button login the another form appears in which thier respective stores and sales are saved, their is a combo box named area in which i want to limit it by LAS VEGAS i mean it would only show LAS VEGAS in drop down based on the previous selection in login form.
View 4 Replies View RelatedI have two tables, one called 'Company' and one called 'Person'. Both tables have several fields, but they both have the same primary key, i.e. 'Naam'.
When I type in a name in the 'Person' table, I'd like the 'Company' table to automatically display the name too. So for example if I type in 'John Doe' in the person table, I want to be able to switch to the 'Company' table and have the same name displayed there, automatically.
I thought that a phone book might be a good thing to begin with...
Should I be making every single entry field on it's own table and then pulling them from there into one form or....?
How can I get the value from a field in one table (in the sub form) to copy/insert into a field in another table (in the main form) when adding a new record?The main form and sub form are linked using parent/child linking, and the sub form is in a tab.I have table A (Visit Dates) in the main form which is used to record the date of a visit to a church. Table B (Quarters and Peals) is used to record an event that took place at that church during that visit. Note that not all visits in table A require a record to be created in table B - but half or more do.
In tables A and B I have a field called "QuarterOrPealID" and these are both primary keys, though the field in table B is set to 'no duplicates' and in table A it's set to 'duplicates allowed', as table A has its own auto number/pk. They are both linked in the relationships.
So, when I add a new record to table A using the main form, I might then need to click on the tab in the sub form to create a new record in table B, which has to be linked to the same record in table A. When the "QuarterOrPealID" auto number/pk is generated in the sub form (table B), I need that value to update to the "QuarterOrPealID" field of the main form (table A), so that when I'm viewing these records the form pulls all the information nicely together.
I've just returned to work after kids and started managing a large Access database related to health, back-tracking over many years.
Currently in filling a form we physically enter:
Apples 2.2
red apple 2.4
red apple cut 2.45
Oranges 5.6
Cucumbers 8.5
Is it possible to get field 2 to automatically fill with a number code due to the text typed in field 1?
FWIW, I'm confident at more basic Access e.g making follow on default value = Dlast("field""table") type stuff but the more complex stuff I haven't touched since Uni over a decade ago and you will need to be gentle while I blow away the cobwebs
I have a form that users will use to add new records (customers).
There is a field named VAT_Registration_no
First of all i want some code to check for duplicates in that field only,before entering the next field.If the record exists i want to show a msgbox and set focus to the vat_registration_no field. Also I want the same thing to happen if the vat_registration_no field is empty.here is what i have tried:
Private Sub VAT_registration_no_AfterUpdate()
Dim btest As Boolean
If VAT_registration_no = "" Or IsNull(Me.VAT_registration_no) Then
MsgBox "Please enter a Vat Registration No.", vbOKOnly, "error"
Me.VAT_registration_no.SetFocus
Else
btest = True
End If
End Sub
and to all other fields:
Private Sub textfield_Enter()
If Not btest Then
Me.textfield.SetFocus
End If
End Sub
If I just press enter to go straight to the second field I dont get a msg. If I write something and delete it and press enter i get the msg but when I press ok the cursor goes to the next field. I want it to go to the vat_registration_no field again. And I also want this to happen even if dont write something and then delete it.
I am creating a database tracking physicians and their contracts. I currently have two tables: PhysicianT and ContractsT, with corresponding forms to enter information in them. I have an issue with the Contracts form; I want to be able to select a physician from a dropdown list (looked up from PhysicianT) and have Access autofill their Physician ID #.
PhysiciansT looks like this:
physicianID (AutoNum) name (Calculated)
1 Barker, Bob
2 Burgundy, Ron
3 Stark, Tony
Upon selecting Barker, Bob from my dropdown list, I want "1" to appear in the Physician ID # field in my Contracts form.
is there a way to pw or restrict access for ppl to a table, form / control page?
View 1 Replies View RelatedHi
I have made up an Access database form for our Sickness Administrators to fill in when someone is off ill. I want the form to only show (on the drop down list of names) the people that that particular SA deals with - for example, Jill would enter sickness records for the Democratic, Legal & Personnel section. I am not sure how to go about this:
a) if I have one table with all of the employees names on it, is there a way of restricting Jill from seeing any names other than the ones that she deals with?
b) if I have separate tables for each section, would that be easier? and if so, do I just make a "switchboard" for them to click on the appropriate list?
Thanks!
Maria
I have built a database to be used by several users. One of the tables contains some fields which I don't want some of the users to be able to see.
All users log in to the database with their own username and password.
How can I hide the fields to everyone except a specific group of users?
Thanks,
Gary
Hi, a newbie needed help here.
I just wanted to ask whether it is possible to restrict records/rows in a table that has the same values (same date to be exact).
For example, I have an order form and wanted to restrict to only 20 orders per day (could be of any date; not restricted to the current date only). Therefore, if more than that, a message box will appear and no more record will be allowed to be saved.
Whether is it possible or not please justify.
Thank you in advance.
Hey guys-
New here, and fairly new to Access as well (although I can stumble my way around). I am building a database in which I have to import the data on a daily basis (there won't be much, if any, data entry- just manipulating/Queries of the imported data). One of the fields I am importing is a 'category' type field. There's no way to restrict the data entry aspect of it before I import it into my DB- so I have to work with what I got.
The users type in the categories themselves- many all match the same name/spellings, some do not. So I assume I'm going to have a Category table with all the acceptable categories already entered into it. What I need is some way for the ones that don't fit the acceptable Category names to be flagged- once flagged I need to be prompted for each record if I want to ADD the category to the Category Table, or, if I want to match it to an existing category in the category table. If I choose to match it to an existing category, it will always keep that new category name so that queries and whatnot are consistent.
An Example-
The acceptable Category name might be 'Fruits'.
Upon importing the file- there might be entries that match that exactly, or- some might be entered as-
The Fruits
Fruits II
The Fruity Ones
If these three were to be in the import file- I want to be prompted to change the field entry to 'Fruits' or create a new category name, and associate that with the new name.
How would I go about doing this?
Thanks!
ok, basically the way i am structuring my database, there will be several forms used to enter details into certain tables...
in my previous limited attempts, i have simply created forms using the 'wizard' which just makes a form based on that table (for entry)
what i would like to know is a very general question..
if you make a form from scratch.. say it is a completely blank form, if i place a combo box with certain entries in it, can i forefully edit a certain field in a table using those entries.
i mean, is there a way (within the properties section of the various components placed on a form) to make that value entered go into a particular field in a table.. without using any 'wizard' before hand to automatically put certain fields in.
I have created a db and would like to apply the format of the attached zip file
I cannot seem to work out how it was done cutting and pasting the code is not working either if someone could take a look and give me a simple method of creating the desired solution i would be very greatful
The button to access the password will be called DCC Log In.
This will then allow the Command Button DCC to be Clicked (Previously Hidden as in the example)
This opens the DCC Form (already achieved by =openForms("DCC")
What I have is a database that I am using for 3 different sites; NE, NCA, & SCA. What I want to do is have the form ONLY show information for the specific site that the Leader is a member of. I have a security table that contains the persons name, & site location. For example: John Smith works at the NE site; so when he goes to open a form to add/modify/delete members from his site I want him to only be able to see date for NE from the table. So in the listbox NCA & SCA would be filtered.
How can I go about doing this? The forms Record Source is set to the Security Table. Please help so I can do this without having to create a seperate form for each different location. :)
I want to restrict the number of times each person can open a database, but give them the option to force close the database on another PC, if they have left it with the database still open, prior to allowing them to have full access / functionality of the database on another PC.
In order to achieve this, I need advice on how to automatically record the computername as each person "logs in" to the database and secondly, send a command to close the database / Access on a PC with the stored computername.
Thanks in anticipation.
I am trying to set up a database to track truck maintenance, repair, and inspection records for a trucking company. Parts are ordered from vendors, and I have forms set up to facilitate the entry of the information regarding those orders. The problematic form is based on a query. In that query (this form and query deals with the purchase details - product, price, extended price), the product ID is gathered from the inventory transaction table, the product name and price are gathered from the product table (I did this so that price would enter automatically).
Of course, when I change the price, it is changed across all related orders, including those that have been saved previously. I know that the problem more than likely stems from having the info pulled from the products table. I'm just at a loss as to any other option to have the price automatically pulled.
I am new to Access and am currently learning it so that I can construct a database of trivia questions.
I have a "Questions" table with question, answer, type, etc. This table is linked to a "Categories" table with a many-to-many relationship using a junction table called "Questions_Categories". The "Categories" table is also linked to a "SubCategories" table with a Categories as the parent and SubCategories as the child. I have attached the database for ease of understanding.
When I enter data into the "Questions" table using datasheet view, I open up a subsheet that shows me the Category and SubCategory fields which have their own lookups.
My problem is the following: I want each category to have its own set of SubCategories. For example, the "Region-Specific" category has "Canada", "London", and "Ontario" as sub categories. However, the movies category should not have any sub categories. The problem is that when I choose Movies in the sub datasheet, the subcategory lookup still shows Canada London and Ontario when I don't want it to show anything. It doesn't make sense to have a Movies category with a London subcategory.
I have a master to child table relation which is one to many. Each one is represented by a different form.
The problem is, the user enters the primary key "JobID" on the master form, then he needs to enter a few records on the child list with the same "JobID" which is the foreign key in that case. I don't want the user to enter the "JobID" every time he enters a new record in the child form.
Is there a way I can copy the primary key to the foreign key each time the user enters a new record in the child form?
I'm able to pull users that are logged into our network but our company uses a name that has their employee number as well. So deponding on when they were hired it could be a 5 or 6 digit number. so an example would be JSmith123456 or it could be JSmith12345 so is there a way to drop the numbers at the end? Meaning if they are entering data into a form that pulls their name can I drop off those last digits in the name field?
View 8 Replies View Related