I am coding in VBA and use Microsoft Access as the front end. My question is : I have a table(tblStudent) which has two fields Student Name and Gender.
I want to add multiple records in this table through a form at a button click....(Say I want to add least 5 students at a time) So, I have 5 textboxes for student name, 5 textboxes for Gender and an Add button. How do I so this programmatically?
I've got a table tblPatienten, a table tblRechnung and a form frmRechnung.
The primary key in tblPatienten is KundenNummer, the primary key in tblRechnung is RechnungsNummer. The relationship between tblPatienten and tblRechnung is one to many.
Now, every patient (stored in tblPatienten) is allowed to make multiple orders (stored in tblRechnung).
How can I assign each new order entry in frmRechnung to an existing patient in tblPatienten?
I have a very simple database that keeps a track of line performance figures, i have attached a part of it as an example:
In my lines table are 8 production lines
in my log table i need to keep a track of the data,
what i would like to happen:
i need a form that when i open it all the production lines i have are displayed and next to each line are 2 data entry fields so i can enter a performance and an output figure for each line in one go, so in this instance i would like 8 lines and 2 entries per line.
at the moment i have to do each line individually.
i would also like this to grow should i add more lines
I have been trying to figure this one out for a while and I can't seem to find anything helpful. I'm going to present a simplified version of my problem to help keep it simple.
I have an "Activities" table that contains fields: ActivityID ActivityName
I have a table of 7 Risk areas which contains fields: RiskID RiskName
What I need to do is to create a form that will allow the user to select an activity and then, for each of the 7 risk areas, input a risk measurement.
This involves the creation of a 3rd table to collect the user input which would contain fields:
MeasurementID RiskMeasurement ActivityID RiskID
I can do a dropdown of the Activities and have a subform that links to it but the problem I'm having is trying to get that subform to list the 7 Risk Areas AND collect the RiskMeasurement information from the user. How can I have a listing of these 7 areas from one table that are tied to inputs that go into another table? This seems so basic and simple yet I have tried numerous things and all have failed.
I am currently working on a form that involves several linked tables. Since our personnel got so used to a certain form format, I have scanned the form and imported into an access form as a background and have laid corresponding fields on top.(I have attached a snapshot of the form). This works great for a view only form, but I can't seem to add/edit records using this form because of the table relationship. I have done some research, and found that one way to get around this problem is to use subforms, but that would kill the form format. Another way I have seen (which I am not sure how to do) is to have a temporary field and once you hit the submit button it will then fill out the tables.
I have a Form that is used for creating entries to fill a table. On the same Form there is also a list that shows everything in the table and a double click on the list will populate the Form with that data. The problem is that "Save Record" command button will not update the table because it thinks it is creating a new record.
The code to populate the form from the list is
Private Sub EmployeeEntryList_DblClick(Cancel As Integer) Me![Number] = EmployeeEntryList.Column(0) Me![EmployeeNumber] = EmployeeEntryList.Column(1) Me![FirstName] = EmployeeEntryList.Column(2) Me![MiddleInitial] = EmployeeEntryList.Column(3) Me![LastName] = EmployeeEntryList.Column(4) Me![Supervisor] = EmployeeEntryList.Column(5) Me![Group] = EmployeeEntryList.Column(6) Me![Shift] = EmployeeEntryList.Column(7) End Sub
how I can make it populate the form if an entry is double clicked and then be able to save a modified version of that record over top the original.
Okay, for simplicity's sake, I have a data entry form.
It is bound to tableData.
Inputs are:
ProductID Customer Name
ProductID is a combo box.
There is another table called tableProduct. In this table, is ProductID and ProductName.
For convenience sake, when a user chooses a ProductID from the combo box, I want it to lookup that ID from tableProduct. However, how can I do this when everything is already bound to tableData?
Okay, for simplicity's sake, I have a data entry form.
It is bound to tableData.
Inputs are:
ProductID Customer Name
ProductID is a combo box on the form.
There is another table called tableProduct. In this table, is ProductID and ProductName.
For convenience sake, when a user chooses a ProductID from the combo box, I want a separate textbox to lookup that ID from tableProduct and display the ProductName.
In my database I have 4 tables for a small painting and decorating business.
These are: tblCustomers, tblOrders, tblStock, tblPayments.
I have a form based on tblOrders to add new orders when they come in from customers.
In this form there are 3 important fields: "CustomerID" (Lookup to tblCustomers), "StockID" (Lookup to tblStock) and Quantity (Entered by user).
However, I want a calculated field that will be worked out automatically when the user is entering these details. The calculated field should go to tblStock and grab the "Cost" field.
However the main problem is specifying what price it will grab from the table. I want it to get the "Cost" where "StockID" = (the stock ID selected in the lookup). I then want to multiply the price by the quantity which the user has entered.
The control source should be worded something like: = ([tblStock]![Cost] where [StockID] = StockID)*Quantity
but that isn't valid.
Could anyone point me to a piece of VBA or some way to get around my problem? Many thanks for any help, Stephen
Trying to create a simple data entry form to get information and populate fields from another table. i.e. type in info in one field and the other fields are displayed in the form with information from another table.
I have a simple data entry form based on a table. However I have a few fields that I do a lookup in a field on the form from a query, and yes I know I should not have a lookup in the control source however, this is the way that I will be doing it on this occasion.
=DLookUp("[Salary]","[Salary Query]")
How I get the value from this unbound field to enter into the actual field in the table. Do I bring the actual field into the form and hide, and do some sort of after update, as I have tried and it does not work.
I have called the unbound field with lookup "Salary Level Base" and the actual field in the table is "Salary Base".
I Work for an NHS organization. A department here orders prescriptions and sends them out to Gp Surgeries. I have been tasked in designing a database to make this easier for them.
Prescriptions arrive on a Pallet. Each pallet has 100 Boxes of prescriptions on it. Each box Contains 2000 Individual prescriptions. I have a table which contains the following:
Box Number Serial Start Number Serial End Number
When the user enters the first box number and the serial start number, i need it to calculate the serial end number and insert another 99 rows into the table and also calculate the serial start and end numbers for these other 99 records.
I am creating a form for data entry into my main table. It shows all the fields including a name, date, id number etc.
I am trying to put a subform on the form containing a query. The query has two filter parameters fed from two unbound boxes on the main form (date and name). Their purpose is to filter records on the query to just show those for one person on one day. It also calculates a duration based on a start and end time. The query results are ok.
I can't get the query to display properly in the subform.
Subform Properties/data/source object is set to the query name. I'm told i have to link the master fields and child fields but when I try I get the error 'can't build a link between unbound forms'. I thought all that was necessary to bind forms was for there to be a common field between the form and query? Both contain fields from the main table including date, name, start time and end time etc.
What I am trying to do is create a data entry form to an "order table" using 2 cascading combo boxes. I have created a data entry form based on a query. I can't get the cascading combo boxes to work properly.Here is the code:
Private Sub Combo0_AfterUpdate() Combo2.RowSource = "SELECT L2_ID,L4_Element_name,L5_Category FROM qry_ord WHERE L3_ID = Combo0.Value;" Combo2.DefaultValue = [Combo2].[ItemData](0) Command4.SetFocus End Sub
I am trying to create a movie database that uses a table and data entry/view form for films (title, year, director, and actors, etc), and another table that uses a data entry/ record view form for each actor including bio information and films that have appeared in.
The problem I am trying to solve is how I can relate the two tables by entering the film/actor information on the movie form, and have each film an actor has appeared in show up as a list on the actor form. Every time an actor’s name is added to the film table, I want the title of that film to be added to the list of films the actor has appeared in when viewing their bio form.
If I have a single field for “actors” on the film form, it creates an inexact match for relational purposes, due to multiple names being listed. If I use multiple fields (“actor 1, actor 2, actor 3”, etc) I have both the relationship problem and the inevitability that I will not have enough fields on the form for the data entry person to add all actors.
Is there a way to create a relationship between the tables using an inexact match? Is there some way to have a combo or list box for each actor nested in a single field? Would the tables still relate correctly?
Any help you can provide would be greatly appreciated.
Basically a student is allowed to be late three times before they get detention.
I want to record
1. Student Name 2. Late Date (a maximum of three late dates need to be entered) 3. What time they arrived on the corresponding date 4. Whether they have a note or note 6. Their reason for being late. 7. A checkbox for detention.
It would be great if someone could help me with this, I understand it needs to go into seperate tables but I run into difficulty when I want to record the dates.
For example I created 3 tables, LateDate1, LateDate2 and LateDate3 all with the relevant info (time, note reason) but I think there must be a clearer and simpler way to do this?!
I have three tables: Vehicles; Vehicle Reallocated; and Vehicles Retired. I have a form that runs a query to find all the info in the Vehicles tbl that is not "Retired", not visible in the form. I then have the option to toggle to a Reallocated or Retired form. When i toggle to the reallocated form, i have the like fields in that table (ie Van #, Vin, Make etc) pulling the info from the hidden subform with the vehicle query, so i do not need to fill in repeat data. However, when i add a reallocated date and the new clinic that vehicle is for, i get the record ID for the vehicle reallocated table as expected, but when i save none of the data moved over from the query saves in the record?
How to get all the data on the reallocated form to save?
Hi I have a form and sub form, the sub form is populated from a query and has a column of checkboxes. Basically these checkboxes get checked as part of the form, but only a maximum of five are allowed to be checked. Is there a way of achieving this? Cheers Dan
The below isn't the actual use but it is easier to explain like this...
I have a system set up on access and I have a front page (form) where I can enter the reference number (1141#2) of a building (YELDR for example).
This then fills in information in the other fields on the front page. For example address, number of doors and entry codes.
My problem is that this only displays one door and one entry code. I need it to display all doors and all entry codes so that I can just type in my reference number and have something that looks like the below but for all of them. It works fine at the moment but only for one of the results when there can be up to 10.
Reference No. YEDJR <---------------- I ENTER THIS
Address - high Street - London - UK <-------------- All of these fields are populated using the query
Doors - Green - front <-------------- All of these fields are populated using the query - Red - side <-------------- All of these fields are populated using the query - Orange - back <-------------- All of these fields are populated using the query
Codes - 10111 <-------------- All of these fields are populated using the query - 10221 <-------------- All of these fields are populated using the query - 10256 <-------------- All of these fields are populated using the query
I know this is probably nowhere near enough information but is this possible? I don't know SQL so i would have to use the designer.
This is the SQL which I have at the moment though (I didn't write it i just copied this from design view on the query)
Code:
SELECT [Site Info Data].[1141 #2], [Site Info Data].[1141 #2], [Site Info Data].[Site 2 Name], [Site Info Data].freq, [Site Info Data].status, [Site Info Data].[Site 2 Owner], [Site Info Data].[NGR #2], [Site Info Data].[Address #2], [Site Info Data].[Postcode S2], [Site Info Data].[Dish size #2], [Site Info Data].[Dish height #2], [Site Info Data].[Dish Bearing #2], [Site Info Data].RCLO FROM [Site Info Data] WHERE ((([Site Info Data].[1141 #2]) Like [Forms]![Front Page]![txt1141] And ([Site Info Data].[1141 #2]) Like [Forms]![Front Page]![txt1141] And ([Site Info Data].[1141 #2]) Like [Forms]![Front Page]![txt1141]));
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 have a Comments field on a form. I would like to set it up so that multiple comments can be added and logged at different times by different users. I have attached a picture of what I am looking to do.
I have a form with a "WorkerID" field. Once the worker enters his ID once, I want the same ID to show up for all the subsequent records so that he doesn't have to type it over and over again. Is there any way to do this? Sorry if I sound totally new to this (I am)!
I am trying to customize one of my query table, so that it shows the latest review date of an employee (with multiple entries). I have gotten the SQL statement to work so that it shows the latest employee review date which is greater than the current date. But if the employee has 2 records after the current date and I want it to show the latest entry of the 2. I don't know to put add a sub-Select statement or whether to add another criteria in the Where criteria.
e.g. Current Date = May 25, 2005If Employee A's next review date is set for June 1, 2005 but has already been reviewed (the next review date is May 31, 2006). The query will show the June 1, 2005 and not the latest entry of May 31, 2006.
Because there are 2 review dates that are after the current date. What should I do to make only the latest entry appear?
Below is the my VBA code so far, which produces the above result.
SELECT tblEmp.fname, tblEmp.lname, tblEmpWorkHistory.[current store], tblEmp.position, tblEmpSalaryHistory.salary, tblEmpSalaryHistory.next_review_date FROM (tblEmp INNER JOIN tblEmpSalaryHistory ON tblEmp.ssn=tblEmpSalaryHistory.ssn) LEFT JOIN tblEmpWorkHistory ON tblEmp.ssn=tblEmpWorkHistory.ssn WHERE (((tblEmpSalaryHistory.next_review_date)>Now() And (tblEmpSalaryHistory.next_review_date)<=[Please enter the Next Review Date])) ORDER BY tblEmpSalaryHistory.next_review_date;
My brain is stuck and can't figure it out. I hope any fresh mind could help me out. Thanks in advance.
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 am having an issue with a database I'm creating. It is a root cause analysis database. One of the fields is Target_Date. I need to set up queries that alert us if the target date is coming up, (in a week or so), or if it is past due. The issue is that some target dates are simply, "Next Run", which is to say that the corrective action must happen on the next run of the particular item which, at that point, has not been scheduled.
In order to do the above query, the dates must be formatted as Date/Time as <date() will bring up all dates regardless of whether they are in fact less than today if they are in text format. However, this restricts the ability to put in the target_date of "Next Run". Next Run will be required for another query in which we will specify the products coming up and it will let us know which "Next Run" items associated with those products we will do.
Essentially, I have taken the long route to ask, is it possible to create a field format that measures dates as dates and text as text?
