You Must use dbseechanges option with openrecordset Error

I am trying to run queries in other databases by using the following code:

	Code:
	'Opening the Database
Set db = DBEngine.Workspaces(0).OpenDatabase("C:TestTest12Testdb.mdb")
'Running specified queries
db.Execute "qry_try_This", dbFailOnError
Set db = Nothing

But I keep getting the error of:
Run time error 3622
You must use dbseechanges option with openRecordset option when accessing a SQL server table that has an Identity column

I am by far a beginner using DAO, and am not sure what exactly is causing this error, or how to correct it.


Post your answer or comment

comments powered by Disqus
I've migrated the whole database to SQL server and i got popup up with this error:

"error 3622 you must use the dbseechanges option with openrecordset when accessing a sql server table that has an identity column"

Debugging the issue is directing me to this line:

Set rst = CurrentDb.OpenRecordset("Select * from registrygz")

any guideline?

I'm using this code on a cmd button to confirm an order and at the same time take the order off my listbox which lists pending orders.

CurrentDb.Execute "UPDATE tblConfirmations SET ConfirmFlag = True Where JOBID =" & Forms!frmSwitchboardF.ListConfirm
Forms!frmSwitchboardF.ListConfirm.Requery

My problem is I'm trying to link this to my sql server and install it on multiple stations using an MDE file.

When I use that linked MDE file this code doesnt work

Same thing when I use the Main DBA linked to SQL Server

I get an error: "you must use dbseechanges option with openrecordset when accessing a SQL server table that has an IDENTITY column"

what does it mean? and how do I solve this?

I have an application with an Access 2002 front end linked to Access 2002 db. I am trying to convert the db side to SQL Server 2000. Everything appears to link up properly and I can run most everything with no problems....upon further testing I have gotten this error and I am stumped...

"You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has as IDENTITY column."

help?!?!

TIA

CJK

Hi,

I have an Access Front end App, using a link Table with SQL. The table has a Primary Key as "Auto Number". I just imported the Table to SQL and linked through Access, but when I enter a new record or changing an existing rec. I get an error message "You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column."
I am not a programmer so please if someone knows a solution, please explain in a way so I can understand what this means and what I am doing worng.I have to distribute this app on Monday to the users and I am warry about it

Thanks a lot,
Berti

Upsizing Access 2010 back end to a SQL Server 2008. Line 20 creates an error 3622. This worked in Access 2010 but errors in SQL Server.
The error description is:
You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.

If I globally replace all of my dbAppendOnly with dbSeeChanges, can anyone see any downside or problems?


	Code:
	          Dim rst As DAO.Recordset  ' The table Wells_ProgSentToField_History
              '    Table Name
20            Set rst = CurrentDb.OpenRecordset("Const_Reclamation_Remarks", 2, dbAppendOnly)
30            rst.AddNew
40                rst![ID_Wells] = ID_Wells
50                rst![Remarks] = Remarks
60                rst![User_ID] = Environ("username")
70                rst![Date_Entered] = Now()
80            rst.Update
90            rst.Close
100           Log_ConstRemarkHistory = True



Since I've converted to SQL Server, I now get the following error:

"You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column." It didn't happen before. Any help would be super. This has been driving me cracked for hours. I have no doubt it's related to the following:

mySQL = "INSERT INTO tblSuFileActions (fkFileId,fkActionId,actionAuthoriser,actionDate,a ctionReason, actionPerson, actionCompany)"
mySQL = mySQL & " SELECT file_id," & Me.cboAction & "," & Me.cboAuthoriser & ",#" & Me.txtDate & "#, '" & Me.txtReason & "', " & Me.cboPerson & ", " & cboCompany & " "
mySQL = mySQL & " FROM (SELECT tblSuFile.file_id"
mySQL = mySQL & " FROM tblSuBoxLocation INNER JOIN tblSuFile ON tblSuBoxLocation.box_ID = tblSuFile.box_id"
mySQL = mySQL & " WHERE tblSuBoxLocation.box_ID=" & Me.cboBoxNo & " )"


CurrentDb.Execute mySQL, dbFailOnError

I'm running Access 97 and am in the process of converting my tables to SQL Server 2000. My project has a few forms, and in one form, I had no problems opening a table with the following:

Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("dbo_tblNonSap")

However, since I've converted to SQL Server, I now get the following error:

"You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column."

I've tried modifying the dbs.OpenRecordSet line to

Set rst1 = dbs.OpenRecordset("dbo_tblNonSap",,dbSeeChanges)

but that does not help.

Any comments or suggestions are most appreciated -- My head hurts from pulling out too many hairs!

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:

	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:

	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:

	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?
help....

I have a combo-box which is generating the following warning message:

You must use the dbSeeChanges option with the OpenRecordset when accessing a SQL Server table that has an Identity column.

It doesn't seem like I can trap this warning message and I'm not sure how to get rid of it.

The form uses a query as its recordsource, so I'm not using VBA code (OpenRecordset) to access the data.

The data is coming from a SQL 7 table -- the column is an auto-number.

Thanks in advance..

I am converting an MDb to an mdb linked to a SQL back end

In one of my normal mdb tables I had a table with service dates for a specific machine. This table had no primary key as this had an impact on my function that would update the service dates. It worked fine in access.
The problem with a autonumber was that i messes up the order in the record set.

Now that I have a SQL backEnd I had to assign a primary key + identity to the table in order to make it a table I can add/edit records.

Hwr now i am using my code I get the following error message
"You must use the dbseechanges option with openrecordset when accessing a SQL server table that has an Idenity Column"


This is the code i am using

Dim stDocName As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim f As Date
Dim g As Integer
Dim servicedate As Date

g = 365 / (AvgRunHrs / ServiceInt)

Set db = CurrentDb()
Set rs = db.OpenRecordset("TblServicebySerialSummary")
With rs
.FindFirst "SerialId = " & Forms![addserialdata].[SerId] & ""
servicedate = ![plannedServicedate]
Do Until rs.NoMatch
.FindNext "SerialId = " & Forms![addserialdata].[SerId] & ""
.Edit
![plannedServicedate] = servicedate + g
.Update
servicedate = ![plannedServicedate]
Loop
.Close

End With



ANY Suggestions here?

Hello, yet again. I feel like a school kid, always having to ask for help, truth is I've only been working with Access for a couple of weeks and some of the problems I am coming across are way out of my depth. My gratitude to all of you is huge, believe me.

OK, the latest problem. I have a sub form which collects, or is supposed to collect, information on just two fields, one is a number field the other a date field, with a calendar set next to it. My problem is that all of a sudden these fields are no longer updatable. When we try to input data we get "Error number 3622: you must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column."

Now I have checked the forum and dbSeeChanges in access help but can't seem to find the solution to this problem. The code used is as follows:

Private Sub cmdAdd_Click()
On Error GoTo Err_cmdAdd_Click

Dim qdf As QueryDef

