Modules & VBA :: Update Record With Info From Another Table?
May 12, 2014
I have a database which tracks product batches test results and then any reported issues.
I have one table tblOne with [Batch Number] and [Release Time/Date].
The 2nd Table tblTwo [Problem/Issue] at [Time/Date].
What i need to do is get the last batch number from table one which would have been released.
Example:
Batch: 275 Released: 21/09/2012 06:15
Batch: 453 Released: 30/09/2012 07:20
Problem: Wrong Batch Time/Date: 21/09/2012 08:20 Batch:????
How would i update table with correct batch in tblTwo
View Replies
ADVERTISEMENT
Aug 5, 2014
I have a table products with a field "id_product" and "total" (Total items in stock)
I have a query with the fields "id_product" and also the field "total in stock"
I want an update query to update the field 'total' in table 'products' with infos from that query
For each id-product in table products, replace the field total with the field 'total in stock' from the query
So I want to update a filed in a specific table with infos form another table.
View 2 Replies
View Related
Nov 7, 2006
I need help on this, from what the best concept is, to what I need to look into using:
I store Quote data from phone calls into tblQuotes. There is a seperate table that holds much of the same information except that it is for actual orders called tblOrders.
As far as function goes, I have each working much the way I need it to except for one thing... If a sales person is on the phone with a customer with a quote already in the system, right now they re-enter the data into the Order table. Most times, the order is what was quoted, but maybe with a few small changes (so I will wnt to keep a historical record of the quote). How could I copy the contents of the quote recordset into the recordset for an order, where all the sales rep does from there is edit the quote to the actual order?
Would I use VBA or an update query that is executed via VAB? Honestly, I am unsure how to do this at all, I hope someone has seen this before and has a good suggestion...
Thank you.
View 2 Replies
View Related
Apr 24, 2013
I have a form that I need to turn into a table. The table will be updated each day with new information but I dont know what to do to keep the previous days info. I have attached the form ....
View 3 Replies
View Related
Dec 4, 2013
How to do an UPDATE using VBA on a form to update a specific record on the table using an unbound field on the form to filter the update.
Every time the code runs, it tells me: Run-time error '3144': Syntax error in UPDATE statement and takes me to the "CurrentDb.Execute strSQL, dbFailOnError" line at the end of my sample below.
Here's my code:
Dim strSQL As String
Dim strCriteria As String
strSQL = ""
strSQL = strSQL & " UPDATE [tblTicket] SET"
strSQL = strSQL & " ([UpdatedBy], [AssignedTo], [Requestor], [Dept])"
strSQL = strSQL & " Values"
strSQL = strSQL & " ('" & unbEnteredBy & "','" & cmbAssignedTo & "','" & cmbRequestor & "','" & cmbDepartment & "')"
strSQL = strSQL & "Where [tblTicket]![DateTimeOpened] = #" & FORMS!frmTicketTracker.unbDateTimeOpened & "#;"
CurrentDb.Execute strSQL, dbFailOnError
View 9 Replies
View Related
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'
View 3 Replies
View Related
Aug 22, 2013
I have a table called tblCompanies. When a company acquires another company, I need a method by which the acquired company's CompanyID (PK) can be updated to the new company's CompanyID (PK). I also need to be able to update all related CompanyIDs (FKs) to the new value in related tables.
In cases in which the new company does not have an existing record, there is no problem: the company name simply gets changed to the new company and the existing CompanyID is maintained. I then use an audit table and Track Changes function to keep track of the company name data and a union query to keep the old names in the selection lists.
The problem is when both companies already have existing records in the table.
So, let's say I have records for Company A and Company B. Company A merges with Company B and Company B is now the main record. What is the best, simplest and easiest way to update the CompanyID (PK) from A to B and change the CompanyID (FK) to the new value in all related tables?
I am envisioning a pop-up form that directs the user to select the new company and then an update query happens behind the scenes... but exactly how does the criteria for the update query get selected and how do all the related tables get updated? My vba skills are pretty basic, will I need extensive coding to do something like this?
View 6 Replies
View Related
Aug 20, 2014
I am trying to automate the function that imports Infopath (.xml) data into access tables. I can have a button run the MenuCommand, but then the users have to go through all of the prompts and I just don't trust them enough to do it properly.
All I want is the user to click a button, then it lets them browse for the desired file and then imports it.
View 1 Replies
View Related
Nov 5, 2013
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
[code]...
View 5 Replies
View Related
Jan 26, 2015
i want to be able to create an On Click Event when pushing a command button that will run an Update query to update a record and after it has been updated that specific record will pop up on a Form and be displayed. i know a different way is to run the Update query and then have it displayed in a Select query but i want it to be displayed on a Form instead. is it possible?
View 4 Replies
View Related
Nov 23, 2006
Hi, I'm new to the forum and I am currently designing a database that my colleagues can use to book people onto our courses.
I have a form set up with two subforms. The main form is one that has the course details, I then have a subform with the individual dates throughout the year. I also have a further subform that shows who has booked onto each date when I am on the course and date applicable.
Unfortunately I do not seem to be able to add new records to either of the subforms I can only change details of current records. I can however add new records to the main form.
Where am I going wrong??
I would appreciate any help you can give me.
Nikki
View 6 Replies
View Related
Mar 2, 2015
I have a form in datasheet view and on that form I have a yes/no tick box is it possible that when the tick box is ticked it locks the line from further entry.
View 3 Replies
View Related
Jul 11, 2013
I need to update a field with the contents of 2 other fields when exiting a record. The fields (ItemNo) and (LotID) need to create or update the field (LotCode).
i.e.: ItemID (GM-235), LotID (26) will result in LotCode (GM-235-26).
I currectly use a Update Query that uses the expression:
[products].[MasterPN] & "-" & [lotid] in the lotCode Field - "Update to".
Unfortunatly, I am using a button on my form that executes the query, but it updates all the records and although it runs very fast, I would rather have it perform this task automatically when I leave the record.
View 1 Replies
View Related
Mar 27, 2015
I wanted to get the autonumber ID before a record was saved to the table. My fields are on a form that is linked to the table. Maybe my solution is not the most elegant but it seems to work.
I messed around and came up with this solution: it creates the next record and captures the autoID then increments it and creates the record we will actually use. Since we know the current autoID we know 100% the next will be the current+1
Code:
' Code by Witchcraftz
' Button event to add new record
Private Sub cmdAddRecord_Click()
Dim strID As String
[code]...
View 2 Replies
View Related
Apr 1, 2015
I have this on the Form - On Current Event to show how many records I have on the Form, However when the employee filters the combo box's I want the record count to show the current records when they finish each filter. At the moment it just shows random numbers.
PHP Code:
Private Sub Form_Current() If Me.NewRecord Then Me.lblRecordCount.Caption = "New Record"
Else Me.lblRecordCount.Caption = _ "Record " & Me.CurrentRecord & " of " & Me.Recordset.RecordCount
End If End Sub
View 4 Replies
View Related
Mar 12, 2015
I have lots of combo boxes in my forms that are mostly limit to list as I want the people entering the data to actually add the full details of a client or supplier or whatever instead of just typing the information in over and over again. if the person/client/supplier is not in that list I have a button that will pop up a form so they can add a full new record but I need it so when they add the new record it will show up in the combo box in the intial form once it has been saved and closed without having to also close that initial form and reopen it or manually refreshing it.
right now I am using an if statement on the save button on my popup form that looks at what form is open and if that form is open then it refreshes that form after the save and closes which works fine but adding this to every form and combo box combination is very tedious, so I thought I would ask here, what is the best way to update combo boxes after and new record has been added via another popup form?
View 2 Replies
View Related
Feb 12, 2014
i have 2 recordset and i need to compare the two. If a record doesnt existing i need it to be added I have VBA that works but it seems very slow. Is there a better way of doing it
Dim rs As DAO.Recordset
Dim rs2 As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM ExorData")
Set rs2 = CurrentDb.OpenRecordset("SELECT * FROM MainForm")
Dim TJb_Main, TJb_new
If Not (rs.EOF And rs.BOF) Then
rs.MoveFirst
[Code]..
View 14 Replies
View Related
Mar 13, 2014
I found this code and have substituted parameters to suit my own needs however the loop is not working. Only the first record in my recordset (which is a test recordset of only 3 records) is being updated.
Also, for testing only, the edit or update being applied is trivial: Description = "WHITE RESIN". If i can get the loop to work I want to substitute higher functionality to the module.
Private Sub Update_Click()
Dim dbs As DAO.Database
Dim rsQuery As DAO.Recordset
Set dbs = CurrentDb
Set rsQuery = dbs.OpenRecordset("qryRmResin", dbOpenDynaset)
[Code] .....
View 14 Replies
View Related
Sep 21, 2014
I have a database which is slowly evolving. Users needed a feature to delete some records without a trail and some with a trail from the form. So I added an apply action field in the subform using which they can delete a record without a trail and if they wanted to keep a trail they could do that too. When user selects "Delete Violations as it was entered in error" the system deletes the record completely which is what everyone wanted.
After six months of use now I am asked to add an audit trail. I managed to do that also. I also looked at Browne's method but my data structure does not match the requirements for that method. I used an alternate method. It works as intended except when a record is completely deleted using the code I mentioned above. Then it goes in the infinite loop. I somehow need to bypass the before update event so that the function to write the audit trail is not called.
I have attached the database ...
View 14 Replies
View Related
Sep 11, 2013
I have a table with a multi-select listbox as one of the fields. I want to loop through the recordset (table) and changes the listbox selections for each record.
To go into a little more detail, the table (tblEmail) has a field (Label) that is a multi-select listbox. The listbox pulls from another table (tblLabel). I want to loop through records in tblEmail and edit/change the Label(s) for each email though VBA.
I've tried doing something like this:
Code:
With rst
.MoveFirst
Do Until rst.EOF
.Edit
!Label.Selected(0) = True
.Update
Loop
End With
However I get an error that says "Run-Time Error '438' Object doesn't support this property or method" ...
View 2 Replies
View Related
Jul 11, 2013
On a push of a button from a form, I want to locate specific records within the table and update a field to Null if it matches a name
Dim strSQL As String
strSQL = "UPDATE tblmaster SET Score = "" WHERE [AdvocateName] = 'Anna Maria'"
DoCmd.RunSQL (strSQL)
I'm certain I don't have the syntax correct between the Set to Null and WHERE clauses..
View 4 Replies
View Related
Jun 17, 2013
I have created a code below to test whether I can run a query and retrieve a data from an SQL server table. And so far I can return the result using a messagebox. but somehow I just don't know how to use this connection to update the table inside this access file. Basically I want to use this as a front end file. then when the form is open it will automatically update the table inside this access file and load the data to the combo box as a list.
Code:
Option Compare Database
Sub LocalServerConn_Test()
Set conn = New adodb.Connection
Set rst = New adodb.Recordset
[code]....
View 4 Replies
View Related
May 5, 2015
MS Access 2013: I have two database tables as below:
tbl1_MainDB --- It has a field named as "City" where I get huge data for some city names. Sometimes This field may have some unknown/new names which are not listed in our 2nd table ("tbl2_RefrDB")
tbl2_RefrDB --- It's a reference table which has raw names for cities, and then standard names of their city and state in another fields.
Target --- I want to create a VBA prorgram (Sql query) which can look from tbl1_MainDB.[City] to tbl2_RefrDB.[Raw_City] field, and if found then pick the "Standard_State" and "Standard_City" record values from there, and update into the 1st table "tbl1_MainDB".
...if not found in "tbl2_RefrDB" table, then user can be informed & ask for updating the new/unmatched city record as a new record in this table.
Attached sample database for more details.
View 4 Replies
View Related
Dec 4, 2014
I have 2 tables, one is like a main table, containing all of the main data, such as a Job Number, Customer, Quantity, etc. I have a second, related, table that acts a breakdown of information. There may be several related records to one main record, it entirely depends on the nature of the job.
What I'd like to do is run a function that looks at a main record, checks if all the related records COMPLETED field is ticked and then tick a field in the main record. I only want it to do this for records where all of the related records are COMPLETED.
View 3 Replies
View Related
Jun 13, 2013
I am provided a spreadsheet that contains multiple rows of similar data; each row/record represents a different stage in the process of financial transactions (requisition, purchase order, & voucher payment). Each financial transaction has these three records, with the amounts in one of three columns (pre-encumbrance, encumbrance, and expense), depending on the process.
What I am really after is the fuller, more detailed description that is apparently only available for the two records I don't want to import into the database (which is tracking only expenses and not the other two stages of the process). There is apparently no way to cross-reference these multiple rows due to the way the original database was designed (and we apparently have no control over this).
After importing the spreadsheet into Access, I would like to match the partial text string (truncated description) to the full description in another record, and update the record with the truncated description to the full description. To make mattes more complicated, I will also have to match values in the "pre-enc" or "enc" field with the "exp" field across these three records to make sure the correct descriptions are being matched because the truncated description will match multiple distinct records with the longer description.
TypeDescrPreEncEncExp
VOUJsmith-Instructor, 12/16/13$0.00$0.00$45.00
POJSmith-Instructor, 12/16/13, Course1, Parking($45.00)$0.00$0.00
REQJSmith-Instructor, 12/16/13, Course1, Parking$0.00$45.00$0.00
VOUJsmith-Instructor, 12/16/13$0.00$0.00$221.13
POJSmith-Instructor, 12/16/13, Course1, Lodging($221.13)$0.00$0.00
REQJSmith-Instructor, 12/16/13, Course1, Lodging$0.00$221.13$0.00
View 5 Replies
View Related
Apr 24, 2015
I'm trying to use VBA to update a new column in a table with info I already have in another table.The table I want to update is an inventory details table, it has around 25,000 records. I added a column called "UnitCost", of course the column is empty for all 25,000 records so I would like to fill it easily using DoCmd.RunSQL "UPDATE" feature.
I use that through-out the program however I'm unable to connect the dots for this one.What it needs to do is update "UnitCost" in "InventoryDetails" from "Products" where "InventoryDetails.ProductNumber" = "Products.ProductNumber"
The "Products" table has all the different unit cost, it just need to be placed in the "InventoryDetails" table for every record. Of course product1 needs products1 unit cost and product2 needs products2 unit cost, etc.
View 1 Replies
View Related
