I'm doing a TAFE assignment (I'm from Australia) and I have tried to populate a field in my subform called RestaurantID with data and it has given me the error'Cannot add record(s) join key table Restaurant not in recordset.
View RepliesBasically, 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 have been asked to maintain a directory for our local Scout district. It's currently in a spreadsheet, but is crying out to be a database.I have created a "group" table, this lists all the groups in the district.I have a 2nd table called "people" this holds details of all the people in the district.As some people can have a role in multiple groups, I have created a 3rd table called "link", this is to link the people to groups.
Having read various different posts on sub forms, I am still at a loss on how to create a form / subform to populate the link table.My initial thoughts were to have a combo box on the main form, listing the people and then have a list of groups on the sub form (the are only 24). I would have a check box next to each group and if checked, it mean that the person selected in the combo is associated with that group.However my issue is that the tick box would be a "yes/no" field and the entry in the link table needs to be a number.
I have 5 different tables that are all linked with foreign keys. When I made my fifth table and linked it to my primary key like I had with the other tables I got the Join expression not supported error.
View 14 Replies View RelatedI have a corrupt record in a table that I am trying to delete. I keep getting the following error when I try and delete the record: "The search key was not found in any record".
Can anyone help me with this. All I am trying to do is delete this one record
Thanks!
I have a separate button to update the record, but when I run or click the cmdUpdate button it gives me an error;
Code below is the one I'm working thru.
Private Sub cmdUpdate_Click()
strSQL1 = "UPDATE tblAMHMace " & _
" SET ReferenceNo=" & Me.txtReferenceNo & "'" & _
", DateLog='" & Me.txtDate & "'" & _
", DocType='" & Me.txtDocType & "'" & _
[Code] ......
I have a main form with several subforms in tabs. From one of the subforms I list linked records to the main form (Clients) within that subform (Bank account details). I list the records and have a button to add new records.
Where the Client has one or more records in the subform the add button works perfectly.
When the subform has no records the add new records button produces the following error "Run-time error '2498' An expression you entered is the wrong data type for one of the arguments"
The add button has
Private Sub Command52_Click()
DoCmd.OpenForm "AddClientBankDetailsFrm", acNormal, , , , acFormAdd, OpenArgs:=Me!ClientId
End Sub
the "AddClientBankDetailsFrm" popup form has
Private Sub Form_Load()
If IsNull(Me.OpenArgs) = False Then
MsgBox "Form was opened with ClientID = " & Me.OpenArgs
Me!ClientId = Me.OpenArgs
Else
MsgBox "No ClientID was passed."
End If
End Sub
ClientId is the primary key of the main form and the secondary key in the new record.
error message I am getting when I click on my Duplicate Record button (created through the wizard).
I have two combo boxes on the main form that populates data when the user makes a selection from the combo box. First combo box populates project data and the second combo box populates equipment data. The form is working well with the two combo boxes populating the data into the main table.
Now I would like to add a duplicate record button to copy a record and paste the data as a new record. So, I added a duplicate record button using the wizard and I am receiving the following AfterUpdate error.
Run-time error 3020: Update or CancelUpdate without AddNew or Edit.
This is the code I am using to copy and paste a duplicate record:
Private Sub InputForm_DupRec_Button_Click()
On Error GoTo Err_InputForm_DupRec_Click
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunCommand acCmdCopy
DoCmd.RunCommand acCmdPasteAppend
Exit_InputForm_DupRec_Click:
Exit Sub
Err_InputForm_DupRec_Click:
MsgBox Err.Description
Resume Exit_InputForm_DupRec_Click
It seems to be duplicating the data from the first combo box, but not the second one where it errors out.
The below SQL is basically trying move a record from one table to another, the table a copies of one another and the AlphaName variable is a string.
I keep getting the incomplete query error, somethings missing but what.
Code:
strSQL = "INSERT INTO [Holding Table].* SELECT ([Import Table].* FROM [Import Table]
WHERE ([Import Table].[Alpha Name] = '" & AlphaName & "'));"
I am having a problem with my Contacts Database. I have attached it for your review.
My main form entitled "frmContact", is a form to enter a new contact. I have a Notes subform, and I want to be able to enter multiple notes for one person. I have a note for John Doe, and tried to enter a new note for him and got this error message:
"The LinkMasterFields property setting has produced this error: 'The object doesn't contain the Automation object 'tblcontact"."
Why is it doing this? I tihnk I have the relationships correct, and everything...
My database was working fine. Now, if I have to save or edit a record, I get a message Run time error 2107. The value you entered does not meet the validation rule defined for the field or control. I am able to enter and edit new records in the table. I did not change any validation in form. Then it goes to debug mode and highlighted this code (me.recordsource=Listsql - is highlighted)
Private Sub ListSearch_Click()
Dim dBS As Database
Dim Rst As Recordset
Dim Listsql As String
Set dBS = CurrentDb()
Listsql = "Select * from MasterData where employeeid ='" & [Forms]![MASTERFORM]![ListSearch] & "'"
[Code] ....
I have a form that using "Query A" as data source. The Form need add (edit) a field value before save that Query Result to another Tabel. Is it possible to do that?
The PROCESS simply like below: Tabel A --> Query A --> Form -->Edit value a field -->Save to Tabel B
if it is possible, are there some requirements that have to be fullfilled?
I have created three tables, all of them are connected by one-to-one relationship by same field, as you can see in the screenshot. and at the same time I have created three forms for each table. then I brought two forms in one remaining form. so whenever I entered data in first form and click on the next tab in which another form exist, it gives me this error: "you cannot add or change a record because a related record is in table".
View 2 Replies View RelatedI 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?
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 have two tables with a one to many relationship. The tables are linked by the INDEX column.
EXAMPLE:
Code:
TABLE_1
INDEX NAME
1 Name_A
2 Name_B
3 Name_C
TABLE 2
INDEX NUM_INDEX STATUS
1 1 REJECTED
1 2 REJECTED
1 3 OPEN
2 1 CLOSED
3 1 REJECTED
3 2 OPEN
I need the NAME field from TABLE_1 and the Last STATUS field from TABLE_2 (MAX of NUM_INDEX).
Example:
Name_A, OPEN
Name_B, CLOSED
Name_C, OPEN
SQL that I have now.
Code:
SELECT A.FIN_Finding_Number, B.Max_Index
FROM TBL_Findings AS A INNER JOIN (SELECT RES_Finding_Index, Max(RES_Response_Index) As Max_Index
FROM TBL_Response GROUP BY RES_Finding_Index ) AS B ON A.FIN_Finding_Index = B.RES_Finding_Index
WHERE (((A.FIN_Finding_Index)=34));
This SQL statement will return me the Finding_Number and Max_Index. I don't need the Max_Index. I need the Status. If I put the Status in the Sub-Query and GROUP BY it, it will return both REJECTED and OPEN. I just need it to return OPEN.
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?
I have 2 tables. One has employee info and the second has time data based on thresholds reached in the employee info. Obviously, a basic join will bring in all of the threshold records. How do I get it to only pull in the one that satisfies the threshold condition?
View 5 Replies View RelatedHow 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;
I have a form that has a button that calls the following code:
Sub ClosePO_Click()
Dim db As DAO.Database
Dim mvalue As String, strSql as string
Set db = CurrentDb
mvalue = Me.Combo73 'combo box on OpenPO Form
strSql = "UPDATE Print SET OpenPO = NO where [GPO Invoice Number] = '" & mvalue & "'"
Debug.Print strSql
db.Execute dbFailOnError
db.Close
Set db = Nothing
End Sub
When I run (or step through the code, I get the Run-Time Error 3078 ... database cannot find the input table.However, when I copy the debug.print output in the immmediate window and paste into the query builder, the query runs. Here is an example of the debug.print output:
UPDATE [Print] set OpenPO = NO where [GPO Invoice Number] = '40333'
where OpenPO is a yes/no field and [GPO Invoice Number] is a string
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
Have some problems with a Purchase Order (PO) System I'm trying to do.
Figure 1 is the PO entry form. The upper part of the form shows the Header, while the bottom part shows the Line, containing all the ordered item details.
I separated the information keyed in into 2 tables, namely Header table (Figure 2) and Line table (Figure 3). The header table shows only the header details. With the order ID, the header is linked to the Line table where all the ordered items detail can be found.
I want to join the 2 tables and put the data into a new table, which will later be exported to text/spreadsheet for other purposes. I know it can be done using the make-table query. But my concern is about the layout/format of the table.
I would want to put my data where there's one header row followed by the corresponding line rows. Then another new header row with its line rows. I'll give an example:
H XXX XXXXXXXX XX X
L XXXXX XXX XX
L XXXXX XXX XX
L XXXXX XXX XX
H XXX XXXXXXXX XX X
L XXXXX XXX XX
L XXXXX XXX XX
L XXXXX XXX XX
L XXXXX XXX XX
Note that not all the fields for Header row and Line rows are the same. So can I put different data into the same column?
Would appreciate it if anyone could solve my problems. Thanks. ;)