Form With Join Creates Two New Records
Aug 15, 2006
I have a form that uses the following join as its control source:SELECT * FROM [Purchase Order]
LEFT JOIN [Work Orders]
ON [Purchase Order].WorkOrderID = [Work Orders].WorkOrderID;
I also enclosed a small picture of the relationship between the two tables. This is the problem I'm getting when using my purchase order form.
What I Want:
When I type in the "Work Order #" it will lookup the most recent work order with the same number and put its ID into the purchase order record.
What I'm Getting:
When I type in the "Work Order #" a brand new work order is created and the "Work Order ID" field gets the next number in the sequence.
Make note that I'm not using code for any of this. This is happening all on its own. I think the join is messing things up here. Any ideas?
View Replies
ADVERTISEMENT
Aug 23, 2005
Hi,
I have several forms with subforms attached for data entry purposes. In a nutshell - this program is for an interview - where we need the respondent to give us a listing (say as to what intices them to join a study, etc.), but for some cognitive research method we need to be able to know the order they gave us the list in then we have them rank their list, thus the order may not be in the order they gave us the list. I hope that made sense. Anyway, when a form is called and loaded the subform always adds an extra record. Below is a screen shot of what I am talking about:
http://www.geocities.com/msfstl/ScreenShot/ScreenShot.JPG
So, not only do I get an extra record, but when I am entering data and get down to the new record line, the cursor will jump up to the top of the column. What can I do to prevent the extra record and also the cursor skip? I am thinking if I resolve one issue the other will fall into place.
Thanks,
Scot
View 14 Replies
View Related
Aug 29, 2013
I have a form that uses Table A as a record source and has a subform that uses Table B as a record source. There is a one to many relationship between Table A (one) and Table B (many).The user opens the form to a set record, then uses the subform to create a new record in Table B.
This works perfectly and I can do everything I want. But sometimes the subform creates a blank entry in Table B before I enter any data. Then, once I start entering data, it creates another record in which it stores the data that I am entering.The result is two records: One that only has the date of the record's creation (I set that to be automatic upon creating a new record) and the primary key that links Table B to Table A. A second one is the "true" record, the one which stores all of the data that I am entering.
It won't happen for 10 records or something and there is no apparent pattern. In case it makes a difference, the form is set to open as the subform as the focus.
View 14 Replies
View Related
Jul 4, 2013
I have a database that was created by a colleague. They have created a form for adding client records onto the database.This is done via a button that, when pressed, checks mandatory fields for correct entries and then saves the record.However, when we do this, it is creating two identical records on the database.
She cannot see what she has done wrong in the code, and I know next to nothing about coding,
Code:
Private Sub CmdAdd_Click()
Dim dbAddClient As DAO.Database
Dim rstAddClient As DAO.Recordset
Set dbAddClient = CurrentDb
Set rstAddClient = dbAddClient.OpenRecordset("ClientInfo")
[code]...
View 11 Replies
View Related
Jan 24, 2006
Hi,
I have a form with a number of fields and buttons on it. The form is opened in "add new record" mode. I have noticed that when I move from the last field in the tab order to the first field in the tab order it saves an entry in the database.
This is causing me problems because when users move from the last field in the tab order to the field in the tab order an entry is saved to the database but it by passes all the data checks ensuring that certain fields have been filled-in etc.
I don't know why the movement between these two fields is causing an entry to be saved. I don't have code associated with either of these objects. I aslo changed the last field in the tab order to a different field and it still happens.
Any ideas or suggestions would be greatly appreciated.
View 2 Replies
View Related
Jul 29, 2013
I have a form that uses a text box to search for a specific record in a table.(Cotton12) This works perfectly. What I would like to implement however is if the user so wishes he can edit the data he is shown, whithout editing the actual data in cotton12 and then with this new info create a new record in cotton13.
View 1 Replies
View Related
Mar 13, 2013
I have several different sub-forms that have a button that opens a new form which creates a new record. Each of the different sub-forms have a field value that needs to be passed to the new record when the other form is opened. I've tried a few solutions, but to no avail. Right now I'm using the macro functionality as follows for one of the subforms:
ACTION ARGUMENTS
--------------------------------
RunCommand SaveRecord
OpenForm frmDocumentNew, Form, , [AssociatedClientTracking]=[Forms]![sfrm_ClientTracking]![ID-ClientTracking], , Normal
OnError Next,
GoToRecord ,,New,
MsgBox =[MacroError].[Description], Yes, None,
SetProperty [AssociatedClientTracking], Enabled, Me.ID-ClientTracking
The problem I think is that I'm creating a new record so the value doesn't get passed. The new record is only created after the user begins to enter data in the new form that was opened.
View 2 Replies
View Related
Aug 14, 2015
I am attempting to create a customized task manager. I have created a form that has a combo box that list a series of categories. This list is pulled from a query. I also have a sub-category list that is pulled from a separate query. The relevant section of the subcategory query looks like this:
CategoriesID / subCateogiesID
1 / 1
1 / 2
1 / 3
2 / 4
2 / 5
2 / 6
3 / 7
The query has a criteria that sources the combo box on the task creator form. This filters out all other primary categories. I have a macro that auto refreshes the page after the primary category combo box is updated. The sub category combo box then displays the related sub categories.This works great as a stand alone form. However, when I attempt to use a navigation form or use the tab navigation window I get the error message "Enter Parameter Value." I know am getting this message, because the related categories query is looking for a category in the combo box on the form, which at this point in the process is missing. It also does not update once it is moved to a navigation form or tabbed window.
View 3 Replies
View Related
Nov 20, 2007
Hi,
i've got a query that is linking 2 tables.
i'm having problems with the join properties.
basically from what i can understand, there are 3 options.
1. Where join field from both tables are equal
2. All records from table 1 and only those from table 2 where they match
3. All records from table 2 and only those from table 1 where they match
how do i go about having all records from both tables showing?
View 10 Replies
View Related
Nov 21, 2012
I inhereted a database, and i know nothing about access. I'm trying to add a new payment record and I get this error: cannot add record(s); join key of table 'tblPayments' not in record set. I have never created or updated access before.
View 1 Replies
View Related
Nov 25, 2014
Basically, I want to be able to click on the New (blank) record button, and then start adding in my Attraction, Date, etc fields in the main form, however I keep coming up with the error:
Cannot add record(s); join key of table 'OrdersList' is not in recordset.
I've gone back and checked all my relationships, and the query the form is based on, and all appears to be working there fine. I can manually enter information on to the tables & queries just fine, (but obviously I don't want other users to be able to see these).
I'm wondering if it's something to do with the Auto Number, which is also my PK in table 'OrdersList'. As the first design of this database had this field set to a Text field and I would manually enter the next sequence and I didn't have any issues adding new Orders to the form.
The form is 'Orders', which is based on a query called 'Orders List'.
View 12 Replies
View Related
Jul 20, 2015
I want a query that gives me something like this from 2 different tables:
table1 - AA, AC, DE
table2 - AA01, AA02, AA03, AC01, DE01, DE02
query -
column 1 - column 2
AA - AA01
------ AA02
------ AA03
AC - AC01
DE - DE01
------ DE02
Is that possible?
View 3 Replies
View Related
Apr 14, 2015
I have a form that gets its info from a query, I would like to be able to add a new item, Customer, etc, etc. But, when I try to add one (I have a button using VBA code, ill post that at the end) it gives me the error
Code:
Cannot add record(s); join key or table tblitems not in recordset.
Code:
Private Sub cmdEventNewI_Click()
Me.Visible = False
DoCmd.OpenForm "frmItemsEdit1", acNormal, , , acFormAdd, acDialog
Me.Visible = True
Me.lstItems.Requery
End Sub ' cmdEventNewI_Click
View 8 Replies
View Related
Apr 11, 2013
I have 2 tables that are joined by a many to many table:
tblProductInfo
- ProductID
tblProductLinerMM
- PLProductID (FK to [tblProductInfo].[ProductID])
- PLLinerID (FK to [tblLiner].[LinerID])
tblLiner
- LinerID
I have a range of products that each use 2 liners. An inner liner and an outer liner. I need to add 2 records per product to the tblProductLinerMM table.
for example
tblProductInfo has the following records:
- 2138557
- 2378954
- 4387657
tblLiner has 2 liners in particular that relate to these products:
- L5475
- L5468
I need to create the following records in tblProductLinerMM preferably with the use of a query :
- 2138557 | L5475
- 2138557 | L5468
- 2378954 | L5475
- 2378954 | L5468
- 4387657 | L5475
- 4387657 | L5468
View 1 Replies
View Related
Jan 10, 2008
Hi all,
I have the following issue:
In my job we work with several raw data .txt files exported from Oracle ERP system. These data include information about: inventory, sales, backorders, purchase orders, forecasts, product line.
My goal is to put information from all of these imported txt files together to create an easy to use snapshot file.
The common field between al of these files is the item description. I have an issue where records are repeated for several fields every time the same item description is showed. For example for the same product description the january sales forecast QTY is repeated on several rows because there are several orders for this product description. Is there a way to make forecast QTY appear only once but keep the multiple orders and their information?
Example of current result
http://img178.imageshack.us/img178/5659/currentsu7.th.png (http://img178.imageshack.us/my.php?image=currentsu7.png)
to achieve result
http://img179.imageshack.us/img179/5100/toachievefk4.th.png (http://img179.imageshack.us/my.php?image=toachievefk4.png)
thank you for any comments
View 4 Replies
View Related
Apr 14, 2008
I have a list of PC SN#s in tbl1;
I have a list of other PC SN#s in tbl2
I want to display ALL records of tbl1, AND ALL records of tbl2, matching up the SN#s (where there is a match) - but I want to display all records whether they match or not.
How can I accomplish this?
View 1 Replies
View Related
Jan 15, 2015
In Access 2013 I have a query that join two tables, Conduminium Owners to their possetions (fractions of conduminium).
Owners.OwnOwners.Vote SumOfTblFracPerc
O; P................................No............... .9,54
F....................................Yes.......... ......5,48
L; Q................................Yes.............. ..7,13
M; U...............................Yes............... .7,86
H...................................Yes........... .....5,71
D; R...............................Abstention......7, 78
etc...
This query is grouping and summing, and I want put the results in a form and see it grouping the fractions in lines like this:
"These were the votes of the owners of fractions:
O;P, voted No = 9,54
F, L;Q, M;U, H, G;W, C, N, I, voted Yes = 37,72
D; R - voted Abstention = 7,78
How can I do it?
View 1 Replies
View Related
Oct 1, 2014
I have a simple nested query that is not working as expected. My inner query returns 102 records but when I run with outer query I only get 96 records. Below is my query, I don't really want to pull the same fields from both tables but I was doing to test. The values that are missing are those that don't exist with the monthenddate 8/31/2014 - a left join should fix that but doesn't seem to be working ..
Code:
Select distinct a.entity, a.gl_account,a.profit_center,[Open Items_1].profit_center,[Open Items_1].gl_account,[Open Items_1].entity
from(
SELECT DISTINCT [Open Items].entity, [Open Items].gl_account, [Open Items].profit_center
FROM [Open Items]
)a
left outer JOIN [Open Items] AS [Open Items_1] ON
(a.profit_center = [Open Items_1].profit_center) AND (a.gl_account = [Open Items_1].gl_account) AND (a.entity = [Open Items_1].entity)
Where ([Open Items_1].MonthEndDate=#8/31/2014#)
View 1 Replies
View Related
Mar 25, 2014
I have two queries. The unique key in both queries is GUID for katalogposition.
One is showing me records which has an product end date (Produkt slut dato) between today and end date of next month. This query works fine and is called q_termination.
The second one shows me unmatched records in the first query (q_termination). The query works fine and is called yq_NonTermination.
The goal is now to show me records from the first query "q_termination" that fullfill one of two criterias.
1. No match in second query "yq_NonTermination"
2. Match BUT product end date (Produkt slut dato) is greater than the match in "yq_NonTermination".
I have made a left join query on the field "Dublet_Lagervarer". From the join query the goal is to show me only q_Termination.Guid for Katalogposition number 47 and 134008.
How can I do that? Is there another way to do it? Please see attachment.
View 3 Replies
View Related
Nov 10, 2006
I've been toiling with the issue of WHERE clauses on the "Right" side of Left Joins. I'm aware that you need to use JOIN ON......AND.... rather than JOIN ON....WHERE.... if the WHERE relates to the Right Hand table.
I've even got an example in my DB where the above works, but now am struggling to use the same theory for other tables. Therefore, I went and created two Mickey Mouse tables to test the logic but am getting an error.
I have
Table 1 with one field called Field 1 - values A, B, C
Table 2 as follows
Field 1.....Field 2.......Field 3
A.............100
C.............200..........XXX
I hoped to have a query that finds all records on Table 1 and records on Table 2 where Field 1 matches on the two tables and Field3 = XXX
My SQL is
SELECT Table1.Field1, Table2.Field1, Table2.Field2, Table2.Field3
FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1
AND Table2.Field3="XXX";
but I get Join Expression not supported
What am I doing wrong?
Thanks
Andrew
View 7 Replies
View Related
Nov 16, 2013
And then called this join as a symbol or variable, and then have it use to select the items from these joined tables, can this be done in Access? Here is an example of a code that I created, but it has an error message saying the FROM syntax is incorrect.
Code:
SELECT firstJOIN.trainID, firstJOIN.trainName, firstJOIN.stationID, firstJOIN.stationName, firstJOIN.distance_miles, firstJOIN.time_mins
FROM (trains INNER JOIN ((station INNER JOIN lineStation ON station.stationID = lineStation.stationID)
INNER JOIN bookingLeg ON bookingLeg.startID = station.stationID or bookingLeg.endID = station.stationID )
ON trains.trainID = bookingLeg.tid) as firstJOIN
Can Access do something similar to this, in the FROM statement I joined 4 tables, because each unique fields are in each table and I have to joined them to get those fields. I called this join firstJOIN and in the SELECT statement, I list those columns in the table by calling it firstJOIN.trainID. Can Access do something like this, but syntax it differently?
View 6 Replies
View Related
Apr 7, 2005
We use a 3D program that uses access for it's data base. For reasons I have yet to figure out when the data base is accessed tables are creted called "at_(random number)". For example at_31789. These tables sometimes contain no information or duplicate information of other tables. How can I prevent this from happening and what causes this?
Mickey :rolleyes:
View 1 Replies
View Related
Aug 29, 2007
Hello,
I am trying to do a simple division. But I am getting an #Error when Access query tries to divide 0 by 0. Here is my example:
fldOne = 0
fldTwo = 0
fldCalc: IIf(([fldOne]/[fldTwo])=0,0,([fldOne]/[fldTwo]))
result = #Error
However:
fldCalc: IIf(([fldOne]+[fldTwo])=0,0,([fldOne]/[fldTwo]))
result = 0
If I change the IIF to [0]+[0] (plus) then it works. So I think I am trying to find out how to divide two fields that both contain zeros? (...I think!)
Any help appreciated!
dj_T
View 13 Replies
View Related
Jul 31, 2015
When I launch my modal, I want the user to be able to 'cancel' without creating a new record. It's not doing that and creating extra 'junk' in my table. How do I prevent that?
Here's my code:
Private Sub Btn_Exception_SubModal_Click()
DoCmd.OpenForm "Frm_Exception_UpdateModal", acNormal, , acFormAdd
Forms! [Frm_Exception_UpdateModal]![clientnmbr].value = Me![clientnmbr].value
End Sub
View 6 Replies
View Related
Feb 1, 2005
I am upgrading a .mdb to MSSQL. The .mdb is 17MB, but the resulting MSSQL is 72MB. Tried using both the Access Upsizing Wizard and Enterprise Manager DTS. I have done this a number of times before, but never ran into this problem. Any ideas what coule be going on, and how to fix it?
View 1 Replies
View Related
Jan 12, 2007
I have a form that has a main for, a subform, and then another subform inside the 1st subform. The form works like this:
Conact info
--->Call info and notes
---> Orders worked
Each of those being nested as they appear. The problem is, the user usually jumps strait to putting the order number in "Orders worked" before entering in any notes or info... So no record exists in it's parent form to tie to. How can I have the database create a new record in its parent form when the user types in the subform?
Thanks!
View 1 Replies
View Related