' Append current information to the AppendQuery
If SaveRecord(Me) Then
Set qdf = CurrentDb.QueryDefs("qAppStandardisedScoresCopy")
qdf.Parameters("theID") = Me!txtKeyField
qdf.Execute
DoEvents
qdf.Close

' update the current query to have no results

Set qdf = CurrentDb.QueryDefs("qupdStandardisedScoresCopy")
qdf.Parameters("theID") = Me!txtKeyField
qdf.Execute dbSeeChanges
DoEvents
qdf.Close

'run the query

Me.Requery
Me.Parent.subStandardisedScoresPrevious.Form.Reque ry

'Make the necessary fields available on the current screen

Me!txtAB_GRP_ACTUAL.Locked = False
Me!txtAB_GRP_ACTUAL.BackColor = vbRequired
Me!txtAB_GRP_DT.Locked = False
Me!txtAB_GRP_DT.BackColor = vbRequired
Me!cmdFromDateCalendar.Enabled = True
Me!txtAB_GRP_COMMENT.Locked = False
Me!txtAB_GRP_COMMENT.BackColor = vbWhite
Me!chkAB_IPM.Locked = False

End If

Once again, I truly appreciate any help anyone can offer.

Many thanks

Ouma

Good morning all,

I have a form which acts as a search form to find records within the database.

The database was created in access but has since had the table upsized to SQL 2005. The Search Form was working but now I get the following error displayed.

You must see the dbSeeChanges option with OpenRecordset when accessing a SQL Server table that has an IDENTITY column.

The debugger takes me the the following lines of code.

Set db = CurrentDb
Set rcd = db.OpenRecordset("select " & _
"IT_SupportTable.LogNumber " & _
"FROM IT_SupportTable" & _
" WHERE " & strWhere & ";")

Can anyone help me with where I should be putting the dbSeeChanges.

Many thanks,

I have a form with unbound fields and using an option group (gStartStop) with two buttons, Start/Stop, I want to control the execution of a DO WHILE loop where a series of actions are performed, including action queries.

Code of the option group:

	Code:
	Private Sub gStartStop_AfterUpdate()
    If Me.gStartStop = 2 Then 'START button is pressed
        Call XYstat
    Else
        Me.gStartStop = 1 'STOP button is pressed
    End If
End Sub

Partial Code of the XYstat(), the loop:

	Code:
	Do While Me.valStartDraw > dblStopDraw And Me.gStartStop = 2 '"START"
    Call cmdGetRDraws_Click
    'Show counter
    Me.txtCounter = "CurDraw: " & Me.valStartDraw & vbNewLine & "LoopNo: " & lnCnt
    DoEvents
    Me.cmdGetRDraws.Enabled = False
    
'Create currend bunch of X,Y records
DoCmd.SetWarnings False
    'Empty table
    DoCmd.RunSQL "DELETE * FROM tblRecordsXY"
    'Calculate and append respective records
    DoCmd.OpenQuery "AnalAppendXYrecs", acNormal, acEdit

...etc
Loop

The problem is that when I click on the STOP button to terminate the loop, I get the error Quote: The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Microsoft Office Access from saving the data in the field. (kindly see the attachment for the full error) and after I press OK the loop continues and finishes normally, doesn't exit.

Anybody with enough experience can tell me what I'm doing wrong?

Thank you!

Hey folks got a small problem with some code I'm using that results in the following error message:

"Run-Time Error '1004':

The pivotTable field name is not valid. To create a pivottable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a pivottable field, you must type a new name for the field."

Here's the code:


	Code:
	
Option Explicit

Sub main_prog()

    Call td_metrics_import
    Call pt_td_metrics("Pivot_Page1", "PivotTable1", "PivotTable2")
    Call pt_td_metrics("Pivot_Page2", "PivotTable3", "PivotTable4")
    Call create_graph

End Sub



Sub pt_td_metrics(Chrt_Pg_Name As String, p_tbl_name1 As Variant, p_tbl_name2 As Variant)

Dim ws2 As Worksheet
Dim ws1 As Worksheet
Dim wb As Workbook
Dim pt As PivotTable
Dim ptCache As PivotCache
Dim prange As Range
Dim lastRow As Long
Dim lastCol As String
Dim i As Integer




Set pt = Nothing
Set prange = Nothing
Set ptCache = Nothing
Set ws1 = Nothing
Set ws2 = Nothing


Set wb = Workbooks("td_metrics_excel3.xls")
Set ws1 = wb.Worksheets("Data_Page")



'For i = 1 To Worksheets.Count
'
'    If wb.Worksheets(i).Name  "Data_Page" Then
'        wb.Worksheets(i).Name = "Pivot_Page"
'    End If
'
'Next i

wb.Worksheets.Add after:=wb.Sheets(wb.Sheets.Count), Count:=1

wb.Worksheets(wb.Worksheets.Count).Name = Chrt_Pg_Name

Set ws2 = wb.Worksheets(Chrt_Pg_Name)

lastRow = ws1.Cells(65536, 1).End(xlUp).Row
lastCol = ws1.Range("IV1").End(xlToLeft).Column

Set prange = ws1.Cells(1, 1).Resize(lastRow, lastCol)

Set ptCache = wb.PivotCaches.Add(xlDatabase, prange.Address)

Set pt = ptCache.CreatePivotTable(ws2.Cells(1, 1), p_tbl_name1)

pt.AddFields RowFields:=Array("BG_DETECTION_DATE", "BG_SEVERITY"), ColumnFields:=Array("BG_PROJECT_DB", "BG_USER_01")

With pt.PivotFields("BG_USER_08")
    .Orientation = xlDataField
    .Function = xlCount
    .Position = 1
End With

pt.PivotFields("BG_DETECTION_DATE").LabelRange.Group Start:=True, End:=True, periods:=Array(False, False, False, False, True,
True, True)

With pt
    .ColumnGrand = False
    .RowGrand = False
End With

pt.PivotFields("BG_PROJECT_DB").Subtotals(1) = True
pt.PivotFields("BG_PROJECT_DB").Subtotals(1) = False
pt.PivotFields("BG_DETECTION_DATE").Subtotals(1) = True
pt.PivotFields("BG_DETECTION_DATE").Subtotals(1) = False

Set pt = Nothing

Set pt = ptCache.CreatePivotTable(ws2.Cells(40, 1), p_tbl_name2)

pt.AddFields RowFields:="BG_DETECTION_DATE", ColumnFields:="BG_PROJECT_DB"

With pt.PivotFields("BG_USER_08")
    .Orientation = xlDataField
    .Function = xlCount
    .Position = 1
End With

pt.PivotFields("BG_DETECTION_DATE").LabelRange.Group Start:=True, End:=True, periods:=Array(False, False, False, False, True,
True, True)

With pt
    .ColumnGrand = False
    .RowGrand = False
End With

