Update field using vba Results

I've got three fields with three different dates, all based upon one or the other for when reports should be turned in where.

What I want to do, is have it set up so that whenever the report is due, the dates will be automatically entered, and updated if the due date changes.

I know that in Access 2003, you could use the SetValue in a Macro, but I don't believe (but haven't seen anything that works the same) that option exists in 2007, and I believe that creating the code in VBA would probably be "easier."

If there's another topic dealing with this, I must've not been searching using the appropriate keywords, and for that I apologize.

Again, I'm using Access 2007.

Big surprise, right? A converted Macro doesn't work the same way in VBA?

I've got a Macro that will update a date field based on the users selection. As a macro, it works perfectly. However, I'm wanting to sign my database so I can rid my users of the Security Content warning whenever they open it. Using the .mdb format, I don't have the Publish option and as much as I would love to be in the 2007 file format, I can't due to some users not having Access 2007 on their stations.

Here is what was converted over from the Macro:

	Code:
	[Forms]![frmReports]![Due to J0] = Eval(IIf([Forms]![frmReport]![Report Type] =
"A","B","C"),[Forms]![frmReport]![MajorRptDue],[Forms]![frmReports]![RptDue])

Any clues on what I'm doing wrong here? Again, this is in Access 2007 using the .mdb format. Also, this is used as an event procedure after the Report Type is selected.

Alright, this just doesn't make any sense.

I have two MDBs, one is the front end application, the other is the actual table database.

Some modifications have arisen that basically require me to add a few things to the "table" database, so the MDE application will perform according to client specifications. Well, I don't want to toast the data that already exists in the database, nor do I want to deal with copying the data, so I figured, I would write an Update script, that would simply add the columns and constraints to the existing table and add the primary key parent table to the table database, and then add the linked table definition to the mde database.

So, being more of a SQL Server fan, i perfer sql , so I use it to create the table.

set db = OpenDatabase(path & dbfile)

sql = "Create table X (...)"
db.Execute SQL
sql = "ALTER Table Y ADD Column X"
db.execute sql

