I have a table orders details with a sub form displaying the order, I have a combo box to display the product from products table to place in the sub form products box . I keep getting the error message join key of table details not in record set I have checked my relationships and for keys and there all there .
View RepliesI am fairly new to access, and have the following problem with the attached new DB. Thought I have designed the start of a good database with a "Junction Table". Have 1 problem, cannot enter a new name on the from, gives the error below:
"Join key of personnel table not in recordset" ...
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'.
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
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 RelatedI have some code. I want to copy all columns from
Code:
tabela_zrodlowa = "tblGoraZlecenia"
but only one column which is called "Id_rodzajpracy" from
Code:
tabela_zdrodlooffset = "tblOffset"
How could I modify red instruction to do inner join?
Code:
Dim rec_GoraZlecenia As DAO.Recordset
Dim rec_GoraZlecenia2 As DAO.Recordset
Dim rec_GoraZlecenia3 As DAO.Recordset
Dim tabela_docelowa As String
Dim tabela_zrodlowa As String
Dim tabela_zdrodlooffset As String
Dim s As String
[code]....
I have almost all of my info with the exception of few hundred pieces of data entered into my database. After looking it over and looking for ways to improve it, would it be easier to import all the tables into a brand new database and starting fresh with everything? I have made a lot of changes since the inception of the database so there are forms and queries and reports that no longer work because of the changes. Or should I go back and change them?
Also, if I am looking to join to tables can they be joined by their foreign keys? They are listed below:
tblEmpInfo
EmpInfoID <PK>
EmpIDFK <FK>
JobNumberFK (joined to tblJobs)
EmpType
CraftCode (would get rid of this)
tblEmpRating
EmpRatingID <PK>
EmpID <FK>
EvalDate
SuptID
StatusChange
CraftCode
I want to join the tables based on the EmpID can this be done or do I have to add another field to EmpRating ID?
I have the following VBA code to select certain records from table to recordset, however, sometimes no records existed and movefirst should give error message. Just want to know how to know there got no records in the TRANS2 in case no criteria is matched??
Set TRANS2 = db.OpenRecordset("SELECT * FROM TRANSACTION WHERE tcode = 't12345'", dbOpenDynaset)
TRANS2.MoveFirst
I have three tables see below
tblplantitem
tblplantgroup
tblplantservice
so I have frm called frmplant which holds the plantgroupname I.E plant type (airblow,leads) then I have subform call plantitemtblsubform which holds the plant item serial number, value ,service date which works great now my problem I have command buttom on subformplantitemsubform then opens form call frmpopupPlantService this opens to show plantitem service history but when I add a record then save close then click the same plant item to open form frmpopupPlantService show no service history even when I just entered ...
I have three tables that contain different columns but linked by a primary column call Name. I want to create a table where all these different columns in the three tables join to form a master table which can be updated regularly either through the master table or the smaller tables. The master table also has the primary column as Name.
If I update the master table with records, it should update the respective linked table and vice versa. I also want to link these tables to my SharePoint site.
Note: except the Name column, none of these tables have any other columns in common How do I go about this?
My form respectively subform has a couple of problems related to the recordset as it is available in VBA.
The mainform contains material data, the subform contains the components of that material and a quantity, while the components are materials themselves. The subform's control source is an SQL statement created by the query builder.
Everything worked fine before i replaced the material-selecting combobox in the continuous subform by a textbox and a button. That button leads to another (dialog) form with some filtering options, which in turn returns the number of the selected material. This material gets inserted into the textbox. To this point it works fine.
But when i enter a quantity before i selected a Material, i get an error message after selecting the Material: This Action was Cancelled by an Associated Object. This happens while executing the following VBA Code on click of the material selection button (exact position commented in code):
Code:
Private Sub cbuSelectComponent_Click()
' Select component
Dim SQL As String
Dim rs As Recordset
DoCmd.OpenForm "Material Selector Dialog", , , , , acDialog, "Dialog"
If GLB_selected_mat = -1 Then 'cancel
[Code] ....
I've found the following Microsoft KB Article: [URL] ..... In their example code they use:
' Restore text box value to the original record contents
' in this case, that is NULL
datDataCtl.UpdateControls
I assume this is the relevant part, but i have no DataControl (what's that?) and neither found an UpdateControls method in the subform object.
The second error, "no current recordset", occures when i edit an existing component line in the subform that has been added right before (also using the same event and code as mentioned above). If i close the form after adding the component and open it again, it's no problem.
I would like to get minimal value from "Ski" column. Need to use recordset instead Dmin function because this value will be changed many times. I work with dao.recordset but I never used recordset filter so I need to do something like this:
Code:
strSQL = "select tblGoraZleceniaNowaWyceny.id_gora_zlecenia, tblGoraZleceniaNowaWyceny.naklad_pracy, tblGoraZleceniaNowaWyceny.nazwa, tblGoraZleceniaNowaWyceny.Ski, tblMontazSzczegoly.iloscuzytkow from tblGoraZleceniaNowaWyceny inner join tblMontazSzczegoly on tblGoraZleceniaNowaWyceny.id_gora_zlecenia = tblMontazSzczegoly.nazwa where tblGoraZleceniaNowaWyceny.id_wycena_pre=" & Forms!frmWycenyObszarRoboczy!ID_wycena_pre & " order by error desc"
Wor = 5
Get the minimum value from strSQL (Ski column)
update this value = Ski+1
wor = wor-1
and loop all procedure until Wor = 0
So the problem is how to requery strSQL to show NEW minimum value at the beginning query - rst.requery doesn't work.
I am looking for a complete working example of a fabricated ADO recordset bound to a form. The purpose of this is to provide a selection tool for records of a table in a multiuser database.
I could do this using a table to store the selections in the db, but I want to get a tool I can reuse in the future, obviating the need for extra tables and keeping track of users and multiple copies of the application ran by the same user.
So far I can display MEMO text, all the other values of my recordset (yes they are present) generate #ERROR in the form controls.Yes I did google. Apparently I am not the only one with this problem, but the solutions range from bizarre to weird.
Code:
.Append "MailSubject", adLongVarWChar, 10000
' .Append "MailSubject", adVarChar, 255
.Append "MailBody", adLongVarWChar, 10000
.Append "MailID", adBigInt
.Append "MailTemplateID", adInteger
.Append "RevisionID", adInteger
.Append "SentOn", adDBTimeStamp
.Append "myField", adInteger
Any complete (with form) working example that includes at least one integer field in a record ...
Set TRANS1 = db.OpenRecordset("SELECT * FROM MASTER WHERE DatePart("yyyy", TRANS1![CDATE]) = " & 2014, dbOpenDynaset)
In the above recordset selection, the ACCESS seems do not allow to have the DATEPART("YYYY") function in the syntax. As CDATE is a table field with date/time data type. Just want to know, how to do this kind of date selection. My purpose is to select all records according to a specific date to the TRANS1 recordset.
Example of a fabricated ADO recordset bound to an ADO recordset here: [URL] ..... and it is reattached here.
I have been unable to discover how to add records using the form. So, what needs to be changed, so that one could add records just like with a normal form bound via DAO to a table?
I have a table that is basically a survey form. The same series of options was available for 35 questions, and the table used to have a text string written for each answer. Because of all the repetitive data, I created a second table that assigned a number value to each of the nine possible options in these 35 separate fields. What happened is that, instead of the same text strings repeated over and over (and taking up real estate), now each of the 35 columns had a single number in them.
Now comes the day of reckoning and TPTB want a query with the raw data and the original text strings back in instead of the numbers. I was thinking doing something along the lines of a DLookup, but I can't seem to make that work in a query correctly. Apart from calling the same table and linking it over and over to the different fields in the original data table (see photo for how insane that is).
I am creating a database for call quality monitoring. Just when I thought everything was working perfectly, I get an error saying the record set can't be updated. I can't find anything useful about it online either.
Here is my database:
I have a table which is essentially an template for call quality. each record has a primary key ID number. I also have a table for staff and their teams which is linked to the other table.
I have a form where you can select the staff member, and there is a tab control where the second tab has their quality results via a sub form. I have made the primary key a hyperlink so when clicked it brings up the quality form of the original record. however it can't be updated and gives me the message saying record set can't be updated.
I want the user to be able to click the hyperlink, bring up the record and edit where required.
Is this even possible?
I have a form with a continuous subform whose RecordSource is a predefined query. There are a number of controls on the main form which allow for various filtering on the subform / query and a command button to export the data to Excel.Because I want to export exactly whatever the user has filtered using the form, I take a clone of the subform recordset and pass that as a recordset object to my export function.
I've just noticed, however, that a couple of the fields in the Excel spreadsheet are always blank, even though the corresponding records on the subform show values? I can't figure out why; there is nothing unusual or distinctive about these fields (2 x Text and 1 x Date/Time) other than the fact that the contents don't seem to copy? I do get the field names - just no data for them?
The data is visible in the subform, and when I run the query on its own, the fields are populated in the returned dataset as well? So why aren't they included in the RecordsetClone?
I have 4 queries in which data needs to be connected from the date and shown as a single date showing each sections entry in a row and a cumulative total is maintained as the balance .
See the attached image ...
I've been building a small app for some friends. At present, they enter "records" line by line into a word processor, then use (archaic, IMHO) macros to produce the desired printouts. They wanted the new data entry UI to mimic that process.
Ok, so I've adopted the datasheet form as the principal entry vehicle. Based on the initial info they gave me, there were two convenient fields to sort on, so I used them in the OrderBy clause of the query which serves as the form's recordsource. I also developed the code to maintain the sort order after they inserted or deleted a record.
Now it turns out they don't require an entry into those fields, and in fact there's no logical field at all on which to order by.
So the question is: how to approach this? From my reading, I can't use the primary key or an autonumbered field, since the former won't necessarily reflect the desired incrementation and the latter only triggers for a new record, not an insertion (is this correct?).
My thought is to add a simple numerical field (which will be hidden from the user) with sequential values. I can do the OrderBy on it, and add code to adjust those values whenever a record is deleted or inserted. (That will require looping through the recordset from the point where the deletion or insertion occurred.)
For instance, is there some way to add a calculated field to the recordsource for this purpose?
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
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?
How do I join two tables. I have a table and a lookup table. My table has products on there that are listed as custom or basic. I have a look up table that has an ID for basic and Custom. In my table, i want it to read what the id is for each product instead of it reading "basic" or custom. Someone said that I need to join the two tables and do an update query, but I don't understand how to.
View 2 Replies View RelatedQuick question so i can clairify what I am thinking. I have two tables. Both have a job code fieldname in them. What I wantto happen is where the job code in table 1 = job code in table 2 display the job description in table two. Example
table 1
name | mo | date | code
table 2
code | job description
If i understand this correctly I would need to do a table join correct... somthing to the order of SELECT * from table2 where me.table1.code = me.code? I'm not entirely sure. Am I understanding this correct?
SELECT tech_id.CORP, tech_id.TECH, tech_id.TECHCONT
FROM tbl_PPVResearch INNER JOIN tech_id ON Mid([tbl_PPVResearch].[AccountNum],1,5) = tech_id.CORP;
im having problems updating a table. im trying to take a column from table2 and place it in table one...i tried exporting it to excel, copy and pasting it and what not, but since it has about 200k values, it only copied half, incomplete...
i tried this query, i dont know if im doing it right.
SELECT DlexLoad.pah, DlexLoad.doc, DlexLoad.vol, Bate.BATES
FROM DlexLoad
INNER Bate
ON DlexLoad.Bates = Bate.BATES
im trying to replace the Bates column from Dlexload table, with Bates from bate table...
i attach a screen shot of my tables
thanks