pt.PivotFields("BG_PROJECT_DB").Subtotals(1) = True
pt.PivotFields("BG_PROJECT_DB").Subtotals(1) = False
pt.PivotFields("BG_DETECTION_DATE").Subtotals(1) = True
pt.PivotFields("BG_DETECTION_DATE").Subtotals(1) = False


End Sub

Sub td_metrics_import()

Dim db As Database
Dim qdf As QueryDef
Dim rs As Recordset
Dim accapp As Access.Application
Dim wb As Workbook
Dim ws1 As Worksheet
Dim path As String
Dim i As Integer
Dim tblName As String

path = "C:Documents and SettingshendersrMy Documentstd_metrics.mdb"

tblName = "tbl_initial_td_select"


Set accapp = New Access.Application

accapp.OpenCurrentDatabase (path)
    accapp.Run ("qry_run")
accapp.Quit

Set db = Workspaces(0).OpenDatabase(path, ReadOnly:=False)

Set wb = Workbooks("td_metrics_excel3.xls")
Set ws1 = wb.Worksheets("Data_Page")

Application.DisplayAlerts = False

For i = 1 To wb.Charts.Count
    wb.Charts(i).Delete
Next i

ws1.Cells.ClearContents
    
For i = wb.Worksheets.Count To 1 Step -1
    
    If wb.Worksheets(i).Name  "Data_Page" Then
        wb.Worksheets(i).Delete
    End If
    
Next i

Application.DisplayAlerts = True

Set rs = db.TableDefs(tblName).OpenRecordset

For i = 0 To rs.Fields.Count - 1
    
    If i = 0 Then
        ws1.Range("IV1").End(xlToLeft) = rs.Fields(i).Name
    Else
        ws1.Range("IV1").End(xlToLeft).Offset(0, 1) = rs.Fields(i).Name
    End If
    
Next i

ws1.Range("IV2").End(xlToLeft).CopyFromRecordset rs

End Sub

Sub create_graph()

    Charts.Add
    
    With ActiveChart
        
        .SetSourceData Source:=Sheets("Pivot_Page1").Cells(1, 1)
        .Location Where:=xlLocationAsNewSheet
        .PivotLayout.PivotTable.PivotFields("BG_PROJECT_DB").Orientation = xlHidden
        .PivotLayout.PivotTable.PivotFields("BG_DETECTION_DATE").Orientation = xlHidden
        .PivotLayout.PivotTable.PivotFields("BG_USER_01").Orientation = xlHidden
        
        With .PivotLayout.PivotTable.PivotFields("BG_SEVERITY")
            
            .Orientation = xlColumnField
            .Position = 1
        
        End With
         
        .PlotArea.Interior.ColorIndex = xlNone
    
    End With
            
End Sub

The code breaks on the line:

Set pt = ptCache.CreatePivotTable(ws2.Cells(1, 1), p_tbl_name1)

in the pt_td_metrics sub.

If I comment out one of the lines where I call the sub pt_td_metrics i.e.:


	Code:
	Sub main_prog()

    Call td_metrics_import
    Call pt_td_metrics("Pivot_Page1", "PivotTable1", "PivotTable2")
    'Call pt_td_metrics("Pivot_Page2", "PivotTable3", "PivotTable4")
    Call create_graph

End Sub

Everything works fine.

So anyone got any ideas?

Hi All

We have moved an access backend to SQL Server 2000, and use Access 2k as the front end.

When a user tries to delete a line record using the following code


	Code:
	DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

we get an error message

Quote: The DoMenuItem action was cancelled I changed the code to

	Code:
	DoCmd.RunCommand acCmdDeleteRecord

but got a message

Quote: You must see the DBSeeChanges option with open recordsets when access a SQL Server table that has an identity column Doing some searching on this forum, I amended my code to be the below. I get no error messages, but the record is not deleted either.

Any ideas?


	Code:
	Private Sub cmdDelRec_Click()
On Error GoTo Err_cmdDelRec_Click

Dim strSQL As String
Dim intLineNo As Integer

intLineNo = Me.ContLineNo


strSQL = "delete from tblContAttribLin where ContLineNo = " & intLineNo

If MsgBox("Delete this record?", vbQuestion + vbYesNo, "Delete?") = vbYes Then

    CurrentDb.Execute strSQL, dbSeeChanges
    Me.Refresh

End If


    'DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    'DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
    'DoCmd.RunCommand acCmdDeleteRecord

Exit_cmdDelRec_Click:
    Exit Sub

Err_cmdDelRec_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelRec_Click
    
End Sub



Hey All,

Trying to send an email with an attachment. I keep getting this error
"Can't find this file. Make sure the path and file name are correct" They are correct I am using a code sample I found on the forum.

I'm stumped. HELP

Thanks
Rich

Here's the code.

Option Compare Database
Option Explicit
' You need to declare a reference to the Outlook library, and the filesystemobject.
' this is not as hard as it sounds.
'
' Look in the menu above, and click Tools, then select References
'
' Scroll down the list until you see
' Microsoft Scripting Runtime -- and put a check next to it (if one is not there already)
'
' Microsoft Outlook Object Library -- check that.
' There will be some version number there as well; it doesn't matter.
' This will work with Outlook98 and Outlook2000 and OutlookXP. It hasn't been tested on Outlook 2003 yet.


Public Function SendEmail()


Dim MailList As String
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim fso As FileSystemObject
Dim MyBody As TextStream
Dim MyBodyText As String

Set fso = New FileSystemObject

' First, we need to know the subject.
' We can't very well be sending around blank messages...

Subjectline$ = "Accurate Public Records, LLC."
' If there's no subject, call it a day.

If Subjectline$ = "" Then
MsgBox "No subject line, no message." & vbNewLine & vbNewLine & _
"Quitting...", vbCritical, "E-Mail Merger"
Exit Function
End If

' Now we need to put something in our letter...

'BodyFile$ = "C:Proposal.Txt"

' If there's nothing to say, call it a day.

'If BodyFile$ = "" Then
'MsgBox "No body, no message." & vbNewLine & vbNewLine & _
' "Quitting...", vbCritical, "I Ain't Got No-Body!"
'Exit Function
'End If

' Check to make sure the file exists...
'If fso.FileExists(BodyFile$) = False Then
'MsgBox "The body file isn't where you say it is. " & vbNewLine & vbNewLine & _
'"Quitting...", vbCritical, "I Ain't Got No-Body!"
'Exit Function
'End If

' Since we got a file, we can open it up.
'Set MyBody = fso.OpenTextFile(BodyFile, ForReading, False, TristateUseDefault)

' and read it into a variable.
MyBodyText = "This Is A Test"

' and close the file.
'MyBody.Close

' Now, we open Outlook for our own device..
Set MyOutlook = New Outlook.Application


' This creates the e-mail

Set MyMail = MyOutlook.CreateItem(olMailItem)

' This addresses it
MyMail.To = [Forms]![Marketing]![Email]

