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 ....
View RepliesI have a table products with a field "id_product" and "total" (Total items in stock)
I have a query with the fields "id_product" and also the field "total in stock"
I want an update query to update the field 'total' in table 'products' with infos from that query
For each id-product in table products, replace the field total with the field 'total in stock' from the query
So I want to update a filed in a specific table with infos form another table.
I have a database which tracks product batches test results and then any reported issues.
I have one table tblOne with [Batch Number] and [Release Time/Date].
The 2nd Table tblTwo [Problem/Issue] at [Time/Date].
What i need to do is get the last batch number from table one which would have been released.
Example:
Batch: 275 Released: 21/09/2012 06:15
Batch: 453 Released: 30/09/2012 07:20
Problem: Wrong Batch Time/Date: 21/09/2012 08:20 Batch:????
How would i update table with correct batch in tblTwo
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...
Thank you.
Suppose I have a customer database. Is there a way if I click on a button, it will create a new record and copy some of the info from the previous record?
View 1 Replies View RelatedI have a log in screen called frmLogIn and on there i have Unbound Combo Box cboUser and Unbound Text Box txtPWord. I also have cmdOK.
IF the user sucesfully enters his password it takes him to frmMainMenu.
On that form i have a lable called lblYouAreCurrentlyLoggedInAs and a unbound text box called txtUserName.
How do i update that txtUserName to whatever was entered in the previous form's cboUser.
I have a table (tblStats). The user enters his current weight in a field named Wt. There are 2 other fields in the table named WkLoss and TTDLoss. These two fields are intended to be calculated. What I am trying to do is when the user enters his current weekly weight figure into the Wt field, I want the field WkLoss to get the user's weight from the previous record and place the difference in the field WkLoss. Also I would like to keep a total to date in the TTDLoss field.
So far this has been my attempt.
Code:Public Sub setWeight()Dim rs As DAO.RecordsetDim Startval As DoubleStartval = 190Set rs = CurrentDb.OpenRecordset("tblStats")'Select records to be updatedSet rs = CurrentDb.OpenRecordset _("SELECT * FROM tblStats ") rs.MoveFirst Do rs.Edit'field to update rs.Fields("WkLoss") = Startval rs.Update rs.MoveNext'increment step value Startval = WkLoss - Wt Loop Until rs.EOF rs.Close Set rs = Nothing End Sub
All I get is the value 190 placed in the first record and all zeros in the subsequent records. Also I don't have a clue as to how to get the Total To Date field to calculate.
Any help would be appreciated
Thanks for your time.
************** edit: Fixed!!!! *************
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)
Thanks again peeps!
how data is best structured in Access.I have a table of values (for instance: weight) and I need to be able to look up a weight based on the column header (age) and row header (height).How is this sort of data best structured and accessed in Access?
View 12 Replies View RelatedI am wondering if Access 2010 has the capability of automatically filling in a field from info keyed into previous fields. For example, I want a confirmation number issued which is made up of all the characters keyed in the previous three fields. For example, assume the previous three fields are:
Block = 01
Building = 125
Room = A
Confirmation Number =
Therefore, the confirmation number would automatically be filled in as 01125A.
If so, how do I go about setting it up?
I have Access 2010 tables linked to Sharepoint 2010 lists and my table becomes disconnected when I run a delete query on the table in Access.
I can append the table and Sharepoint stays connected.
Can I do an append that deletes the previous contents of the table?
I have two Tables, Contacts and FlowMeters.
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.
Please help if you can.
Thank You
I have a form with two tables referenced. I am using the form only to update one of the tables. I am using the other table to pull a reference field. When I add my second table using the query builder, it makes it to where I can't edit/add in my form. I assume its because of the SQL insert statement, but I don't see this statement and can't find where it is to edit it. How to have the form only update one of the tables, while just using the second table as a reference for a field?
View 1 Replies View RelatedHi 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.
I'm out of ideas now- can anyone give any tips?
I know there should be a simple solution to this problem but i have not worked on access for some time and the cobwebs is too thick.
I have 2 tables one being Stock codes with related fields: Stock code id; Stock code;length;width;height.
Second table is Order Details with related fields: ID; Order id; Stock code;quantity;length;width;;height.
Did a query running the stock codes with length width and height.
Did a form with all details, calling up the stock codewith the query. The info on length width and height to the related stock code is on the form but how do i relate it back to the table Order details.
In the order details table the stock code is showing but none of the other related info.
As i said it is something small but i cant get round it.
This may be a bit detailed, so stick with me. Is there a way to have a form which you can pull tabel information into, but look at a specific line? I am trying to create a database that will pull up the first line of a specific tabel's information, then when a command button is pressed, it will pull up a seperate form with the information from line 2 of that SAME tabel. I don't want this to be done by clicking the arrows at the bottom of the form. I actually want this to populate seperately.
Lets say I have a Loc_ID box that is pulling the Loc_ID from a tabel.
The tabel I am trying to pull from has an autonumber format and is sorted in that manor from a-z. Is there a certain type of logic that I should use however? As of now, each time I have a form come up, it starts at the first record, which is not what I want it to do. I want it to come up with the first record, then click a few buttons, then the next time a new form comes up with the same info on it, I want it to show the second record's information.
Any information anyone might have will be MOST helpfull! :D
Hi I have a field on my form called cost of order
I also have a field called deposit on the form that is generated by dividing the cost by 2.
is there a simple way (most probably) to the send this data to my payment table, I already have a deposit filed set up in the payment table I just want to post the data in from the form?
Hi,
I am an Access newbie and I have a form running about 5 seperate formulas and I would like to store the info in the table that it is associated with so that I can use it later in a query to create another formula. Any help would be greatly appreciated.
I'm VERY new to MS Access 2010 - in fact, I used some online youtube videos to get me started with the database I'm currently working on.
Here's what I have so far: 2 tables, one for customer information (name, phone, addy, etc) and one for invoices (inv #, customer ID (which should be the same as my primary key from the customer info table), phone, services rendered, etc etc)
I'm currently working on a form for Invoices, where I can simply put in the customer's name, date, cost, etc without having to look up the ID# and phone number for every time I use it.
Hello,
New to access :eek:
Trying to create a simple data entry form to get information and populate fields from another table. i.e. type in info in one field and the other fields are displayed in the form with information from another table.
Any help apprecated :)
Dear all,
i have searched /researched and looked at hundreds of posts, and no luck.
Im litlle bit out of my depth, i have posted this before but just got confused.
i have two update querys, the first one identifys if a persons age (by date of birth) is under 16 a check box identifys if this person is "ticked" as junior.
the second update query identifys by (date of birth) if the person is over 16 a check box identifys if this person is "ticked" as senior.
The results are returned into my main query in two columns, these columns return -1 or 0 for junior or senior.
I need to produce a report that returns J or S (Junior or Senior) is there a way of converting these results to one column and converting the -1, 0 to J or S.
Please see attached file.
very lost, still searching for the answer. been on this foe three weeks now.
any help appricated.
Hello,
I'm having a small problem converting a select query I wrote into an update query. Below is my original select statement:
SELECT Mid([address3],1,InStrRev([address3]," ")-4) AS CITYx, Mid([address3],InStrRev([address3]," ")-2,2) AS STATEx, Mid([address3],InStrRev([address3]," ")+1,10) AS ZIPx, [address3] AS Expr1
FROM Exercise2
Where right([address3],1) <> "E" and address3 is not null;
The above statement basically parses the address field. Now what I need is an update statement that will use the above code. I'm needing to update the empty fields for city, state, and zip from the field address3 which contains all 3 combined.
Thanks,:mad:
I created a navigation form on which I put a form call [frmAnimal Setup].
I then placed one combo box on the Navigation frm Header. I have bound it to its source and it actually queries the tbl and show the right info. However when I select one, it will not let me.
In addition, [frmanimal setup] will not allow me to select a breed although it does query the tbl and shows the right information.
I am trying to build a Form that will show an estimate (then eventually will be moved to a project if customer and employee aggree to price and project) in a Form F_Estimates is a M_Customers(Customer_ID) (Based on a Table) and thier info in a Subform. Also is the "projected costs" from parts out of the Parts(Part_ID) (Based on another Table) in a second Subform as a list that I need to calculate $$$ in
(Dang that still sounds evil and definately NOT understandable even after edit... so)
Here's some basic info
Tables
EstimatesandParts - Table
EstimatesandParts_ID : Autonumber
Estimate_ID : Number
Part_ID : Number
Parts - Table
Part_ID : Autonumber
PartNumber : Text (not a number due to some part#s have letters in them)
PartName : Text
Unit Price : Currency
Description : Text
Estimates - Table
Estimate_ID : Autonumber
InvoiceNumber : Text (again can have letters in it)
EstimateDate : Date/Time
EstimateTime : Date/Time
Employee_ID : Number
Customer_ID : Number
ProblemDescription : Memo
Customers - Table
Customer_ID : Autonumber
FirstName : Text
LastName : Text
CompanyName : Text
Address : Text
City : Text
Province_State : Text
Postal_ZIPCode : Text (CDN Postal codes are letter num letter...)
you can see the link table in the EstimatesandParts Table
Now I want to use that link to populate a subform in the F_Estimates form
Forms
SF_Customers - SubForm
(all boxes atm are text boxes on this form till I figure out the Parts section then will use same base for this so I can pick any customer in the database to be the customer for this estimate. Also will have ctrl button for making new customer with customer form and a refresh on Focus Gain bit of code)
FirstName
LastName
CompanyName
Address
City
Province_State
Postal_ZIPCode
SF_Parts - SubForm
Default View -Continuous Forms
(want it to be a list of parts that I can grab prices and descriptions from then in a bit of code to calculate a cost of parts)
Part_ID : Combo Box
Control Source - Part_ID
Row Source Type - Table/Query
Row Source - SELECT Parts.Part_ID, Parts.PartNumber, Parts.PartName, Parts.UnitPrice, Parts.Description FROM Parts ORDER BY Parts.Description;
(Pulls info from the table Parts for input into a list of parts to be used on that project)
PartName : Text Box
UnitPrice : Text Box
(here's where I run into problems due to the fact that the form is not based on the parts table but rather the link table EstimatesandParts so I can't propogate the info to the 2 other text boxes, ps I dont care if they cant be text boxes and have to be linked or some other type I'm not "set" just need to find out how to make it work )
(have tried a couple things to complete this task)
Me.txtPartName = Me.Part_ID.Column(2)
Me.txtUnitPrice = Me.Part_ID.Column(3)
(works AWSOME ... for ONE ROW then propogates the second selection to the first and second and third selection to first second and third and so on ...)
(tried to make control source for the txtPartName to)
=Forms!Parts!Partname
(Doesnt exist .. akkk, cant use ActiveForm either as it doesn't focus on the SubForm but the MainForm ... cry)
F_Estimates - Form
Estimate_ID
InvioceNumber
EstimateDate
EstimateTime
ProblemDescription
(all basic Text Boxes)
Employee_ID
Customer_ID
(Combo Boxes Select Customer and Employee from list of present ones of each)
SF_Customers
SF_Parts
(Both SubForms on the main form)
Now this is an Exerp from my entire Database I like to work on one small problem at a time and I have made this its own little database till I figure out the problem then I will bring the info I learn back into the rest of the database and go from there ...
Hope you can help I have a feeling I will need to make a recordset and go from there but I'm just not able to wrap my head around that for some reason
Thanks in advance for ANY and ALL help that I get from here
Hi, I'm new to the forum and I am currently designing a database that my colleagues can use to book people onto our courses.
I have a form set up with two subforms. The main form is one that has the course details, I then have a subform with the individual dates throughout the year. I also have a further subform that shows who has booked onto each date when I am on the course and date applicable.
Unfortunately I do not seem to be able to add new records to either of the subforms I can only change details of current records. I can however add new records to the main form.
Where am I going wrong??
I would appreciate any help you can give me.
Nikki
Hello, this is my first post (anywhere ever)!:eek:
While I have lots of Access programming experience over the years I have broken new ground this week and am in unchartered territory and need help please!
In the organization I am working for (as Procurement Manger), we use AMMS (by Microwest) for work order generation and inventory management, etc. I have been able to import into Access the tables of interest from this SQL-based package. The problem is that I need to share the tables (via nice front end forms/queries) with about 20-30 people in the field.
I don't know much about ODBC but the administrator of AMMS was kind enough to give me access to the AMMS database tables via ODBC and I, personally have access to read the table data but none of the other people in the field can access these tables due to security rights.
So, what I have done is I created MS Access tables for each of the 4 AMMS tables of interest and I run code that runs delete and append queries on each table. I then have a separate public db that the field people access with all the queries and forms but only links to the Master db containing the 4 Access tables derived from the ODBC tables. This works ok but there are issues:
1) if any field people are on the Public db, the code to import the ODBC table data loops and does not complete the delete/append cycle on one or more of the tables. I can only refresh the table data when nobody is connected to the db - impractical.:(
2) the ODBC tables are rather large - one is over 38,000 records and it takes several minutes to run the delete/append queries.:mad:
3) When I created the Master db it was some 83MB. After running the delete/append queries it is some 179MB and appears to increase in size every time I run the delete/append code. When I run "compact db" it stops running after a short time. Does nothing but create a new file (db1.mdb) of the same huge size as the Master.:confused:
I suspect the most graceful solution would be to give each user rights to the ODBC tables from AMMS in Access. In this case, I wouldn't have to run delete/append queries and the data would always be in relative real time subject to the refresh timer. Unfortunately, due to AMMS licensing restrictions, this is not doable.
I have tried to search the internet for guidance but no luck. This forum appears to be an excellent opportunity to get expert advice on this particular issue. I thank you for your thoughts and guidance.