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?
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.
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.
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 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)
(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)
(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
When you open the form, it asks for a tool # (which will be a drop-down box). There will be a command button that will bring up a sub-form. In the subform, there are 2 combo boxes. One picks the group that the tool falls under, and the other has the section of the group for the tool.
When the user picks the section, I need it to filter the questions that pertain to that section (which is listed in a list table), and pull those questions, and populate the master table with them. (i.e., Pulls "where is tool?" from tblQuestions, and puts it in the tblQuestionMaster when section is selected from combobox). :confused:
I need to get x and y coordinates for each device, but the data has to get looked up from 2 other tables.
I have a table (called InstReclosers) that has device names. Each device is on a Section. I can go to another table (called InstSections) and look up what Node that particular section is tied to. Then I need to go to another table (called Nodes) to get the X and Y location for that particular node.
How I can go about getting this X and Y data into the InstReclosers table?
...in summary, InstReclosers has device name and section name. InstSections has section name and node name. Nodes has node name and XY coords. Need XY coords for each device in InstReclosers.
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'm a relative newbie to the more advanced features available to Access.
If someone could help me or point me in the right direction, I'd really appreciate it.
I have one table that contains companies and associated contact info. Each company can have more than one unique number ID (3 digit alphanumeric).
I want to take this table and run a query to create a new table that will only have one instance of each company name, along with the other contact information. The unique ID's aren't needed in this table (but it would be great if I could somehow toss them in, too).
This new table would then be used to create a form with a combo box containing all of the names to quickly jump to their contact info.
Any and all help would be appreciated. If a query isn't the best avenue for this, then please point me in that direction, too.
Guys i have tried to seach the Forum but can't find a solution to my need.
I would like to get a collection of table names from another DB, what i am trying to do is populate a table with a list of table names that the front end links to and their paths once a user has selected the data file location.
Instead of me having to force a user to place the DB files in a location of my choice when the database first loads it will prompt the user to select the location of the data file then (this is why i need the above) populate a table with a list of table names and their source, then establish a link to them for the front end to work.
Also if i ever send amended DB files or they move the location of the data files they can automatically re-establish the linked tables.
Please note that I am self taught (90% of what I have learnt has been off these boards!). I did make this form with info I found on this forum. I am having problems with a log in box for a database, the line of code is - If Me.txtpassword.Value = DLookup("password", "customers", "[customerID] =" & Me.EbayName.Value) Then This code is in a Form; "password" is the value in the table of "customers"; [customerID] is the primary key value I wish to remember; Me.EbayName.Value I assume is the value in the table that access is looking for.
Questions - A) It is not looking up the value for [customerID] - what have I done wrong?
B) In the line of Me.EbayName.Value what does the 'Me.' part tell Access to do? I assume it's a pointer to it's own form? What part of the line do I need to change to make it point to another form (or table)?
C) When this is finished how do I get the program to remember the 'customerID' while the customer is fillling out other forms?
I have an inventory table and a repair table for computer equipment. The inventory table is already set and working with all the data I need.
In the repair table, I would like to just enter the ID number and have some of the information already stored in the inventory table automatically entered into the repair table as needed.
For example, computer 138 has repairs done to it. I would like to then go into the repair table to add a new entry, type in 138 and have the some of the data from inventory table (model, serial number, etc) transfer into the repair table.
Is this possible with Access?
I have only been able to do something with the lookup wizard, but that doesnt seem to work well.
I have managed to get one combo box on a form (Categories) to look up the approprate values for that category and populate another combo box, i.e. if I choose Premesis costs in the categories combo box, I am given the relevant choices in the Details combo box, eg. rent, cleaning...
Now this is all great but I want the values I select in BOTH boxes to be entered into the underlying table. I have tried putting the relevant field as the control source, but the way it is set up means that only numbers are put into the table. Here is the code and stuff (I got this from a help site, so I have changed my table and control name to theirs to make life a bit easier):
cboStore (i.e. the Categories) Row Source: SELECT tblStore.lngStoreID, tblStore.strStoreName FROM tblStore;
Event - AfterUpdate:
Private Sub cboStore_AfterUpdate() Dim sManagerSource As String
sManagerSource = "SELECT [tblManager].[lngManagerID], [tblManager].[lngStoreID], [tblManager].[strManagerName] " & _ "FROM tblManager " & _ "WHERE [lngStoreID] = " & Me.cboStore.Value Me.cboManager.RowSource = sManagerSource Me.cboManager.Requery End Sub Private Sub Label5_Click() DoCmd.OpenQuery "qryCategories", , acReadOnly End Sub
The Tables: tblStore: Field names: ingStoreID (Autonumber); strStoreName (text) tblManager: Field Names: ingManagerID (Autonumber); ingStoreID (Number); strManagerName (text)
The Query (very simple): qryCategories: strManagerName From tblManager; strStoreName from tblStore.
Phew! Is that enough info for someone to help me? I wouldn't mind even the numbers being in the table if there was some way that I could change them back to text for a report.
I'd be really greatful if someone out there could help - be gentle with me though, as I'm note very good at this code thing!
I've got a huge table with one column that shows a lot of duplicate info in it. How can I let it identify the unique names and "hide" all the duplicate info that falls under it e.g.
Columns: Col.1:Fruit Col.2 Col.3 Apples A 1 Pears B 2 Peach C 3 Apples D 4 Apples Apples Pears Pears Pears
I would like the table only to show the say Fruit column with Apple, Pears,Peach and list when you click on it all the related data to it. Along with this I would like to associate all this info in the form. If I select Fruit in the form the related data should appear below in maybe a subtable or something which I should be able to select from which info I need.
I have a table that lists all of my available seating for different events.
My table columns look like this: EventID - Section - Row - Seat - Available
Sample data in columns: 1 - A - A - 1 - Y 1 - A - A - 2 - Y 1 - B - A - 1 - N 1 - B - A - 2 - Y 2 - A - A - 1 - Y 2 - A - A - 2 - Y 2 - B - A - 1 - Y 2 - B - A - 2 - Y
EventID actually goes from 1-6 Section actually goes from A-F Row actually goes from A-J Seat actually goes from 1-36
When the user selects an event (or events) in the first box - I would like the query to eliminate the available seating, from the other events, in the results.
When the user selects a section (or sections) - I would like the query to further eliminate the available seating, from sections not chosen, in the results.
When the user selects a row (or rows) - I would like the query to further eliminate the available seating, from rows not chosen, in the results. And finally display whatever available seating is available - based on all three of the above criteria.
I purchased every access book I could find.. and I am slowly learning this. I'm a complete novice.. I'm not looking for any of you to do the work for me.. I'm more looking for guidence. I'm still getting to know all of the controls and their limitations. I have not yet written any VBA code. (It's still very confusing) I'm trying to get by on using the toolbox controls, using queries, macros and form controls.
I have a report in my DB that displays the results of several queries. How can I have the information displayed on my report entered into a table as a record when the report is generated?
Is it possible to construct a query that uses information from one table to run a series of Like OR Like... criteria?
I have created a table that contains partial part numbers and would like to query a second table for all the part numbers that contain these partial part numbers.
Meaning, BACB30DX6 would return the desired information for BACB30DX6-7, BACB30DX6-8 and so on.
The TBL_PARTS_FAM_DIA table contains approx. 200 items so 200 LIKE statements can't be the way to go... I'm hoping.
I tried: LIKE "*[TBL_PARTS_FAM_DIA]![PARTS_FAM_DIA] *" thinking that perhaps this would go through all 200 items... but of course that was wishful thinking.
Do I really need 200 LIKE statements or is there some easier way to do this.
hi, i've been trying to find a solution to this problem but i can't seem to get my head around it. i have searched through the forums but found all the threads to be confusing although it probably is simple.
im a doing a project for a2 for a trailer tent company
got 2 tables where the problem lies in order stock
they only sell one trailer tent to a customer when a trailer tent is sold, i want to update no in stock in stock table i.e. trailer tent sold --> "no in stock"field for that particular trailer tent goes down by one. I would like the user to be able to change stock level any time as well so don't want it to be fully automated
sorry if i'm not making it clear, not really sure how to explain it
I have a report in my DB that displays the results of several queries. How can I have the information displayed on my report entered into a table as a record when the report is generated?
On my form I have a tape Textbox where I put tape numbers in that is on a other table inside my access file (for now I just type a tape number in), then save the info to other table also inside access file.
I want to know how can I make it that if I put a tape number in the textbox, it must look at the table for tapes, and if the tape number is not in the tape table, it must not let me use the wrong tape number that is not in the table for tapes.
I am creating a database but I am having a problem with one little bit (so far!!)
I have form (and table) 1 and form/table 2. When I am in table 1 (which is company details) I want to be able to click a button which will open form 2 and allow me to enter a new record of incoming mail details regarding the Co. selected in form 1.
I have made a relationship between the Company ID on both tables.
I cannot seem to work out how to make Access take the Co ID no from form/table 1 and stick it into form/table 2 when I push the button.