'This gives it a subject
MyMail.Subject = Subjectline$

'This gives it the body
MyMail.Body = MyBodyText

'If you want to send an attachment
'uncomment the following line

MyMail.Attachments.add ("C:APR DocsAPR Application"), olByValue, 1, "Apr Application"
'MyMail.Attachments.add "c:dbgout.txt", olByValue, 1, "My Displayname"

' To briefly describe:
' "c:myfile.txt" = the file you want to attach
'
' olByVaue = how to pass the file. olByValue attaches it, olByReference creates a shortcut.
' the shortcut only works if the file is available locally (via mapped or local drive)
'
' 1 = the position in the outlook message where to attachment goes. This is ignored by most
' other mailers, so you might want to ignore it too. Using 1 puts the attachment
' first in line.
'
' "My Displayname" = If you don't want the attachment's icon string to be "c:myfile.txt" you
' can use this property to change it to something useful, i.e. "4th Qtr Report"



'This sends it!
'MyMail.Send

'Some people have asked how to see the e-mail
'instead of automaticially sending it.
'Uncomment the next line
'And comment the "MyMail.Send" line above this.

MyMail.Display

'Cleanup after ourselves

Set MyMail = Nothing


'Uncomment the next line if you want Outlook to shut down when its done.
'Otherwise, it will stay running.

'MyOutlook.Quit
Set MyOutlook = Nothing

End Function

I'm hoping someone can help me out with this code below. Trying to send emails from outlook 2010 using a specific email account (NOT the default) based on a static template that pulls data from a table (senders_table) for the (TO:, Subject, and a few variable fields within the email body). So far the code below works except that it is not cycling through all of the records in my table. Emails go out through the specified account and with the proper data pulled from the table in the email but stops after the first record.



Private Sub test_Click()

'You must add a reference to the Microsoft Outlook Library
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem
Dim strbody As String
Dim strmail As String
Dim strsubject As String

Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)


Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Senders_Table")
With rs

If .EOF And .BOF Then
MsgBox "No mails will be sent becuase there are no records assigned from the list", vbInformation
Else
Do Until .EOF

stremail = ![mail]
strsubject = ![property]
strbody = "Dear " & ![name] & "," & _
Chr(10) & Chr(10) & "Some kind of greeting" & ![property] & "!" & _
" email message body goes here"

.Edit
.Update
.MoveNext 'THIS MOVES TO THE NEXT RECORD IN THE RECORDSET

Loop

End If
End With

On Error Resume Next
With OutMail
.To = stremail
.CC = ""
.BCC = ""
.Subject = strsubject
.Body = strbody

'Change Item(1)to another number to use another account
.SendUsingAccount = OutApp.Session.Accounts.Item(2)
.Send 'or use .Display
End With



On Error GoTo 0

If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

Can anyone tell me why I keep getting a compile error with this come, which following "Unable to create an .mde"

I am using Access97

Option Compare Database 'Use database order for string comparisons
Declare Function WNetGetUser Lib "mpr" Alias "WNetGetUserA" (ByVal lpName As String, ByVal lpUserName As String, lpnLength As Long) As Long

Global gLogonID As String
Global gRestriction As String
Option Explicit
Function GetRestriction() As Integer
On Error GoTo GetRestrictionError

Dim Msg As String
Dim DgDef As Integer
Dim Title As String

Dim MyDB As Database
Dim MyQuery As QueryDef, MyParameter As Parameter
Dim MyRecordset As Recordset

Title = "Restriction"

Set MyDB = DBEngine.Workspaces(0).Databases(0)
Set MyQuery = MyDB.QueryDefs("Get Restriction Query") ' Open existing QueryDef.

MyQuery.Parameters("Enter the Logon ID") = gLogonID ' Set parameters.

Set MyRecordset = MyQuery.OpenRecordset() ' Open Recordset.

If MyRecordset.RecordCount = 0 Then Exit Function ' not Forms Request user yet

gRestriction = MyRecordset.Restriction

If Not (IsRuntime() = -1 Or gRestriction = "DEVELOPER") Then
Msg = "Sorry, you do not have design mode access." + Chr(13) + Chr(10)
Msg = Msg + "You must use this system in run-time mode." + Chr(13) + Chr(10)
Msg = Msg + "Please contact your system administrator."
Title = "VIOLATION!"
DgDef = 0 + 16
MsgBox Msg, DgDef, Title
MyRecordset.Close
Application.Quit
End If

MyRecordset.Close

GetRestriction = True

Exit_GetRestriction:
Exit Function

GetRestrictionError:
MsgBox Error$
Resume Exit_GetRestriction
MyRecordset.Close
Application.Quit

End Function

Function IsRuntime() As Integer
On Error GoTo IsRuntimeError

IsRuntime = SysCmd(acSysCmdRuntime)

Exit Function

IsRuntimeError:

If Err = 5 Then
IsRuntime = False
Else
IsRuntime = False
Error Err
End If

End Function

Sub SetSecurity()
On Error GoTo Err_SetSecurity

Dim lpName As String * 255
Dim lpUserName As String * 255
Dim lpnLength As Integer
Dim Status As Integer

Status = WNetGetUser(lpName, lpUserName, 20)
If (Status = 3) Then
gLogonID = "WNetGetUser Failed"
Else
' Return up to first Null.
gLogonID = Left$(lpUserName, InStr(lpUserName, Chr(0)) - 1)
End If

If Not Len(gLogonID) > 0 Then ' if not a LAN user
Msg = "You must have LAN access to use " + Chr(13) + Chr(10)
Msg = Msg + "the Forms Requests Application." + Chr(13) + Chr(10)
Msg = Msg + "Please contact your LAN Security Manager."
DgDef = 48
MsgBox Msg, DgDef, Title
DoCmd.Quit
End If

If Not GetRestriction() Then
Msg = "You must have security access to use this system." + Chr(13) + Chr(10)
Msg = Msg + "Please contact your Unit Head or Supervisor."
DgDef = 48
MsgBox Msg, DgDef, Title
DoCmd.Quit
End If

'SetSecurity = True

Exit_SetSecurity:
Exit Sub

Err_SetSecurity:
MsgBox Error$
Resume Exit_SetSecurity

End Sub



Thank you in advance

In general, my application appears to be working. But, here is a huge difference. If anyone has ideas on the best way to fix the dates.

Used the SQL Server Migration Assistant Wizard for Access.
Very noticeable are the date format changes.
2003-04-29 00:00:00 - the Date text boxes don't bring up the Date Picker anymore. The dates use to be 4/29/2003
It would appear SQL Server-s format is YYYY-MM-DD Access' format is DD-MM-YYYY
The SQL Server conversion by the tool is datetime2(0), null i.e. DateTime2


If I will try the Access Upgrade Wizard, would it manage the dates better?


FYI: Updated dozens of forms, but missed this form's OpenRecordset statement. At least Microsoft gave a nice description:
Microsoft Access You must use the dbSeeChanges option with OpenRecorset when accessing a SQL Server table that has an IDENTITY column.

