Modules & VBA :: Find Record In A Subform / Getting A Runtime Error 438?
Feb 26, 2015
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
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 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.
Access 2010 on both Windows XP and Windows 7..I have an application which USED to use a postcode address utility that created an Activex. I have removed all references in vba to the utility but Access runtime gives an error 'cannot create Activex' on my clients PC's (both with Windows XP and Windows 7).On my own PC the error does not show with either full Access or simulated runtime. I have tried decompiling the database and even using the old trick of copying the modules code into Notebook and pasting back, but to no avail.
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
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'm trying to delete a file in a network location. Sometimes the file is there, sometimes it's not, so I'm using the following code:
Code: strPath = "etwork driveMy Folder" strFilename = "MyFile.doc" If Not Dir(strPath & strFilename) = "" Then Kill strPath & strFilename
Occasionally, I get a runtime 52 error when the file doesn't exist. I don't understand why I'm getting this when, if the file doesn't exist, the Dir function should return "".I searched the forums for Runtime 52, and didn't find anything pertaining to this.
Why I get a runtime 3134 error on this piece of code.
They are all text values
Code:
Dim strUserName As String strUserName = Forms!FrmPrimaryData.FrmPrimaryDataInstallsSubFrm.Form.txtMacAddress Dim strIDValue As String strIDValue = Nz(DMax("[ID]", "radreply"), 0) + 1 Dim strAttribute As String
Basically I have a form where a user has selected a couple values from a dropdown element, and entered a few other text values into the form. I am then trying to grab the values and append them to a table.
Code: Private Sub btnSubmitInputVendorPerformanceForm_Click() 'Button Is part of frmInputVendorPerformance Dim ValueCheck As Integer 'Increments to make sure no errors in entry Dim ErrorShow As String Dim YesorNoAnswerToMessageBox As String Dim QuestionToMessageBox As String Dim DeliveryRate As String
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 am currently creating a form which includes a command button to update a field in a table to show Today's Date, at the time of running a query I am still yet to write.
I have the following code for my Command Button, which is returning the Runtime Error 3464 (Data type mismatch in Criteria Expression):
Private Sub cmdAddTodaysDate_Click() CurrentDb.Execute (" UPDATE tblMyTable SET TodaysDate = " & Date & " WHERE tblMyTable.ProductCode = " & Me.txtProductCode) End Sub
The query above seems to be having issues matching the text from the 'txtProductCode' textbox to the table field 'ProductCode'.
When I try to open a recordset based on this sql, it gives me the runtime error - which is odd since I don't have any criteria in the statement.
I think the problem may be that vba is somehow adding a line break between "fullvals" and "18", but I don't know why it would do so and it doesn't always (only if the string is long).
In my database I am trying to produce a "Statistics" function. As part of this, the user will enter a "Start Date" and "End Date" in a form and then click a button which will open the requested report with the date drawn from a query. The code on clicking the "All Jobs" button is:
I am trying to stop the user from leaving the date fields blank or entering dates outside of the range of the database so I have tried the code:
Code: Private Sub AllSalesEnquiries_Click() If Me.DateFrom = "" Then Beep If MsgBox("You have not entered a start date", vbCritical, "Start Date Not Entered") Then
[Code] ....
If the user enters dates within the range of the database the report is presented correctly.
If the user does not enter a date or enters one outside of the range it produces the correct message box however if the user then corrects the mistake I receive a Runtime Error 3071 message. Clicking "Debug" highlights the final line of code:
I know that the code is correct because it works fine as stand alone code and it works if the user enters the correct dates so I am not sure where I am going wrong.
The date format works perfectly for the way dates are formatted in the database.
I have managed to sort out the data to be used in the final table. However, I am having trouble transferring the data from each of their own tables into the final table.
Each time I run my code I receive "run-time error 3021: No current record."
It seems that only my timestamp is being added properly, but the error pops up and highlights the first "rstInsert.Edit" of my code. I'm suspecting that my function is running too fast, such that it did not have time to read that the table has already been populated by the timestamp in the AddNew code
Code: Private Sub Command9_Click() Dim dbs As DAO.Database Dim rstTimestamp As DAO.Recordset Dim rstAcknowledgement As DAO.Recordset Dim rstAgent As DAO.Recordset
I'm using Access 2010 and I want to export a query to Excel by clicking a button. Every time I click on it Excel opens but doesn't open my worksheet, it's just gray like you just open the program without a table. After exiting it Access tells me I got a run time error 1004: the open-method of the workbook object couldn't be executed. After opening my Excel file it says that Excel found unreadable content and asks if I want to restore the content of the workbook.
Here is my code:
Code: Private Sub Befehl62_Click() Dim cdb As DAO.Database, qdf As DAO.QueryDef, SheetName As String, xlApp As Object Set cdb = CurrentDb Forms!Export_to_Excel!txtSheetName.SetFocus SheetName = Me!txtSheetName.Text
I have a database which has a main form and subform built in linked by parent/child customerid, what i would like to do is search all the subform records from the whole DB and return its parent record on the main form?
Can this be done? because if i use find it will only search the filtered form i have onload of the form?
I have a form with a form header section that contains a combo box and a text box. The form also has a detail section that has a tab control that contains the rest of the data entry boxes. I put a Find Record button in the detail section of my form. When I try to Find Record using the combobox or textbox values in the form header, I get an error saying it cant search the tab control, use the Gotocontrol action before the Find Record Action, change current field action from yes to no, or change the focus to a searchable control. If I search items in the detail section I do not get errors. The code created by the Find Record button is below. I'm not sure how to get it to set focus to the appropriate box in the form header section if that is the field I am searching on.
Private Sub cmdFind_Click() On Error GoTo Err_cmdFind_Click
I built a form—frmDataEntry—whose Record Source is a query called qryEvent, which contains various fields from tblEvent. The primary key field is called EventID.
I also build a subform—sfmDataEntry—whose Record Source is a query called qryEventImages, which contains various fields from tblItem. One of those fields is ItemEventLink, which links records from tblItem to the EventID field in tblEvent. There is also an image box in this subform: filling in the field ImageFile with an image name (example: Logo.jpg) causes that image to display in the image box.
I’ve inserted the subform into the form using the Subform Wizard. Now I’m getting an error message that says "2450: Microsoft Access cannot find the reference form ‘sfmDataEntry’." Why this is popping up. The Link Master Fields and Link Child Fields sections of the property sheet are filled in correctly. Moreover, the subform records are still appearing, but the images linked to each record aren’t loading.
I have a form for accounts, which has a subform listing the customers within each account.
On the customers form I can use the wizard to add a combo to find a record, but if I want to add a similar combo within the subform, the option does not appear in the combo wizard.
Can anyone enlighten me on why/how to resolve?
Many thanks
Mark
(using Access 2003 on an Access 2000 format db, Win XP Pro SP1)
On a from ("Customers") with tabs I have on the second tab a subform ("Orders") as a single form with a field "Ordernumber". "Customers" and "Orders" are linked through "CustomerID".
On the mainform I have a "searchfield" in which a enter the Ordernumber, I want to find.I cannot manage it, that After_Update of the "searchfield" the ordernumber is found on the subform and the focus is on that subform.The focus only shows me the CustomerID on the first tab, but does not jump to the second tab exactly to thes searched ordernumber.
Code:
Private Sub searchfield_AfterUpdate() Dim rs As Recordset Me.CustomerID.SetFocus Set rs = Me.Orders.Form.RecordsetClone rs.FindFirst "ordernumber = " & Me.searchfield If Not rs.EOF Then Me.Orders.Form.Bookmark = rs.Bookmark End Sub
how to deal with errors specifically with error "The Microsoft Access database engine cannot find a record in table".Database which I'm creating have table Products, which contains information about products. Most important information in this table is if product is VATable or not. I decided to define product names in invoice table that database can recognize products and calculate VAT in invoices if product is VATable. Problem starts if you have new product. When I add new product in invoice it counts VAT but when i go to next record I receive message mentioned above. It doesn't allows me to go to next record or save record.
I was wondering is it possible to make it that access allows to fill invoice, informs about new products only when i have finished adding all products in invoice and than gives opportunity to add them to product list?
i have the following code and i have the below error:
can't find the project or library in red part
Code: Option Compare Database Option Explicit Function DatumUmwandeln(Datumstring As Variant) As String Dim Monat, Tag, Jahr As String If Datumstring <> "" Then Tag = Format$(Datumstring, "dd")
