VBA code to update field in table

Hi,

I am currently trying to add a last updated field for each field on my table. I figure I would have to add a before update code for each field on my form. However, the field that I want to have updated is not located on my form, but just on the table. I would like to have the field on the table updated with the last user and time the field was updated.

Any help will be greatly appreciated.


Post your answer or comment

comments powered by Disqus
Hello
I have a table which I need to use to update values in another table. I do not want to create a lot of update queries (although this does work so I know what I'm trying to do is feasible)

I've got a table called ForecastUniqueInsts which has 6 different pricing fields to update. The table ForecastReport has rows which when the ID matches ID in ForecastUniqueInsts I check the Yes/No box to say that I have that value.

The SQL for the first field is :-
UPDATE ForecastUniqueInsts INNER JOIN ForecastReport ON ForecastUniqueInsts.ID = ForecastReport.ID SET ForecastUniqueInsts.[Mid Price] = Yes
WHERE ((([ForecastReport]![Column_list]) Like "*MP*"));

I need to repeat this 5 times changing the field set and the value in the where condition. I can't get this to work in runSQL in VBA and am getting run time errors on my field specifying that Column List contains value MP.

Help! This is driving me insane as I'm sure its very simple and I'm being very blonde.

Thanks!

Hello Everyone,

I know it can be done and I have seen some code snippets before, but how can I use Access vba code to count all forms, tables, queries, reports, vba modules, objects to get a total count of each for an Access database?

Any help is greatly appreciated.

Thanks,

Kurt


Hi

I want my database to update field in a table each time the database is opened.

The field in the table that I want to update is today's date: so that if I open the database today, it will be 12th October, if I open it tomorrow, it will change to 13th Oct?

Can I create a macro to do this or do I need to do something to the field itself? at the moment the default value is Now(). But once it is entered - it stays as is.

Also - to further complicate matters, I don't want the field to update where someone has actually gone in a typed a date in? (The field is supposed to contain the current date, until a user puts an "end date" in).

Thanks!!

Maria

Hy,
I have a table having one date field formatted as short date "15.12.2011", and on a form text box formatted as short date format. When I click on text box calendar appears and I choose proper date, but when I want to update date in table via query it returns error.
My query looks like:
CurrentDb.Execute ("UPDATE category SET date=" & Forms![main form]!txtDate.Value)
I don't see anything wrong but it keeps me returning error

And If I use US format update works but with date 31.12.1899

Hello,

Is it possible to make a query which has a value in one field that is used to extract that specific field form another table?

What I'd like to do is this:
I need to somehow keep track of changed that are made in my table "Members" (fields like "Member number", "first name", "last name", "address", "city" etc.). And I need to perform those changes by hand in a different database (I'll only get access to that one in a web interface allowing me to change data per member *sigh*, that is doubling the work). Changes are like a new street address, or a new sports activity. Changes in the second db via webinterface have to be done by december 31 of a specific year. Changes in 'my' database have to be done 'instantly'. (That's why I'd like to spread my work)

What I thought of was to make a table "Changes" containing the fields "member nummer" (equal to its counterpart in table Members) and a field that contains the name of the field of the Members table that should be changed, so I'd get
1234 Last name
2345 Address
2345 City
3456 Phone number
for table "Changes". From that I wanted to get a list that gives me those records and the contents of the field that was changed: with a query that contains both tables "Members" and "changes" and I'd get
1234 Last name House-Cuddy
2345 City New York
3456 Phone Number 555-123456

That way I hope to get an easy list of what fields were change, and what I have to change it into. Then delete the record when the change is done.

Well, I'm also in for better ideas than this one :-)
Thanks!

how to create field in table that just get number?

Trying to get vba code to enter in repetive data.

Basically there is two tables that this deals with, Chemical and Chemical Details Table.

Chemical has this field called Method, which determines what chemicals are tested, hence the parameters field in chemical details, which has a record for each chemical.

Since the methods always use the same chemicals, i want the vba code to generate the records with the paramaters automatically depending on which method is chosen.

The problem is getting the data entered in the ChemDetails tables so it corresponds with Chemical Table, meaning the primary key doesn't match up, Chemical_DataID, which is a autonumber.

I tried setting the Chemical_DataID in the details table to what the Chemical table would have, but that doesn't work, it doesn't seem to let u change the number.

Any Ideas?
Thanks Greg

Private Sub Method_AfterUpdate()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim tempID As Long
Dim rstDet As DAO.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("Chemical", dbOpenDynaset)
Set rstD = dbs.OpenRecordset("Chemical_Details", dbOpenDynaset)

rst.MoveLast
rst.AddNew
tempID = rst![Chemical_DataID] - 1
If rst![Method] = "EPA 602" Then
rst.MoveLast
rstD.AddNew
rstD![Chemical_DataID] = tempID
rstD!Parameter = "t butylmethylether"
rstD.Update
rstD.AddNew
rstD!Parameter = "Benzene"
rstD![Chemical_DataID] = tempID
rstD.Update
rstD.AddNew
rstD!Parameter = "Toluene"
rstD![Chemical_DataID] = tempID
rstD.Update
rstD.AddNew
rstD!Parameter = "Ethylbenzene"
rstD![Chemical_DataID] = tempID
rstD.Update
rstD.AddNew
rstD!Parameter = "m + p Xylene"
rstD![Chemical_DataID] = tempID
rstD.Update
rstD.AddNew
rstD!Parameter = "o-Xylene"
rstD![Chemical_DataID] = tempID
rstD.Update
End If
End Sub

It's a report that is called from a Main Screeen. The VBA is located in the detail Onformat section of the report. This is what I got the code down too. I used to have it in the OnPage for the report, however, after some reading I found that it doens't update the OnPage until after the formating. So it was suggested that I use the OnFormat of the Detail section. This still doesn't really work. I have a field on the report that has the checkbox for rushed and the PO_Number is listed on the report as well. I want to have the TextBox93 (I know I didn't name it, Yet!) say Rush Order if in the table the field Rushed is checked. This is filled via a different form when they enter their purchase orders. I took out the responce code to see how it flowed without the stops. One thing I did notice is when it was using the msgbox's it seems to evail this code twice before displaying the first page.
Rushed = the field that is a checkbox that is stored as a lookup and yes/no
so I am seeing in my testing that is either 0 or -1
PO List = my table that stores all the information about each PO.
Me![PO_Number] is the po_number the query pulled to fill the report and is on the report and
[PO_Number] = should be what is pulled from the table PO List to be compared with.

Let me know if you need anything else.... thanks.

VBA:

	Code:
	Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) 
    Dim test 
    test = DLookup("[Rushed]", "[PO List]", [PO_Number] = Me![PO_Number]) 
    If test = "-1" Then 
        Me.Controls!Text93 = "Rush Order" 
    Else 
        Me.Controls!Text93 = "" 
    End If 