UPDATE: Nobody responded so I will up date this post for now. Think I found the answer. Will document it and post it once I re-migrate.

Hi All,

I have been trying to get mail merge to work using information and help from the forum. This is what I have come up with, it works with no errors.

Private Sub merge_Click()
Dim objWord As Word.Document
Set objWord = GetObject("C:Test.doc", "Word.Document") 'YOUR MERGE DOC
' Make Word visible.
objWord.Application.Visible = False
' Set the mail merge data source as the Current database.
objWord.MailMerge.OpenDataSource _
Name:="C:Text.mdb", _ 'YOUR DATABASE
LinkToSource:=True, _
Connection:="QUERY TestQuery", _ 'YOUR TABLE OR QUERY
SQLStatement:="SELECT * FROM [TestQuery]" 'YOURTABLE OR QUERY
' Execute the mail merge.

objWord.MailMerge.Destination = wdSendToNewDocument
objWord.MailMerge.Execute

'The following line must follow the Execute statement because the
'PrintBackground property is available only when a document window is
'active. Without this line of code, the function will end before Word
'can print the merged document.

objWord.Application.Options.PrintBackground = False
objWord.Application.ActiveDocument.PrintOut
End Sub


Thanks to the following member: NAVYGUY It was Navyguys original post and code that I have used. The only change I have made is this. The original code was written as a FUNCTION. I have used the same code but as a PROCEDURE run from a command button on a form.


Many thanks Navyguy.


Kind Regards, keep safe and well.


Dereck

I have a table of results from soccer matches (MatchTeams) where each record represents a team in a match and the number of goals they scored. In other words, there are two records for each soccer match, one for the home team and one for the away team (the primary key is a MatchTeamID field).

For each record, I want to assign a number of points based on whether they won or lost the match (2 points for a win, 1 for a draw, 0 for a defeat). This means for each record comparing MatchTeams.Score with the other record that has the same MatchTeamID. I’ve used an IIF statement for this, which works fine in a SELECT query.

My problem is, I actually want to update each record in MatchTeams with these points, in a field MatchTeams.Points. When I use the same IIF statement in an UPDATE query, I get this “operation must use an updateable query" error.

UPDATE MatchTeams SET MatchTeams.Points = IIf([MatchTeams].[Score]=(SELECT Dupe1.Score FROM MatchTeams AS Dupe1 WHERE Dupe1.MatchID=MatchTeams.MatchID AND Dupe1.MatchTeamIDMatchTeams.MatchTeamID),1,IIf([MatchTeams].[Score]>(SELECT Dupe2.Score FROM MatchTeams AS Dupe2 WHERE Dupe2.MatchID=MatchTeams.MatchID AND Dupe2.MatchTeamIDMatchTeams.MatchTeamID),2,0));

I don’t get why MatchTeam.Points is not updateable. Any help please?

At some point, you may find it necessary to use VBA to get your job within a database done. Also, there are cases where some databases model a complex set of business rules and requirements and you must use VBA generously.

The most important thing to understand is that there are no truly hard and fast rules regarding how to be a good programmer. However, since the time of vacuum tubes, several programmers have observed patterns and thus contributed to a set of guidelines that help other programmers, regardless of whether they are using C, COBOL, Java, Visual Basic, or any one of many programming languages available.

This article deals in generalities and a bit of programming theory. The overall objective is to provide a maturing programmer with a framework to work within when considering how they will implement solutions to the problem at hand. Thus, the article is intended to call your attention to *potential* best practices, but it is still *your* responsibility to decide whether you will apply these recommendations or not.

Have hammer, will see nails

The first thing to consider is a caveat: VBA is truly a wonderful way to extend your application's functionality, and it can be tempting to turn to it for problems for which there exists a better solution without need for VBA. There's a saying- if your only tool is a hammer, everything starts to look like a nail. Thus, just because it's possible in VBA, it doesn't mean you should do it in VBA. SQL has its niches, and so does Access's built-in functionality and bound forms/reports/controls. Therefore, you may want to make a point of asking yourself what is the right tool for the job before swinging away.

Topics CoveredNaming Conventions & Reserved Words Option Explicit and Variable Declaration Data Types do matter Scope of Action Another Meaning of Scope Compile early and compile often and test your code just as often How do I code this? Using Constants, Functions, and Properties Eight atomic procedures are better than one big sub Using class modules to maximize reusability and reduce work
Naming Conventions & Reserved Words

Our first step is to decide on a naming convention. It actually doesn't matter what you ultimately decide on. The real criteria here is that this is a convention you are able to stick with throughout the life of project. It has failed as soon as you stray away from the convention because you found something more convenient.

The most commonly used convention among Access developers is Leszynski-Reddick style. That should give you an idea of what is usually included in such conventions.

Furthermore, you should be aware of 'Reserved Words'; it may seem intuitive to name a field storing dates "Date", but how would Access know if you're talking about a field named "Date" or a function named "Date"? This kind of naming can create confusion and unpredictable behavior. Generally, if there's a function, object, or property that has a name, it's a reserved word and thus should be avoided (or at least prefixed/suffixed).

Here's a list of reserved words that will serve as a good starting point and give you a sense of what constitutes a reserved word.

Option Explicit and Variable Declaration

The single thing that will do the most to help you to code efficiently is to require variable declaration for everything.

This can be set by Tools -> Options. On the dialog, select Editor tab and check "Require Variable Declaration"

This will automatically insert a line at top of every module:

	Code:
	Option Explicit

This protects you from accidentally creating extra variables that you did not intend. For example,


	Code:
	Public Sub TryMe()

Dim st As String

sr = "foo"

End Sub

Without the option explicit, we would end up with two variables, st which is never set, while sr now has value of "foo" which should have been set for the variable st. With Option Explicit, this would raise an error and thus call your attention to the troublesome variable which you can then take steps to correct.

Another benefit of using Option Explicit is that it helps to reinforce the good habit of specifying a data type explicitly. The default data type for any variable is Variant when we do not specify a type. While variant has its uses, it is relatively expensive because VBA has to resolve the type every time you use a variant.


	Code:
	Dim x 'This will be initialized implicitly as a variant. Not good.
Dim y As Integer 'Better now we have an explicit data type

Data Types do matter

As alluded to earlier, it is important to be very precise in what data type you use. Each data type has its costs in terms of memory consumption and time to process that data type.

Generally, it is best to use the smallest data type that fits your needs. For instance, an Integer data type will serve you well if you do not need a number less than -32768 or greater than 32767.

Likewise:

	Code:
	Dim obj As Object 'May be iffy
Dim wrksht As Excel.Worksheet 'This is more explicit

Declaring a generic Object data type is also expensive because, like the Variant data type, VBA has to resolve the capability of data type Object before it can use it. For programmers who actually need late binding, this is a legitimate use.

