Topics Search

Set MySet = MyDef.OpenRecordset(MyDef, DB_OPEN_DYNASET)

Set MySet = MyDef.OpenRecordset(MyDef, DB_OPEN_DYNASET)
Views: 8
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)
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])
CheckNum = "00000100"
End If
Sponsored Links:

More 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!okul = MySet!Name


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)
HoldName = RS!RACF_No
updateName = rs1!RACF_No
I = 1 Begin:
RS!RACF_No = updateName
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
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
Do While Not myset.EOF
strList -strList & myset![FullName] & vbCrLf

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)


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)

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

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