General :: Update Name Fields From Related Table Along With Auto-number?

Jul 1, 2014

The "PersonalDetails" table is related to the "Application" table using the autonumber from table, "PersonalDetails".

The "PersonalDetails" table has "StudentID" (autonumber), "First Name" and "Last Name" fields.

The "Application" table also has "StudentID", "First Name" and "Last Name" fields.

The "Application" table is related to the "PersonalDetails" table using the "StudentID" field.

How do I make it so that the first and last names on the "Application" table are automatically updated when the StudentID is entered?

View Replies


ADVERTISEMENT

General :: Web App Displaying Fields From Another Table In Related Items

Jul 30, 2015

I have two tables to track our engineer visits, one tracks the visit as a whole and the other tracks the individual instruments the engineer worked on during that visit. This way I can track visits to customer sites separately to the visits made to an individual instrument.

Right now, I create a visit and then add Visit Lines (containing the details of the instrument visited). These instruments, or 'Visit Lines', are being displayed in the Visits Table via a related items box. Visit lines are associated with Instruments in the Instrument Table via a serial number lookup. All this works great...

However, I want to display both the serial number and the instrument description in the related items control in the Visits Table. Since the Visit Lines table only has the instrument ID lookup and not the instrument description I can't display it in the control.

I need to either:

a) Create an instrument description in the Visit Lines table as a lookup and have this automatically pull in the description based on the serial number the user selects.... which I can't figure out how to do - it's just an autocomplete.

b) Create an instrument description in Visit Lines and have a macro grab the corresponding description from the Instruments table based on the serial number input - but just for this record...

View 2 Replies View Related

Beginners Question - Auto Update Fields Based On Fields In Another Table

Nov 26, 2007

I don't have any database experience whatsoever so please go easy.
I'm guessing this kind of this is extremely simple for all of you.

I'm constructing a database of network resources and devices and I'd like to automatically update the values in one field based on the values of a field in another table.

The first table is called "IP" and the fields are called "Address", "IP Type" and "Device".
The second table is called "Devices" and contains the fields "Name", "Description", "Asset Number" and "IP".

Here's an example of the tables: (ignore the "code" tag. i've only used it to align my columns properly)

DEVICES:
NameDescriptionAssetIP
XserveFile Server107203.30.144.75
ProliantXDHCP119203.30.144.15



IP:
AddressIP TypeDevice
203.30.144.75Static
203.30.144.15Static


What I want is for the Device field in the IP table to automatically update it's values based on the values found in the Devices table. In this case, the values that should appear in the Device field in the IP table are "Xserve" and "ProliantX".

I've searched through but haven't found a complete solution, just little pieces which I'm too inexperienced to put together myself.

thank you
-Tim

View 2 Replies View Related

Tables :: How To Have 2 Auto Number Fields In One Table

Jun 4, 2013

I am having a table for invoicing and vouchers, and I need to have two fields that will have auto incremental numbers. How I can have two fields in the same table with autonumbers and how can I get one of them to start from a different value.

Eg. Record 1 - [inv No] 1 [Vno] 0005
Record 2 - [Inv No] 2 [Vno] 0006

View 14 Replies View Related

General :: Update Record ID To Another Record ID In Same Table And Update Related Records

Aug 22, 2013

I have a table called tblCompanies. When a company acquires another company, I need a method by which the acquired company's CompanyID (PK) can be updated to the new company's CompanyID (PK). I also need to be able to update all related CompanyIDs (FKs) to the new value in related tables.

In cases in which the new company does not have an existing record, there is no problem: the company name simply gets changed to the new company and the existing CompanyID is maintained. I then use an audit table and Track Changes function to keep track of the company name data and a union query to keep the old names in the selection lists.

The problem is when both companies already have existing records in the table.

So, let's say I have records for Company A and Company B. Company A merges with Company B and Company B is now the main record. What is the best, simplest and easiest way to update the CompanyID (PK) from A to B and change the CompanyID (FK) to the new value in all related tables?