The bottom line is that you need to know various data types that are available to you and be explicit when defining variables. This also works for disambiguating different libraries. Suppose we used both ADO and DAO (a problem area for those using Access 2000, 2002, and 2003 with MS flip-flopping between the libraries). There is nothing wrong with using them both as long we disambiguate:

	Code:
	Dim drst As DAO.Recordset
Dim arst As ADODB.Recordset

There is one more point to remember when working with data types- it's easy to end up with several different types when you think you're using just one. This operation actually involves two data types:

	Code:
	Dim dtMyDate As Date
dtMyDate = "2008-31-10"

Which has two data types, but VBA will automatically coerce the string to a date for you, but this may not be always what you want. Therefore, there are instances where you will have problem with data types. It will be useful for you to remember that not everything is what it seems to be. This is especially true when you are dealing with objects such as controls or recordsets where you may set various data type to its various properties; being careless can have unintended side effects.

Furthermore, if it happens that you have two different data types but need to perform operations with both, it's usually a good idea to be explicit in casting them.

	Code:
	MyString = MyInt + MyDbl* 
'How does VBA know if you wanted a Double stored as a String or an Integer stored as a String?
MyString = Cstr(CDbl(MyInt) + MyDbl) 
'This explicitly tells VBA that you want your Integer cast to Double, added to another Double, then cast as a String.

Scope of Action

One simple concept becomes incredibly important all too soon when dealing with VBA. That concept is "scope of action" - which in programming terms, means - how far-reaching is this program's operation? How much do you want it to do? How much data will it have to "touch" to accomplish its goal?

At one end of the spectrum, we can talk about a bit of VBA code that is associated with a single control on a single form, with the intent of making the control's background pale green when a number is positive or pink when the number is negative. This is a very simple code requirement that probably has the smallest possible scope of action. In VBA terms, the hardest part here is knowing where to put the code, which will probably not exceed five or six lines including the IF statement and its two main branches - one for the positive case and one for the negative case.

At the other end of the spectrum, VBA can read a directory, open a file, control a loop that reads records, import data to a table via Recordset operations, and update a progress bar on a display while ALL of the above is going on. Or it can open an Excel Workbook to read various cells. It can open a Word document to perform searches and statistical operations. It can do all of these at once.

With this incredible range of action, VBA can be your friend for all sizes of problems. However, there are times when VBA is more of a hindrance than a help. For beginners, the temptation is prevalent to write some VBA code to convert some complex data from one format to another. However, an update query can often do this without any VBA code at all. OR you can write the VBA code as a function that could then be used by a query. That means that the data conversion would be in VBA code but record navigation would be done via SQL.

Therefore, one useful skill is to know whether some action is or is not within the range of actions available within VBA. It is recommended that any person wanting to solve a problem with VBA should first know other aspects of Access and SQL syntax. VBA can and will help you "re-invent wheels." This sometimes leads to a glut of wheels, which isn't necessarily a good thing.

Another Meaning of Scope

VBA actions can have implied scope. However, the VBA routines themselves also potentially have a specific scope in terms of "visibility." The visibility of a VBA function or subroutine depends on where it is defined and who can call it (activate it). Scope of visibility, in simplest terms, is "who/what can see it?"

Access supports two types of VBA (code) modules. These are the standard modules and the class modules. A standard module is accessible from an object shown in the Module tab of the database window. Code in a general module could be activated from any form or report you have defined.

Forms and reports have class modules. All private procedures in a class module is visible only from the object associated with that module, and public procedure are accessible only when the object is open. A closed form doesn't exist in the list of forms. (It is, however, in the "Documents" or "AllForms" collection. But that's a concept for another lesson.)

A common mistake is to define a function in a class module for form A and expect form B to be able to use that function. If you want to reuse a function in multiple forms, it must be stored in a general module. This, too, is a practical application of the concept of "scope."

One fine point: The scope of action for a piece of VBA code does not have to match the scope of visibility for that code. But some authorities suggest that the two should be related by your choice of module design.

Therefore, an issue to consider when you are thinking about using VBA is to decide HOW you want to use it - from many places or one – and how much you want to do with it - narrow scope of action or wide-open scope of action. "Ambitious" is a word that accurately describes a novice VBA programmer wishing to write his/her first VBA subroutine to have a wide scope of action from a class module.

So when you are contemplating VBA as a solution, ask yourself two questions. (1) Who/what will need to do this? (2) To what must this be done when run from each of the possible answers to #1? THEN and ONLY THEN are you ready to think about the steps required to implement a VBA solution to your problem.

Compile early and compile often and test your code just as often

The biggest favor you can do to yourself is to make a habit of compiling your code frequently. This aids the process of debugging because you confine yourself to only last few lines you added since the last successful compilation.

Likewise, you definitely want to get in habit of testing your code often. By focusing on writing one procedure at a time and testing it, you can make the process simpler. Of course, this is not always possible, so discretion is required in determining what constitutes smallest block of code that can be meaningfully tested.

This is especially true with "Object or With variable not set" or "Too few parameters; expected 1" and similar run time errors. Run time errors are those not caught by the compiler, which only looks at syntax errors. Thus, it may be not always clear what triggered the error in first place. You do not want to see that error after having written a hundred lines of code without having had compiled & tested the code.

How do I code this?

A common situation that crops up even for most experienced programmers is they have a process that they haven't quite coded for and don't know the appropriate methods, properties, or statements to execute the statement. When you consider that Windows APIs collectively can have 60,000 different procedures, it should become apparent that even the C programmer has to know how to get the data he needs to program, just as much as we need to programming in VBA.

Therefore, a good habit is to get quite familiar with help files. I even reference help files for several methods that I've already used just to make sure I am aware of all possible side effects of using those methods. At this point, I should note that for Access 97, help files is quite extensive, but not as in 2000 & 2003. In case of 2003, I actually find that using search from the Visual Basic Editor to get the appropriate information is more effective than using the search in Access UI.

Another useful tool is to use Object Browser (can be called by pressing F2 or Views -> Object Browser), which is very useful for a quick perusal of different libraries, objects contained inside each of libraries and what arguments they take and/or return.

Also, it bears repeating that for any problem you are having right now, someone had it before you and in all probability already solved it, so it is usually a matter of searching effectively on various websites. Sometimes they are contained in whitepapers, technical documentation, and/or kb/msdn articles provided by Microsoft. Sometimes they are just a post on a bulletin board. The greater length you are willing to go through to read the fine print and test it out, the better programmer you will be.

Finally, there are times where it's just beneficial to test for yourself instead of running on a wild goose chase or waiting for an MVP to swoop in and rescue you or even if you just plain don't understand. This also implies that your debugging skills should be honed so you can be fully informed when examining the result of the test case. Tips on debugging is beyond the scope of this paper but there are several resources detailing different tools used in debugging.

Using Constants, Functions, and Properties

