Forms :: Form Produces Error After Creating Query From 3 Tables
Feb 11, 2015
I have 3 tables:
tblMain
BalanceID PK
GasID FK
ElecID FK
tblGas
GasID FK? not sure
BalanceID
tblElectric
ElecID FK? not sure
BalanceID
I created 1 query from all 3 tables, then created the form from that 1 query.Now when I enter data into the form and click to save it , it produces this error message:The Microsoft Database Engine cannot find a record in the tblGas with key matching fields 'tblMain_GasID.The tblGas does contain a field GasID, but does it mean that the field should be tblMain_GasID
I have a query that add up the numeric values in a value list assinged in a combo box in response to each question, the row source for question 15 for exmaple is as follows:
ROW SOURCE: 0;"I have no idea";5;"I indicated that I wouldn’t have time today";0;"Was mentioned early on and then not offered again";5;"The salesperson said the vehicle wasn’t available"
I then run a query that adds together the responses to 3 questions, including question 15, the field in the query appearing as follows:
LS5: ([q14]+[q15]+[q16])
It was working fine but has stopped working, the fault lies with q15, if I take it out it works again. So I looked at the table as I am sure it must be the way it is set up, why it worked before I don't know and I attach a screen shot of how the field is set up in the table, which is no different to q14 and q16.
Anyone got any ideas?
One last thing is that it makes no difference if the fileds contain a number (including zero) or are blank
The expression On Click you entered as the event property setting produced the following error: Procedure declaration does not match description of event or procedure having the same name
Now this is the error message that I am constantly getting from any command button I hit on a certain form. Here is the code of the form.
Option Compare Database Option Explicit Public inputCSV As String, ORG As String
I changed the names of the buttons, reconstructed the code under those names, went to the modules and changed names, made sure that a sub o function name is not duplicated in the project... But helas the error is still there. It used to work and suddenly does not work.
My report produces multiple copies of the same record. I know why, but don't know how to fix it.
EmployeeTable.
With a one to many relationship with TrainingTable (via employee PK as FK in trainingtable).
Training table has a one to many relationship with a table called Range.
Report is based on a query that picks up the Employee/Training/Range (range just describes the training unit).
However, If I have more than one range expressed organized a training unit, the report spits out several copies of the Employee record to display all the ranges.
I have a project at hand and it's been a predecessor of mine and client has asked me to do some work on it and extend functionality - but I have not really delved into Access before and I have had to worked my way through to this final snag :/
The Main Form has one sub form. This sub form allows the user to add multiple order items i.e. qty, stock, description from records within the system - fairly straight forward.At the last column of each row is the sub total of those particular items i.e.
Qty Unit | Item ID | Total ----------------------- 2 | 1234 | 80.00 ------------------------ 1 | 43526 | 20.00 ------------------------ > | |
So the total is a function of =[Qty Unit] * [Unit Price].Then in the Footer of this SubForm is the Sub Total
=SUM([Qty Unit] * [Unit Price])
All fine and well..... However, the additional functionality kicks in.
Lets add the additional customer_id from the Main Form. Each Item bought is dependent on the customer_id i.e. they get special prices depending on who they are.So a New table is made which has the Item ID and SpecialPriceID (of a table to define as a specialPrice) and the Price linked to this Item and Special Price category. So say that there are two groups of users "wholesale" and "nonwholesale" these would be SP_1 and SP_2 and each client is defined either one of these, and each stock item has a Price for each SP_1 and SP_2. Hopefully I've explained myself there.
Back to the SubForm. So now the Total needs to calculated differently with needed the external customer_id from the Main Form.
Code:
Function CalculateSpecialPrice(ItemID As String, CustomerID As String, Unit As Integer) Dim SPSelect As String SPSelect = "SELECT Price FROM [Items_SpecialPrices] WHERE" SPSelect = SPSelect & " ItemID = '" & ItemID SPSelect = SPSelect & "' AND SpecialPriceID = (SELECT SpecialPriceID FROM Customers WHERE customer_id = " & CustomerID & ") "
[code]....
its the sub total I just keep on getting #Error on. I have even watched (using alerts) that the correct return variable is the same as the individual rows. This is the equation I used for the SubTotal within the footer.
I am trying to create an input form for input to tables.There is a main TblContacts table with fields such as: LastName, FirstName, Address, etc. There are also ID fields for the Company and Country and joins to the Company and Country tables, each with just ID and name fields.I want to have all the fields from the TblContacts table and the Name fields from the Company and Country tables on the form. I want to be able to select the Name fields from the Company and Country tables from combo boxes and for these also to populate the Company and Country ID fields (from the TblContacts table).
To create the form, I selected Create Form whilst the TblContacts was open. Then, I added the combo boxes pulling the names from the Company and Country tables.My problem is that the selection of the Company and Country names above is NOT populating the Company and Country ID fields on the form (taken from the TblContacts table. I have checked in relationships that the joins have been created.
When I have a simple table, users pictures inside maps (jpeg,png...) defined as OLE object, when creating the form from that tables, don't have picture shown, only name of the picture? I tried all the options, package, copy, paste... nothing...always the same... just picture name but no picture.
I am creating a database of bird banding and breeding records for the refuge where I currently intern. To do this, I have created the following tables:
Adult Banding Records Nestling Banding Records Historical Banding Records
The specific issue:
I have already created forms for entering data into these tables, but now I would like to create a "search" form that will use the primary key (which is always the band number of the individual bird, across all tables) to search ALL the tables and pull up all the information on that record. This will be convenient when we recapture a bird that is already banded, so we can look up their information in our database. Also, since I plan to add many more tables, it would be nice to not have to search each table individually.
How do I create a form that will allow me to enter data into three different tables?
I have a rather simple database for tracking students. When I get a new student I need to take information off their paper application and enter it into three separate tables, Student, Families, and Demographics. Each of these tables contains the fields StudentID, StudentFirst, and StudentLast.There is a relationship between the StudentID field on each table, with Student being the main table and Families and Demographics coming off of it. All relationships are one to many. StudentID is the key for the table Student.
I want to create a form to add a new record to each of these tables. I want my data to be displayed in the Columnar style. I only want to enter StudentID, StudentFirst and Studentlast once and have it populate to all three tables.
I can create a form and a query that displays existing entries exactly like how I want to enter them, but of course I can not edit or add to them. I have tried using subforms, but they don't seem to link up. Once I enter the Name, I want it to be on all the forms. I don't want to have type it three times. I also tried creating three separate forms and connect them with the Navigation Form. The issue again is that once I enter the name in one form, it is not automatically on the next form.
I've been tasked with creating an Access database..We have a main table that includes information found on every lease, however there are 3 leases types. Each which lead to another table or two. Basically, I want to create a form that allows me to link the main table with the corresponding variable tables.
I am building a form to create a user record and at the same time i have some yes/no options which are located in other tables but when i want add a user i cannot select any yes/no options they seem locked?
In my database I have a form who's record source is a query. That query is based on a table, and have set a criteria on one of the fields.
When I use the form to add a new record I want the value of the field in question to automatically be what I have specified in the query.
Instead, the record is added but without having that value in the field, and when I go back to the form it doesn't display the record because it doesn't meet the criteria specified in the query that the form is based on.
I have an "Returns" master form that contains two subforms. The subforms contain items that we are returning back into our inventory. The underlying queries in each subform show only those records where the "Return Date" is null. The query(s) works fine, except that if there are 3 items that need to be returned there are 3 records that show in the master Returns form. I tried the Totals option in the query but the I need that Return Date on the subform. I only want one Returns master form to show the 3 records (not 3 records of the same master form).
Wasen't sure whether this is a Reporting thing or a Querie thing as it resides inbetween the two.
Basically (and believe me I thought this would be pretty straight forward!!!!), I am trying to create a chart from a query that returns the occurences of concerns raised each month for a year. The query returns the following:
In order that the user can specify a year that the chart will be applicable to, there is a field within the query that relies on the input of a year on an unbound text box on a form: [Forms]![frm_concern_frtpage]![Enter Year] is inputted into the critera box of this field.
However, if you try to create the chart it does not like this and just gives the following two error messages. If you enter a value into this text box and run the report containing the chart, it just gives a blank chart!!!!
Why is this error occuring?
I just want months along the X axis and the values from the Expr 1 column to be displayed on the Y axis!
I can run a query but when I try to display this information on a report I get the following message:
The specified field “tblMachineData.TagName’ could refer to more than one table listed in the FROM clause of your SQL Statement.
Here is my SQL code from the quey.
SELECT tblMachineData.TagName, [tblMachineFloat Query].DateAndTime, tblMachineRunTag.TagName, tblMachineFloat.Val FROM tblMachineRunTag INNER JOIN (tblMachineData INNER JOIN ([tblMachineFloat Query] INNER JOIN tblMachineFloat ON [tblMachineFloat Query].DateAndTime = tblMachineFloat.DateAndTime) ON tblMachineData.TagIndex = [tblMachineFloat Query].TagIndex) ON tblMachineRunTag.TagIndex = tblMachineFloat.TagIndex WHERE (((tblMachineFloat.TagIndex)=0 Or (tblMachineFloat.TagIndex)=1 Or (tblMachineFloat.TagIndex)=3 Or (tblMachineFloat.TagIndex)=4)) ORDER BY [tblMachineFloat Query].DateAndTime DESC;
If i have a new entry from the user, and i want access to create a table on its own, with the table name defined by the user and the elements of the tables already pre-defined by a sample table that i already do out... Is there any way of doing it? Thanks for any helpful souls around...
I am trying to develop a DB that is multi user. i ahve not split the data from the forms into separate db's as the users are in different locations and our network can be very slow.
part of the requirerment is to produce a drawing approval form, my current sticking point is that the method i am currently using to create the forms only allows one person at a time to use the process.
currently i have a command button that runs a query and then creates a table from the results. the table is then used as the data source for a mail merged form in word.
what i ultimatly need is a process which allows multiple users to generate these forms at the same time and have the ability to save the form (with no code behind it) as a word document (either RTF etc)
i am thinking that if i create a query on the fly in VB and then use the results to use in a report this would allow me to do the above.....
So my questions are:
1) is my assumption correct 2) If so, how do i do it as i have never done it before 3) If the above is not the best solution/ does not work... what will/is
I'm having a few issues with creating a Lookup on an Access form. I'll recreate the scenario below as best i can to help with my explanation.
I have 2 data entries. The first is a Post Code (Zip code), and the second is an Area Rating that applies to the Post code. For example:
Post code AB1 8 is awarded a rating of A Post code SO1 3 is awarded a rating of F
I have already created a table in Excel with all 2,922 Post Codes (AB1 1 to ZE3 9) and the corresponding Area Rates (A to F)
What i'm making is a form that holds customer details. I'd like a combo box or text box that when you type in the Post Code, another text box automatically displays it's corresponding Area Rating.
Code:Post Code: Area Rating:------------- --------------| CM2 7 | | A |------------- --------------
I apologise for my really very poor diagram :P
So the Post Code box is either typed into or a combo box with the list of Post Codes, and the Area Rate box cannot be typed in to, but it automatically displays the record dependant on what is in the Post Code box.
Ideally, these will also be recorded onto the main table (IE not the table with the list of post codes or area ratings, but a 3rd table holding the rest of the details that will be used to create statistics and graphs)
Can anyone help me with how to do this? I've been trying to work it out all afternoon!
I was wondering how to create a form which is using data from three tables, the data in the tables does not have any relationship setup as they are not related to each other. When I use the wizard and I select the three tables, Access starts complaining about the fact that no relationship has been setup...
I have a table called MiscORders where all the orders are tracked. There is an ID, order number , other fields and ParentorderID fields in it. Whenever an order is modified they create a new order with ordernumber and have the previous order number as the parent. The next time it is amended or closed another order is created with the parent order attached. Here is some sample data
Order ID OrderNum Parent order id 1 MISC 2013-10 2 MISC 2013-10A MISC 2013-10 3 MISC 2013-10B MISC 2013-10
Now I am creating a query that shows the lifecycle of the order
MISC 2013-10 Issue and deadline date MISC 2013-10A issue and deadline date MISC 2013-10B issue and deadline date.I tried to create a query using self joins to the same table. SQL is attached
SELECT MISCORDER.OrderNum, MISCORDER.[Date Issued], MISCORDER.[Deadline Date], MISCORDER_1.OrderNum FROM MISCORDER LEFT JOIN MISCORDER AS MISCORDER_1 ON MISCORDER.OrderNum = MISCORDER_1.RescindedOrderID WHERE (((MISCORDER.OrderNum) Not Like '*A' And (MISCORDER.OrderNum) Not Like '*B' And (MISCORDER.OrderNum) Not Like '*C' And (MISCORDER.OrderNum) Not Like '*D') AND ((MISCORDER_1.OrderNum) Not Like '*B'));
How can I put a condition on the table to show only records with order num ending with A. It is not working if i use it in the where condition..
is there anyway you can create one form which updates all the information in multiple tables. I need to create one of these to make the navigation of the database more easy, so if anybody could help me it would be much appreciated.
I have built an Access DB containing 3 tables: dimensions, time, companies. The tables are not linked and are to be used to look up values for the new form. The goal is to create an Access form that would allow the user to select distinct values from all 3 tables, enter some own data and then execute an append query to add the record to the main table.
Something like this:
Initially I have 3 tables:
Prepopulated Dimensions table with fields: dimension ....
Prepopulated Time table with fields: Date Day Month Year
Prepopulated Companies table with fields: Company ....
My form is to be able to select distinct values (combobox) from all three fields: Company Dimension Day Month Year Value (data entered by user)
The record then is appended to the Main table containing: Company Dimension Day Month Year Value (data entered by user)
I'm trying to create an invoice system with a sub-form within the main form. The issue I'm having is that the data in the sub-form keeps getting overwritten each time a new record is created for the main form. So when I go back to edit an older record, the data in the sub-form is changed to whatever the latest record had in it. Is there a way to lock in the data in the sub-form so that it stays with it's own record?
