Modules & VBA :: Creating A Record And Then Updating With Additional Info In Various Fields
Apr 24, 2014
In the code below I am creating a record with the INSET INTO statement and then Updating with additional info in various fields. it is not working the way I thought it would, so I am trying to create the record in it's entirety.
Code:
SQL_Grade_GUSD_ID = "INSERT INTO Grades (GUSD_Student_ID) VALUES (" & Me.GUSD_Student_ID & ")"
SQLM1_1_ELA = "UPDATE Grades SET Grades.Subject = ""BM1(ELA)"""
SQLM1_2_ELA = "UPDATE Grades SET Grades.Type = ""Exam"""
SQLM1_3_ELA = "UPDATE Grades SET Grades.Score = ""0"""
SQLM1_4_ELA = "UPDATE Grades SET Grades.Nam = ""GUSD BM-1"""
DoCmd.RunSQL SQL
[Code] ...
I am running to syntax problems when I try to USE the INSERT INTO to create the record with all the info in one statement.
Code:
SQLM1_1_ELA = "INSERT INTO Grades ( GUSD_Student_ID, Subject, Type, Score, Nam ) " & _
"SELECT (" & Me.GUSD_Student_ID & ")"" AS GUSD_Student_ID, ""BM2(ELA)"" AS Subject, " & _
"""Exam"" AS Type, ""0"" AS Score, ""GUSD BM-1"" AS Nam " & _
"FROM Grades"
I am Getting this error:
Syntax error (missing operator) in query expression '(12345)" AS GUSD_STUDENT_ID,
"BM2(ELA)" As Subject, "Exam" AS Type, "0" As Score, "GUSD BM-1" AS Nam From Grades'
I am trying to use transferspreadsheet to import access worksheet and then I want to append additional fields. I am able to import the excel sheet into access, but need appending the other fields.
Code:
Dim fd2 As FileDialog Dim xlapp As New Excel.Application Dim xlsht As Excel.Worksheet Dim xlWrkBk As Excel.Workbook Dim db As DAO.Database Dim tb2 As TableDef Dim fdx, fld As DAO.Field
I a trying to list all fields from ALL tables in a certain DB.I am using the tableinfo function. However, because the output of the immediate window is limited to 200 lines, I can only see the last 200 fields. Is there a way to export this in another way than the debug.print procedure, so I can bypass this limitation?
Code:
Public Sub showtablefields() Dim db As Database Dim tdl As TableDef
I'm creating a database for a travel agency. There are 2 tables, one for customers and one for their bookings. I have established a relationship between the tables so the AutoNumber for customer ID links to a field on the add booking table.
In practice though, it would be easier to enter the customer NAME into the booking table and have it retrieve their ID that way to link them. This seems like I am missing something simple but can't fathom a way to do it.
The other factor is obviously more than one customer will have the same name so I may need to link first name as well to differentiate?
I am trying to return the MIN in a numeric field (single result) from my database and I have no issues doing this BUT the problem is that I also need to display additional fields in order to make the result useful. The additional field can't be grouped by b/c that changes the result of the MIN function.
I have a sub form that adds and displays appointments related to an order. The form has two combo boxes, cboVenderTypeID and cboVenderID and a date picker. There is a 3rd combo box on the parent form called OrderNode. The contents the cboVenderID combo box are filtered by the selections made in cboVenderTypeID and VenderID comb boxes. This works fine and adds the appropriate appointment to tblAppoints. The problem is when I try to add an appointment with of a different type (different value in cboVenderTypeID) all the values in the existing appointments change to the latest value selected in cboVenderTypeID.
Private Sub cboVenderTypeID_AfterUpdate() Dim strSQL As String Dim db As DAO.Database Dim rs As DAO.Recordset If Me.cboVenderTypeID = 4 Then
I'm working on a form with almost a hundred various fields (it's what my employer needs). He wants to be able to create new records with much of the same information as the old ones by clicking on a button that will copy the information to a new record, then clear certain specific fields (or some process that will safely create the same result). I don't need specifics on how to write the same line of code for each and every field I wish to clear.
Say I have a form with 5 fields: part_number, part_owner, procedure, file_name, and date.
How could I copy the record to keep the fields part_owner and procedure the same, but clear part_number, file_name, and date?
All of the fields are from the same table (no, it's not normalized...).
I have a database that will take lots of data entered by an employee and calculate some additional data and generate quotations. Within each quote there is a possibility for 15 different metals (5 Precious Metals, and 10 Base Metals). There is also 5 fields that need to be filled out about each Metal (What the metal is, the market being used, weight, whether it is included in a different price, and the price). I currently have 75 fields to address each Metal and their 5 fields respectively. Is there a better way to Normalize this data, and accomplish what I need accomplished? I want at the form level the employee to tell the database whether they want to add a Precious Metal, or Base Metal, or Move on to other data entry.
Here is a Screenshot of the design view of one of my tables with too many fields : table screenshot1.PNG
I have the following code working great with creating new contacts in Outlook from Access. My problem is I can't figure out a way to update already existing contacts in Outlook.
Function AddContacts()
Dim OutlookObj As Outlook.Application Dim Nms As Outlook.NameSpace Dim MyContacts As Object Dim MyItems As Object Dim MyItem As Object Dim Db As Database Dim Rst As Recordset
Set Db = CurrentDb Set Rst = Db.OpenRecordset("CustomerOutlook", dbOpenDynaset) Set OutlookObj = CreateObject("Outlook.application") Set Nms = OutlookObj.GetNamespace("MAPI")
'to point to a nested folder in Outlook you have to create folder items in folder items: Set MyContacts = Nms.Folders.Item("Public Folders").Folders.Item("All Public Folders").Folders.Item("Our Contacts") Set MyItems = MyContacts.Items
While Not Rst.EOF 'Declare which form to be used to add your contacts Set MyItem = MyItems.Add("IPM.Contact")
'add fields, to find out which fields are available take a look at all the available members of the 'ContactItem class in the Outlook object library. 'Be careful, Microsoft is using various names for outlook fields! MyItem.CompanyName = Rst!BusinessName MyItem.FirstName = Rst!CFirst MyItem.LastName = Rst!CLast If Not IsNothing(Me.WebSite) Then MyItem.WebPage = Rst!WebSite End If If Not IsNothing(Me.CMiddle) Then MyItem.MiddleName = Rst!CMiddle End If If Not IsNothing(Me.Suffix) Then MyItem.Suffix = Rst!Suffix End If MyItem.JobTitle = Rst!JobTitle MyItem.BusinessTelephoneNumber = Rst!Phone MyItem.Business2TelephoneNumber = Rst!BackPhone MyItem.MobileTelephoneNumber = Rst!Mobile MyItem.BusinessFaxNumber = Rst!Fax MyItem.Email1Address = Rst!Email If Not IsNothing(Me.Address1) Then If Not IsNothing(Me.Address2) Then MyItem.BusinessAddressStreet = Rst!Address1 & ", " & Rst!Address2 Else MyItem.BusinessAddressStreet = Rst!Address1 End If End If MyItem.BusinessAddressCity = Rst!CCity MyItem.BusinessAddressState = Rst!CState MyItem.BusinessAddressPostalCode = Rst!PostalCode MyItem.Categories = Rst!CustomerType MyItem.FileAs = Rst!BusinessName
'MyItem.Etc = Rst!Etc MyItem.Close (olSave) Rst.MoveNext Wend End Function
My father tables contains all the relevant fields with info of my products (which are listed in records/rows). My sister table contains less fields but are all present in the father table. The info is imported from excel daily into the sister table - all the fields here change regularly except for the unique ID which is present in both tables. Because the sister tables' information for the records/rows changes, it is difficult to update the selected fields of the father table.
I want to update selected fields within my father table based on the information I upload from the excel sheet (which becomes the sister table when uploaded)
I am trying to edit an existing record in my database.It goes to the correct record to update but will not update if "RecordUpdate" is specified as auto numbered.If the "RecordUpdate" is just specified as a number in the query it is fine.
I am very new to access and I am trying to create two tables called Customers and Orders.
The fields in Customers are: CustomerID (Primary Key), First Name, Last Name, Address.
The fields in Orders are: Order# (Primary Key), CustomerID, Product Purchased, Address.
There is a One to many relationship between CustomerID in the table Customers, to the CustomerID in Orders.
The problem I am having now is with the Address field.
GOAL: What I am trying to do is have the Orders table automatically get the Address from the Customers Table.
If that doesnt make sense, whenever I create a new Order, I want to enter the customerID, and have it get the Address data from the Customers table automatically put into the proper field in the Orders table.
I'm quite new to using Access 2007 and I'm trying to create a form from different tables which have relationships. I've used the form wizard and added all the fields I need from the different tables, however, when I click form view it only shows the title and nothing else. When I'm in design view everything is showing..
Now I have created the relationships between the tables for the various ID's. My issue is the following:
I will be automating this process, so when the data is imported to the various tables, I need the relevant keys from tbl_1 tbl_2 and tbl_3 to be added to tbl_4 when the new data is brought into the tables.
As to the process of importing the data, I was looking at using PHP to facilitate this. Any ideas, help or direction would be greatly appreciated. I have looked around the net for an idea of how to solve this, but, being so new, I am not exactly sure how to word the question.
how to use the Like statement when creating a record-set of data through VBA. Before I was always able to find work-arounds but now is the time to slay this issue once-and-for-all.A person can build several sales quotes for a specific company and I am trying to find the last sales quote that was built. The function is passed a variable length string and I am trying to build a recordset of all quotes based on the variable. Here is the offending line of code:
strSQL = "SELECT * FROM Quotes WHERE Quotes.ProposalNo Like " & "*" & strProposalBase & "*" & " ORDER By Quotes.QuoteID DESC" (This yields an empty recordset)
strSQL = "SELECT * FROM Quotes WHERE Quotes.ProposalNo Like ""*" & strProposalBase & "*"" ORDER By Quotes.QuoteID DESC" (This yields an Error message stating there is an invalid column name)
strSQL = "SELECT * FROM Quotes WHERE Quotes.ProposalNo Like " & strProposalBase & "*" & " ORDER By Quotes.QuoteID DESC" (This yields an Error message stating there is incorrect syntax near the word ORDER)
I have tried different variations above and beyond these strings and get one of the three listed errors.
I have a form where we fill in information for supply of PPE to employees.
Each item must be signed for on a printed report.
I am encountering problems trying to create enough rows in my report detail for each signature of the number of items supplied.
For example, on the form I will select the "equipment" - 4 hats supplied and 3 boots. On the report I want the equipment set as the group and the detail to be a number or rows which equals the number of selected items. therefore under the Hats group heading I want 4 blank rows which are made up of 3 text boxes - Print Name, Signature & Date and another group heading for boots but with 3 lines.
The Query gives the results below: (Item name is linked to a PPE table containing Item ID, Item Name, Cost etc..)
IssueIDDateProvidedItem_Name AmountIssued 0001 01/11/2013 Gloves (Orange) 10 0001 01/11/2013 Hard Hats (Black) 2 0002 02/11/2013 Hi Viz (Large) 5
The report I aim to generate from this should look as attached ...
The query/report is set up as a parameter report so I will enter the issueID such as 0001 and only those items will appear on the report to print and sign.
I am starting to get further into access development, originally i started from a sample database and have been teaching myself as i go along. This database has come pretty far and the farther it comes along the more my company demands of me from it!
either way, it is basically a database that holds all of our asset information, equipment parts, workorders and preventative maintenance. We've been plugging along just fine but I want to help their productivity and have access automatically create "Equipment ID"'s from information that is entered by the person adding an asset. I would like it to create an ID from Entry of the Department, Location, and then a 4 digit Autonumber after that.
Basically we have a few basic departments, IT, Facilities, etc. and a few locations... So we have been setting our equipment ID's like this:
AA - Two characters for the Location BB - Two characters for the department 0000 - numbered field
So I really would like to be able to create these automatically instead of manually typing them in to a text field with an input mask.
AABB-0000 to automatically create entries from the departmental info, location info and then automatically create a 4 digit number in order to follow it.
What is the best way to do this? Keep in mind I am just barely able to do any code, I only have been going from what i see in the database and building upon it and learning a few bits and pieces of code from there. So if you start going into a code explaination go slow~!
I need to be able to produce an error on a form where the user attempts to create a new user that already exists. The error needs to be produced when the save button is clicked.
See below for the existing code on my save button which exists in form, 'frmAddEditUser':
Private Sub cmdSave_Click() If IsNull(cboManagerID) = True Then MsgBox "Please select a manager", vbExclamation, "Details Missing!" Me.cboManagerID.Requery
[Code] ....
Currently, if I attempt to create a user with the same Payroll ID as an existing user, my 'User details saved' message box is displayed and to the user it looks like a new record has saved successfully which isn't the case.
I need a message box to appear displaying something like "Payroll ID n already exists" where n is the 'PayrollID' of the duplicate record. (Is it possible to combine a value from a certain control and ones own custom text?)
Note: PayrollID is a combination of letters and numbers, i.e. two letters, followed by 5 numbers, e.g. JD12345.
For reference, the table/field and and control are called, '[tblUser].[PayrollID]' and 'txtPayrollID' (on 'frmAddEditUser') respectively; it is from this field and control that I want the error to be produced if the user is entering the same values which are existing in 'tblUser'.
I have a linked table to a DB2 database. this table contains key-pair values and has about 140k records.
I use a Sub to update the value of a specific record.
The sub starts by opening the needed DAO recordset Then it uses the rs.Findfirst method It checks if rs.Nomatch is not true (so the records exists!) Then it starts updating the record with rs.edit rs!value1 = myvalue1, rs!value2 = myvalue 2 rs.Update There is where I get the '3021 No current record' error
I use the same sub on the same table to update to different parts. One part works the other gives me the error.
I need to add two additional columns for each imported spreadsheet, which are not in the original files.
First column, named 'Customer Name', needs to retrieve information from cell A6 in Excel file, which is not part of range "name" that is imported to Access. If necessary, I could somehow include it in this range but then I still would have a problem of how to repeat a value of A6 in each row of table in Access.
Second column, named "File Name", I would like to include an Excel file name from which data was imported. Again, I would like to repeat value for each row in the table, i.e. if I have 6 rows with data from file X, I would like to repeat X.xlsx in each row in column "File Name".
Code: Dim strFile As String DoCmd.SetWarnings False DoCmd.RunSQL "DELETE * FROM Data;" ' Set file directory for files to be imported strPath = "C: est"
I have a main form that controls 2 subforms. sbfActiveProjects displays a list of records that have not been flagged as complete. Upon clicking on a record in sbfActiveProjects, the details of that record show up in sbfProject. This functionality works great, except when I try to create a new record. The code works fine for getting a new record, however once I run the command I lose my ability to navigate to other active records.
Code: Private Sub cmdNewProject_Click() 'set focus to sbfProject sbfProject.SetFocus 'navigate to new record DoCmd.GoToRecord , "", acNewRec End Sub
I have access table that stores details as attached. Just to give an example how data is stored , I copied data from Access table to excelsheet. Now I would like to create a report that will run when a button is clicked on the form and the vba code should display the last 7 dates from todays dates in the report along with all the data and colour the record Green,Amber and Red depending upon the Update Date and Target Date. If the Target date exceeds then that records should be coloured "Red" and if todays date equals to Date then the record should be amber and if greater than Date but less than Target Date then it should be Green.
So the report should display the last 7 dates records from todays date and the appropriate color of each record to let everyone know if its under target, meets target etc.
I am not sure if it is possible to implement this but I would like your input. We get request from client via a web based form which contains a unique ID number called RDEFNumber. When we process the request internally, we use this number to identify the request throughout the process. The request from client contains processing of multiple files which are transmitted to us in different times of the day or days.
Every time we receive a file, an email is sent to the IT team with all the information about the file. We use an internal database to send the email to the IT team and use the RDEFNumber as the identifier in the subject line. What I am trying to do is to add an additional number at the end of the RDEFNumber automatically to differentiate the subject line.
For example, we take "1791" as a RDEFNumber. For the first time we enter 1791 to the database RDEFNumber field, it should check the table "tblRDEF" (record source) for duplicate value. If no Duplicate exists, it should keep the number as same. When we get the next file for processing with the same 1791 number and enter this number in the RDEFNumber field, it should automatically change it to "1791-1" since "1791" already exists in the database. When we get the next file, the number should change to "1791-2" and then "1791-3" and on.
