Set MySet = MyDef.OpenRecordset(MyDef, DB_OPEN_DYNASET)

This proc used to work fine in A97 but just doesn't work in 2010 any help on converting to the correct proc would be much appreciated

Function CheckNum() As String
Dim db As Database, ssMaxCheck As Recordset, MyDef As QueryDef, MySet As DAO.Recordset
Set db = CurrentDb
Set MyDef = db.QueryDefs("ChNumbQry")
MyDef![Ent] = MyNumb
Set MySet = MyDef.OpenRecordset(MyDef, DB_OPEN_DYNASET)

Set ssMaxCheck = MySet
If Not IsNull(ssMaxCheck.Fields(0).Value) Then
CheckNum = CStr(ssMaxCheck("MaxOfChNo") + 1)
Else:
Dim CurDB As Database, BA As Recordset, SQLStmt As String
Set CurDB = DBEngine.Workspaces(0).Databases(0)
SQLStmt = "SELECT AccTypes.StChNum, AccTypes.AccountTypeID FROM AccTypes;"
Set BA = CurDB.OpenRecordset(SQLStmt, DB_OPEN_DYNASET)
If Not IsNull(BA.Fields(0).Value) And BA![StChNum] > 0 Then
CheckNum = CStr(BA![StChNum])
Else
CheckNum = "00000100"
End If

Sponsored Links:

Related Topics

Issue with find first
I am geeting "loop without do" error message for the code below which looks like right. please can you help

Dim myDb As Database, MySet As Recordset, MySet1 As Recordset
Set myDb = CurrentDb()
Set MySet = myDb.OpenRecordset("Table1", dbOpenTable)
Set MySet1 = myDb.OpenRecordset("Table2", dbOpenTable)
Do While Not MySet.EOF

With MySet1
.FindFirst "Name=MySet1.Name"

MsgBox "MySet1 kayit"
MsgBox MySet!Name
MsgBox MySet1!Name

MySet1.Edit
MySet1!okul = MySet!Name


Begin:/Endit:
What/how is the above used? I inherited some code that has that in it and I've never seen it before. It is used with a loop. See code below

DAO.DBEngine.SetOption dbMaxLocksPerFile, 40000
Set RS = db.OpenRecordset("QryCCUAssignOpen", DB_OPEN_DYNASET)
Set rs1 = db.OpenRecordset("QryCCUEmp", DB_OPEN_DYNASET) '
Set rs2 = db.OpenRecordset("QryCCUAssignOther", DB_OPEN_DYNASET) '
Set rs3 = db.OpenRecordset("QryCCUAssignDefaults", DB_OPEN_DYNASET)
RS.MoveFirst
rs1.MoveFirst
HoldName = RS!RACF_No
updateName = rs1!RACF_No
I = 1 Begin:
RS.Edit
RS!RACF_No = updateName
RS.Update
RS.MoveNext
If RS.EOF Then GoTo Endit
If I < [Forms]![FrmProcess]![TxtAvg] Then
I = I + 1
GoTo Begin
End If
If I > [Forms]![FrmProcess]![TxtAvg] Then
rs1.MoveNext
updateName = rs1!RACF_No
HoldName = "99999999"
I = 1
GoTo Begin End If


compile error: expected sub, function or property
I have been researchin this issue all morning. I am getting this error on some code I'm trying to run:

Compile error: expected sub, function or property

What does the error message mean? It highlights a field I am refrencing but the field name is correct? This is the code and I have typed in red the part the error refers to:

Sub DriversAvailable2()
Dim mydb As Database
Dim myset As Recordset
Dim strList As String
Set mydv = CurrentDb()
Set myset = mydb.OpenRecordset("qryDriversAvailable")
strList = "This is a list of available drivers: " & vbCrLf
myset.MoveFirst
Do While Not myset.EOF
strList -strList & myset![FullName] & vbCrLf
myset.MoveNext

Loop
MsgBox strList


