I have two tables in the same Access 2003 database.
Table1 is called "Clients" and has address type fields, e.g. street_name, postcode, etc. It also has a field called CYPAN_area.
Table 2 is called CYPAN_Postcodes and has two fields; postcodes and the CYPAN area that postcode belongs to.
I want to create a function so that when someone enters the postcode in table1, the database searches Table2 and identifies which CYPAN area that postcode belongs to and automatically populates the "CYPAN_area" field in Table 1.
any tips greatly appreciated
Harry
PS I'm only a novice so I don't know anything about VBA etc, but I understand excel type formula
I'm kinda new at ACCESS, and I've accepted an assignment that is giving me some trouble. For beginners, I could use some help as follows: I am constructing a data entry form that is keyed on PID (Personal ID). I would like to be able to have certain fields in my form (Name, Title, Department, etc.) fill in automatically from information already stored in a different table, also containing the PID (which I can set as key). I would like this action to occur either when the PID field has reached 3 characters or when the PID field has LostFocus.
Is it possible to have some fields filled in automatically as soon as a certain value has been entered into another field. e.g. if a town is entered in one field, then the postcode is automatically entered into the next field.
I am new to Access, but have an urgent need. I want to have certain fields (Yes/No type) to be selected based on a certain field (also a Yes/No) being selected.
It's very straight forward. Select West Coast, and the states CA, OR, and WA are selected. De-Select West Coast and the states CA, OR and WA are deselected.
Hi! I have a table with several fields. I would like to have a lookup in one field (combo list - get data from another table field) and for 2 other related field have the coressponding data appear automatically according to the selection in the lookup field.
Let's say in one table (called Employees) I have two columns, Name (the Primary Key) and Job (Both are text fields). One row contains "Jack" and "Gardener" in each column respectively. If I were to have a form for a different table where I select Jack's name from a drop down menu of all the "Names" in "Employees" and it then automatically fill in the next field with his Job (which would be "Gardener").
I really hope I'm making sense... Is this possible?
I have a form (Project Form) with (Project_ID,Applicant_ID,Project_description, etc). To make it easier for the user who may not know the Applicant _ID when he/she is adding a new project for the applicant, I want to put a combo box with the Applicant Names in it, and once the Applicant Name is chosen, the Applicant ID will be filled out automatically and be saved in the Project Table.
My Approach so far was adding a combo box with two columns(Applicant ID, Applicant Name), and basically adding the following code:
Private Sub Combo36_AfterUpdate() Me.Applicant_ID = Me.Combo36.Column(0) End Sub
This approach works well, when I select an applicant, the Applicant ID will pop up correctly. However, This ID is not being saved in the Project Table.
I prepared students details in access, but when i look the form view students particulars looks automatically (like name, age, weight and address). But what i need is students details has to come after I enter the name.
I have a database in which we are trying to have a field "Headcount" automatically fill with the correct value based on information in multiple fields. The "Headcount" field will be based on about 6 or 7 rules. Most of the rules i can figure out, but im having trouble figuring out what the best way would be to search for a single individual's projects when they are on more than one project at a time. I thought about using a counter of sorts to count how many projects someone was one, but how do I pick them out of the entire table once i have found out the number of projects? Would a for loop work best or just many if statements?
(*It really isn't code, I just couldn't figure out how else to keep the columns in place!)
------------------------------------------------------------------- Store Name Invoice Number Customer Name Customer Address Store1 1 Bob PO Box 55 Store1 2 Joe PO Box 789 Store2 3 Chris PO Box 1254 ------------------------------------------------------------------- On my input form, for entering a new invoice, when you select the customer name, I want the form to automatically fill in the correct address in the "Customer Address" field.
I think there has to be a way to do this because there is only ever going to be one address for every customer. And the same customers will be comming back very often.
I have a table with date,item number,quantity,price.and so on... Im using form to input new record and is there a way to automatically search the table and if I enter the item number, it will fill in the rest of the values with the previous record of the specific item number? since the only thing that will change would be, most of the time, quantity and price? and also date and invoice number but I have that set up to repeat previous record until updated...
I have inherited a database where the structure is that everyone has a People ID which is unique and is stored in a central table. However to create a person you create their People ID in the People table (the ID is generated by autonumber) and to make the rest of the database work you have to manually type the People ID into a separate field in the form to allow you to do anything further. How can I automatically add this new number to the people ID list?
Could anyone explain me the best way to fill external HTML pages automatically with data from an Access dbase? I have spotted several commercial tools that use macro recording. Surely there must be another way using visual basic for example?
Assignment Info: Assignment ID (Primary Key) Assignment Number Criteria Number
Grades: ID (Primary Key) Student ID - Linked to [Student ID] Assignment ID Criteria Number Grade
What I would like to do is be able to link the tables in such a way that for each student entered in the Student Info table, entries are automatically entered into the grades table for each assignment criteria.
For example: Assignment 1 has criteria 1.1, 6.3, 7.2, Assignment 2 has 4.2, 3.3
When John Smith is entered in student info, the grades table is automatically updated with 5 new entries in the form:
John Smith - 1 - 1.1 - Enter Grade John Smith - 1 - 6.3 - Enter Grade John Smith - 1 - 7.2 - Enter Grade John Smith - 2 - 4.2 - Enter Grade John Smith - 2 - 3.3 - Enter Grade
That way I can have a form that automatically shows the possible criteria for each assignment on the sub form for each available student without having to type it in each time.
I would like to have a date control on a form filled in automatically with the current date when a new record is created (the date would remain as is unless changed manually). I've tried programming it in VB but, being a newbie, have not been able to come up with anything that works. I'm not even sure how to trigger an event to do it just the one time when the record is created.
I have a table called tbl_post_staff, within this table I have an id number (autonumber), user id (text) and user full name(text). Then I have a form called ec_main with a combobox linked to tbl_post_staff, but stores its value in a field called officer_is in a table called tbl_extensions. also on the ec_main form is a text box that displays current user.
What I am trying to do is; on my form ec_main; default the combobox (Called officeris) to the staff full name based on there userid that is logged in. how can I do this?
I have a mainform with a combobox and a button. What i want is when i click on the combobox and then on the button I want that the textbox in the subform automatically fill in.
The mainform name is FrmTakenInvoerenEnToewijzenAanEenMonteur,
I have 2 fields in access table. In one field i fill numbers eg (1,2,3,4,5) drop down list. In the second field I need to fill another data. When I chose one of the numbers from Field 1, I want automatically fill the second field with appropriate data.
I have a totalquery that runs fine and give me the sum for both fields I'm looking for but I can't get the outputs to fill the fields on the form. I have tried the Dcount query in the control source but that just returns an error and locks up access.
Code: SELECT [Tble-wcDelays].Causedby, Sum([Tble-wcDelays].HoursDelay) AS SumOfHoursDelay FROM [Tble-wcDelays] GROUP BY [Tble-wcDelays].Causedby, [Tble-wcDelays].LinkingID HAVING ((([Tble-wcDelays].LinkingID)=[Forms]![Frm-ePlusCent]![cleanID]));
How can I get some mainform fields' data to be the first entry in a subform? From the mainform, I would like the Head of Household name and date of birth to be carried over to the first entry in the HouseholdMembers subform.
The two forms are tied to separate tables. For each household member I need to be able to enter full legal name, date of birth and some additional pieces of information such as income and source of income.
What I'd like to avoid is making the users enter the Head of Household's name and date of birth twice. Is there a way to carry that information over to the subform?
I currently use forms for adding new pupils, and updating pupil subjects/arrangements/levels.
I also have forms to search for specific pupils, and to create lists for faculty heads showing which pupils are taking subjects within their faculty and the arrangements we expect to provide.
I use the forms to run queries, which can then output to reports for printing.
Where I am currently having an issue is the faculty field in the tbl-SubjectLevelArrangements. (If I didn't have to report to faculty heads I would just leave it out, but management will insist.)
Currently I have a form with dropdowns for adding subject, faculty, level and arrangement manually. This is acceptable for the subject, level and arrangement because they are completely interchangeable and dynamic throughout the academic year as pupils may drop down a level, or change the type of arrangement they require.
However as faculties are inextricably linked to subjects, I want to remove the possibility of human error. i.e. when a user (me) chooses either geography, history, or RE, then the faculty will always be Humanities, likewise if the user chooses French, German, or Spanish, then the faculty can only be Modern Languages etc.
I'm convinced there must be a very simple way to ensure that the faculty field prefills based on the limited keywords available in the subject field, but I just cannot figure it out.
MAIN TABLE Job_No - Looks up Job table Source - I want this to be automatically filled from the record associated with the job number from the Job table Test_Result - Looks up Result table PASS/FAIL etc Technican - Looks up technican names table
I have a table being filled everyday that contains the following:
ID EntryDate Ward_ID (linked to the Wards table) Census
At present, the data encoder manually chooses the ward via drop-down list to identify the ward, and fill the census field. Since we have 20 wards, we find this exercise eating our precious time (since we have tons more of data to enter aside from the census). I'm wondering if there's a way to automatically list all the wards in the table in a specific date, so that the encoder would just proceed in filling the census per ward.
One idea thrown was to design the table like an excel sheet (each ward has its own column). Another idea was to make individual tables per ward and make a default value for the ward_id. However I think these two are not the right directions.
I have an order form that requires me to change the current promotion we're running continually. I need to populate all rows based on the update changes of one textbox. Is this possible? Right now when I update the promotion text box, it only updates the text for row one.