I am envisioning a pop-up form that directs the user to select the new company and then an update query happens behind the scenes... but exactly how does the criteria for the update query get selected and how do all the related tables get updated? My vba skills are pretty basic, will I need extensive coding to do something like this?

View 6 Replies View Related

Auto-fill With Related Fields?

Feb 15, 2006

Is it possible to connect data so that when one field of a record is entered the rest of the fields associated with that first field of the record automatically fill in?

Example: If I were to enter a new contact record name in the name field I want the corresponding fields of address, city, state, zip, phone....etc. to fill in automatically.

View 4 Replies View Related

Appending Records With A Desired Auto Number In A Table With Auto Number

Oct 26, 2005

Here is my issue. In a table with an Auto Number index some records have been deleted. I have been able to recreate them along with their original auto number. The problem is that I do not know how to append these records forcing the original auto number. I have tried changing the auto number field to a number field in the table, this works except I cannot change it back to auto number.

I am sure I’m not the first with this question or issue. I did search through a couple hundred entries about auto number before I posted this question.

Jim

View 7 Replies View Related

General :: Getting Invoice Number Field To Auto Generate Next Number

Jun 2, 2014

I'm trying to get an invoice number field to auto generate the next number, keeping the format as "00000"...this is what I have, which gets the next number but drops the leading 0

Code:

Private Sub Customer_AfterUpdate()
If Len(Me.[InvoiceNumber] & vbNullString) = 0 Then
Me.[InvoiceNumber] = (DMax("[InvoiceNumber]", "[tblInvoiceNumber]") + 1)
DoCmd.RunCommand acCmdSaveRecord
End If
End Sub

invoice numbers are 04024, 04025 etc...how I keep the formatiing?

View 5 Replies View Related

General :: Auto Number PK Change Starting Number?

Dec 18, 2013

I have a table with an auto number PK. This table will contain orders. I'd like to use the PK from this table as the Invoice number on the invoice. I'd like to have it start at a number other than "1" just because it looks better on an invoice. I don't know how to do this. I looked at the table design to see if there were options available to me there but couldn't find anything. Is it possible? (I do not know how to use code.)

View 7 Replies View Related

Auto Creating Record In One Table For Groups Of Related Records? + Stock Levels

Mar 23, 2008

Hey guys,

OK, your gonna have to bare with me a little bit as its hard to explain and if any VB is given please add a few annotations as I have to explain everything i do in a report (doest have to be too detailed, just to make the code understandable :) ) and if it needs better clarification feel free to ask :), but basically, I have the following relationship set up:

http://img512.imageshack.us/img512/3246/relationnshipswd9.jpg

At the moment, because of the way it is set up, I cannot create a record in the transactions table unless an income record is given for it (because tb_income (one) to tbl_transactions (many)) but the way I want to work is as follows:

If you have a look at the tables tbl_transactions and tbl_income and their link. The way I want the system to work is when a new transaction is made, a new income record in the "tbl_income" table would be made with the date (in tbl_income) being the date at that particular time and all transactions created on the same date would all go in the subdatasheet for that one record created for that date; and if another transaction is made on an alternate date (say 00:00am of the next day) another income record would be created automatically with the date being of that particular day etc.


My other problem im facing is that everytime there is a transaction created, I want the stock level(s) field of products in that particular transaction to be decreased by the quantity purchased of that product but i have no idea how to do so

Any help?

Thanks a lot in advance!

Daniel

View 4 Replies View Related

General :: Group By Query And Adding Checkbox Fields To Update A Table

Oct 24, 2014

I have a invoice system I have created in Access and it did used to work perfectly and then maybe an update Who knows stopped the system working.

I have an customer order screen that has customer details and then a subform within the same form this takes a total of items & costs for this order and then it entered it into the customers table from the order_Details table using me.Order_total = Order_Total.

I know this is bad design but I store it because once the order is places I need the total to be static because the invoice has been sent and so if someone changes the order then the total owed doesn't change.