End Sub



I'm trying to update a table value based on another value in the same table.

Here's the scenario:

In the table I have 3 estimate descriptions: Sizing, Planning and Commitment. Each description is tied to a series of numbers representing estimates across different teams. For example:

Estimate_Type Team 1 Team 2 Team 3
Sizing ................0........ 0......... 0
Planning .............0........ 0......... 0
Commitment....... 0......... 0......... 0


I currently have all 3 estimate types default to all zeroes when a new team is added to the table. The teams then enter their respective values in the Sizing estimate field. Most commonly, their Sizing estimate will end up equaling the same value as their Planning and Commitment estimate....so, I would like the values for the Planning and Commitment estimates to update automatically when the Sizing estimate changes. My first thought was to use dLookup but I wasn't sure how to make this work. Would I have to run VBA code to sync up the 3 estimates?

This would be easy to accomplish in Excel, but I can't seem to think of a solution for Access. In Excel, I could simply place a reference to the cell with the sizing estimate into the cells for Planning and Commitment. Looking for something comparable in Access??

Thanks in advance.

I am new to VBA and have spent over 3 hours attempting a relatively simple procedure. Any help very much appreciated. I have 3 text fields in a table. Field1 holds values 0,1,2,3,4 as text. Field2 holds a value which I want to store in a variable when Field1="0" and I want to update Field3 with the prevailing value of the variable. The code is below and it simply does not work.

Dim i As Integer
Dim x As String
Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("Usage interim")

For i = 1 To rs.RecordCount

If rs.Fields("field1") = "0" Then
x = rs.Fields("field2")
End If

rs.Edit
rs.Fields("field3") = x
rs.Update
rs.MoveNext
Next i
End Sub

Hi everyone,

I don't know how to actually create a Query or a Code to Update Information on a Table that have a field that in the index is a YES/NO DUPLICATE. The only thing that does not change is the auto number. but all the information may change.

I have created a macro to transfer that information to excel and then another person will extract that information from excel to access. when we did the transfer and the extraction the first time, it works, but when you change information in the table and try to pass it again. it doest not make any changes...
there are any way to create a query or a VBA for this excersice.??

Thanks in advance...

and i did my home work trying to find something like this in the forum...

Hi All,

I have a complex one - or at least i think it is complex

