1 Form, 2 Tables, 1 Field To Multiple Fields

Jul 1, 2005

Please be kind, i have little VB Knowledge, and wish to expand my learning on this topic.

I have a form that updates information on one table, and has a subform displaying info from another table.

the subform is filtered, and only shows data from what is specified from the filter of the main form.

If I update information on the main form for instance,
field 1, (the data on the subform has the same data so there is the relation), how do i update the subform by only updating the mainform? can this be done through some VB or something?

if you folks out there can give a Smidget of info on which VB codes i can use i can pretty much figure it out.

like Docmd.write something?

View Replies


ADVERTISEMENT

Tables :: Update Single Field From Multiple Fields

Nov 6, 2014

I wrote a database several years ago and recently pulled it out to give to a friend. The problem is, back then (not knowing better) I set the Employee table up with as a single field "NAME". Now, in order to make it effective, I need the Employee's name in four (4) parts (First, Middle, Last, Suffix).

I have several queries based on the "NAME" field and and would like to avoid changing all of them. I have a simple form "frmUpdateEmployees" that populates the Employee table and Name field. I was hoping to change the form and/or add a query that would be easier and more simple.

Table: Employee
Field: Name
Form: frmUpdateEmployees
Queries: 16 that depend on the table and field above.

View 5 Replies View Related

General :: Auto Fill Multiple Fields In A Form From 2 Tables

Apr 3, 2015

I have two tables, "Summary" and 'POC Information". In the "POC Information Table I have all my Contacts Information (Name, Title, Phone, Email, etc...) and I am trying to assign 2 POCs to each of my multiple projects located in the "Summary" Table. I am using a Form called "JCIDS Tracker Input Form" as the link. So far I am able to assign one POC by a combo box that lists "Full Name", then it autofills the other information...Phone Number, Email, etc... The problem come into being when I want to assign a second POC to the same Project...I can assign a name, but it won't correctly autofill the rest of the information...it just autofills in the information from the first POC that was selected.

View 8 Replies View Related

Forms :: Unable To Enter New Data In Form With Fields From Multiple Tables

Jan 6, 2014

I had an existing database with 2329 records entered into it. All of the fields (220ish) were all in one table. Myself and my co-workers wanted to rebuild the database without losing the data. We wrote queries to transfer the data from the original database to the new database and split the data from the original 1 giant table to 9 smaller tables.

The transfer of data worked so I went to start making forms. When I went to add fields from different tables I had to built a relationship, which I did. All of the data that transferred over from the new database is in the form (now multiple forms linked by button) but I can't add new information. I get an error stating, "You cannot add or change a record because a related record is required in table ..." and the table referenced keeps changing.

View 2 Replies View Related

Forms :: Create A Form Using Combo Box To Populate Multiple Fields And Tables?

May 26, 2013

I'm trying to create a form using a combo box to populate multiple fields and tables.

I've created a text field to display the added information using this format:

=Comboboxname.Column(x)

in the text box control source field, and this works for display purposes.However, I need it to populate this data into a field on a table.

For example:

My combo box looks up data that has 2 columns, Part Number and Description.

The control source for the combo box is "Part Number". And that populates the part number in the "Main" table no problem.

The text box I created using the above format in the control source populates the field in the form, but not the "Main" table.

Is there a way for the other (description) field to also populate the "Main" table as well?

View 5 Replies View Related

Search Multiple Tables From One Form Field

Feb 18, 2006

Hello,

Stuck on a problem and was wondering if anybody has a possible solution. I currently have the beginnings of a database setup that uses a search field on a form to find a reference listing using a part number input by the user. It finds the part number, and then displays all the information about it on another part of the form. My question/problem is, I now want to expand this database to multiple specialties, all with different parts in their respective listings, so they would have different tables. The tables would be the same format, just different information. No one table would ever contain the same info.

Q: How would I set up a form to search for a unique part number over multiple tables, and return that parts info?

It works great for one table, but I am stuck on the multiple tables.

Thanks in advance!

View 5 Replies View Related

Adding A Field To A Form Using Multiple Tables (And A Combo Box)

Jun 15, 2006

Hello!

It's been a while since I've asked a question here, but I can't seem to figure this problem out.

I am setting up a form using data from 4 different tables, all related, and I can't figure out how to add fields that I didn't initially add during the wizard set-up. I used the wizard because it's easier for me to do that and then go in and make the changes I want to make.


When I get data from just one table, all of the available fields from that table are in the "Field List" no matter what fields I chose to include in the wizard process. That's good. But when I get data from more than one table, only the fields I initially chose in the Wizard process are showing up in the field list. I can't figure out how to add a new and different field from one of those tables.

