Modules & VBA :: Constructing SQL To Insert New Records (Access 2007)
Jul 11, 2013
I have a command button on a form which loops through pages on a tab control and constructs an SQL string to insert a new record into a corresponding table (each tab has it's own separate table)
The tables are linked via a common account ID (an autonumber on a separate 'Core' table, the latest record for which is generated earlier in the code; this section creates a linked record in each of the other tables)
I've used a naming convention in each page such that each control name and it's corresponding field name in the destination table are the same (apart from the first 3 characters which I use to identify the control type, i.e. "chk", "txt" etc.)
For one of my pages, the insertion of the new record keeps failing. I've gotten it to work once or twice but only by randomly changing some of the values on the form (checking / unchecking boxes or keying data into random textboxes etc.) But I can't identify why it works sometimes and not others?
The other 3 tabs/pages insert records with no issue, every time.
I've tried debugging but I don't get any error message when I execute the constructed SQL; there are no apparent control violations and all of the relevant fields are correctly named & referenced. The only 'required' field in each table is the common Account ID, which is present, correct & not duplicated, so omitting null / empty fields or passing empty strings / values for the remaining fields shouldn't be an issue (?)
I can only assume it's a problem with a value being passed somewhere but what's stumping me is that on the other pages, it inserts the records exactly as expected (whether data has been provided or not)
Stepping through the VBA, it looks like a record should be inserted - but when I check the table, there's nothing there?
Code:
Dim pge As Page
Dim ctl As Control
Dim strSQL As String
Dim strSQLFields As String
Dim strSQLValues As String
I have 12,000 cvs that i need to get into An access database so i can start to extract email info etc. They are stored in one folder All Cvs under each of their names edc.txt (i have converted them to .txt) i want to create one table with two fields name (taken from the cv filename and contents (taken fromthe contents of the .txt file). I am using Access 2007.
If you want to use a "DoCmd.RunSQL "INSERT INTO" command to insert data in a table and the data to insert comes from a table and a form, could this be done in one pass?
So...writing a record wit 4 values from table1 together with a additional value from a textbox in table2 as 5 values.
I am reading through a table looking for duplicate values in the FullName text field. I want to store in a new table the duplicate records I find, storing just the MemberNumber and the FullName. When the VBA code runs and finds duplicates, the SQL statement to insert a new record into the Duplicates table asks for the value of LastMemberNumber and LastFullName when it already has the values and has displayed them in the message boxes! What am I doing wrong?
The code is:
Private Sub Command0_Click() Dim rs As DAO.Recordset Dim dbs As Database Dim LastMemberNumber As Integer Dim LastFullName As String
I am trying to get a form to insert info into a access database. I am using dreamweaver 2004, i created the form with 4 text fields and a submit button. Ive added a ODBC connection and dreamweaver can qurery my table.
I added an insert record server behaviour, and i figured dreamweaver does all the work for you but when i saved and uploaded the page to my test server it would not submit and redirect to my chosen page, it only refresh the form.
Has anyone out there done this before and can notice a step that im missing.
Do i need to setup a recordset? because ive tried that as well and no success
How would I set up an Outlook email using a list of recipients from an Access table? I'd like to be able to run this from Access 2007 and not Outlook. However, once the email is set up, I'd like to be able to go into the email and add subject, body, etc. before sending the email.
My problem is I need to know if the execution of the SQL statement was successful or not. I use the RecordsAffected method, but it always returns 0, no matter what.
"how to COMPACT the DB by introducing delay of 10 seconds and then close the DB".In the Database, I'm able to accomplish the "Compact" the database using the function below.
Function Compact() SendKeys "%(FMC)", False End Function
As my DB is quite huge, the Compact action takes around 10 seconds to complete.Now, i would like to Close the Database after Compacting the DB. I tried including "DoCmd.Quit" in the function. The commands in the function, closes the DB but the Compact function doesn't seem to have executed as it needs 10 seconds to complete.
Function Compact() SendKeys "%(FMC)", False DoCmd.Quit End Function
how to introduce this delay of 10 seconds and then close the DB.
How to generate an export specification file by using Access 2007. Many instructions says click on "Advance" while exporting a text file manually but unfortunately i can not find that "Advanced" option in my access software.
I have been asked to create a database very quickly for a small group that needs to scan multiple pages into an Access 2007 form. They they would like all scanned documents to be converted to PDF and stored on a shared network folder which can be accessed by their group. I have tried several ideas posted but, I have been unable to get any to work correctly. Any sample of working database that the scanning portion is working? I am so involved with other projects right now that I just don't have the time to create a scanning process on the database.
I've been using Access 2007 to run queries on a database where we eventually export results as separate Excel spreadsheets for individual clients. The process is quite involved, using queries to change fields from code letters to words and splitting the database up into different client tables, saving the tables under date order and with different client codes.
I now need to pass this role on to colleagues, so need to make everything as straightforward as possible.
I had thought to use a Form as the user interface, with a minimum number of buttons, however I need either the system or the user to amend the date for the initial table, then to use this new table and run a series of standard queries on it, then produce the separate tables.
I don't think I can just use macros behind the buttons, because the database name is changing each time.
I assume some parts will be too tricky to automate - it will be necessary for colleagues to follow instructions instead.....
Is it possible (and how...) to declare a module-specific form variable (or any variable for that matter) at the top of said module, so it doesn't need to be set at the start of each subsequent procedure?
I have a module of code specific to one form with a number of procedures, each one of which requires me to Dim / Set the form variable. It would be much neater if I could do it once at the start.
Code: Option Compare Database Option Explicit Public Sub Populate(lngParameter As Long)Dim frm As Form
I have the below SQL statement... In table2 there is another field called timestamp1... Is it possible to have the timestamp1 included in the below statement so that I will have a record of time the moment the records were inserted in table2? There is no timestamp1 field in table1.
Code: strSQL = "Insert Into Table2(Business_Unit, Account) Select Business_Unit, Account From Table1"
In an Access 2007 module, is there a way of sending an email to a list of people stored as a list in a table in the database, rather than having to put all the names into the function?
I have a function I'm using to generate and send out an email to certain people, but the list is constantly growing so I'm looking for a better way to manage it! The main issue is having to kick users out of the database every time I need to update the recipients list... because it's stored in the code.
If I create a simple table containing all the names, how could I then ask it to use that instead?
This is the function I'm using currently - found on here and adapted to my own purposes
Syntax to use for the function: SendNotesMail "recipients", "Body Message", "Additional Text", "Subject"
Code: Public Function SendNotesMail(strSendTo As String, strBody As String, strExtraText As String, strSubject As String) 'This public sub will send a mail and attachment if neccessary to the recipient including the body text and additional comments from the Active record DoCmd.OutputTo acOutputReport, "REP09emailnotification", acFormatRTF, "x: endersgroup tendering databaseTenderUpdate.rtf", False Dim Subject As String Dim Attachment As String
[Code] ....
So I guess my question is: how do I get my recipients from a table into the strsendto?
I have a listbox which is populated by a query from a table.
I also have a sub which allows users to quickly select multiple items from the listbox (based on a pre-defined criteria) rather than scrolling through and selecting them manually / individually
The sub works fine but when it is finished, the listbox is automatically scrolled to the bottom (or, rather, to the last selected item)
I would like to have the listbox scroll back to the very top once all of the appropriate items have been selected.
(I could loop through the listbox items in reverse - or 'bottom-up' - in the code but that would only have the desired effect if the topmost item was selected. I want it scrolled to the top regardless of whether the topmost item is selected or not).
I've got a form which emails out a report when a new record is added to my database but I'm stuck when it comes to making it send it to more than one recipient.
I came across this on the web... which suggests I should be using an array but I don't know anything about arrays.
For multiple email addresses you just set MailDoc.sendto to an array of variants each of which will receive the message. So
Code:
Dim recip(25) as variant recip(0) = "emailaddress1" recip(1) = "emailaddress2" e.t.c
maildoc.sendto = recip
My problem is I'm not sure how to implement it? My 'recipients' are being pulled from a query... how do I get that into the array of variants?
Using Access 2007, can I return a value for CurrentProject.FileFormat WITHOUT opening the database?
I'm looking for a way to determine the file version of a given Access database without actually "opening" the database in Access (I don't want it to ever be visible at all or in any way).
So to open a database in Access 2007 "without opening it," I'm using:
Code: Dim db As DAO.Database Set db = DBEngine.Workspaces(0).OpenDatabase("C:Path-ToFile.accdb")
From here, there are at least two different "version" indicators that can be used, as in this example:
Now, if you run this code on an Access 2002/2003 .MDB file, you will get:
Code: 4.0 09.50
If you run this code on an Access 2007 .ACCDB file, you get:
Code: 12.0 09.50
Note two important factors:The db.Properties("Version") seems to be returning the JET version from what I can see in research. This is NOT what I'm after.
The db.Properties("AccessVersion") returns THE SAME VALUE for an '07 ACCDB as it does for an '02-'03 MDB, since Microsoft never updated it, apparently.
Now.... the one way that I have found that ACCURATELY describes the version of a given access database file is to use:
Code: Debug.Print CurrentProject.FileFormat
...as this will always return, for example, 2.0 for an Access 2.0 .MDB file; 10 for an Access 2002/03 .MDB file; and 12 for and Access 2007 .ACCDB file (and there are others; these are AcFileFormat bitmask constants as described in Access support).
BUT, this CurrentProject object doesn't seem to be available (so far as I can see) when using the "open without opening" method described above.
SO, is there any way to return the FileFormat value without opening the database visibly? (Merely basing it in the extension isn't right either.... that would be easy but not accurate as several formats use MDB.
I'm doing a project for my work. I created a few reports in Access. Some of these reports are simple graphic bars. How can I insert these reports into a word document template?
I have an access database with a query which inserts system date/time from Now() function to a column. My system date is 12-05-14 (12 May 14) and when it is inserting in the table is taken as 05-12-14 (5 Dec 14).
My company recently upgraded our MS Office from 2007 to 2010 (except for Access).
Previously, when I had Access 2007 and Outlook 2007, I had a process that generated 50+ dynamic emails from an Outlook template file (.oft).
The code would loop through a listbox and replace the template's default text to a string of text specific to the selection in the listbox by utilizing the Replace() function on the MailItem .HTMLBody.
Since the upgrade to Outlook 2010, the code is able to run, however, the Replace() function is no longer working; Instead, each email that is generated maintains the template's default text.
The only thing that is not working is the Replace() function, all other aspects of the code work fine.
I've provided a simplified version of the code below:
Dim myOlApp As Outlook.Application Dim objMailMessage As MailItem Dim stBody As String Set myOlApp = Outlook.Application Set objMailMessage = myOlApp.CreateItemFromTemplate("C:UsersDesktop emplate.oft")
[Code] .....
I've recreated the template file in Outlook 2010, thinking that the template created with Outlook 2007 would be the culprit, but to no avail.
What could have changed from Outlook 2007 to Outlook 2010 that would render my previously valid code ineffective?
Are there certain references I need to enable in both Access and Outlook to allow VBA in Access modify the content in an Outlook email?