I then have a reconcile form which is on a datasheet form straight from the tab;e so it is editable which has a checkbox that then once ticked copies the total from the Order_Total in the table to the reconciled field in the same table and then I use a report to show who owes me still.

So I have made a Select query from the Orders table & Order_Details table and used a Group by Order number (Which is the link between the 2 tables) this shows correctly but now not editable because of the rules So I am trying to add an editable checkbox on the same form.

I tried to use a dlookup makes all of the boxes either ticked or not. I've been looking at Recordsets but I'm unsure if this is what I need or not really

So really I just need to see if I should be putting the Total from the Subform into the mainform and then entering it into the table (Like previously) using a calculated unbound field and then using the Afterupdate event to insert the data into the table. but for some reason it just wont work.

I can get the OnClick to work but then as soon as i go to the next record for some reason it then resorts to 0.00 but then the table shows correctly which ever record the form is working on.

Or should I be using the new query based idea to create the reconcile form and if this is the best way I just don't now where to start on how to get a multiple table query that I can then add a reconciled checkbox which then copies the Order_Total from the table to the Paymet_Recieved field.

View 13 Replies View Related

General :: How To Update A Table At Client Site To Change Couple Of Fields

Sep 23, 2013

I need to update a table at a client's site to change a couple of fields, and would like to be able to send them something that they can run on their end to perform an insert query.

I cant update the tables via RDP etc. because the database needs to be in Access '97 format, and they all run access 2003.

I also cannot get them to send me the file for updates since it needs to be online basically 24/7.

Is there any way for this to happen?

View 5 Replies View Related

Update A Table In All Related Forms And Queries

Feb 15, 2007

I have created a database for storing, query and deleting data. Now the user wants to delete a row from a table and create two new rows. In my opinion this will need lots of work in order to replace all related queries forms and reports in the form. Is there any whay to do my job more easy in modifing a table and all related items be modified by themself? I still do not have any data in the table but if I did will I loose those data?
Thanks.

View 2 Replies View Related

Related Table Fields

Mar 13, 2008

Greetings to everyone,

I'm building a database which will generate work orders, and those have a opening date and closing date, besides that it has a work order number that is of the following format:

YYYY/ NNN (being the NNN the work order number in that year)

to create the number of the work order I'm trying to fill the WOYear field
with the year of the WODate field...but I'm not being able to do it...
How should I do it?

Thanks.

View 5 Replies View Related

General :: Auto Number Other Than Just 1

Mar 14, 2013

I am creating a file and I am not stuck on the last part. I have to keep track of which numbers of a particular item is sold due to serial number tracking. Items are bundled in sets of 50 so I need to form to auto populate the next set of numbers (by 50) when I create a new line.

I found how to change my start number to 1751 but I can not find how to have a Begining number of 1751 Ending number 1800 and have the next line auto populate begin $ 1801 end # 1850. I have a feeling it will not be the Auto Number option and I am sure there is another way but I just can't find it.

View 4 Replies View Related

Auto Update Fields

Feb 17, 2006

Could someone please shed some light on any methods on a way to update fields automatically in different tables but with the same field name when data is entered into one of the fields in any table?

Thanks

View 3 Replies View Related

How To Reset Count Of Auto Number Fields After Testing

Dec 12, 2012

I would like to know, how do i reset the count of my auto number fields after testing? is it also possible to specify which number the auto number should start counting from?

View 3 Replies View Related

General :: Change Auto Number

Jan 27, 2015

before ACCESS Excel was used to create orders etc,we are up to order number 16223 now I have access and the auto number will start at 1.how can I get the auto number to start at 16223.

View 11 Replies View Related

General :: Auto Number Reset After Import

Apr 18, 2013

I have a BE database that we have been using for over a year. I imported data from an external database into this database. Now I've found that my Autonumber has reset to a very low number -- that was imported from my external database. Is there a way to change this autonumber BACK to what it was and increment up? The lowest autonumber in this database is 275944, so I don't have to worry about duplicates in the near future. I would like it reset to 457230, or at least higher than that number.

