It seems that there are a few different ways to get info from the tables when developing an applications that i know of:
1)queries
2)dao
3)ado
4)selects without any of the above
I lean towards the last 2 but I need to know this: is there any advantages in using ado as opposed just sql?
For instance i ran this sql:
Private Sub Command0_Click()
Dim SQL As String
Dim strCriteria As String
strCriteria = Forms![form2]![Text1]
SQL = "SELECT * FROM Table1 WHERE (((Table1.clinic)='" & strCriteria & "'))"
DoCmd.OpenForm "frmClinic"
Forms![frmClinic].RecordSource = SQL
End Sub
it worked fine. I could see how a beginner would prefer to use queries over sql but I would prefer to use sql in vba. I've read that dao is older and I should use ado instead. So that leaves ado vs. the way I displayed it in the above code. So which way should I go? If I'm over looking queries or dao in favour of ado/sql, just point out their advantages if you don't mind.
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.
I have a sizable list of GIS polygon shapes(~9000 data points), collected over 15 years, that I am trying to shrink down to unique ID's. Each years data has a unique label, but for many of the polygons the shape does not change from year to year, so I want to assign a unique ID that encompasses all of the years that shape existed. Some examples:
1950- Parcel A - 2 acre star same 1951- Parcel K - 2 acre star same 1952- Parcel L - 2 acre star same 1953- Parcel F - 2 acre star same
1954- Parcel J - 3 acre box same 1955- Parcel Z - 3 acre box same
Ok, simple enough. I created a unique polygon ID in a separate table, and set the first 4 records to some value, let's say AAAA. The latter 2 records I called something else, BBBB let's say. These values are used in a combobox so my data entry stooges can easily assign each duplicated polygon the same ID, so a quick query will show that polygon XXXX is comprised of so many years worth of the same value, just with a different label. Now my question...
In the above example, after 1953 the 2 acre star polygon never exists again, so I want to 'retire' that option from the combobox so it cannot be chosen in error. As it stands now, if I were to enter the following date point 1956- Parcel N - 3 acre box
I can still choose AAAA or BBBB as the unique polygon ID to equate it to. Is there some way (maybe a boolean option) to disable just a specific combobox value so it cannot be entered in new records, but STILL shows up in old records, a.k.a. is not deleted?
I have Linked a few ODBC Databases to my database in the Tables objects. How do I make them usable for queries or as record source for my form? I tried to create a query but the ODBC databases do not show up in the Table options.
The database I'm working on is coming on nicely, thanks to some valuable help from this forum. But I've got a couple of new problems that I just can't seem to get my head around. Really hope someone out there can help!
So...
I have two tables (well, there are more.. but there are two main ones with the important data on them). The first is a list of Customers (you know, the usual Name, contact, telephone etc...). The second is a list of jobs for each customer.
After starting from scratch, I created a nice looking form wth control tabs that on one page shows you the customer information and on the second; the job history for that customer. I then have a third tab which lets you add new jobs. So I'm all chuffed because that is the basics of what I wanted it to do.
However, I need a hard copy of the job report to print out and give to an engineer to fill in or to print in future should a customer wish to see it.
Try as I might, the reports function didn't look as if it was something that could be "designed" the way I wanted it to look. So, I figured another form was in order.
I started by building a query which included all the fields from the two tables mentioned above in it; I.e. So it would pull up a Job Printout by a workorderID number. That way it would show all the company info PLUS the detail of that one particular job.
Problem is this: I can see from the query in table view that the Jobs are listed; but alongside them is a straight listing for all the companies in the database. Basically, the Company who received the job in question is not being shown by the query.... if you follow me. (Apologies if I'm explaining this like a fool).
Question 1 then... is how do I fix this? Is this something to do with these arcane relationships things?
Question 2: How do I create a button to print that one "form" by workorder ID.
Question 3: Did I do the right thing by using a "form" or is there a better way to create a "report" for printing that can be formatted the way I want (with logos and stuff)
I am trying to get an email address from a table by choosing a employee number from a combo box...it is almost working, however, it is returning the employee number as opposed to the email address...here is the code I am trying to use:
Private Sub Combo0_AfterUpdate()
Dim myConnection As ADODB.Connection Set myConnection = CurrentProject.Connection Dim myRecordSet As New ADODB.Recordset myRecordSet.ActiveConnection = myConnection Dim mySQL As String
mySQL = "SELECT EmailName .*,[Employees] FROM EmployeeNumber"
I'm a genealogy nut and not so great at computers. I've got a report on cemeteries that gets things like a cemetery number, peoples names and tombstone information from one table. Got that working pretty well. I've got it to print the cemetery number in the detail header (there are 300+ cemeteries each with 1-100s of people in each). I want to take that number and use it in a different table to get travel directions and GPS for each cemetery.
I am very new to access and I am trying to create two tables called Customers and Orders.
The fields in Customers are: CustomerID (Primary Key), First Name, Last Name, Address.
The fields in Orders are: Order# (Primary Key), CustomerID, Product Purchased, Address.
There is a One to many relationship between CustomerID in the table Customers, to the CustomerID in Orders.
The problem I am having now is with the Address field.
GOAL: What I am trying to do is have the Orders table automatically get the Address from the Customers Table.
If that doesnt make sense, whenever I create a new Order, I want to enter the customerID, and have it get the Address data from the Customers table automatically put into the proper field in the Orders table.
I just can't figure out how to solve my next problem. What I need to do is to make a query showing all the country's next to germany(nr.5) with their capitals. There are three tables which I can use, nr.1 showing the country_id's with their "neighbour country's", nr.2 showing the name of the country's with their capital_id's and nr.3 showing the name of the capital with their corresponding country-id. Is there anybody who could help me with this problem, or has some suggestions? If you find this message unclear, i would love to e-mail you the tables. As i am dutch my english isn't that good. Thanks in advance
I created the database in the attachment. I have three tables which are connected. I am trying to create a form to show the connection of the tables. As you can see, I have inserted the 'Item Code' in the form 'Products'. I am trying to add 'Cndl Codes' and the 'Cndl Quantities' of each 'Item Code' showing above 'Item Code' in the form, but I don't know how to do it.
I am trying to update an existing table in Access with current information that I have imported into Access from excel into a separate table. I was able to use an unmatching query and append all records that were not already in the original table. But there are records in the update table that match the original table, and there in information in fields within that records have new information.I need to find the records that match in the two tables and update the missing data.I am able to create a query that displays the matching records but when I run it as an update query the information in not entered into the original table.
My father tables contains all the relevant fields with info of my products (which are listed in records/rows). My sister table contains less fields but are all present in the father table. The info is imported from excel daily into the sister table - all the fields here change regularly except for the unique ID which is present in both tables. Because the sister tables' information for the records/rows changes, it is difficult to update the selected fields of the father table.
I want to update selected fields within my father table based on the information I upload from the excel sheet (which becomes the sister table when uploaded)
On the Flow Meter Form Info is listed specifically for each Flow Meter.
I would like to bring some of the information from the Contacts table on this form.
For Instance if I am on Meter 1T-02 I would like for it to associate that meter number with the name of the customer thats already on the form, then look up that customer's name in the contact table and bring over that customer's contact information.
I built this database over a year ago and it has been a long time since I have worked with the design of it, therefore I am a bit rusty.
I have now been asked to create 'something' (form, query, whatever) that will:
Allow the engineers to change the date in multiple tables for parts; and,Change (in)activity status information in those tables; and,Delete the same obsolete part(s) from two tables and add that information (one entry) into another table.
Now I have created the relationships between the tables for the various ID's. My issue is the following:
I will be automating this process, so when the data is imported to the various tables, I need the relevant keys from tbl_1 tbl_2 and tbl_3 to be added to tbl_4 when the new data is brought into the tables.
As to the process of importing the data, I was looking at using PHP to facilitate this. Any ideas, help or direction would be greatly appreciated. I have looked around the net for an idea of how to solve this, but, being so new, I am not exactly sure how to word the question.
Hi guys. For my coursework I was told I need complex queries to gian high grades.
This is my problem: I have many many tables all identicle. They have the same field names just different information on them. I wanna be able ot query ALL those tables to bring 1 result which I will then make a report out of.
I have tried everything, I fidled with the relationship but can't understand it.
Oh, and the tables have been linked form excel. I know i can't edit it then, but i dont need to.
I would really really apretiate all responses as this needs to be done asap.
Hi everyone, I've tried several different things to get around this problem and have found out a lot about what won't work!
Basically I am building a HR database. The main table is M_CandidateDetails and the primary key is CandidateID. This is a foreign key in all of the other tables: MCandidateEducation, MCandidateEquipExp, MCandidateSector. (I've attached a screenshot of the relationships.)
Goal: What I want to do is create a continuous form with one record for each candidate. A record will show all of the info pertaining to a candidate. The form is to be used to for searching and filtering candidates.
Attempts.. First I tried creating a query to combine all of the information. (I've attached a screenshot of the query). The only problem with that was if joe bloggs had worked in 3 different industry sectors there would be 3 records for him. (I did have problems with the query not being updatable - solved that though by using the Dynaset property of the form)
Next I tried basing the form only on the M_Candidates table and using listboxes to display the information from the many-side of the various tables. But that didn't work as you cannot use an unbound control on a continuous form.
Then I tried putting in subforms instead of listboxes but you can't use subforms on a continuous form.
One is a list of fishermen with all their info. I used a Code as the primary key.
The 2nd is a fish ticket sheet with fish tickets entered and the code in there as a foreign key in the relationship between the two.
I create a report listing the fish tickets and prices perfectly for the 4 fishermen I have entered fish tickets for. (I have 140 fishermen in the main table)
I added a 3rd table for payments made to the fishermen. There are two payments for 2 of the fishermen.
then, I go into report design view and drag in two of the cells from that 3rd table into my report.
The problem is the report then prints JUST the fish tickets and payments for the two fishermen that have payments...not the info for all 4 fishermen. I need to print out settlements for all the fishermen whether they have payments or not?
I am importing the updated Employee Roster information from Excel to a table called "Weekly Roster Check" (contains new info). I have a table called "Current Chit Board" that has an Employee Roster that I update every week (contains old info). I want a query to find employees who are promoted and their title has changed. then I want to run an update query to update these results to the Current Chit Board table.
Every method I try, returns either no information or information that is not what I want.
I have DB used for inventory for many different categories. I have a table and form for the following: Location, Printers, Pc's and many more.
What i am trying to accomplish is to have a advanced search form that will display how many pc's and there makes and model from selecting the location name or Cost center from a combo box.
So an example would be I want to select MPP-WDF from the combo box click a button and it will return the number of PC and there makes of model's and some other information in a list of records.
I have a form that I need to turn into a table. The table will be updated each day with new information but I dont know what to do to keep the previous days info. I have attached the form ....
I have a few questions about tables and forms joining, and auto-population of data.
If I join all 8 of my tables one to one to one to one to one, is that possible? That is my first question.
Secondly: If I have my RSN number that I'm using to track each record, is it possible that if I type in the number on a different form, and two of the fields are similar on every form that they will auto populate since the tables are joined? And if they're filled out in one form, they should know that these were the fields we said were attached to this number it'll just appear? Is that a real thing?
I need help on this, from what the best concept is, to what I need to look into using:
I store Quote data from phone calls into tblQuotes. There is a seperate table that holds much of the same information except that it is for actual orders called tblOrders.
As far as function goes, I have each working much the way I need it to except for one thing... If a sales person is on the phone with a customer with a quote already in the system, right now they re-enter the data into the Order table. Most times, the order is what was quoted, but maybe with a few small changes (so I will wnt to keep a historical record of the quote). How could I copy the contents of the quote recordset into the recordset for an order, where all the sales rep does from there is edit the quote to the actual order?
Would I use VBA or an update query that is executed via VAB? Honestly, I am unsure how to do this at all, I hope someone has seen this before and has a good suggestion...
I was using access 2000 to build a small office app. It will be on a network and will generally be used by only 2 or 3 people(max). I was wondering if the default jet technology would be good enough for this. If it isn't, what should I use? I looked at other options such as msde and ado but it's a bit confusing when you're new to this stuff.
I'm trying to create a database for a taxi-type service that runs on thursday, friday and saturday nights. My question is: Is there a way to save the database at the end of the night separate from the previous nights, and so that the database is empty for the next night, but reports can be viewed for all of the nights combined?