Sending Multiple Emails
I have table that contains Employee Name and Email; I have a query that includes 'Employee Name'; I have user form with two text boxes named txtname and txtemail. Within the query criteria for 'Employee Name' I have added [Forms]![Control]![txtname]

I also have a button with the below code however I cannot get this to run can anybody please assist me with making the below code work?

Private Sub Command4_Click()
Dim MySet As DAO.Recordset

Set MySet = New DAO.QueryDef

MySet.OpenRecordset "Example", CurrentDb.Connection

Do Until MySet.EOF

[Forms]![Control].[qadviser].Value = MySet![AdviserName]
[Forms]![Control].[qemail].Value = MySet![Email]

DoCmd.SendObject acSendQuery, "Example", "MicrosoftExcel(*.xls)", [Forms]![Control].[qemail]


Getting a compile error
I am trying to run a select case scenario to update a recordset based upon the value in a particular field.

Private Sub Command4_Enter() Dim mydb As DAO.Database Dim myset As DAO.Recordset Dim mycategory As String
Set mydb = CurrentDb Set myset = mydb.OpenRecordset("Table1") Do Until myset.EOF
Select Case [myset]![Mileage]
Case 0 To 24000
[mycategory] = "24,000"
Case 24001 To 36000
[mycategory] = "36,000"
Case 36001 To 50000
[mycategory] = "50,000"
Case 50001 To 60000
[mycategory] = "60,000"
Case Else
[mycategory] = "Greater than 60K"
End Select myset!Category = mycategory myset.MoveNext Loop End Sub

It errors out on the line highlighted. Can't seem to see what I've done wrong here


Too Few Parameters
I have the following code executing on the click event. For some reason I keep getting the error message "too few parameters". It always hangs up on the set rs1 = . portion. When I open the query there are records in the query. I don't understand why I am getting the message.

Dim db As Database, RS As Recordset, rs1 As Recordset, rs2 As Recordset, rs3 As Recordset Set db = CurrentDb Dim Strmsg As String, Strtitle As String Dim IntStyle As Integer Dim stDocName As String
DAO.DBEngine.SetOption dbMaxLocksPerFile, 40000
Set RS = db.OpenRecordset("QryCCUAssignOpen", DB_OPEN_DYNASET)
Set rs1 = db.OpenRecordset("QryCCUEmp", DB_OPEN_DYNASET


Multiple use of SendObject
I am trying to use a block of code from a freeware demonstration. The code went in complete with some auto prompts. It compiles OK, but on running, it fails at the first line "Set MySet = New ADODB.Recordset" I can't think why? Would greatly appreciate any help. The original db was written in Access 2000, now being run in A07.

Dim MySet As ADODB.Recordset

Set MySet = New ADODB.Recordset
MySet.Open "qryPerson", CurrentProject.Connection, adOpenStatic

Do Until MySet.EOF
Me.qDonor = MySet![MPersonID]
Me.qema = MySet![MEMail]

DoCmd.SendObject acReport, "rptMailPerson", "RichTextFormat(*.rtf)", [Forms]![Form2].[qema], "", "", "Gift Aid", "Please see message", False, ""


Run Time Error 13 with upgrade to 2007
I just tried to upgrade my db and noticed the following error RUN TIME ERROR 13 Type Mismatch in two of my forms.

This is the code highlighted:
Form 1
Set rs = db.OpenRecordset (strSQL)

Form2

Set myset = MyDb.OpenRecordset


referring to a control
How do I refer to a tabbed subform control or field from main form.

eg. myset![empID]= me.empid

1) myset![empID]= me.empid
works well when empid is within main form.what about when empID is within a page on a tabbed subform.

I wrote:

1) myset![empID]=me.tabCat36.empid


Swapped DAO recordset update with SQL Action Query. Result? Broken.
I recently migrated some backend data from a local access MDB to SQL Server. Because of the use of link tables, the existing ADO style updates were horribly slow.