View 4 Replies View Related

Forms :: Calculating Fields And Auto Update

Jun 15, 2013

In this table I have two fields Seats Available and Seats Remaining

When I register a student to a Course in the Courses table I would like the corresponding Seats Remaining field for that course to decrease automatically.

I can, of course, run a report or a query to see how many are enrolled in the course and then manually update the Seats Remaining field (which is what I currently do), but I would like this process to be automated each time I register a new student.

View 4 Replies View Related

Queries :: Adding Fields In A Table That Are Related To Other Tables

May 13, 2013

I am a beginner in access and I want to populate a new field (REPUN_1 which corresponds to SEGMT_ID in the other table) in my table and this field is coming from another table. The values of the fields need to correspond to the row of my actual table (JMTable) having the same CO_ID, MOVEPLANCD and TTY_NO as the table I want to extract the values from (DI_Treaty_Crosswalk).

Here is my query:

UPDATE JMTable SET [REPUN_1] = DI_Treaty_Crosswalk.SEGMT_ID WHERE JMTable.TTY_NO = DI_Treaty_Crosswalk.TTY_NO AND JMTable.CO_ID = DI_Treaty_Crosswalk.CO_ID AND JMTable.MOVEPLANCD = DI_Treaty_Crosswalk.MOVE_PLAN_CD

and it doesn't work since access ask me to enter a parameter value.

View 1 Replies View Related

Updating Form After Adding Fields To Its Related Table

Apr 25, 2012

I decide to add a new field to its related table. I always wait to create the form until I think my table is complete, but sometimes I just end up needing to add more info. Is there a quick way to update the form to include my new fields?

View 7 Replies View Related

Modules & VBA :: Check Policy Number In Access Table And Populate Related Data In Excel Sheet

Aug 14, 2014

See attached the Workbook. I need to check the policy Numbers in Column A of all the sheets in the attached workbook if its present in Access Table. If yes then write the corresponding ScanDate and BatchNo from Access table to columns I and J of all the sheets. I need to write VBA code to perform it.

In the attached workook, only Sheet1 contains the data but in actual there will be data in 5 sheets in the workbook.

View 1 Replies View Related

General :: Create A New Record / Also Generating A New Auto Number

Dec 3, 2014

When I create a new record I am also generating a new Auto Number. This is so I have a sure fire way of returning the records that I want to return. I have read where it is a known issue that when using the Compact and Repair it can reset the Auto Number to a lower number and generate a duplicate Auto Number. That is the problem that I have at this time. I have tried using the Allen code but it doesn't seem to worked on linked tables. I use linked tables because I have multiple users who can access this system at any given point in time.how to have the Auto Number field select a number that is unique

View 12 Replies View Related

General :: Auto Generate Report Number When Required

Jul 1, 2013

We have a large split database with 3 users on a single site. We will soon be increasing this to 6 users across 2 sites.

One of the fields in the main table is "report number" where a line is given a unique number. Not every line is given this number, as it depends on the finished status of the product.

The numbers are currently hand written onto a sheet as they are used, which obviously avoids duplicates. This however will be near on impossible once the database is shared across 2 sites.

the format of the number is 00/0000 where the first two digits are the year, and the last four increase sequentially.

Is there a reasonably easy way to incorporate this into the database itself, where a number can be generated as required, as apposed to every time?

View 14 Replies View Related

General :: Creating Auto-Sequential Order Number

May 6, 2013

I am trying to create an auto-sequential order number

Example : AB000112 - AB is standard prefix, ''0001'' is the first invoice number, ''12'' is the year 2012.

so the next order number should read AB000212 and the next AB000312

Out of this, I have to issue several debit notes relating to the same order number when the payments are due from customers over a period of time, this will be

001AB000112
002AB000112
003AB000112

Can such numbers be the primary key?

View 4 Replies View Related







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