I am creating two listboxes side by side with add and remove buttons in between.

Left Listbox: lstAvailable
Right Listbox: lstSelected

The left listbox contains a list of equipment that is available for hire which is determined by its current status (ie. NOT On Hire) and all the information comes from the EquipmentDetails table.

On the right listbox is a list of entries that have been added to the ItemList table from the left listbox and only shows the records that match the main form EventID.

The problem i have is that when i add a record from the left listbox, it doesnt disppear because the status within the EquipmentDetails table has not been updated during the process of adding that equipment to the ItemList table.

My question is; the VBA code added to the OnClick event property references the recordset of ItemList only so i cannot see a way to also update the same EquipmentID within the EquipmentDetails table at the same time.

Also the code for the Remove Records button doesnt remove any of the records in the right listbox.

Any ideas on how this could be done?

I have attached a copy of my database for reference, where the listboxes in question are located on the EventsDetails / Equipment tab.

Any help on this would be greatly appreciated.

Regards
Mike

I am trying to loop through a recordset and update a currently empty field in a table with something depending on another field. After hours of frustration I can't seem to get it to figure out what I am doing wrong. Only the first record is updated though I know it is cycling through all the records. Here is the code, any help would be appreciated.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim count As Integer

Set db = CurrentDb
Set rst = db.OpenRecordset("TblTransactions", dbOpenDynaset)
With rst
rst.MoveFirst
count = 0
Do Until rst.EOF
Select Case rst!PlanNumber
Case Is = 21
rst.Edit
rst!Subcode = 20351
rst.Update
Case Is = 23
rst.Edit
rst!Subcode = 20352
rst.Update
Case Is = 25
rst.Edit
rst!Subcode = 20353
rst.Update
Case Else
rst.Edit
rst!Subcode = "error"
rst.Update
End Select
count = count + 1
rst.MoveNext
Loop
Me!testtxt = count
End With
rst.Close

How to trough VBA code to create dynamic table in MS Access 2003? Any sample code is welcome. TQ

Hi all

I can't seem to find how to do this.

I have a form that has no controls source with unbound text boxes that are populated when I place a corresponding code in a text box then hit enter.
Most of the text boxes are just to view data but one of them I want to edit the source field in the table if I press a button. The caveat is I can not have any of the fields bound it all needs to be done via VBA.

Thanks in advance.

I have a table that I want to update (via VBA) prior to any any data being retrieved from it.

Is there a way to do this so that I don't need a separate copy of the update code for each different object that would require the table to be updated?
Is there some way to make a global VBA function and call it whenever I need to update?

Hello,

I have attached a picture to illustrate my needs but I will provide some back ground first. I have 4 tables; one of which I call the [Master] Table, it contains information such as the item's name and a given ID (auto-number). The other tables contain information such as descriptions and a 'Program' ID (auto-number).

The 3 'Program' tables may be linked via the [Master].ID if there is a relation. Currently in my form [Master Details] I ask a simple YES or NO question to see if there is a relation. (see picture) If there is a YES specified in my Master Table for one of the Programs I manually go into the appropriate program table and add the [Master].ID into a new row creating a new Program ID for that item.

What I need is this [Master].ID to be generated automatically if YES is selected.

In the jpeg it shows the Master table at the top. Below this is my form where we create new items and select the appropriate linkages. Below this is one of my program tables [Scribe] where I input the [Master].ID and create a new row with a new Scribe ID. I would like the VBA to start when the Save and New button is clicked in the form.

Thanks. Attached Thumbnails   Reply With Quote 01-22-2013, 12:06 PM #2 June7 Super Moderator Windows XP Access 2010 32bit Join Date May 2011 Location The Great Land Posts 15,116 Why do you have 3 'Program' tables? Are the fields identical?

Use form/subform arrangement. Master/Child links properties of subform container control will automate saving the MasterID to the child table. No VBA code necessary. Nor are the Yes/No fields.

Hi,

I have 2 tables.

Table 1: Master table
Table 2: Temporary table

This is my current process:


Every quarter I run a report that pulls loans that meet specific criteria.I export this report into excel (the loans fall into column A)I add a file number and box number in columns B and C.I import the excel spreadsheet to table 2 (they're linked so I don't need to import, it's automatic)

Now that I have the updated information back into the database (table 2), how can I get this information back into table 1? The excel spreadsheet only contains a few loans that need to be updated in table 1. I have tried creating an update query with both tables linked and use the "update to" field. However, when I tried to run the query, it says I have 0 records updated.

My update query is as follows:

Field: Access Bar Code
Table: Table 1
Update To: [Table 2].[Access Bar Code]

Field: Access Box Number
Table: Table 1
Update To: [Table 2].[Access Box Number]

Basically I'm trying to have the query update specific fields in table 1 based on the information from table 2.

I am sure this is pretty straight forward but having some difficulty here. I am trying to update a field called "LastUpdated" in a table after a subform has been updated. I am using the following code: Code: DoCmd.RunSQL "UPDATE tblQuestions SET tblQuestions.LastUpdated = Now() WHERE tblQuestions.QuestID = QuestID" Instead of just upating the one record, I get a message asking me if I want to update all the records in the table (in this case 295). What am I doing wrong?

I have the following code to try and update a field but it seems to fail at the strSQL statement with Error Number 3129 coming up, doesn't recognise UPDATE.

One field called SlipId has records which begin with "L" and where they can be found I a requirement to update a field called Headings with Binder Decs Earn

Function upDatePhoenix3()
Dim strSQL As String
strSQL = "UPDATE [Combined data:Phoenix] Set [Combined data:Phoenix].Headings = 'Binder Decs Earn' "
strSQL = "WHERE ((([Combined data:Phoenix].SlipId) Like 'L*')); "
Debug.Print strSQL
DoCmd.RunSQL strSQL
End Function

Hi,
I am trying to write a VB script that would copy rows of a csv file and append an existing table with new entries. But I only want to copy a subset of columns of the csv file to a subset of table fields.

E.g. the csv file contains a few columns, but I am only interested in importing data from three columns titled (in the first row) "Date", "Amount", "Transaction Narration" to an existing access table called "tblTrans" with field names BankDate, Credit, BankDescription. tblTrans also contains other (required) fields, which will be filled out by the user in a form after the csv import is complete.

Any ideas of how I can do this in a VBA script?

What is wrong with this vba code ?
PHP Code:
Private Sub CmdCancelLoan_Click()
On Error GoTo Err_CmdCancelLoan_Click

    Dim stDocName, stLinkCriteria, strMessage, strReason, strSQL As String
    Dim LID, AID As Integer
    Dim strUserRole As Variant
       
    
    If Me.LID = 0 Then  'Approved Applications - Loan not yet Created
    
    stDocName = "frmLoanProcessData"    'Main Loan Process Form to allow Cancellation of Loan
    stLinkCriteria = "[APLPK]=" & Me![APLPK]    'Link Criteria set to AID - Application ID
    DoCmd.OpenForm stDocName, , , stLinkCriteria, acFormEdit    'Open Form
    
    Else                'Assume must be a Created Loan with an LID number exceeding Zero - Not Zero
    
    'stDocName = "MacroLoanCancel"   'Set name of Macro to Cancel Loan
    'DoCmd.RunMacro stDocName            'Run Macro
    
    'Try to do task by VBA
    strReason = InputBox("Please Enter reason to Cancel this Loan")
    strUserRole = DLookup("[TeamStatus]", "TBLTeamMembers", "[TeamID] = '" & CurrentUser & "'")
    LID = Me.LID
    
    strSQL = "UPDATE TBLLOAN SET TBLLOAN.LDPrin = 0, TBLLOAN.LDInt = 0, TBLLOAN.LDAFee = 0, TBLLOAN.LDPFee = 0, TBLLOAN.LDPayK = 0, TBLLOAN.LDTerm = 5"
    strSQL = strSQL + " WHERE (((TBLLOAN.LDPK)=LID));"

    
    End If                  'End Procedure

Exit_CmdCancelLoan_Click:
    Exit Sub

Err_CmdCancelLoan_Click:
    MsgBox Err.Description
    Resume Exit_CmdCancelLoan_Click
    
End Sub 
The SQL string is supposed to update the fields on TBLLOAN where [LDPK] = [LID] on the form where the event is.

No error message rec'd.

Appreciate any guidance

***These are both Access 2000 databases.

I want to put a button on a form in DB1, and when the button is pressed, I want to update field1 (or whatever the field is called) in DB2 to "Richard (or whatever value is decided to be set)." What VB code would I need in order to make this happen? I think it would be something similar to this, but I am missing the if....then portion of it.


Private Sub Update()
Dim IsFormLoaded As Boolean
Dim AccessDB As DAO.Database

IsFormLoaded = False

If ....... Then

End If

I have VBA code in my field validation which turns the error field(s) to red background for the fields which have data errors.

I am dealing with testing key violation conditions currently. The Err.Description value does not state which field(s) is/are in error.

Is there a way to programmatically determine this so that the error field(s) may be set to red background?

Thanks!


Not finding an answer? Try a Google search.