A rule of thumb is that whenever you have a magic number, a formula or something that exemplifies your business rules, it belongs as a constant, function, and/or property of a module.

For magic numbers such as Pi of a circle, Euler's constant 'e', or anything that will never change ever, constants makes the most sense. (If you need a magic number to be stored persistently but not immutable, the lookup table may be more appropriate.)


	Code:
	Const Pi As Double = 3.14159

With numbers that you use as a part of business rules... say, calculating tax rates, functions are more appropriate.


	Code:
	Public Function TaxRate(SubTotal As Decimal) As Decimal

TaxRate = SubTotal + (SubTotal * CurrentTaxRate)

End Function

Note that the 'CurrentTaxRate' isn't defined in that function- that is because it is up to you to decide the best place to store such information. Perhaps querying a table holding history of all tax rates is what your business requires. Or the business may be content having one tax rate that is updated periodically. Regardless of how you decide to implement this, functions are big time-savers.

A prime example is when you need to use calculated controls. It's easy to say "Why write a function when I can just do it all in a calculated control with the control source set to "=SubTotal + (SubTotal * CurrentTaxRate)"?

Well, that is true. Now say that you have 100 of them. Now you've been told that you must charge a shipping & handling fee after the tax. Now you could go and change every one of 100 calculated controls, or you could just go to the function and change it once and all the 100 controls will work as expected. Your decision.

Ideally, your programs should be a bunch of black boxes that can be strung up with a given string so you do not have to go through the arduous process of figuring out which one line causes an error in a giant main function (and there are plenty of errors that will raise on one line but is actually caused by a bad line that could be several lines earlier).

Furthermore, by far the biggest advantage of using functions (and subroutines as well) in a standard module is that it's incredibly easy to debug. You do not even need to open a form to debug it. You do not need to run the application and test it as if you were an end user to debug it. You only need to run it either by clicking the play button or calling it from immediate window (the small window at the bottom of the Visual Basic Editors):

	Code:
	?MyFunction(MyArguments)
Result will be printed in the immediate window

Property is like Public variables (aka Globals) but is much more versatile and provides you with more control over how it can be manipulated.

A good example would be when you want to use a listbox and its rowsource needs to be changed for every record. Instead of referring to the querydef and recordset every time you fire the OnCurrent event of the form, you can just make it a property and actually concentrate on modifying the parameters. This is what it would look like in the form's module:

	Code:
	Private rst As DAO.Recordset

Property Get qdf() As QueryDef

'MyQuery is a Parameter Query with the SQL:
'PARAMETERS lngPrimaryKey
'SELECT a FROM table WHERE b = [lngPrimaryKey];
Set qdf = CurrentDb.QueryDefs("MyQuery")

End Property

Private Sub Form_OnCurrent()

qdf.Parameters = Me.MyPrimaryKey 'Set the parameter
Me.MyListBox.Recordset = qdf.OpenRecordset

End Sub

Note that qdf is a read-only property. You can refer to it from everywhere within the form's module (actually, from anywhere as properties are public by default but can be set private if so desired), and if you need to change the name, there's only one place to do it. Everything else will just work™.

Eight atomic procedures are better than one big sub

A common mistake for beginning programmers is to beam with pride over their one-hundred line subroutine. Rarely, if ever, should any procedure should be that many lines. This usually results because you try to wrap a process into a single procedure, when each procedure should in theory represent single task.

It's much easier to write out a procedure that does only one thing but does it well, and then pipe several simple-minded procedures together to generate a complex output. In fact, that is how programmers have been doing it for decades. Suppose we had a process that required us to extract a set of data, format it into something, then export it out. It would be easier to write one sub:

	Code:
	Public Sub Foo()

Extract it!

Format It!

Export It!

End Sub

But this is better for a production environment:

	Code:
	Public Sub foo()

Extract(MyData)

Format(MyData)

Export(MyData)

End Sub

Public Function Extract(MyData As Arguments)

Extract It!

End Function

Public Function Format(MyData As Arguments)

Format It!

End Function

Public Function Export(MyData As Arguments)

Export It!

End Function

Now, a case can be made that that the second approach is more work than first one because you now have to write four procedures instead of one. However, by writing four smaller procedures, you significantly simplify your debugging process because you only need to know that your function works as expected then forget it. Furthermore, down the road, you may discover that you needed a similar task for a different process... Well, golly, it's a function so it's ready to go! Increasing reusability of your code will make you more productive.

Also, it's easier to go back five years later and re-read the atomic functions and understand what they do, rather than reading the big sub and wondering what the heck you did there. So in this sense, the second one leads itself to better documentation without requiring you to write comments as would certainly be required in first example.

Using class modules to maximize reusability and reduce work

One common task that crops frequently is automating Excel and manipulating its spreadsheet. Thus, it's not uncommon to see code for initializing such automation such as this:


	Code:
	Private Sub foo()

Dim objExcel As New Excel.Application
Dim objWorkBook As Excel.WorkBook
Dim objWorkSheet As Excel.WorkSheet

Set objWorkBook = Excel.OpenWorkbook("MyWorkBook")
Set objWorksheet = objWorkBook.Worksheets(0)

'Do something....

Set objWorksheet = Nothing
Set objWorkBook = Nothing
Set objExcel = Nothing

Whew, that was a lot of work managing several objects, especially if all we wanted to do was to export a bunch of data in a specific worksheet for same workbook. And then multiply that by every time we need to create a new procedure to deal with different aspects (e.g. maybe to read off a worksheet for example, or to add new worksheet or whatever).

Some may use public variables to help resolve this issue but that has its own set of problems. One good example is if you set a public variable to this worksheet but later refer to it thinking it was set for that worksheet, you have a problem.

A Class module is a perfect solution in this circumstance. You only need to declare one line:


	Code:
	Dim MySpreadSheet As New MyExcelClass

And the class module will run through the steps to initialize the application for you, retrieve the workbook and worksheet for you from this single line. Depending on what methods and properties you give to a class module you can even make it as simple as:


	Code:
	MySpreadSheet.AppendNewData(MyData)

In which the method AppendNewData (written by you) already knows the correct position to place the new data, so you only need to write the class module once, then just refer to the methods of that class module. Intellisense (that tiny scrolldown menu that pops up to help you complete code) also will work for your custom class modules.

If you find yourself needing classes; Here's a primer. Generally, your code should aim to be highly cohesive yet loosely coupled.

Conclusion

Whenever you turn to VBA to fill in the niches to meet your business requirements, it is possible for you to start out with a generic procedure that encapsulates the task at hand. When you have a rough draft, it become more apparent how you can refactor your code into black boxes that are 1) easy to use, 2) can be used for several different tasks, 3) are self-documenting or self-evident. Developing those habits will serve you well in long time.