The way I "solved" this problem the first time was to start over, creating my form from scratch--but now that I've done a lot of work, it just occurred to me that I will need to add 2 more fields. (I actually don't even have those fields in a table yet, but I will add them later.) So, this will be an ongoing problem. Since this form will get much use, by several people, I don't want to have to re-create it every time I want to change something.

So, is this possible? How can I add another field?

Also, while I am at it... A combo box was working beautifully to look up specific records when I was gathering information from only one table. It isn't working anymore.

Thanks in advance!

-Siena

View 8 Replies View Related

Modules & VBA :: SQL - Select Multiple Fields From Multiple Unrelated Tables

Oct 28, 2013

A small issue I was wondering of for a few day . Is it possible in SQL query to SELECT multiple fields from multiple tables ? Example for the question is

Code:

dim my_var as String
my_var = "SELECT Emp_FName , Emp_LName , Emp_Adress " _
& " FROM Table1 " _
& " AND Emp_Date_Of_Payment , Emp_Sum_Of_Payment " _
& "FROM Table2 " _
& " WHERE Emp_ID = 3 "

Is this code actually valid in SQL gramatics , and is it usable if passed to a Recordset variable ( rs = CurrentDB.OpenRecordset(my_var) ) ? Just FYI - The two tables are not related and I want to keep them that way (If possible relate their records just via SQL/Vba )

View 7 Replies View Related

Multiple Fields Of Multiple Tables To One Table Query Or Report

Apr 12, 2013

I have 10 tables, 30+ fields on each table (every table has the same 'account number' field). I only need from 5 - 20 fields from each table. How do I get the certain fields from each table and put them in a table, query or report?

View 1 Replies View Related

Tables :: Updating Fields In Multiple Tables Without Onclick Event

Oct 23, 2013

I am working on a database which has two tables used as part of a registration and login process.

I would like a couple of fields from table one to automatically update in table two, once the fields in table one are populated without using an 'on click' event.

The reason I would prefer not to use an onclick is because the completion of the form used to generate the users table does not require any buttons for the data to save.

View 1 Replies View Related

Update Fields In Multiple Tables

Jun 27, 2005

I am trying to setup a database for vehicle stock control.
Im not sure if I have gone about this the right way as I am new to this but thus far it is working correctly except for one annoying problem.
The database consists of so far

tblIAWVehicleDetails (Primary key "IAWvehicleID" autonumber)
tblIAWSellers (Primary key autonumber)
tblIAWBuyers (Primary key autonumber)
tblIAWStates (Primary key autonumber)
tblIAWStatus (Primary key autonumber)
tblIAWSafetyDetails (Primary key autonumber)
tblIAWSold (Primary key autonumber)

In the Vehicle Details table a stock number has to be manually added as this will be used for new stock as well as current stock (Number range from 100 - whatever) "IAWVehicleNo".
This table contains all relevent data with reguards to make, model, bodytype, color etc.
The sellers table contains the details of the seller Name, address, Phone, LicenceNo etc.
The Buyers table contains employee names.
The States table contains all Australian states.
The Status table contains current vehicle status Retail, Wholesale, Wrecking etc.
The Sold table contains the details of the person who purchased the vehicle if sold.
The Safety details table contains a safety checklist for pre purchase inspections eg: Headlights yes/no checkbox, Headlight text field for any extra info.
It also has a field for a safety Certificate No once the vehicle is checked and recieves a Safety cert.
The forms are setup as
frmIAWVehicleDetails
frmIAWSellers Subform
frmIAWVehicleSafetyDetails
frmIAWVehicleSafetyDetails Subform

The Vehicle Details,Sellers,Sold & SafetyDetails tables all have the IAWVehicleID & IAWVehicleNo Fields but when the details are entered through the forms the IAWVehicleNo which is the manually entered number only updates to the tblIAWVehiclesDetails but the other IAWVehicleNo fields in the other tables remain blank.
Any advice or help would be greatly appreciated.

View 6 Replies View Related

Tables :: Duplicate Over Multiple Fields

Sep 21, 2012

I have a table that is going to track people. First Name, Last Name, and Date of Birth in three separate fields.I dont want to be able to add the same person in the table. How do i do this when the data is in separate fields.

View 9 Replies View Related

Tables :: Multiple Fields From Lookup

Oct 21, 2012

I have a member table and donation table. For a donation, I'd like to lookup the last and first names from the member table. I set the lookup for the last name and can see bothe first and last names in the drop down list. When I pick, I get the last name in the DonationT but how to I pick up the first name? That is, how do I fill DonorFirstName in DonationT from the FirstName field in the MemberT?