So I swapped out the code for DAO and reduced the insert time from 11 seconds to less than one.

However, later in the execution, when the VBA code for one of the forms attempts to reference a field within the same form, the txtField.Value renders "You entered and expression that has no value"

In an "all things equal" branch that keeps the ADO there is no such problem.

the code changed from the form:

Set dbOrgDB = DBEngine.Workspaces(0).Databases(0)
Set snTbl = dbOrgDB.OpenRecordset(sTable, DB_OPEN_TABLE)
snTbl.AddNew
snTbl.Update

Set dbOrgDB = DBEngine.Workspaces(0).Databases(0)
Set snTbl = dbOrgDB.OpenRecordset(sTable, DB_OPEN_DYNASET) --only used to determine # of fields.
tempSQL = "INSERT INTO ."
CurrentDb.Execute tempSQL

Do I need to "Commit" these executed statements to the local buffer or something


sql update in VBA
I have a table called tblListings with a field called "Status"

I have another table called tblSales. In the form for this table I want to have cmd button that will change the "Status" field in tblListings from PEN to SLD. The two tables are related with a field called "TRNumberL"

I can't seem to get it to run right. Can someone tell me what I'm doing wrong?

Private Sub cmdCloseSale_Click() Dim myUpdate As String Dim mySet As String Dim myWhere As String Dim strSQL As String 'set sql update statement myUpdate = "UPDATE tblListings " 'set sql set statement mySet = "SET Status = 'SLD' " 'set sql where statemewnt myWhere = "WHERE TRNumberL=" & " ' " & Me!TRNumberL & "'" 'define full sql statement strSQL = myUpdate & mySet & myWhere & ";" DoCmd.RunSQL strSQL End sub


Error #3622 After Migrating my Database
I successfully (I think) migrated my Access 2007 db to MSSQL Server 2008R2 but I am getting error #3622 "You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column." when I try to run the following code:

Set rsCompany = dbs.OpenRecordset("SELECT * FROM [Company Information] WHERE CompanyDefaultLocation Like 'Yes'")

Do you know what is causing this? I get a similar error with the following lines of code:

Set rs = CurrentDb.OpenRecordset("SELECT * From Contacts WHERE ContactCompany =""" & Me.Client & """") and Set rsLoginAttempt = db.OpenRecordset("LoginAttempt")

I don't see a pattern to know what I need to change.

I do not get the error when I run the code:

Set rs = db.OpenRecordset("SELECT * FROM Employee WHERE EmployeeUserName =""" & EnteredUserName & """") and
Set rstProjectName = dbs.OpenRecordset("Project File")

What is the dbSeeChanges option that is referred to in the error message


openrecordset question
basically what I am trying to do is open a recordset based off of a query, here is what I have

Dim db As DAO.Database
Dim rs1 As DAO.Recordset

Set db = CurrentDb()
Set rs1 = db.OpenRecordset("Select * from main where offshore_analyst = Forms![main_form]![offshore_analyst].Value;")

the query I have above is already defined as a seperate query, is there a way to just refference the query name?

like openrecordset ("qry_name")
when I try this it says too few parameters

if not why is the above example giving me an error? im sure im not refferencing the form correctly


Open a recordset against another recordset?
I have opened a snapshot-type recordset against a lengthy query. I want to query against the recordset, but I can't get recordset.openrecordset to work for me.

Here's the code:

set rst1=currentdb.openrecordset("tbl",openforwardonly )
set rst2=currentdb.openrecordset("qry",opensnapshot)
do
set rst3=rst2.openrecordset("SELECT * FROM rst2 WHERE fld = """ & rst1!str & """", dbopensnapshot)
rst1.movenext
loop until rst1.eof

I also tried replacing rst2 in .FROM rst2. with qry to no avail. (I didn't expect to find qry in rst2)

I'm stumped. I suppose I could dump rst2 into a temporary table, but I hate littering the database (and, oh!, the bloat)


OpenRecordset vs. Query
So I've run into a problem and I've tried everything I can think of and can't seem to find a solution. The problem I am having is the following:

Dim rs As DAO.Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordSet(".")

So when I insert a query name (or SQL string) instead of "." in the OpenRecordset command it does not return any records. When I run the query with the exact same criteria (same SQL statement) by clicking run or datasheet view it returns the appropriate records.

There are no errors given by ms access it is always rs.EOF when I check if there are records. I have concatenated the criteria into the SQL string properly as far as I know as I have redone it 3 or 4 times.

One more detail is that the strSQL that I am trying to use in the OpenRecordset command is selecting data from another query (not table).

Any help is appreciated, I can post more details if required


Recordset Error
If I run the following code:

Set rst = db.OpenRecordset("tblBooking")

Then it returns 7; the correct value

If I alter this slightly:

Set rst = db.OpenRecordset("SELECT * FROM tblBooking")

Then it returns 1; obviously the wrong value.

The reason why I wish to do this is because I actually wish to run the following:

Set rst = db.OpenRecordset("SELECT * FROM tblBooking WHERE dteDate=#" & txtDate.value & "#")

But this returns 1 (clearly since the first section returns an error.

Why?

Oh, BTW, I have set the following:

Dim db As Database
Dim rst As DAO.Recordset
Set db = CurrentDb()

The returning code is:

lblNo.Caption = "There are currently " & rst.RecordCount & " bookings for that date."


SQL statement in OpenRecordset
I'm working on a bit of VBA in Excel that's going to pull out some record counts for me based on various criteria. It's a work in progress, but I'm stuck on a rather annoying hurdle. Here's the code:

Sub ExportFigs() Dim DBPath As String Dim db As Database Dim rs As Recordset DBPath = "\\gents02\shared\complain\Aardvark\mdbstore\Aardvark_backend.mdb" Set db = OpenDatabase(DBPath) Set rs = db.OpenRecordset("SELECT * FROM tFile WHERE FileName=XXX")
MsgBox (rs.RecordCount)
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing End Sub

Everytime I run this code I get an error 3061: Too few parameters. Expected 1.

I'm guessing I'm missing something from my OpenRecordset statement, but I can't for the life of me figure out what. I've tried adding dbOpenDynaset as a type, but without success


Select Statment in VBA
I am trying to sum total invoice amount(invamt) from a table Debits and update a table called BLT with the sum. when I run the below code it does not return anything. what am I doing wrong, please help
Private Sub btnSave_Click()

Dim db As Database
Set db = CurrentDb
Dim Sxtyplus As Integer
Dim rstemp As Recordset
Dim tblpool As Recordset

Set tblpool = db.OpenRecordset("blt")
Set tbldebits = db.OpenRecordset("Debits")
tblpool.AddNew
Set rstemp = db.OpenRecordset("Select sum(invamt) AS[Aging] from Debits ")
With rstemp
.MoveFirst
Me.Sixtyplus = Aging
tblpool![Crid] = Aging
End With


Mismatch type on openrecordset
I am using ms access 2007 and I am getting this error when I run this code:
Dim rst As Recordset
Dim db As Database
Dim temp As String
Dim svrname As String

Set db = CurrentDb
Set rst = Recordset

Set rst = db.OpenRecordset("tbl_AS_ReportClms", dbOpenDynaset)

error:


using .addnew for a an entire table
Set rs = CurrentDb.OpenRecordset("qry_employee")
rs.AddNew
rs.Fields("EMP_LASTNAME").Value = Me.txtLastName
rs.Fields("EMP_FIRSTNAME").Value = Me.txtFirstName
rs.Update
rs.Bookmark = rs.LastModified
lngPatientID1 = rs!ID_EMPLOYEE
rs.Close
Set rs = Nothing
lngPatientID2 = lngPatientID1 Set rs = CurrentDb.OpenRecordset("

anyone know or could tell me how to convert this using an entire table instead? cause I need to query each primary key entered.