this part works fine (other than my post concerning the add constraint but this is about the link table so, i won't repost the same question).

So now, I've DB a database variable that is set tot he the "tables.mdb" and then CurrentDB which is the current open MDE file.
I try:

set tdf = CurrentDB.CreateTableDef("X", dbAttachedTable, "X", ";DATABASE=" & path & dbfile);
CurrentDb.TableDefs.Append tdf

the createtabledef says invalid argument. okay. why, dunno, but access is twitchy and not very helpful on error mearning, so i try instead:

set tdf = CurrentDB.CreateTableDef("X")
tdf.Connect = ";DATABASE=" & path & dbfile
CurrentDb.TableDefs.Append tdf

on the Append() call, i get "No field defined -- cannot append tabledef"
Well, frankly that's stupid. Of course id didn't define any fields. the fields are defined in the linked database, not this one. When i use the GUI to link a table I don't have tell it ANYTHING about the fields. only what the access mdb file is, and the table i want.

So, where is that command, the simple: "Add Link to this table on that MDB" command. I mean, if the GUI can do it that simply, I should be able to too. It also begs the question, I can create the links and then have my App.mdb file reference the new tables and columns i've added. But if i send over the mde file which is going to be looking at the "old" tables.mdb (that is missing those tables/columns) is that going to muck up my queries and forms, from the get go, or will it not be concerned since my first action is to update the tables.mdb with the script before any of those new features are accessed?

Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner

I have a piece of VBA on a form that I run manually from a command button, but I want to run this using a timer event.

I also want to change the timer (its a field on the form) as and when and this automatically updates the timer event, how do i do this please?

Many thanks.

I've used the code below to import a text file(with header record) into Access, but I don't know how to take the header of my file when I bring the file. It's a small part but I'm not sure how to get it done.

Thanks


Dim F As Long, sLine As String, A(0 To 4) As String
Dim db As Database, rs As Recordset
F = FreeFile
Open "c:temptest.txt" For Input As F
' uncomment following line if you want to skip field headings
' Line Input #F, sLine
Set db = CurrentDb
On Error Resume Next
' db.Execute "DROP TABLE TestImport"
On Error GoTo 0
' db.Execute "CREATE TABLE TestImport (ID LONG, [Desc] TEXT (50), " _
' & "Qty LONG, Cost CURRENCY, OrdDate DATETIME)"
Set rs = db.OpenRecordset("TestImport", dbOpenTable)
Do While Not EOF(F)
Line Input #F, sLine
ParseToArray sLine, A()
rs.AddNew
rs(0) = Val(A(0))
rs(1) = A(1)
rs(2) = Val(A(2))
rs(3) = Val(A(3))
rs(4) = CDate(A(4))
rs.Update
Loop
rs.Close
db.Close
Close #F
End Sub
Sub ParseToArray(sLine As String, A() As String)
Dim P As Long, LastPos As Long, i As Long
P = InStr(sLine, "|")
Do While P
A(i) = Mid$(sLine, LastPos + 1, P - LastPos - 1)
LastPos = P
i = i + 1
P = InStr(LastPos + 1, sLine, "|", vbBinaryCompare)
Loop
A(i) = Mid$(sLine, LastPos + 1)
End Sub

I'm using the below code to import records from a CSV file. I'm running into problems when I run into double quotes with a comma in the field.

This extra comma is causing another field to be created.




Dim F As Long, sLine As String, A(0 To 4) As String
Dim db As Database, rs As Recordset
F = FreeFile
Open "c:temptest.txt" For Input As F
Set db = CurrentDb
On Error Resume Next
On Error GoTo 0
Set rs = db.OpenRecordset("TestImport", dbOpenTable)
Do While Not EOF(F)
Line Input #F, sLine
ParseToArray sLine, A()
rs.AddNew
rs(0) = A(0)
rs(1) = A(1)
rs(2) = A(2)
rs(3) = A(3)
rs(4) = A(4)
rs.Update
Loop
rs.Close
db.Close
Close #F
End Sub


Sub ParseToArray(sLine As String, A() As String)
Dim P As Long, LastPos As Long, i As Long
P = InStr(sLine, ",")
Do While P
A(i) = Mid$(sLine, LastPos + 1, P - LastPos - 1)
LastPos = P
i = i + 1
P = InStr(LastPos + 1, sLine, ",", vbBinaryCompare)
Loop
A(i) = Mid$(sLine, LastPos + 1)
End Sub

I built a query in the QBE to find the max value of a particular table that has a date/time stamp. The query returns one record (a PKID autonumber) with one field. I want to use this number to populate a field on my form using vba in the before update event.

I am at a loss as to how to do this however.

After some research I thought I might need to do something with a recordset using ADO or something and have a book that tries to teach how to use it, but I don't understand it all. And was wondering if there is an easy way that does not require me to learn this new method.

Or do I need to forge ahead and learn how to use ADO?

I have a split form to update a table and on the form I have a clear button to clear the table for the next entries. I want the split form to requery the table after the delete query runs in the data portion of the split form I get the #Deleted in all the data fields. I know that the table is empty but it confuses the user and looks terrible in general. I would use vba to open and close the form to eliminate the problem but there is several key unbound text fields that I don't want to have to repopulate. Please check my code or give me suggestions as to correct the issue.


	Code:
	Private Sub Command20_Click()
On Error GoTo Err
'Clears the tblSelectedOrderNumber table for new entries
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDeleteSelectedOrderLines"
DoCmd.Requery "tblSelectedOrderNumber"
DoCmd.SetWarnings True
OEOO_ORDR = ""
txtRecCount = 0
Err:
DoCmd.SetWarnings True
End Sub



I have a database that contains a [UNITS] table, with {miles and hours} fields. This data base also has a [WORKORDERS] table that contains {miles and hours} fields.

A workorders form is used to open new workorders where current {miles and hours} fields are populated. A [CLOSED WORKORDER] button is (checked) when work is done, (M+/orH), parts and labor data is complete.

Currently I manually update the [UNITS] table {miles and hours} fields. I would like to VBA code or something,(?) so when workorder form [CLOSED WORKORDER] button is checked, the workorder form mileage/miles updates the [UNITS] table fields.

I DON'T KNOW HOW TO DO THIS, can anybody show me? / I might learn something.

I am looking to see what the code would be for an update query based on the following:

I have 2 tables = UW Table and New Extract

In the tables there are 2 fields that have correct data = Underwriter and UWr.

I want to then update data from a Field called Team into a field Tech Team

So an underwriter will appear in both tables, and in one table I have the team they belong to and want to update the table where currently there is no information. Both tables are updated through different systems and code is used to bring the data into my database.

I would appreciate any guidance.

UW Table has UWr and Team and I want to update the table New Extract and the field Tech Team based on Underwriter = UWr and use the Team in UW Table

I hope this makes sence.

Posted elsewhere, a very complex report is taking a long time to run.
The report has a few custom functions (see code below) called in some queries fields. This is of course taking time.

The goal is to ask for streamline code for this purpose. Searching throught 6,000 wells (i.e. Customer ID) takes its toll on time.
While updating and normalizing each part of someone else's database, the Stip codes are found in three different tables and support three diffent forms.

The objective is to use a formula now for the rest of the application. The day that I normalize the three tables, one formula can be updated so that all the previous forms, reports and activities don't need to be modified.

The purpose of this formula is to concat all the codes. The codes in each of the three tables are child tables, so there can be zero (most oftern the case) or one - or many Stip codes. (This function only calls 2 of those three)

Considering a DbOpenForward Only, but this may prevent the RecordCount from being efficient. In about 85% of the cases, there is not Stip code.

Any ideas on how to save computer bits of time would be appreciated.
One consideration is to move the dbStipsCodes to the application form level so it is not constantly created and destroyed. But, is that really saving all that much time?
This function is primarly used for a column in Reports.

All comments and suggestions are welcome. Thanks!

dbOpenForwardOnly


	Code:
	Function ConcatStipsCodes(intPrimaryKeyID As Integer) As String ' Wells Primary ID passed in
      Dim dbStipsCodes As DAO.Database
      Dim rsStipsCodes As DAO.Recordset
      Dim StipString As String                      ' Output String
      Dim strCriteria As String, strsStipsCodesQL As String
      ' Change SQL statement to accept intPrimaryKeyID as shown below
10        strsStipsCodesQL = "SELECT APD_Stips1.Stip_Abv " & _
          "FROM Wells INNER JOIN (APD_Stips1 INNER JOIN APD_Stips2 ON APD_Stips1.ID_APD_Stips1 = APD_Stips2.ID_APD_Stips1) ON
Wells.ID_Wells = APD_Stips2.ID_Wells " & _
          "WHERE (((Wells.ID_Wells)=" & intPrimaryKeyID & ")) " & _
          "ORDER BY Wells.ID_Wells, APD_Stips1.Stip_Abv;"
20    On Error GoTo Err_ConcatStipsCodes
30    StipString = ""
40        Set dbStipsCodes = CurrentDb
50        Set rsStipsCodes = dbStipsCodes.OpenRecordset(strsStipsCodesQL, dbOpenSnapshot)             ' snapshots run faster
60        With rsStipsCodes
70            If .RecordCount  0 Then
80                Do While Not rsStipsCodes.EOF
90                    StipString = StipString & rsStipsCodes("Stip_Abv") & " "    '

All,

I have a table of data containing 30 fields updated every morning by operations. For the purpose of providing some background to my problem, I will provide samples for 3 of these fields.

Feild 1: ITEM
Field 2: STATE
Field 3: UOM

My problem is that some of the UOM's recorded by the system are blank. For example, ITEM: 23, STATE: 1, UOM: "EA", ITEM: 23, STATE: 2, UOM: "BLANK", etc..

When I track back to the source data, I find that they (the UOM's) are not recorded in the system. As a result, I need to update all the blank records with the first valid record. Problem is, I need to do this for like 30,000 records. So it is very time consuming.

I want to know how I can do the following using open recordset loop method in VBA:

1. Open the table and find a UOM for each ITEM
2. Update all the instances of "NULL" with the UOM

Notes:

I have enclosed a sample db with data for reference.

Any help would be appreciated.

Regards,
CF

I have created a report based on a query. I now want to access the records selected by the query i.e the records that show up in the report and update the printed field from no to yes. how do I do it. My thinking is to use a while (!EOF) Do... Statement. But being a vb newbie I dont know how to access the read the records in the report.
Thanks for your help

Hello everyone.

First of all, I apologize for this question being too basic and/or lacking Access jargon as I just started using Access a month ago.

With that being said, I have a form with controls named: [Personnel 1], [Personnel 2], … , [Personnel 10] (combo boxes that store integers to be more precise)

On that form I have a date field that if updated needs to trigger a set of updates on different tables for all 10 “Personnel” fields.

The following is the beginning of my subprocess:

Private Sub Actual_Start_Date_AfterUpdate()
Dim intCounter As Integer
Dim strPers As String

For intCounter = 1 To 10
strPers = "Me![Personnel " & intCounter & "]"

'Checks if a value has been entered in the Personnel slot
If Not IsNull(strPers) Then
……..

The problem with the above code is that as I assemble a string (strPers) that would contain the path to the field I want to use as reference, it ALWAYS evaluates to true because the string itself is never Null.

What I’m trying to do is automate an update process that in other parts of my code I successfully accomplished using something like:
If Not IsNull(Me![Personnel 1]) Then

This “If” statement will only be true if there is any data stored in the field Me![Personnel 1].


Just in case it is important, I am using Access 2003 (not my choice but my company’s) and Windows XP.

Any help would be greatly appreciated.

Cheers!

Sebastian

Hi guys,
I'm new to this forum. I need the help of some expert!

I'm trying to build a report interface of an Access 2007 DB using excel and ADODB. All the stubs and tests I did using code found on the web worked fine. So I started a clean coding.

1. What I did before (testing time):
A sub triggered by a button, using ADODB.Command object. It worked fine, the parameter query I called gave back the right result data.

2. What I did after:
A function (so I could pass query parameter stored in other spreadsheet cell) which would have copied the result into a range (also passed as argument). Result: it doesn't work. The function went into error when calling ADODB.Recordeset.MoveFirst method, or ActiveWorkbook.Sheets("Sheet1").Cells(1,1) = rs(0).Value.

I thought the problem was accessing data while excel holds the cell calling the function...

3. The workaround
I tryed working around this. I thought the problem was using the Recordset object into a function so I stored the parameter in global variable and I called a sub to update data using the Calculate event trigger. It worked.
So I wanted to clean the code and I made a class to store the parameters and the method to update data in the spreadsheet. And this time, a sub in a class module, it didn't work.

I can't image any other workaround or solution. This seems to be crazy.
I know this post is borderline among Access, databases and Excel, but as I did this after have thought to create an report interface for my db, I'm pretty sure people here have already coded something similar or at least tryed as I did...

I hope somebody can give me an help, or at least give me a scientific explaination on why this VBA seem to give a lot of limitation.

Thanks in advance to all the readers and replyers!

Here below the piece of code which doesn't work:

CLASS MODULE

	Code:
	Public StartDate As Date
Public FinishDate As Date
Public Interval As String
Public Accounts As Range
Public Destination As Range
Public BalanceType As String
Public Orientation As String
Public isDirty As Boolean

Public Sub getData()
    
    On Error GoTo ErrHandler
    
    Dim cn As ADODB.Connection
    Dim com As ADODB.Command
    Dim rs As ADODB.Recordset
    
    Dim dbpathname As String
    Dim QueryName As String
    Dim formatString As String
    Dim plotH As Boolean
    Dim k As Long
    Dim j As Long
    
    ' ------------------------------
    ' -- Setup process parameters --
    ' ------------------------------
    
    ' INTERVAL
    Select Case Interval
    Case "D"
        formatString = "yyyy/mm/dd"
    Case "M"
        formatString = "yyyy/mm"
    Case "Q"
        formatString = "yyyy/qq"
    Case "H"
        formatString = ""
    Case "Y"
        formatString = "yyyy"
    End Select
    
    ' BALANCE TYPE
    Select Case BalanceType
    Case "SUBTOTAL"
        QueryName = "qryInterface_TransactionBalanceByAccount_Subtotal"
    Case "TOTAL"
        QueryName = "qryInterface_TransactionBalanceByAccount_Total"
    End Select
    
    ' ORIENTATION
    Select Case Orientation
    Case "H"
        plotH = True
    Case "V"
        plotH = False
    End Select
    
    ' -------------------------------
    ' -- Start database processing --
    ' -------------------------------
    
    ' Retrieve database path
    dbpathname = "D:myAccountingmyAccounting.accdb"
    
    'Create the connection string and open the connection
    Set cn = New ADODB.Connection
    cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbpathname & ";Persist Security Info=False;"
    cn.Open cn.ConnectionString
    cn.CursorLocation = adUseClient
    
    ' Create and setup the command object
    Set com = New ADODB.Command
    com.CommandType = adCmdStoredProc
    com.ActiveConnection = cn.ConnectionString
    com.CommandText = QueryName
    
    ' Setup query parameters
    com.Parameters.Append com.CreateParameter("FormatString", adVarChar, adParamInput, 255)
    com.Parameters(0).Value = formatString
    
    com.Parameters.Append com.CreateParameter("startdate", adDBDate, adParamInput)
    com.Parameters(1).Value = StartDate
    
    com.Parameters.Append com.CreateParameter("finishdate", adDBDate, adParamInput)
    com.Parameters(2).Value = FinishDate
    
    ' Execute the command and get result into a recordset
    Set rs = com.Execute
    
    ' Copy result into excel worksheet
    'ActiveWorkbook.Sheets("Sheet3").Cells(10, 2).CopyFromRecordset rs

    ' Copy results with a loop (less performant)
    rs.MoveFirst
    j = 1
    Do While Not rs.EOF
        For k = 0 To rs.Fields.Count - 1
            ActiveWorkbook.Sheets("Sheet1").Cells(j, k + 1) = rs(k).Value
        Next
        rs.MoveNext
        j = j + 1
    Loop
 
    Exit Sub

ErrHandler:
    If Err.Number  0 Then MsgBox _
        "CustomerName- Error#" & Err.Number & vbCrLf & Err.Description, _
        vbCritical, "Error", Err.HelpFile, Err.HelpContext
End Sub

This is the error arose
#-2147467259
"Operation is not supported for this type of object"

I have a program where I am calling an application programmed in C#. When my process finish, it writes a value on a table on my Access database which a want to read. I am using a recordset to get this value on my code VBA. But the problem is that sometimes I am not getting the value updated if I don't wait for a few seconds (not always the same time).

I was looking on internet but the only thing i found was that Access has an interval of refreshing when the values are modified by other user and this applies to the forms for example. But I am not working with a form, in this case I am just reading the value with my recordset.

I would like to know if there is a way to force the database to refresh before reading my value.

Thanks in advance for your replies

Hello, i'm new to the forum and i've been working on this problem for the best part of a week. So far all was going well until i came to this issue.

I've recently started a new job which involves using access 2003 and VBA and was given a few tasks to do, as follows
find or create code that can get a users login name (done) make a form that takes a snapshot of a user selected field for editing (done) Make the form save any edits to the selected fields in the form table (done) Make the form get all the edits from the selected table and log them in a new table (very stuck)
I have successfully created a loop that cycles through the fields and gets the values inserted whether edited or not. And i have an SQL Statement that inserts fields into the edited field and the username of the person who inserted them (although the loop and the statement are not linked yet, as i have tested them individually not together yet)

However my loop cannot compare the value of the field in the loop with what was originally in the field.

i shall give you the code of the 'save' button i have created to perform this and comment what it does and the variables etc to give you an idea. if you need anymore information just say.



	Code:
	Private Sub save_click()

Dim SQLstr As String
Dim Field As Integer

' prikey string1 and dateone are global variables Me!field1edit et all are the values when the save button is clicked,
whether edited or not 
PriKey = Chr(34) & Me!PrimaryKeyFieldEdit & Chr(34)
String1 = Chr(34) & Me!Field1Edit & Chr(34)
DateOne = Me!Date1Edit

' this is the update string which updates the fields in the table
SQLstr = "UPDATE TwentyFieldTable SET TwentyFieldTable.field1 = " & [String1] & ", TwentyFieldTable.Date1 = #" & [DateOne] &
"# WHERE (((TwentyFieldTable.PrimaryKeyField)= " & [PriKey] & " )); "
Debug.Print SQLstr

'this is the for loop which is incomplete
  For Field = 1 To Me.Count - 1

'this bit breaks the code
      If Me(Field).Value  Me(Field).Value Then
'this bit tells me whats value is in the field, for testing purposes mainly
         MsgBox ("field contains " & Me(Field).Value)
'this is where IF the field has been altered the SQL Insert Statement (below will be placed)

      'elseIf  field is the same then end if and continue the loop
      End If
   Next Field
   ' Toggle the flag.
   status = Not status

'this is my insert statement with test values except getusername(), which goes to a module to get the users login name

INSERT INTO project2 ( EditedField, oldvalue, newvalue, username )values( 'field1' , 'test' , 'test2' , getusername()) ;


DoCmd.SetWarnings False
DoCmd.RunSQL SQLstr
'DoCmd.Close , frmMassiveEdit
End Sub

like i said i've really struggled with this part, i can't get my head around it.

Within Lotus notes I have set up a dummy mail box to store emails sent and received from an access database. I have got my code to save a copy of sent emails to the dummy box, and have set up the outgoing email so that reply to is set to the dummy box, however the e mail says that it is sent from me, not the dummy box, so some of my recipients reply direct to me. Also any delivery failures are sent to my box, not the dummy box. How can I write my code so that the 'sent from' appears as the dummy box?

	Code:
	Sub TestDue()
'notes objects and variable for email addresses
Dim NotesDB As Object
Dim NotesDoc As Object
Dim NotesRTF As Object
Dim NotesSession As Object
Dim strsupportemail  As String
'Dim avarattach(1) As Variant
Dim richStyle As Object
'database objects
Dim mydb As DAO.Database
Dim myquery As DAO.Recordset
' variables to check if the email addresses match
Dim currentemail As String
Dim Driver_Name As String
Dim regolist As String
Dim expdate As String
Dim RetVal As Variant
Dim Make As String
Dim Model As String
Dim Client As String
Dim KAM As String
RetVal = SysCmd(5) 'Clear Status Bar
 
Dim testcheck As Integer
On Error GoTo help:
 
    'set the database objects to the email database
    Set mydb = CurrentDb
    Set myquery = mydb.OpenRecordset("WOF Email", dbOpenDynaset)
 
 
        'check if the database has any records
        If myquery.BOF Or myquery.EOF Then
            MsgBox "There are no records"
            Exit Sub
        End If
 
 
 
        testcheck = MsgBox("Is this a test?", vbYesNo)
 
        'ensure we are at the first record
        myquery.MoveFirst
 
 
        Do Until myquery.EOF
 
                    'connect to the notes session -- notes must be open as I am not checking that it is or for a password
                    Set NotesSession = CreateObject("Notes.Notessession")
                    Set NotesDB = NotesSession.GetDatabase("", "names.nsf")
                    Set NotesDoc = NotesDB.CreateDocument
                    Call NotesDoc.ReplaceItemValue("BlindCopyTo", "wof@leaseplan.co.nz")
                    Call NotesDoc.ReplaceItemValue("Form", "Memo")
                    Call NotesDoc.ReplaceItemValue("ReplyTo", "wof@leaseplan.co.nz")
'        'get the email address of the current record then move to the next record
                    currentemail = myquery.Fields("email")
                    regolist = myquery.Fields("REGISTRATION")
                    expdate = myquery.Fields("WOFDate1")
                    Driver_Name = myquery.Fields("DriverName")
                    Make = myquery.Fields("MKDS")
                    Model = myquery.Fields("MDDS")
                    Client = myquery.Fields("CUSNAM")
                    KAM = myquery.Fields("CMCMNM")
 
                    'set the email address to contact if the email address is blank or JWT for Test
                    If testcheck  7 Then
                        strsupportemail = "john_wallace-thexton@leaseplan.co.nz"
                        Call NotesDoc.ReplaceItemValue("Subject", "Test - The Warrant of Fitness is about to expire on " &
regolist)
                    Else
                            If currentemail  "" Then
                                strsupportemail = currentemail
                                Call NotesDoc.ReplaceItemValue("Subject", "The Warrant of Fitness is about to expire on " &
regolist)
 
                            Else
                                strsupportemail = "wof@leaseplan.co.nz"
                                Call NotesDoc.ReplaceItemValue("Subject", "WOF Notification - No email address")
                            End If
                    End If
                    Call NotesDoc.ReplaceItemValue("Sendto", strsupportemail)
 
                    'create the body of the email
                    Set NotesRTF = NotesDoc.CreateRichTextItem("body")
 
                    'fill the body of the email -- it would be good to use style sheets
                    Call NotesRTF.AppendText("We would like to remind you that the Warrant of Fitness (WOF) or Certificate of
Fitness (COF) on the following vehicle is due to expire shortly:")
                    Call NotesRTF.AddNewLine(2)
                    Call NotesRTF.AppendText("Vehicle: " & vbTab)
                    Call NotesRTF.AppendText(regolist)
                    Call NotesRTF.AddNewLine(1)
                    Call NotesRTF.AppendText("Expiry Date: " & vbTab)
                    Call NotesRTF.AppendText(expdate)
                    Call NotesRTF.AddNewLine(1)
                    Call NotesRTF.AppendText("Current Driver: " & vbTab)
                    Call NotesRTF.AppendText(Driver_Name)
                    Call NotesRTF.AddNewLine(1)
                    Call NotesRTF.AppendText("Make / Model: " & vbTab)
                    Call NotesRTF.AppendText(Make)
                    Call NotesRTF.AppendText(" ")
                    Call NotesRTF.AppendText(Model)
                    Call NotesRTF.AddNewLine(2)
                    Call NotesRTF.AppendText("You have received this notification as your email address is listed against
this vehicle.  If this is incorrect, please ask your Fleet Administrator to advise us of the correct email address and pass a
copy of this email on to the driver to action.")
                    Call NotesRTF.AddNewLine(2)
                    Call NotesRTF.AppendText("As it is the responsibility of the driver to ensure that their vehicle always
has a current WOF/COF and Registration, please ensure the WOF/COF is completed prior to the expiry date above.  Please
arrange a time with your local dealership or take the vehicle to your closest VTNZ Station. An online listing of these can be
found at http://www.vtnz.co.nz/NearestStationSearch.  Let them know that it is a LeasePlan managed vehicle so they know to
contact our Maintenance Team for pre-approval.  It may also be timely to check your windscreen for when the next service is
due for your vehicle, in case the two can be combined at your local dealership.")
                    Call NotesRTF.AddNewLine(2)
                    Call NotesRTF.AppendText("Our process is to check weekly with the New Zealand Transport Agency (NZTA) to
see if the WOF/COF has been completed.  Until we receive confirmation that the vehicle has passed inspection, we will
continue to send reminder emails. ")
                    Call NotesRTF.AddNewLine(2)
                    Call NotesRTF.AppendText("Please note")
                    Call NotesRTF.AddNewLine(2)
                    Call NotesRTF.AppendText("*" & vbTab & "If a vehicle is due for registration, this cannot be purchased
until the WOF/COF is completed.  ")
                    Call NotesRTF.AddNewLine(1)
                    Call NotesRTF.AppendText("*" & vbTab & "Any fines received are the responsibility of the driver to pay.
These are usually around $200 per notice for unwarranted and/or unregistered vehicles.")
                    Call NotesRTF.AddNewLine(1)
                    Call NotesRTF.AppendText("*" & vbTab & "In some instances, unwarranted vehicles will not be covered by
insurance if they are involved in an accident/incident. ")
                    Call NotesRTF.AddNewLine(2)
                    Call NotesRTF.AppendText("This is a system generated email, please do not reply. If you need further
assistance, please call us on 0800   ")
                    Call NotesRTF.AddNewLine(2)
                    Call NotesRTF.AppendText("Kind regards")
                    Call NotesRTF.AddNewLine(2)
                    Call NotesRTF.AppendText("Client Services Team")
                    Call NotesRTF.AddNewLine(1)
                    Call NotesRTF.AddNewLine(1)
                    Call NotesRTF.AppendText("Email sent to " & strsupportemail)
                    Call NotesRTF.AppendText(" on " & Now())
                    Call NotesRTF.AddNewLine(1)
                    Call NotesRTF.AppendText("Client: " & Client)
                    Call NotesRTF.AddNewLine(1)
                    Call NotesRTF.AppendText("KAM/AM: " & KAM)
 
            NotesDoc.Send (True)
 
            'move to the next record
 
            myquery.MoveNext
            RetVal = SysCmd(4, "Progress: " & CInt((myquery.AbsolutePosition / myquery.RecordCount) * 100) & "%") 'Progress
status bar update
        Loop
            RetVal = SysCmd(4, "Complete") 'Progress status bar update
            'tidy up time
            Set NotesSession = Nothing
            Set mydb = Nothing
            Set myquery = Nothing
            ' Create Hard Copy
            Call CreateHardCopyDue
 '           Form_Email.Exit.SetFocus
 '           Form_Email.Command10.Enabled = False
Exit Sub
help:
    MsgBox Err.Description
    MsgBox Err.Source
    MsgBox myquery.Fields("email")
 
    Debug.Print
End Sub



Have a system with 3 tables (tblCaseMain, tblItems, tbleItemsTemp). The tblItems is related to the tblCaseMain by CaseID so each case file can contain many Items. The temp table is used as a holder for items until saved. As items are received they can be either Accepted or Rejected which is a field in the tblItems. The tblCaseMain contains numeric fields for the qty of items Accepted or Rejected. What i am trying to do after the Items have been entered and the tblItemsTemp and appended to the tblItems I want a count of how many Accepted and how many Rejected. Those values then need to be placed in the [ItemsSubmitted] and [ItemsRejected] fields in the tbleCaseMain for the CaseID which is obtained from the open Main form txtCaseID. The VBA works for the APPEND of the records and the Count seems to work but it is not working when I put it all together I get a ERROR 3061. This code is on the Save button on the TempItems form.

Option Compare Database
Option Explicit

Private Sub cmdCloseSave_Click()
On Error GoTo Err_cmdCloseSave_Click
Dim iMsg As String
Dim stDocName1 As String
Dim stDocName2 As String

stDocName1 = "qryDelTempItems"
stDocName2 = "qryAppendItems"

iMsg = "Do you wish to UPDATE Exhibit Items to this Case?"

Select Case MsgBox(iMsg, vbYesNo + vbQuestion, "Exhibit Items")

Case vbNo
'delete records from Temp table for current CaseNoID
DoCmd.SetWarnings False 'Turn messages OFF
DoCmd.OpenQuery stDocName1 'Delete items from temp table
DoCmd.SetWarnings True 'Turn messages ON
DoCmd.Close acForm, Me.Name
Case vbYes
'append records from Temp table to Item table for current CaseNoID
DoCmd.SetWarnings False 'Turn mesages OFF
DoCmd.OpenQuery stDocName2 'Append from tblItemsTemp to tblItems
DoCmd.OpenQuery stDocName1 'Delete items from TblItemsTemp
DoCmd.SetWarnings True 'Turn messages ON
Call exhibCalc
DoCmd.Close acForm, Me.Name
Forms!frmCaseMainTab.Refresh
End Select
Exit_cmdCloseSave_Click:
Exit Sub
Err_cmdCloseSave_Click:
' Handle errors
MsgBox "An unexpected error has occurred." & _
vbCrLf & "Please note of the following details then click OK:" & _
vbCrLf & "Error Number: " & Err.Number & _
vbCrLf & "Description: " & Err.Description _
, vbCritical, "Error"
Resume Exit_cmdCloseSave_Click

End Sub

Private Sub exhibCalc()
Dim strSql As String
Dim strSqlAcc As String
Dim strSqlRej As String
Dim stAccept As Long
Dim stReject As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim qdf As QueryDef
Set db = CurrentDb
strSqlAcc = "SELECT Count(1) AS [""Number Accepted""] " & vbCrLf & _
"FROM tblItems " & vbCrLf & _
"WHERE (((tblItems.ItemAcceptReject)=""Accepted"") AND ((tblItems.CaseNoID)=[Forms]![frmCaseMainTab]![txtCaseID]));"
strSqlRej = "SELECT COUNT(1) AS [""Number Rejected""] " & vbCrLf & _
"FROM tblItems " & vbCrLf & _
"WHERE (((tblItems.ItemAcceptReject)=""Rejected"") AND ((tblItems.CaseNoID)=[Forms]![frmCaseMainTab]![txtCaseID]));"
Set rs = db.OpenRecordset(strSqlAcc, dbOpenDynaset)
If rs.RecordCount > 0 Then
rs.MoveFirst
stAccept = rs.Fields(0) 'this means the first field returned, you can also use rs.fields("name") to get the same result.
Else
stAccept = 0
End If
Set rs2 = db.OpenRecordset(strSqlRej, dbOpenDynaset)
If rs2.RecordCount > 0 Then
rs2.MoveFirst
stReject = rs2.Fields(0) 'this means the first field returned, you can also use rs.fields("name") to get the same result.
Else
stReject = 0
End If
strSql = "UPDATE tblCaseMain SET tblCaseMain.ItemsRejected = & stReject &, tblCaseMain.ItemsSubmitted = & stAccept & " & vbCrLf & _
"WHERE (((tblCaseMain.CaseID)=[Forms]![frmCaseMainTab]![txtCaseID]));"
db.Execute strSql
Set rs = Nothing
Set rs2 = Nothing
Set db = Nothing
End Sub

I'm trying to send values from an excel workbook to an Access database using VBA. Problem is that sometimes strLongString gets really long, and apparently the Execute command can't use any SQL string longer than ~306 characters, so the SQL statement gets truncated and doesn't make any sense.

NB that field1 is a memo field, so that shouldn't be the problem. The problem is the SQL statement getting cut short.

Any workaround? Can I make Execute method accept longer strings? Or any way to send these values to Access without using an SQL string?

Thanks in advance.


	Code:
	Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=" & strPathToMDBFile)
conn.Open

'...
'...
'...

'now the SQL statement where I send data to the db
conn.Execute "UPDATE [tbl_EIQ_Point_Information] " & _
             "SET [field1] = '" & strLongString & "' " & _
             "WHERE [field2] = '" & strField2Value & "' AND [field3] = '" & strField3Value & "'"




Not finding an answer? Try a Google search.