Modules & VBA :: Comparing Two Recordsets - Sub Or Function Not Defined
Sep 2, 2013
When I try to compile the code below I get the error message 'Sub or Function not defined'. I'm using Access 2007.
Code:
For x = 0 To 20
If Nz(rec1(x), "") <> Nz(rec2(x), "") Then
DoCmd.SetWarnings False
strSQL = "INSERT INTO tblResults ([Version], CUSTOMER_ID, CUSTOMER_NAME, CUST_LONG_NAME,
[Code] ....
What I'm trying to do is compare two recorsdsets and if it finds a difference, record the result line from both recordsets in a new table. This worked fine until I tried to go beyond more than 21 records from recordset 2 to publish to the Results table.
The problem arises in the 2nd INSERT INTO... statement. I get the error and it highlights the rec2(20) value. I can enter anything after it (it doesn't have to be a recordset value) but the error still occurs and it wont compile. If I close the statement at Rec2(20) it works fine.
I have quite a few user defined / custom functions which I would like to be available in any new database that I create without have to copy the VBA codes in a module each time. Basically, some sort of add-in that loads on MS Access startup may be (but I am not too sure - never created one). So something that will start with the MS Access Application and not just a particular database. I know excel addins can be copied to the XLStartup folder to achieve something like this in excel, but not sure about Access.
I am trying to use the Environ Username function, which i have working! But i want to compare the result with a Username Table to determine if they have access etc...
I copied some VBA from one database to another. I didn't change anything and I am able to run it fine in the first database. But in the DB I pasted it to, I am received a Compile Error message with the XlApp As Excel.Application area highlighted.
Function OpenAutoCount() Dim xlApp As Excel.Application Dim xlWB As Excel.Workbook
I have used Allen Browne's code (see http://allenbrowne.com/ser-16.html, bottom of page) as a model for my user-defined type and public function in an events management database.
Using the function in forms/VBA works fine.
But my DB crashes whenever I try to use it in a query.
My modules code: = = = Option Compare Database
Public Type EvCheks EvType As String EvAttCat As String EvUnitAss As String EvCheksAll As String End Type = = Public Function getEvCheks(EV, EvUnit) as EvCheks
'Event Type: Event or DL If (DLookup("evtype", "tblevents", "[evid] = " & EV)) = "Event" Then getEvCheks.EvType = "Y" Else: getEvCheks.EvType = "N" End If
'Event Attendance Category: INDB= in database or LIST If (DLookup("evattcat", "tblevents", "[evid] = " & EV)) = "INDB" Then getEvCheks.EvAttCat = "Y" Else: getEvCheks.EvAttCat = "N" End If
'Event Assessing Organisation Dim AOROName As String Dim AOName As String AOROName = DLookup("evunitassessable", "tblevunits", "[evunitid] = " & EvUnit) AOName = Mid([AOROName], 1, InStr([AOROName], "/") - 1)
Select Case AOName Case "NA" getEvCheks.EvUnitAss = "N" Case "ABCD" getEvCheks.EvUnitAss = "Y" Case Else getEvCheks.EvUnitAss = "X" End Select
getEvCheks.EvCheksAll = getEvCheks.EvType & getEvCheks.EvAttCat & getEvCheks.EvUnitAss End Function ==== On a form, I have a button with on-click event code that defines the EV and EVU variables and displays a message box MsgBox getEvCheks(EV, EVU).EvCheksAll
This works fine. ===== In a query I have this SQL code: SELECT tblEvUnits.EvId, tblEvUnits.EvUnitID, getevcheks([evid],[evunitid]) AS EventDetails FROM tblEvUnits;
This causes the db to crash.
I cannot get the "back-end" definition of the function into the query, as Access rejects it because of the dot, i.e. ".EvCheksAll".
I suspect there is some formatting quirk for using a public function that includes a user-defined type, in a query.
I have created user defined function for lastdayofweek, lastdayofmonth, and firstdayofmonth. When I apply this function to an expression in my query, it returns a julian date. How do I apply the format for the calendar date?
Maybe I am not calling my function correctly. Here's my functions below as well as my expression..
Function LastDayThisMonth() LastDayThisMonth = DateSerial(Year(Date), Month(Date) + 1, 0) End Function
Function FirstDayThisMonth() FirstDayThisMonth = DateSerial(Year(Date), Month(Date), 1) End Function
I have a form based on an underlying query in a .mdb database. I want to be able to navigate backwards and forwards in the form using .movenext and .moveprevious etc.
Which sort of recordset should I use? I've seen discussions about ADO and DAO, and have come across the term "forward-only", which I'd not been aware of before.
Having made the choice, what is the correct syntax for opening the appropriate recordset, and also for .movenext and .moveprevious etc?
I have written a user defined function that calculates the end of the current month. This I named EndOfThisMonth. It works well as a function. Now I would like to use it as date criteria to include in a query. The function is included as such EndOfThisMonth().
The field on which this function is to enter as a criteria is another calculated date function called Due.
When I run this query I get an error message saying Undefined Function 'EndOfThisMonth' in expression.
I have 3 tables; tblProjects, tblTesting, and tblEmployees.
The issue I'm having is that I've created a form which collects testing information from testers that test a particular project. Each project can have multiple testers. tblProject and tblTesters have a one to many relationship and the form (frmTesting) consists of a main form for project info and a subfrom for testing info.
For each project tested there are 4 possible outcomes. 1st not all testers have tested to project and that case project status is "InTesting", 2nd all testers pass testing in that case project staus is "Waiting Final Approval", 3rd all testers fail testing in that case project status is "Maintenance" and last some testers pass and some fail in that case project status is "IPR".
Here is my code for a button I've placed on the sub form.
Private Sub BtnTest_Click() Dim db As DAO.Database Dim rsSQL As DAO.Recordset Dim intMax As Integer Dim strSQL As String Dim csSQL As String Set db = CurrentDb()
[Code] ....
My select statement for my recordset isn't working and I've tested it by putting a specific project number in the statement and I still only pulling one recordset.
Is there any way of opening recordsets as public or global in a form? E.g. if i do something like
Set rs = db.OpenRecordset("TblCustomers", dbOpenSnapshot, dbReadOnly)
the variable rs should be available in all the private sub i have on that form, and i should be able to access records without opening database/ record sets for individual subs.
I have parameter form that ask for state and begining date. on click it runs a query and creates a report by state that has multiple pages.. I want to create a separate pdf file for is page (which would be by delegate_name).
This is the coding I have so far and am gettting the error '424' object required. This is the coding I currently have. I haven't worked with access in over 9 yrs so am a little rusty with it.
StateAbb = Me.State PeriodStartDate = Format(Me.Period_start_date, "MM-DD-YYYY") Dim strSQL As String Dim db As Database Dim rs As Recordset Dim strRptName As String
This is giving me the error 'User defined type not defined'. I know I have to set something in a list somewhere but have forgotten how to do that. Where to go, and what to set?
On Compiling my assecc database VB code I get the following error message "User defined type not defined". I understand it is beecause I have not declared the Variable Type, but have no idea to exactly which part of the code the error is referring to.
How do I find out WHICH User defined type is not defined, especially when I have not got any (or do not want to use any) user defined types?
Thank you in advanced programming wizards. Kind regards, Adam.
Hello, I have the following code and i don`t know what's wrong Private Sub cmdCautare_Click() Dim strSQL As String, strOrder As String, strWhere As String 'Dim dbNm As Database 'Dim qryDef As QueryDef 'Set dbNm = CurrentDb() strSQL = "SELECT DOSARE.DosarID,DOSARE.DenumireDosar,DOSARE.CodDosa r,DOSARE.DataDosar,DOSARE.Denumire,DOSARE.Data,DOS ARE.Stadiu FROM DOSARE" strWhere = "WHERE" strOrder = "ORDER BY DOSARE.DosarID " If Not IsNull(Me.txtDenumire) Then strWhere = strWhere & "(DOSARE.DenumireDosar) Like '*" & Me.txtDenumire & "*' AND" ' " End If If Not IsNull(Me.cmbStadiu) Then strWhere = strWhere & " (DOSARE.Stadiu) Like '*" & Me.cmbStadiu & "*'" End If DoCmd.Close acForm, "frmPrincipal" DoCmd.OpenForm "frmRezultateCautare", acNormal Forms!frmRezultateCautare.RowSource = strSQL & " " & strWhere & "" & strOrder End Sub Here: Forms!frmRezultateCautare.RowSource = strSQL & " " & strWhere & "" & strOrder The error is the following "Application-defined or object-defined error"
I have this function and I can't get rid of this error:"Compile error! External Name not defined"..This is just doing some calculations. I am just calling the function on the after update event on my form.
Code: Dim fHrs As Double Dim Ttl As Double Dim Ttl1 As Double Dim Ttl2 As Double Dim Ttl3 As Double Dim Ttl4 As Double Dim Ttl5 As Double Dim Ttl6 As Double Dim Ttl7 As Double
I use the following code behind the BeforeUpdate event of a control, to validate input.
Code: Private Sub StockPrice_BeforeUpdate(Cancel As Integer) 'strings used for the MsgBox Dim strTitle As String Dim strMsg1 As String Dim strMsg2 As String Dim strMsg3 As String
[Code] ....
There are two different forms that use that control and this code behind the BeforeUpdate event.
It works on one but not on the other, allowing to input anything in the control with checking and without bringing up any msgbox. Searching what's the problem in the immediate window, brings up a message saying "External Name not defined'.
I have a table onto which i would like to add new column based on a set of if statements. (to assign weekly dates)this is only a portion of the code, but when I clicked on run, it gives me "external name not defined error".
Code:
Option Compare Database Option Explicit Public Sub AssignWeekly() 'declares weekly column as a new variable Dim Client_Weekly_1213 As Integer
I am studying error handling. I am using VBA Developers Hand Book and some Google stuff. I have been over the material several times and cannot find my mistake. When I click cmdFindState it give me "compile error" " label not defined",and stops at:
Code: "On Error GoTo Err_cmdFindState_Click"
Code: Private Sub cmdFindState_Click() On Error GoTo Err_cmdFindState_Click DoCmd.OpenForm "frmChurchesAll" DoCmd.ApplyFilter "qryFindState" '----------------------------------------- ' 51 Vermont '------------------------------------------ DoCmd.GoToControl "ComboState"
I just realized that I do not know where my ShellAndWait module is obtaining the constant definitions for the WindowStyle arg. They are all defined presently:
However, I would sort of like to know where the constants are actually defined that support my ShellAndWait VBA Module. I do not like relying on "osmosis"!
I am trying to send emails using CDO to an array of recipients loaded from a query. I have made this work using Lotus Notes so i know the first part of my code is correct, and the current code I have works when I input an actual email address. But when I try to add the Array name into the 'To' field I get a data mismatch error.
I have used the following script to export a subform to excel but it isn't working for a report.
The report name is spelled correctly etc... but the error message keeps saying error 2465 or if I enable the error handler it says application defined or object defined error.
I don't want to have to export the data to a table first, but if I have to then I guess I will have to.
Code: Sub fFunctionNameExportExcel(ctrl As IRibbonControl) 'print to excel Dim rst As DAO.Recordset Dim ApXL As Object Dim xlWBk As Object Dim xlWSh As Object Dim fld As DAO.Field Const xlCenter As Long = -4108
I have got 2 access tables named Table1 and Table2. There is one field common in both the tables and that is "Reference" . So I need to match the data in Reference field in both the tables using VBA.
steps to perform:
1. Check each Reference in Table1 against all the values in "Reference" field of Table2.
2. if match occurs then store all the details of that Reference in Table3.
There could be more than 1 match of that Reference in Table 2 and we need to check the whole Table2 for each "reference" value in "table1".
