Problem - Automatically Fill A Table Field From Another Table
I have a table which contains these fields
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
Job_No 0001 0002 0003 0004 etc
Source Cust1 Cust2 Cust3 Cust1 etc
Note also that we also have different job numbers for the same customer
I know I can fill in a value on a form from a combo box looking up another table but dont know how to do this in the main table
I have 3 tables:
Student ID (Primary Key)
Assignment ID (Primary Key)
ID (Primary Key)
Student ID - Linked to [Student ID]
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.
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.
Your help would be wonderful
A first time poster here, I have searched the forumns but couldnt find what i was looking for.
I am making a simple money budgeting database.
Currently I have a table of transactions with the following fields:
TransactionID, Date, Account, Type(credit/debit), Description, Amount
I would like to know if there is a way to put an ongoing weekly/monthly etc transaction into the table e.g rent that is say every tuesday.
If i could have a way to specify a start date, what the day is and frequency of the transaction and an end date - and automatically fill out the table.
Any help here would be greatly appreciated.
Edited by: mitroberts on Mon Aug 13 3:55:14 EDT 2007.
I have a table that has 4 columns as shown below:
(*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.
Does anyone have any ideas?
I have 2 tables:-
1 Master Input Table.
1 Job Lookup table - (contains Job Number and Name)
When in-putting job details into the Master Input table i would like to enter the job number only and have the job name appear automatically without running a query, much the same as a 'vLookup' command in excel.
Is this possible?
Can anyone help?
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?
Hope this makes sense
I Have 2 Tables
I have a form enterorders2 made from the orders table. when entering data into the form i use a dlookup in the control for the customers first name field that retrieves its value when the customer phone number is entered on the form. My problem is the Orders table field Customers First Name does not fill in from the form. It sounds like i need an afterupdate event to do this but all examples i have tried do not work. Any help is appreciated.
Greetings (again). I guess I've been away from DB too long (doing more GIS than DB).
All I want to do is to take a value from a textbox on a form and update a table with the value.
I believe that I've read this before but can't find it after searching so my apologies, although I think that was related to dropdown menus but is probably similar.
I have a company that has about 300+/- branches & each of these branches is in a different location (city & state). These branches fall into 4 salesman regions (Northeast, South, MidWest & West). For example: branch 100 is in New York, NY and belongs to Salesman1 because it's in the Northeast region. The wrinkle is that there are (many) exceptions to the region 'rule'.
To save time and having to either remember or look up the exceptions I am trying to code it so that if I enter in branch 100 into the branch_number field, the office_city, office_state & salesman fields are populated with the correct info automatically.
I have been trying select case and entering all of the info on a (pardon the pun) case by case basis (which works) but I'm wondering if there is a simpler or more straightforward way.
Thanks in advance
Edited by: Nightcrawler on Tue Nov 6 9:10:35 EST 2007.
I have two fields in a table, "First Name" and "Last Name", and I want to create another field that automatically combines these two fields, and fills in the information to form a first and last name, in the same table. Is this possible? Thanks.
I am having some difficulty with using Access (aren't we all?) ;-(
I have a table with a the following fields: START_DATE, END_DATE, RENTAL_PERIOD, DAILY_COST & TOTAL_COST.
What I am tring to achieve is that when someone fills in the START_DATE, END_DATE & DAILY_COST fields (which update the table behind the form) that the form will take the END from the START and put the result into the RENTAL_PERIOD field and then multiply this by the DAILY_COST value to automatically fill in and display the TOTAL_COST field.
It must be simple but I just can't get it to work and it's stopping me from sleeping :mad: I have found myself doing Access in my dreams which is not good!!
Any tips much appreciated - Thanks
PS I have written 2 queries which will calculate this for me but dont seem to be able to action the queries from within the form (if that helps!)
I am having trouble with our receiving database. This database consists of two tables. One for vendors, which basically contains their vendor ID as well as vendor name and phone # etc. The other table is our receiving data table. When our receiving person receives product in, they log this in the receiving table including info such as date, autonumber for record, vendor etc. The problem is, when the person selects the vendor id, which is set up as a lookup field, we would like to have the vendor name pop up atuomatically within that record. I cannot for the life of me figure out why this isn't happening. The person is using a form to enter all of this data and runs a summary report at the end of the day. Our accounting dept. is requesting this info be added, but I cannot seem to figure it out.
Thanks so much for any assistance!
I have two tables, each has a "status" for a project. In the first table there is only one instance of each project name, in the second table there may be more than 1, will always have the same name but may have a different "Status" (field).
I need the Status field of the first table to be set to "Assigned" if ANY of the records in the second table pertaining to that project are set to "Assigned".
Can I do this with my table or will I need to do it with a query/form combination?
I need to base a field date in a table off of another field date in the same table to add 7 days from the date entered. I'm not sure if I can do this in a table or not though?
Field DateInspected = 02/27/06
Field DateDue = 03/06/06
I have a table named tblOrderDetails with a field called fkeyProductID. This field is the same information found in another table called Mouldings with a field called pkeyProductID. I want the field in the tblOrderDetails table to automatically be updated from the data entered on a form called Sales Quote Subform. The Sales Quote Subform is tied to a query named qryOrderDetailsExtended which is fed by the table named above called tblOrderDetails.
I currently have the fkeyProductID field set with a lookup of the following:
Row Source Tyoe is set to Table/Query
Row Source is:
SELECT DISTINCTROW Mouldings.*, Mouldings.pkeyProductID, Mouldings.strProductName FROM Mouldings LEFT JOIN tblOrderDetails ON Mouldings.pkeyProductID = tblOrderDetails.fkeyProductID;
All this does is gives me a list of ALL the Product ID's. What I need is for the single individual Product ID that matches the Item No chosen in the Sales Quote Form to automatically fill in.
I have attached my database for anyone who is interested in trying to help a "rookie" out.
Thanks in advance
I am a total newbie.
I need to know vb script or a way of using a query or a macro to add an autonumber field to a table? I have a lot of tables that get replaced monthly and need to ad unique ID field to them but want to avoid doing it manually.
Can anyone help???
I am trying to create a macro using an UPDATE SQL statement to populate one column from a table with a column values from another.
I can get the UPDATE statement to work if I just use a static value. However, when I try to nest a subquery it gives the error: "Operation must use an updateable query"
---SET PtoF.Attribute= (SELECT Attribute from Table2 WHERE Table2.ID="One")
---WHERE PtoF.ID = "One";
The above outputs the "Operation must use an updateable query" error.
--(Select ID FROM PtoF WHERE [PM = "One";)
This outputs the correct value. It is "Two".
---SET PtoF.Attribute = "Six"
---WHERE PtoF.ID = "One";
If I use this statement, it works and completes the update successfully.
The idea is to repeat the statement for ID = "One", "Two", ... etc. How can I make the nested subquery not result in an error. Or, is there a better way to bring in data dynamically from one table to another?
Here is what I want to happen but don't know how to get there:
If a record is entered into a table and get a message that it is a "duplicate" entry, I want it to automatically go to a different table. Is that possible?
Hi, I have a problem, I have a table were I list networkservices and their logical ports and I have another table were I list IPaddresses used by different machines. I'm using a multivalued lookupfield to pick which services I'm using for each Ipaddress, and thats working fine, but now I want to make the ports which is listed in another column, to automatically show in it's own column in the IPaddresses table depending on which services I'm choosing for the different IPaddresses. Is this possible?, and if it is how can I make this happen?
AUTO FILL-in - Of course, this is banal and simple - if you know how to do it. If you don't it seems impossible. Bizarre that on the whole internet so far I can't find a clear, simple example!
Table A is a list of lessons; table B a list of courses. They have various fields in common, such as "courseref", "course title", "room number", "teacher" etc
I want to enter the "courseref" in a form created for table A and fill in the other common fields in the same table (A) from table B, which is acting here as a lookup or reference table.
So, is it a combo box? What code goes where? Do I make a select query? How to link it to the key field and the associated fields. SELECTDISTINCT seems to come in somewhere, but NO SOURCE I can find explains it clearly to me (and I am not yet senile ....). Can any of you?
I have a very complex issue.
First of all there is a table called "tblParTeam" wich contain the teamname and ID of teams partissepating in a certain Tournament. The tournemant data is kept in the table tblTournament.
Each tournament contains a couple of variables: "Number of teams per tournament", "Number of Rounds" wich reside in the tblTournament.
Here is the thing. I also have a form based on the tblTournament. Each tournament had a number of partisipating teams (stored in the variable "Number of teams per tournament"). Then I have a table called tblTournamentDetail wich containt the Tournament_ID, TeamPro_ID (looks in the table "tblParTeam"), TeamContra (also looks in "tblParTeam").
When I pusch a button on my form, wich is based on the tblTournament, tblParTeams and tblTournamentDetail, I would like the X (Number) of particepating teams to be filled in randomly in the tblTournamentDetail.
Lets say I have 10 teams I want 5 to be filled in randomly in the TeamPro_ID and 5 in the TeamContra_ID.
How do I go about this?
Thanks in advance.
Ok Ive looked and looked and read a multitude of threads to no avail. Please help me on this one.
I have imported a table into Access, I had no control over the original format of the table so Im stuck with the data as it is in that original doc.
So as it sits now in access..
index mnem client name
1 310055 blah foo
2 blah2 john
3 blah4 joe
4 310099 grr12 bob
5 blah2 john
etc. * about 50,000 literally in size.
Looking at the empty cells in the 'number' column I need each of those to be filled. to end up with:
index mnem upin name
1 310055 blah foo
2 310055 blah2 john
3 310055 blah4 joe
4 310099 grr12 bob
5 310099 blah2 john
I did this once in VB/excel but it was prohibitively slow. Im positive there is a better SQL/Access solution but just cant quite get it. My best effort to date is:
DLookUp("[mnem]","upin","[index]=" & [index]-1)
criteria mnem Is Null
In an update query updating mnem.
Downside all I get is say there is a block of 4 empty spaces in a row before the next mnem comes along, only 1 cell will be filled per run of the query.
index mnem upin name
1 310055 blah foo
2 310055 blah2 john
3 blah4 joe
4 310099 grr12 bob
5 310099 blah2 john
I figure #3 is somehow looking up at #2 before its updated and taking that empty value. ..just cant figure a way around it though.
I know this has been done before but no matter how much googling and forum diving Ive done I havent found something to specifically address this.
Thanks in advance for your help
ok, here goes...
sorry if this is really simple, i just can't figure out how to explain exactly what i'm trying to do...
first of all, i have a list box populated by a table, when i click my button, i want to create a record in the table (done). then i want to display the id for that record in a text box. this will allow me to update the record, which i have also worked out. i just can't seem to figure out how to display the id from the table.
Thanks for your help
hello all, any help greatly appreciated.....
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
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.
Thanks for any help in advance.
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 have a field called CompanyName with the autofill property. (You will begin typing the name and it will be completed automatically)
In the same form, I have other fields such as company's address, phone number, etc.
This is what I need:
Once the CompanyName field is completed by autofill I need the rest of the information associate with this Company to be automatically filled out (ej address, phone number, etc) In cases when the company has a different branch, to be able to change the information with the new address ( I know this will create a new record like it would be a new company, but that's ok)
The DB basically will have clients and these clients will be receiving services from a particular company. Clients and company providing services will be linked, so I will have a form with all the clients information and then another form that will link the client with a particular company, this second form is the one I need to do the auto completion.
Thanks in advance for any suggestion.
I have a VB6 form that I want to fill with data from a field in an Access DB. I get the connection and the first field, but want the whole column to show up as the combo box items so that one can be selected, then when saved, populate and/or update another Access table. Can anyone help me cause the whole column to display instead of only the first record? TIA--Ed
Its been a long time since I did dB work - stopped back in the days of dBase IV.
Still, using Access 2007 is not that difficult - except right now. I have 2 tables; one with customer contacts, the other with project details. Heres what I want - hopefully someone can help:
Within the project table, select from a lookup into customer contacts a name, which then auto fills in the fields for phone and email in the project table. I want to do this in the table or form, but have the data deposited into the table for sure!
Any help really appreciated.
I am not entirely sure that i am in the right place in the forum but my question is concerning updating data via a form so i hope it will be ok.
I have a 'product' table linked to a 'batch received table' with a 1 to many relationship.
I wanted the employee to fill the 'batch received form' (where all information about the item received are entered on the form and a 'transaction table' subform) when an item is received instead of calling the 'product form' for each item.
Obviously, when the item received is not registered in the 'product table' a Broken join error occurs.
I was thinking of putting a code in the 'on the error event' of the 'batch received form' requesting the user to register the product first with the 'product form' coming on the screen once the error text box closed.
Once the new value entered, the form is still stuck and i can't save new data with the product unless I close and reopen the form.
Here is my error code
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim Msg As String, NewLine As String
Const ERR_BROKENJOIN = 3101
Const ERR_NULLKEY = 3058
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Item"
NewLine = Chr$(13) & Chr$(10)
Select Case DataErr
Msg = "You need to Register the product first to be able to proceed"
Response = DATA_ERRCONTINUE
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec
I have try to requery the form but it still not working.
I am just starting VBA and would be gratefull for any advice, thanks.
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...
Thanks In Advance.
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.
Any thoughts? thanks in advance.
I've created a table.
Now I need something to fill this table with 365 records where tDate starts at 1-jan-2006 and ends at...you've got it!
I've got a Query that will add a date but I don't want to edit this 365 times.
Probably very stupid simple but please help