Last thing. Whenever a solution seems out of reach, or the light bulb above your head turns on, no matter how brilliant and crystal clear it is, you want to first ask yourself: Who else has had this problem or task in front of them, and how did they solved it? Their solution is just a search away. By doing this, a solution can probably be found to your insurmountable problem, or if others solved similar tasks differently than you had planned to, then maybe your planned solution is not the best way to approach your task. Paraphrasing AWF's geoergedwilkinson: "People are very clever in their ability to twist VBA or Access to do easy things in the hardest way possible". A critical eye is what saved me from running down a long alley only to hit a wall or re-inventing unnecessarily complicated wheel when there already exist a nice & simple wheel.

Contributors
ChrisO
datAdrenaline
georgedwilkinson
Rabbie
stopher
The_Doc_Man

This is an employee database that will be used by management to keep track of tasks, backlogs, etc.

I have a QBF form set up to query based on user input.

The form queries many tables and turns the data to a report.

My problem is when the form displays, it lists all the fields from all the tables. I do not want this. I just need individual records listed from each table in the report. The individual field names of each table do not need to de displayed, the data must appear under my "generic" fileds listed in * below.

I have many tables set up like the following: (defined by task) i.e., each table is for a specific task

ID: autonumber
tblatr_ID: text (this field is automatically filled with the name of the task when the user keys)
tblatr_date: Date/Time
tblatr_user: Text
tblatr_num_req_rec: number
tblatr_num_req_pro: number
tblatr_time: number
tblatr_oldest_date: date/time

The query by form allows the user to do a parameter query. The report I created picks up the query (QBF) info.

I need the report(s) field(s) to be displayed dynamically like this:

*user - date - # of requests processed - # of requests received - time - ID (task)

is there any way to have records from all searched tables display under these field headings in the report? The records need to be returned one after another, as in a spreadsheet, but they will be from different tables.

Please help! I have a headache.......

If anyone needs me to post my code, etc. let me know!

below is an example of my QBF form (work in progress)

Option Compare Database
Option Explicit


Private Sub cmdRunQuery_Click()
Dim db As DAO.Database
Dim QD As QueryDef
Dim where As Variant

Set db = CurrentDb()
'Delete the existing dynamic query; trap the error if the query does not exist.

On Error Resume Next
db.QueryDefs.Delete ("Dynamic_Query")
On Error GoTo 0

' Note the single quotation marks surrounding the text fields [Ship
' Country] and [CustomerID].
' Note that there are no type-casting characters surrounding the
' numeric field [EmployeeID].

where = Null
where = where & " AND [tblce_user]= '" + Me![UserName] + "'"

'NOTE: In Microsoft Access, when you use the plus sign (+) in an
'expression in which you are concatenating a variable of the numeric
'data type, you must use parenthesis around the syntax, as in the
'following example:
'
' where = where & (" AND [EmployeeID]= " + Me![Employee Id])
'
'You must also use a conversion function to make sure that the proper
'conversion (to either NULL or String) takes place.

' The following section evaluates the ShipCity criteria you enter.
' If the first or last character of the criteria is the wildcard
' character (*), then the function uses the "LIKE" operator in the
' SQL statement instead of "=". Also note the single quotation
' marks surrounding the text field [ShipCity].
If Left(Me![UserName], 1) = "*" Or Right(Me![UserName], 1) = "*" Then
where = where & " AND [tblce_user] like '" + Me![UserName] + "'"
Else
where = where & " AND [tblce_user] = '" + Me![UserName] + "'"
End If

' Note the number signs (#) surrounding the date field [Order Date].
If Not IsNull(Me![UserEndDate]) Then
where = where & " AND [tblce_date] between #" + _
Me![UserStartDate] + "# AND #" & Me![UserEndDate] & "#"
Else
where = where & " AND [tblce_date] >= #" + Me![UserStartDate] _
+ " #"
End If

If (Me!task) = "cycle ends" Then

GoTo exit_tblce
End If
If (Me!task) = "all" Then
GoTo exit_all
End If

Exit Sub


' Remove the following MsgBox line if you do not want to display the
' SQL statement.
' NOTE: The Mid function is used in the following MsgBox function to
' remove the word AND that follows the first Where clause. If you do
' not use the Mid function, the SQL statement contains the word AND
' at the beginning of the WHERE clause, for example:
'
' Select * from Orders where AND [CustomerID] = 'CACTU'
exit_tblce:
Set QD = db.CreateQueryDef("Dynamic_Query", _
"Select * from tblce " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenReport "test report", acViewPreview

Exit Sub

exit_all:
Set QD = db.CreateQueryDef("Dynamic_Query", _
"Select * from tblce,[tbldts],[tbl3541],[tblatr],[tblatr1],[tblatu]" & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenQuery "Dynamic_Query"

Exit Sub
MsgBox "Select * from tblce " & (" where " + Mid(where, 6) & ";")
Set QD = db.CreateQueryDef("Dynamic_Query", _
"Select * from tblce " & (" where " + Mid(where, 6) & ";"))
DoCmd.OpenReport "test report", acViewPreview

End Sub


Private Sub Detail_Click()

End Sub

Hi ,

Could someone please help me with the VBA code behind my post button which is on my mainform. What I want is to check that the following controls have been populated. The first three work fine (Title, TransactionDate and Username) but as soon as I try the subform it doesn't work properly. If I deliberately leave out say CC and then click the post button it say's "please enter a/c no" and then comes up with "There is an invalid method in an expression".

I'm probably doing something really daft.

cheers
Wrightie


This is the code I'm using ...


	Code:
	 
Private Sub Post_Click()
On Error GoTo Err_Post_Click
If Len(Nz(Title, "")) = 0 Then
   MsgBox "You must enter a Title", , "Input Error!"
   Cancel = True
   Title.SetFocus
   Exit Sub
End If
If Len(Nz(TransactionDate, "")) = 0 Then
   MsgBox "You must enter a Transaction Date", , "Input Error!"
   Cancel = True
   TransactionDate.SetFocus
   Exit Sub
End If
If Len(Nz(UserName, "")) = 0 Then
   MsgBox "You must select a Username", , "Input Error!"
   Cancel = True
   UserName.SetFocus
   Exit Sub
End If
If Len(Nz(Me.Journal_Entries_Subform.Form.AcNo, "")) = 0 Then
   MsgBox "Please enter an A/c No", , "Input Error!"
   Cancel = True
   Me.Journal_Entries_Subform.Form.SetFocus
   Exit Sub
End If
If Len(Nz(Me.Journal_Entries_Subform.Form.CC, "")) = 0 Then
   MsgBox "Please select a CC", , "Input Error!"
   Cancel = True
   Me.Journal_Entries_Subform.Form.SetFocus
   Exit Sub
End If
If Len(Nz(Me.Journal_Entries_Subform.Form.Dept, "")) = 0 Then
   MsgBox "Please enter a Department", , "Input Error!"
   Cancel = True
   Me.Journal_Entries_Subform.Form.SetFocus
   Exit Sub
End If
End Sub




Not finding an answer? Try a Google search.