View 3 Replies View Related

Possible To Link Fields Through Multiple Tables?

Dec 6, 2012

What I have is a table ('tblJobLog') with our main listing in it where we add customer job information as it comes in. The 'Customer Name' is a dropdown list that links to our table 'tblCustomers' so that we can ONLY select a customer name that we deal with. The alternative is to add a customer or list them as COD.

Now this is where I would like to target, the COD's. We still like to keep a record of who the COD is and we have some common customers that are listed as COD only.... So when we go to enter the customer name we have to put COD and in the description we are suppose to input the customer name (We will says RandyShop <--- me, for now).

What I would like to have done, if possible, is to see when we select 'RandysShop' from the dropdown, it checks to see if the COD Status (in the 'tblCustomers' table) is set to YES (its a check box, or YES/NO field). If it is set to YES (or selected) then the change the name shown in my main 'tblJobLog' to look something like COD: RandysShop.

The reason I would like, if possible, to do it this way, is because some customer go on and off COD regularly as the mess around with payment. So instead of creating a new customer that is listed as COD: RandysShop to have access do the leg work based on a simple YES/NO field (COD Status).

View 12 Replies View Related

Multiple Excel Tables With Identical Fields

Feb 20, 2006

Hi All,
Being a newb, have a hopefully straightforward question. I'm writing a vehicle management database which covers eleven seperate areas. The data is currently contained in a spreadsheet with eleven seperate data sheets, one for each area. My thinking is I use linked tables as the spreadsheet needs to be occasionally updated.
My difficulty...
If I want to cycle through all records, I assumed I could query against all tables but don't seem to be able to.
The tables are not currently linked in any way and contain fields such as registration, emissions, list price, make and model and so on.
Any suggestions would be greatly appreciated.

Many Thanks

Q :)

View 1 Replies View Related

STDEV Arcoss Multiple Fields/tables

May 25, 2006

Below is a Tab Delimited section of text to represent a SQL Table.

This is the result set of a select * from table where RNmbr = 0508.

I added the Top Row of A TAB B…TAB H

Row 1 references Column names of SQL Table.

And the First Column (under A) as Excel references.



A B C D E F G

1 RNmbr MesPt R1 R2 R3 R4

2 0508-1 1 28.0 48.0 48.0 74.0

3 0508-1 2 77.0 78.0 75.0 48.0

4 0508-1 3 81.0 59.0 65.0 56.0

5 0508-1 4 54.0 46.0 24.0 25.0

6 0508-1 5 21.0 2.0 15.0 74.0

7 0508-1 6 4.0 88.0 68.0 14.0

8 0508-1 7 8.0 94.0 87.0 96.0

9 0508-1 8 9.0 76.0 66.0 58.0

10 0508-1 9 48.0 48.0 35.0 74.0

11 0508-1 10 36.0 59.0 26.0 888.0



The challenge:

We can easily create STDEV(R1), STDEV(R2), STDEV(R3), STDEV(R4) from the above info.

However, we need to do a STDEV of all these points combined.

Within Excel, this is easily accomplished with the formula

= STDEV(D2:D11,E2:E11,F2:F11,G2:G11)



However, we are tasked of getting away from an Excel spreadsheet and putting this data into a database (imagine the above several hundred thousand strong).

We are using Access as a front end to SQL 2000. We need to have this conglomerate STDEV to be within Access (should the resulting STDEV fall out of spec) it will trigger an alarm for the production operator.

We are not having much success using T-SQL or Access in getting something which seems so simple in Excel. An Access query would suit our needs better.


Now, once this hurdle is overcome; there is a second phase. This table represents one of three, and, you guessed it, there is a need for a Conglomerate STDEV of ALL these points. Any help would be greatly appreciated.

View 4 Replies View Related

Trying To Return Specific Fields From Multiple Tables

Feb 10, 2008

OK so here is a working query:

SELECT Assets.*
FROM Assets
WHERE (((EXISTS
(SELECT *
FROM LCAMdump
WHERE Assets.BarcodeNumber = LCAMdump.T_TAG
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT BuildingName
FROM Building_Names
WHERE ASSETS.BuildingNameID = Building_Names.BuildingNameID)=LCAMdump.BUILDING)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.FLOOR)=[LCAMdump]![FLOOR])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.DeskLocation)=[LCAMdump]![LOCATION_SEGMENT2])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.BuildingLocation)=[LCAMdump]![LOCATION_SEGMENT1])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT FirstName
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_FIRST)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT LastName
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LAST)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT SSO
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.LOGIN_SSO)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT UserID
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LOGIN)
)) =False));

It works great returns the correct results. But I don't need everything out of Assets. I just need a few things from there and a few things from 2 other tables.

I tried this but it now gives back over 220 repeating results.

SELECT Assets.BarcodeNumber ,
Employees.UserID ,
Building_names.BuildingName,
Assets.Floor ,
Assets.BuildingLocation ,
Assets.DeskLocation ,
Employees.FirstName ,
Employees.LastName ,
Employees.SSO
FROM Assets ,
Employees,
Building_Names
WHERE (((EXISTS
(SELECT *
FROM LCAMdump
WHERE Assets.BarcodeNumber = LCAMdump.T_TAG
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT BuildingName
FROM Building_Names
WHERE ASSETS.BuildingNameID = Building_Names.BuildingNameID)=LCAMdump.BUILDING)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.FLOOR)=[LCAMdump]![FLOOR])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.DeskLocation)=[LCAMdump]![LOCATION_SEGMENT2])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((Assets.BuildingLocation)=[LCAMdump]![LOCATION_SEGMENT1])
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT FirstName
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_FIRST)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT LastName
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LAST)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT SSO
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.LOGIN_SSO)
)) =False))
OR (((EXISTS
(SELECT *
FROM LCAMdump
WHERE ((SELECT UserID
FROM Employees
WHERE Assets.EmployeeID = Employees.EmployeeID)=LCAMdump.USER_LOGIN)
)) =False));

I am sure it something simple but I am a novice at this so please help me. :D

View 1 Replies View Related

Tables :: Multiple Conditions In Calculated Fields

Jan 9, 2013

is there any way to put into the calculated field (in expression builder) conditions? What I need is something like

Sum If (Table1.Field1="Y" And CurrentTable.Field2=Table1.Field3)

I means sum how many times there is "S" value in the field1 Table1, but only for records where the field3 in Table1 is equal to the value in the actual table in Field2 (in the actual row).

View 13 Replies View Related

Tables :: Need Multiple Calculated Fields In One Table

Aug 8, 2014

I am great with Excel but not soo much with Access 2010. I Excel, what I needed to do was very simple but duplicating what I did in Access is not soo easy.I am trying to calculate billings for FSA & HRA. I have set up one table with all the data. I want to add columns to the table to calculate:

1. Is there an account balance-excel formula:
2. If the account is still active
3. If the plan year run out is "active runout" or "runout over"
4. calculate each account type with a rate *count of FSA accounts = total to bill

Here are my excel formulas that for the life of me I can't get to work.

Account balance==IF(V2="HCRA",(IF(AB2-AD2>0,"Available Balance",IF(AB2-AD2=0,"Zero Balance","Negative Balance"))),((IF(AC2-AD2>0,"Available Balance",IF(AC2-AD2=0,"Zero Balance","Negative Balance")))))
Active runout/runout over=
=IF(AG2="YES","Active Runout",(IF(S2=W2,(IF(AF2>$AJ$1,(IF(AH2="Zero Balance","Runout Over","Active Runout")),"Runout Over")),(IF(AE2>$AJ$1,(IF(AH2="Zero Balance","Runout Over","Active Runout")),"Runout Over")))))
Active account=
=IF(D2<$AJ$1,"NO",IF(W2>$AJ$1,IF(S2=W2,"YES","NO") ,"NO"))

Can I have calculated fields that refer back to a calculated field?

View 10 Replies View Related

Tables :: Autofill Fields - Multiple Options

Nov 6, 2012

I am creating an access database for my employer which handles blood donating at different venues. I have come unstuck with a particular request.

They would like to create 3 autofill fields for "dates", which are dependent on the previous field "Venues"

There are different venues, which are visited 3/4 times per year. This data is stored in a separate "Venues" table.

On the main user form, they would like to see the dates available to donate, when the Venue field is selected (this is an autofill box, from the Venue table). So if the London venue is visited on 1/1/13, 2/2/13 and 4/4/13. When the user types London into the "Venue" field then the next 3 cells auto fill with 1/1/13, 2/2/13 and 4/4/13.

View 6 Replies View Related

Tables :: Allowing Duplicates Based On Multiple Fields

Dec 23, 2014

I've been playing around with a new database design and ran into a possible 'error' that I would like to avoid.

It's going to be a payroll database to store time codes for hours spent working on specific projects. I have been struggling on how to put this together to fit with what we've been doing for years and I think I hit a few breakthroughs this morning.

However I want to avoid this error of possible duplication of entry.

Simple table set up - primary key is just a running integer; Employee ID; and Week Ending Date.

I can have multiple week ending dates for a specific employee; but I want to avoid having the same employee with the same week ending date. I cannot set up either field as being unique.

Quick run of data that would be in this table:

Code:
1 ABC 11/21/2014
2 ABC 11/27/2014
3 ABC 12/07/2014
4 DEF 11/21/2014
5 DEF 11/27/2014
6 DEF 12/07/2014
7 ABC 11/27/2014

in this example, when the last row is entered I need to get a popup or some warning that this time has already been entered.

View 13 Replies View Related

Queries :: Find Difference Between Multiple Fields In Two Tables

May 8, 2013

I have a database with two tables, one for the amount that was estimated in each cost section, and one for the actual amount billed for each cost section. The tables have the same number of fields, all with the same names. They can be linked together with event ID. Each table has over 100 fields and I would like to find the difference between what was estimated and what the actual was for each event. I would also like to see which cost section has the most and least variance. I am trying to do this without going through each cost and putting [tEst].[CostName]-[tActual].[CostName].

View 2 Replies View Related

Tables :: Adding Multiple Fields With Blank Section

Mar 27, 2014

I am creating a table in access 2010 for my consumable and bench stock report. I made a 12 fields which I name it the month of the year and another 1 field to add the total disburse materials in one whole year. I did this formula to add the 12 fields

[Jan]+[Feb]+[Mar]+[Apr]+[May]+[Jun]+[Jul]+[Aug]+[Sept]+[Oct]+[Nov]+[Dec].

But the problem is its just adding the complete consecutive months that I disburse and the row with blank section the total disburse doesn't show on the total disburse for the whole year. I try to use the code =Nz([Total Disburse],0) but it shows on the screen i cannot be used in calculated column.

View 4 Replies View Related

Modules & VBA :: Efficient Comparison Of Multiple Fields On Different Tables

Oct 7, 2013

I am wondering if there is an efficient way to compare two fields from one table to another two fields from another table. So basically

Code:

If targetTable.Field1.Value = sourceTable.Field1.Value And targetTable.Field2.Value = sourceTable.Field2.Value Then
targetTable.Field3.Value = sourceTable.Field3.Value

The problem is that I need to run this for all entries in targetTable. The only I could think of was to use 2 nested for loops (one for target table and one for source table) as outlined in the following (my data is currently in Excel, but I want to import it to Access)

Code:
For i = 2 To 5754
For j = 2 To 3500
If targetSheet.Range("I" & i).Value = sourceSheet.Range("AR" & j).Value And targetSheet.Range("K" & i).Value = sourceSheet.Range("AS" & j).Value Then
targetSheet.Range("I" & i).Value = sourceSheet.Range("AT" & j).Value

The above code works but it is really slow (takes about 12 mins on a high-end CPU).

View 4 Replies View Related

Queries :: Query To Combine Different Fields From Multiple Tables?

May 13, 2014

I get tasked to use access very infrequently but now I have been asked to create a database. I am struggling with combining 2 tables. I have different data on each table however I do have a unique Identifier. So on table 1 I have Bud, his height, weight, etc. On table 2 I have Bud his home address, phone #, etc. I am using name "Bud" in this case as my unique identifier. I want to create a query that gives me Bud, his height, phone # etc. I want my query to pull in all records. Bud may only have info on table 1 and Budette could be on table 2 but not on table 1. I would like my query to include all the unique identifiers and as much info as I have in the tables.

View 7 Replies View Related

Query Multiple Tables Using INNER JOIN And Multivalue Fields

Jan 28, 2013

Here is my current table structure (I have omitted some fields from this example and have given some sample data in italics to make the table structure more clear.

tblEmployees

ID (autonumber) 3
EmployeeName John
EmployeePhone 555999555
EmployeeLocation New York

tblClients

ClientID (autonumber) 1 , 2 , 3
ClientName ABC Company , XYZ Company, PQR Company
fkeyLocationID

tblLocations
LocationID 1 , 2
Location New York , Chicago

tblEmployeeClients (junction table)
fkeyID 3
EmployeeClients (multivalued number) 1,2

The junction table tblEmployeeClients only stores ID of the Employee and in the second column (which is a multi-valued field), the ID of each of the clients the employee Supports.

I am trying to generate a report that lists say, EmployeeName alongside the clients supported by the Employee (listing the client location is not required, however, it would be good to know how to do that as well).

The report (for the example above), should look like this:

Name Clients Supported
John ABC Company, PQR Company

Currently, I am able to get :

John 1, 2 i.e the client ID for the clients that the employee supports instead of the corresponding company names.

View 1 Replies View Related







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