Forms :: Runtime Error When Adding A New Record From A Subform?
May 12, 2014
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.
I have an unbound form: frm_ReceiptSearch with some fields that, when something is input, will search a datasheet viewed form on a subform on the main form (subform name is sf_frm_Receipts). Currently, on the txt_CheckNo field's after update event, I have the following code:
Private Sub txt_CheckNo_AfterUpdate() Dim SrchVar As String SrchVar = Me.txt_CheckNo Me!sf_frm_Receipts!REFNO.SetFocus DoCmd.FindRecord SrchVar, acEntire, , acSearchAll, , acCurrent, True End Sub
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] & "'"
I have a form with a subform (in continuous) I have on the main form a few unbound text boxes which once im happy with the results i press a command button on mainform which adds the unbound text boxes to a new record on subform im struggling with this one. I have done similar before but that was with the unbound text boxes in header of the actual form i was adding record too.
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.
I have an 'add new order' form which opens with all the fields blank and focus set to a combo box. The first thing the user must do is select a contract name or number from an unbound combo box. I then want the form to update and show all the relevant information in the form fields.
In the row source of the combo box I select all 3 fields from the same table (Project_Info). Only the contract number is visible all others are set to 0cm width in combo box properties.
In the afterupdate event I have the following code to set the field to the values from the select query
My code looks like this: [code] Private Sub ProjectContractNum_AfterUpdate() Dim sOrder_idSource As String Dim rs As Object ProjectName = ProjectContractNum.Column(1) proj_status = ProjectContractNum.Column(2)
End Sub [end code]
My SQL in rowSource looks like this: SELECT Project_Info.proj_name, Project_Info.proj_contract_no, Project_Info.proj_status FROM Project_Info;
Access tells me that I don't have a join key in my record set. message reads as follows:- Run-time error '2147352567 (80020009)': Cannot add record(s);Join key of table Project_Info' not in record set.
Debug highlights this line: proj_status = ProjectContractNum.Column(2)
'proj_status' is the name of the text box and the control source.
If I just use just the first 2 fields (project number and project name) these two work!!
I currently have this set as the forms default recordsource (which works just fine):
Code: SELECT TOP 5 tblUsers_Phone_Book.EMAIL_ADDRESS, weightedDL('me@mine.com',[EMAIL_ADDRESS]) AS Expr1
And I have this vba to dynamically switch around that email address.
Code: Private Sub Form_Load() Dim intPos As Integer Dim strControlName As String Dim strValue As String Dim sSQL As String If Len(Me.OpenArgs) > 0 Then ' Position of the pipe
[Code] ....
If I msgbox the sSQL - it shows identical to the default recordsource but I get the error:
The error message I get is:
Run-Time error '2580' The record source 'SELECT TOP 5 tblUsers_Phone_Book.EMAIL_ADDRESS, weightedDL('me@mine.com',[EMAIL_ADDRESS]) AS Expr1' specified on this form or report does not exist.
I tried copying the exact working default sql into the vba and get the same result.
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...
I have a Main form (MainForm) with two subforms (SFormA, SFormB). These SForms are connected to the MainForm by a control (controlA) and connected to each other by a control (controlB) via an unbound control in the main form, in order to change data in SFormB according to selected record in SFormA. SFormB has an on current event procedure determined to show different things depending on the value of a bound control that changes every time I select a different record in SFormA. Everything works fine when Mainform and SForms first load.
But, when I select a record on SFormA I get a runtime error and everything stops, application closes and reopens, creating a safety copy. It seems to me that the on current procedure on SFormB causes this problem, because if I delete it no error appears (SFormB not showing what I want though). Funny thing is I have another database, same design, same structure, same procedures, (different objects and names of course) and it works perfectly alright. I even recreated the whole database still the same error.
While I have developed many databases using other RDBMSs I'm afraid I'm something of an Access 2007 newbie so the vast bulk of my experience counts for nought given Access' many "unique features".
What I have is a form with about 3 text fields, 2 date fields and a number of comboboxes on it. Although, for now I'm not worrying about the combo boxes until I get the other fields working.
I am trying to develop an update screen where the user types the primary key value for a record into the first field and using DLookup retrieve the other values from the table and insert them into the form so the user can adjust them if they wish and click the Save button to write the changes back to the table. That's the theory.
Unfortunately the reality is, although the values added to the other fields using DLookup are "there" they don't appear on the screen until the controls lose focus. My best guess is because they are being added programatically and no Return key is being pressed, focus will not change and they will never appear.
So to get around this I put a line of code in the GotFocus event of each control to move focus on to the next control, effectively "looping" the focus once around the controls. This worked well until the focus was being moved from the second last control to the last control when I got, Runtime error 2110: Can't move focus to this control. The control was visible and enabled, as was a dummy control I tacked on the end to see what happened.
Main code block in Exit event of first field:
Private Sub Text6_Exit(Cancel As Integer) Dim varItemType, varItemTypeCode, varItemClass, varItemClassCode, varDescription As Variant Dim varPurchaseDate, varWarrantyEnd As Variant Dim varItemTypeTxt As AcTextFormat Dim PurchaseDateSQL As String
I was trying from a button on my form that will go to and bring up the desktop. I want it so I can put this database on anyone's computer and the button will work.
I am using this and it works but only on my computer.
Application.FollowHyperlink "C:UsersBryanDesktop"
I also tried this
Application.FollowHyperlink "C:Show Desktop.scf" And Application.FollowHyperlink "C:WINDOWSSystemShow Desktop.scf"
Both gives Runtime error 490 cannot open specified file.
Run into a bit of an issue with a combo box that has dates. When I click on a date I receive:
Run-time error '3464': 'Data type mismatch in criteria expression'
Am I receiving this because I have declared it a string when I should declare it something else?
My other question for whenever I get the first part fixed. Is there a way that instead of having all the possible dates to select from, that I could simply have three options available: Within a year, More than a year, and no date. (Once I click on one of those three options, I will filter records that have a date within the past year, over a year, or no date at all. How would I be able to achieve this?
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
Having set a table short text field to 'Indexed (No Duplicates)', I have a form which produces a '2105 runtime error' when the user attempts to submit a duplicate value. I would like to replace this default error message with a more user-friendly MsgBox.
My code for the SaveRecord button is:
Code:
Private Sub SaveRecord_Click() DoCmd.GoToRecord , , acNewRec MsgBox "Record successfully saved", vbOKOnly + vbInformation, "Record Saved" End Sub
My code to capture the 2105 runtime error is:
Code:
Private Sub Form_Error(DataErr As Integer, Response As Integer) If DataErr = 2105 Then MsgBox ("This villa booking has already been logged.") Response = 0 End If End Sub
Unfortunately when the save button is clicked (when attempting to save a duplicate value), the 2105 error still runs. What are I doing wrong?
I get an error message when I add a new record to my database. It occurs when I open my form and use the [arrowright*]-button and when I use the switchboard button to add a new record using a form.
An error message is displayed, but when I dismiss the error (click OK), then everything seems to be normal and the record is added normally with the entered info. It is a bit annoying, though.
Since I have the dutch version, I'll try to translate the message: "You can not add/append a value to this object
* The object possibly is a control-element in a read-only form * The object is part of a form that is opened in the design view * The value is too large for this field"
The error occurs right after I have typed the first character into a field on the form. The form consists of a few subforms. And I don't think any of the by access suggested errors have anything to do with this...
Any help is appreciated, Thanks in advance, MuFfiNimal
I have inherited a partially completed membership Access database which I understand was originally generated from a load of linked FoxPro tables. There is a form which uses multiple tabs, the first tab contains general membership data (ie name, age etc) and this pulls data through from the 'Main' table in the database. Each of the other tabs includes a subform which shows data held in a number of different tables linked to the 'Main' table (eg, first tab - general data from 'Main table', 2nd tab - home addreses from 'Home Address' table, 3rd tab - business addresses from 'Business Address' table). All tables are linked to the 'Main' table through a common 'RegisterID' field. When I open the form I can query on any of the records and amend and items of data on the various tabs them without any problem. However if I add a new record using the >* option from the record navigation I can add data to the first tab (ie. into the 'Main' table) but if I move to the 2nd or 3rd tab I am unable to edit/add data to the fields on those tabs. But if I save the record after completing the fields on the first tab and then close the form and reopen it I can then edit the 2nd and 3rd tabs, can you suggest how I can change the setup to allow editing/adding data to the 2nd and 3rd tabs without having to close the form and reopen it please. I have checked the subforms and they are set to AllowEdit and there are NoLocks.
I want to add a record with attachment to a table in an access (2013) database.
If I ignore the attachment error everything works as it should, but as soon as I add the code for the attachment I receive error code 3259.
Code:
Private Sub add_tune() Dim dbtune As DAO.Database Dim rst_tune As DAO.Recordset Dim rst_tune_in As DAO.Recordset Dim rs_child As DAO.Recordset Set dbtune = CurrentDb
[code]....
Since I have a dutch office version the error text (ongeldig veld gegevenstype) is in dutch, translated it means something like a data type mismatch.
t_first_bars is a field in the table tbl_tune . The field type is attachment.
Hey everyone, This seems like a great community for access - I'm glad I stumbled across here because I have some issues that need figured out.
I've searched the forum for answers and I have found a few posts with suggestions, but nothing has worked yet. (by the way, I'm fairly new to access, but not to databases in general).
So here's my problem, and I appreciate any help in advance.
- I have a main form and 2 subforms on the main form. The main form contains the information of thousands of Titles to different sections of land. The one subform then has multiple "requirements" per Title. So i have a many-1 relationship between the requirements and titles. On the main form, there is an edit button - so everything is locked until the user hits the edit button, and then the main form becomes editable. The requirements subform does not - it stays locked even though in my code I have "Me![Requirements Subform].Locked = False"
But this is not my main concern right now. What gets me is that I can not add a new record from the requirements subform. For example, Title 1 (T-0001) has 11 different requirements. I can use the navigation to browse through the requirements but I can not add a new requirement from the navigation buttons. This is a main concern.
Another main concern is that when I add a new Titles record from the navigation buttons, my requirements subform disappears from the form.
I hope I explained my situation clearly! if anyone needs any clearification, let me know.
I am currently working on an Access Database that houses our security clearance information. Most of the system is up and running but the most recent form has got me spinning my wheels. I have a Word User Form that users will download and complete, once completed the macros will automatically send us the document to be added to our database. Most of this is working the problem is that this portion of the database has multiple related tables and at any given time a user may require multiple records in the related tables. I have created the code to copy most of the information but am getting stuck adding a new record on the sub-form when multiple items are required. Here is a breakdown of the scenario
Word Doc Table 1 = Basic Organization Info Word Doc Table 2+ = Sites to Visit (There could be more than 1 table added here) Word Doc Table 3+ = People to go on site (this might not be the third table based on user interaction for sites)
So far I can get Table 1 and Table 2 data but if there are more than 1 site I cant seem to get the system to create a new record on the related table it is creating a new record on the main form. Here are the lines I used to try to create the related record..
When I put just the above code on a button it seems to have worked as the sub-form showed an additional record was created but when using this on my macro the sub-form is not taking the focus for some reason.
I have two unbound unlinked subforms residing on a 3rd unbound main form. When I enter the current record on Subform1 I would like the matching record(s) on Subform2 to be highlighted or otherwise formatted. I can get this to work for only the first record on subform2 due to the way I have my code setup on Subform1:
Code: Private Sub Accounting_Unit_Enter() 'find where AUs match. only works for first Subform2 record If Me.[Accounting Unit] = Forms!MainForm.[Subform2]!AccountingUnit Then msgbox "Match" End If End Sub
My thought was that I needed to reference the Recordset of Subform2 and search for all AccountingUnits that match the current AccountingUnit of Subform1,
I have a parent form which has a class variable (class module instance) to store the form' status and more.... and when i add a new record to the subform it resets the class variable field' data. but this only happens on first transaction, but if i re-run the steps (re-set the variable field value) it's not happening again.
