Problem - Automatically Fill A Table Field From Another Table

I have a table which contains these fields

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

JOB/SOURCE 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

Any ideas?

Paul

View Replies


ADVERTISEMENT

Automatically Fill A Table From Data In Other Tables

I have 3 tables:

Student Info:
Student ID (Primary Key)
Name etc.

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.

Your help would be wonderful

View Replies View Related

How To Fill A Table With Weekly/monthly Data Automatically - Version: 2000 (9.0)

Hi All



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.



Cheers

Edited by: mitroberts on Mon Aug 13 3:55:14 EDT 2007.

View Replies View Related

Automatically Fill In Field

Hi everybody,

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?

-Chris

View Replies View Related

Auto Fill On Table Field

Please help

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?

View Replies View Related

Automatically Fill Field In A Form

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

Wendy

View Replies View Related

Dlookup Does Not Fill Table Field From Form - Version: 2007 (12.0)

I Have 2 Tables
Customers
Orders

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.

Thanks,

Lance

View Replies View Related

Fill In Field In Table From Value In Textbox On A Form - Version: 2000 (9.0)

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.


TIA,

AvgJoe

View Replies View Related

Enter Data Into One Field & Fill Others Automatically - Version: 2003 (11.0)

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.

View Replies View Related

Combine Two Fields In A Table To Auto Fill Another Field In Same - Version: 2003 (11.0)

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.

Hawk

View Replies View Related

How To Automatically Update A Table Field From A Form?

hi there
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
Mike

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!)

View Replies View Related

Would Like Field To Automatically Pull Data From Other Table

Hello Everyone,

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!
Amy (monet1369);)

View Replies View Related

Automatically Set A Field Based On A Field In Another Table

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?

Thanks,

View Replies View Related

Add 7 Days Automatically In A Field In A Table - Version: 2002 (10.0) XP

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?

I.e.
Quote:
Field DateInspected = 02/27/06

Field DateDue = 03/06/06

View Replies View Related

Forcing A Table Field To Update Automatically - Version: 2003 (11.0)

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

View Replies View Related

Automatically Adding An Autonumber Field To Table - Version: 2003 (11.0)

Hi

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???

Thanks

Ranger D

View Replies View Related

Update Macro To Fill Table Contents From Another Table - Version: 2003 (11.0)

Hi.

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"

Statement:

---UPDATE PtoF
---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".

---UPDATE PtoF
---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?

Thanks!

View Replies View Related

Table Entry Automatically Go To Different Table If Duplicate - Version: 2003 (11.0)

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?

View Replies View Related

How To Make Values In One Table Automatically Show In Another Table?

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?

View Replies View Related

How To Auto Fill Table A From Table B

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?

View Replies View Related

Fill Table Random

Hello,

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.

View Replies View Related

Fill Down Table In Access

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.

i.e.
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

View Replies View Related

Fill A Text Box With A Table Value

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
*j

View Replies View Related

Automatically Fill Fields

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

Harry

PS I'm only a novice so I don't know anything about VBA etc, but I understand excel type formula

View Replies View Related

Automatically Fill Fields

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.
MAE:

View Replies View Related

Automatically Fill Fields

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.

View Replies View Related

Fill Out Different Fields Automatically

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.

View Replies View Related

Use Access Table To Fill VB Combo Box

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

View Replies View Related

Auto Fill In Data From One Table To The Other

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.

View Replies View Related

Fill A Primary Key From A Linked Table

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
Case ERR_BROKENJOIN
Msg = "You need to Register the product first to be able to proceed"
MsgBox Msg
Response = DATA_ERRCONTINUE
End Select


DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.GoToRecord , , acNewRec

End Sub

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.

View Replies View Related

Automatically Fill In The Values With Search

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.

View Replies View Related

New User - Automatically Fill Fields

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.

Warren

View Replies View Related

Automatically Fill A Date In A Record

I've created a table.
Id= autonumber
tDate=date

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

View Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved