I have two tables created. One contains only names of people, the other contains the names along with other information stored about those names. The names consist of first, middle, and last on both forms, but for some reason, I can only get the middle name onto the names only table and not onto the other table with all the other information. if you'd like to see the db I have it posted to yahoo briefcase, just send me a message for the username and pw.
I'm writing to an excel file from two separate Access tables, for which I'm using two separate DAO.Recordsets in VBA.The first table has 190 columns, the second table has more than 66 columns, so more than 256 columns will be written to the excel file.So I'm not expecting any error due to any maximum of 256 columns, but still I'm getting an error while trying to populate the 257th excel-column:
Run-time error '1004': Application-defined or object-defined error.(The same VBA works fine when I use another table with less columns.)Even more strange: on another computer I did not get the error and I was able to populate more than 256 excel-columns.
Code:
Option Compare Database Option Explicit Dim xlApp As Excel.Application Dim xlWkb As Excel.Workbook Dim xlWks As Excel.Worksheet Dim FileNameOut As String Dim RowNr As Long Dim ColNr As Long
I have a feed from ODBC to Access DB. I need to populate several new tables with the contents of the original table. I know I'm being stupid but it's been a long week.
I've got a working database with pretty good structure etc. How I would go about creating 3 prepopulated records on a many side of a relationship on entering a new record in the one side of the relationship.
I have 2 tables tblworkdone and tbltests, both have a date field and are both subforms in a tabbed form on the main form. When I enter a date into the tbltests subform I would like the date to automatically be entered into the tblworkdone date field and create a new record so that when I move to the tblworkdone subform with the date already there.
I have created a query from two tables. One table is Job information the other is job notes. How do i populate the query with the job # and job prefix from the job information table? I have the query(field) structure I want but its just gives me blank fields in the query form. How do I populate the fields with the info from the tables?
Here's the scenario: I create a temp table structure, which works fine. I create a recordset from SQL pulling data from a DB2 connection. This works fine. I want to put the entire recordset result into the temp table.
I have a working means of doing this, but it is very inefficient and leaves the user staring at an hourglass for a minute or two.
What I am currently doing is iterating through each record of the recordset and appending it to the temp table. Desired results, yes, but takes way too much time.
Is there any way to simply 'dump' an entire dataset into the table, instead of on a record-by-record basis?
Here's some code from how I am currently doing things:
Set cmdP1 = New ADODB.Command Set cmdP1.ActiveConnection = cnnP1 cmdP1.CommandText = "SELECT DISTINCT distribution_id FROM " & sDB & " ORDER BY distribution_id" Set rstP1 = cmdP1.Execute
Do Until rstP1.EOF With rst_Temp .AddNew .Fields!distribution_id = rstP1!distribution_id .Update End With rstP1.MoveNext Loop
I have two tables in my Db: tblMaster & tblAddresses. They are joined with a one-to-many relationship, with the addresses being the "one" side. Many entries in tblMaster have the same address.
I use a form, based on a query, to add records which creates the new vendor in tblMaster, and (if I have contact details), a record in tblAddresses. The problem is that while the PK is created in tblAddresses once I add the address info, the related FK field in tblMaster is not populated with that #.
I am trying to create a database which will act as a timesheet and management system.
I have several tables set up. some are: 01-Staff details containing Staff ID, Names, etc 11-Timesheets containing Timesheet ID, Staff ID, Timesheet Period ID 17-Timesheet Periods containing Timesheet Period IDs, Start Date and End Date. 13-Hours containing Hours ID, Timehseet ID, Project ID, Hours etc.
I want to set up a Combo that lists Names from Table 01-Staff Details, and when the user selects their name, enters the Staff ID on a new row in the tabll 11-Timehseets.
I am able to create the Combo to list the names from 01-Staff Details, but am not able to get the selected name to jump into 11-Timesheets. The field is not available in the Control Source.
Could you please assist? I am not an Access expert, nor have I done any programming, so a step-by-step solution would be greatly appreciated!!
Many thanks in advance
Sunil
p.s. I would then like to ensure that users can't edit data in certain tables (eg. they shouldn't be able to create a new Staff ID/name, etc)
My next task will be to create a sub-form (which I am able to do) where the user can select a project from a combo-box and enter the hours worked on it. I would like the project and hours to be entered on a new row under the table 13-Hours
i have made two tables with data from an excel sheet. The excel sheet has many duplicates and im trying to eliminate this. The tables are:
tblTasks and tblTeam. Both have autoincrementing primary keys, and the tblTasks table has the TeamID (primary key from tblTeam) as a foreign key.
My question is, how do i populate the TeamID field via perhaps a query, as it is blank on all records. I have over 5000 tasks so a manual approach is what im trying to avoid. A sample of the fields in the tables is as follows:
tblTasks
TaskID - PK Task Name Team ID - FK
tblTeam TeamID - PK Team Name
there's also a Staff table. tblTeam has a one to many relationship with the Staff table. tblTeam also has a one to many relationship with the tblTasks table.
I have an Access 2007 database with two tables (I will call them table 1 and table 2)
Both tables contain the same two fields. (I will call the FirstName and LastName)
Table 1 has an associated Form where the user enters the two names. When the value in either one of the two fields in Table 1 change I want the corresponding field in Table 2 to automatically update with the same value that were entered in Table 1. Basically I want Table 2 to automatically replicate the same data in Table 1. So if I type the text "John" into the FirstName field in Table 1 then the FirstName field in Table 2 will automatically update with the text "John"
I am new to access and am struggling with the automatic updating.
If the automatic part is too hard then I will be happy to attach the update action to a command button.
I have uploaded my database file with the two tables for reference. I want to get the fields (for all records) in table 2 to replicate table 1 so that when table 1 updates table 2 values changes to show the same text.
I need to make one database with information about torque values according to one defined table. I make 3N so I can avoid duplicating data, by my problem now is how to enter data into the related tables and save the data using a save button. I want to have control about when data is saved that is the reason because I used unbound controls to enter data and I want to use also a new button to add new records.
I have created a database which has 9 tables, the first table being the contact information, but in all tables there are fields for firstname, lastname. Is there a way when you enter the firstname/lastname fields into the main table that it can populate the same information in to the same fields in the other tables? If so how?
I have the following tables: Inquiry (This is the main table I want to populate through the use of a form) Programs
My question refers to two columns in the Inquiry Table Program Group
The program column is populated by a drop down menu that is linked from a programname column in the Programs table.
I I want is the Group column in the Inquiry table to autopopulate based on the selected programname (There is a group column associated in the Programs table) so the form field will be autopopulated.
What I have tried is autopopulating the form (form name is Inquiries) Field called Group by using =[Program].[Column](1) in the source code of the text box. This works well, but it then does not populate the main table.
I need to either - Learn how to populate the main table column called Group based on the form autopopulating or - Learn how to autopopulate the column Group in in the table so the form will autopopulate
I have created two tables. One table list of 100 Categories that I monitor each month. The Categories are never changing month-to month, however, each might be associated with a different client month-to-month. Presently, I am manually typing in the Client information month-to-month with information pulled from the Client's table. The Client's table has a Category Field which is populated when a Client has purchased space to use it.
In essence, I have the Category Table (Fields: Record Number; CategoryName; Client Name; beginning date the client will use the Category and Ending Date the client will stop using it). The Client's Table has a lot more fields/information but it still has the same fields as the Category Table. I am trying to be able to use the Category Table and have it automatically populate with the client who is using the Category at that time. Any Category not being used by a Client then the Query should write "Open" in the Client's name field. I have tried many different scenarios but can't seem to get it to work. I must see all 100 Categories each time I run a report. It doesn't have to say "Open" but where ever a client did not use the Category it should be left blank.
Each client record is unique. A client can have 'several' routing records.
Key field in Client is 'Client ID'.
Routing table has foreign key of: Client IDFK
I created a relationship of one to many from Client table 'Client ID' to Routing table 'Client IDFK'.
I created a form for the Client table and works.
Where I am having issue is: Client data is not populating into the following Routing form.
* I want a 'Routing form' that you can lookup client info and place it into that form. * The bottom of the form will be all the routing table fields. The new routing info will be entered into it.
My client needs that form printed for the driver. Client will have many routing forms(records) but only one client record. The driver will have one completed form for each time he picks up client.
What I have is a database setup with multiple tables in which different areas of my DC can input information simultaneously into their respective tables. I then have another database linked to it for myself to have a live view of each updated record. I would like to see all the records of each table in 1 single table (possibly just sorted ascending by time). Each table has the same Field headings but may have different qtys of records. As I will then have it linked to an Excel table to VLOOKUP from it.
I have tried Union coding but always get Syntax Error etc.
I am new with Access and I am setting up a data base that will have 16 tables and each table with have over 3,000 customers. The reason for 16 tables is that we will be inputting information on a monthly basis for each of these customers (12 of the tables) and I also have 4 tables that will be perfomring different "tasks" for each customer (one is just to keep the notes we make for each customer, one will be to show any billing done, one is going to be a summary of the entire year along with some additioanl information not entered elsewhere and the final one is our customer information table).
The customer account number is the primary key for all of my tables.I want to know how I can add a new customer (either using query or form) at one time that will populate all the tables. Right now I have everything set up as tables but I can set up queries if need be.
I have access that is required to be accessed by multiple users and register their activities which is reflected with the tables in the background and form sends also the time that they are putting their activities.
So i split the tables and put them in a share folder in one of the PCs. however, the problem is about the timing and dates is not correct usually, and i found out some of the PCs their dates are not on sync. I asked and found out sometimes it is required to change the timing in their PCs.
Therefore, i was wondering instead of fetching the timing from their PCs why not to put the timing of share PC (i.e. timing/Date of the share PC which has the splitted tables)
so can i do like this:
User PC: (Form1) & user activity => Share PC: (Split table) & take time of the share
I (will have) a form which a user fills in to enter new data.
I have two fields,
"Branch" and "Branch ID" (Branch ID will not be on the form, just in the table)
Branch will be fed by a combo box with seven choices. I would like it so that when "Branch" is populated it autopopulates Branch ID with a code which relates to the branch, so for example
How can we add value during time if the number in unknown?
For example : I would like to add more phone number but when I design tables I dont know exactly how many numbers of each contact there is , so haw I can make my database flexible to add more phone number during time.
as the topic says, i'm a beginner playing around w/ access and i tried to make a query that used 2 tables, but the problem is that when i try to view the query, (since i'm using fields from 2 diff tables may be why) none of the data shows up and all of the fields are blank.
i'm trying to create a query that runs off 2 tables in order to better organize my data. so what i'm trying to do is (with the invoice database i'm trying to create) get some fields from table1 to automaticallly roll over to table2 on the query because table1 and 2 use a different invoice numbering system but some of the values are identical for the y/n fields.
does anyone know how i can fix my query so that i can make it that it merges 2 tables together and still shows the data?
I am creating my first database and I'm a beginner. I have a table called customers and another table that has customer and product information. What I would like to do is when a new record for my product is entered it also enters the customer information on another table. I have repeat customers though, so I only want it to enter it once. Right now, I have to enter the customer info separately. Customer table contains: Customer ID, first, last, Phone. My product table also has this same info. Is this possible? To enter info on a product form and the selected info be copied into another table at the same time only once.
hi all, I have created the database with multiple tables. I've created one main table to store all records from other tables. Then I have make several forms to enter record into several tables. What I'd like to make is that when I enter new record to any one of those other tables, then it will be automitically add to the main table also. The record no of main table will be automatically increase by itself when the new record came in. Can anyone help me with it? thanks.
While much of the discussions here are out of my league, this may just be the place to finally get some help.
I have a hobby weather station and I am also into gardening. Over the years I have accumulated a fair amount of data/ observations which are currently in assorted Excel spreadsheets & are becoming more an more difficult to maintain.
So, I decided to move the various Excel tables into MS Access. While I was able to draft a entity-relationship diagram for my horticultural data, I am at a total loss when it comes to weather/ climate data which are primarily time-dependent. With other words, I cannot find any references that explain how to design pairs of foreign and primary key for linking datasheets that contain time varying data such as various weather and climate data.
I have ordered dozens of books through the local library. Every book presents exactly the same example: customers and orders/sales. This example is fine when you have a myriad of interdependencies in your data (and proved useful for my horticultural data). But, my weather data are more or less independent of one another; the only thing they have in common is the time/date when I make the observations.
Do I have to create dozens of surrogate keys and add a ton of redundant data ?? I hope not !