Update field in a table using vba Results

I need to update a field in a table based on what another field is

Now_month Count
1 1
1 2
1 3
1 4
1 5
2 1
2 2

What I need to do is insert incremental values in the count field based on what the now_month field value is. When the now_month field changes the counter needs to reset and then start counting again + inserting that value into the table.

I assume that the best way to do this is using VBA ?

Any help much appreciacted !!!!!

I am trying to format a field in a table which is created from a query. The field needs to be formatted as currency as an update query is used to enter monetary amounts from another table. At present when I run the update query the amounts change to whole numbers and not currency amounts.

If I change the properties of the field manually before running the update its fine, but it needs to be done automatically within code before running the update.

Is there any way to do this using VB.

Thanks in Advance

My goal is to programmatically set the OLE datatype field in one of my tables to the OLE object selected by a user via the Open File Dialog Box
ie. if the user selects "c:my documentsDemo.xls" I want to set the 'Multimedia' field in my 'DataSetDetails' table to 'demo.xls'

I have painted a Bound Object Frame onto my form with the intention of setting all the necessary properties to this control and then assigning this control to the OLE field in my table. (hopefully my intentions and methods are clear)

so far my code is:

A With rstDataSetDetails
B .AddNew
D !StudyID = cboStudyID
E !DataSetDescription = txtDesc
F !DataSetStatus = txtDataStatus
G !DataType = cboDataType
I oleDataSetLink.Class = "Excel.WorkSheet" J ' Set class name.
K oleDataSetLink.OLETypeAllowed =acOLELinked L ' Specify type of object.
M oleDataSetLink.SourceDoc = strFile_Path D N ' the User selected source file c:my E O documentsdemo.xls.
P oleDataSetLink.Action = acOLECreateLink Q
R !Multimedia = oleDataSetLink
S .Update
T .Bookmark = .LastModified
V End With

This almost works, I receive no errors on Line R, however it does not successfully link the 'demo.xls' spreadsheet to the DataSetDetails table. Instead of displaying "Microsoft Excel Spreadsheet" in the DataSetDetails table the 'Multimedia' field displays "Long Binary Data" and the field's Value is an awful looking value of:

˙˙˙˙WorksheetExcel.Sheet.8Acc ess.OLE2Link@ĐĎࡱá& gt;ţ˙ ţ˙˙˙˙ ˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙ ˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙˙

Any clues to get around this beauty?

Hi I've worked in access but only creating tables, queries, and macros. I don't know SQL or VBA in access. I'm working on a project now where I need to be able to choose specific records in a table and update those records with a year and period. Normally I would just use an update query, but I've go like 20 of these tables that I will have to update each month so I wanted to use one form that I could enter in the period and the year and it would update those records with blank year/period fields in each of the tables with the data entered on the form. Can anyone tell me how to do this? Thanks a bunch for all your help.

I am using a continuous form bound to a table to record unsafe driving
activites to a separate table. I have that module working. However,
as the form is bound, it is more or less a place marker and I need to
delete the data from one field in the table. I have tried to work out the module, but am stumped. Here it is:

Dim dbObject As DAO.Database
Dim strQuery As String
Dim HoldUnsafeTally As Variant
  HoldUnsafeTally = Forms!frmDriveAudit!subfrmUnsafe.Form!UnsafeTally.Value
  Set dbObject = CurrentDb
  If Not IsNull(Forms!frmDriveAudit!subfrmUnsafe.Form!UnsafeTally) Then
  strQuery = "UPDATE Unsafe " & _
    "SET UnsafeTally = Null " & _
    "WHERE UnsafeTally =" & Forms!frmDriveAudit!subfrmUnsafe.Form!UnsafeTally
  dbObject.Execute strQuery, dbFailOnError
  End If

I don't get any errors, but it simply doesn't work. Can anyone point me in the right direction to get this working?

I am seeking to use a module sheet to create a sub/function to update a field if it meets certain criteria

I have a table called = "GDV_Rates_Conventional_Sum"

and I have a fields called "No_Of_Risks_Sum"

What I need to do is fill a field if the following occurs

No_Of_Risks_Sum is above 100 Then place in 1
No_Of_Risks_Sum is Between 50 and 100 Then place in 2
No_Of_Risks_Sum is Below 50 Then place in 3

The field to update is called Credibility

Where do I start with VBA

Hi all!

I need to update a specific record in a table using VBA. I've tried a couple of codes from this forum, but once again I need your help..

I've tried this code, but I get "Run-time error '3164': Field cannot be updated".

PHP Code:
Private Sub knOppdater_Click()

    With CurrentDb.OpenRecordset("Faktura")
        ![ID] = Me.ID
        ![Status ID] = 5
    End With
End Sub 
Any idea?

Can someone please help. I am using Access 2010. I need to update data in a table of 5 fields from a query but I need to do this with VBA code or even using the SQL update command within the VBA code.

I need to move to a specific record in a recordset using the primary key of the table, (2 fields)based on values that I have extracted from a form. I have tried to use the SEEK, FIND and FILTER methods on the recordset object, but none of them seem to allow the use of variables.
Here is an example of the code I am trying to use.

dim intvar1 as integer (item# from form -user entered)
dim intvar2 as integer (customer# from form -user entered)

dim cnn as adodb.connection
dim rcdset as new adodb.recordset

set cnn = currentproject.connection
rcdset.open ("TableName", cnn , adopenkeyset, adlockoptimistic, adcmdtabledirect)


I could make this work if I used a constant value instead of the variable.

eg: rcdset.find "[field]=12 and [field2]=30"

but I need to replace the numeric values with the variable names

I hope I haven't been too vague with the description of my problem. I am fairly new at this, and I don't have that much experience with VBA.

Thanks for the help
Duane Barker

Hi all.

I have a situation I need some help to fix:

I have a Front-end which several user have a copy of, and they are all using separate Back-end files. (One Back-end for each project)

Now, i have a situation that I have to update the front end, and I need to add a field to every back-end table. But, when that i is done, I need a check in the front end that skip this code if the field in the table are selected.

So I need som hint how to programaticly add a field to a linked table from a front end.
And a code hint on how to check if the field are present...


Hi all. I'm looking for a block of code that will update all null fields in a table to a specific number such as 9999 for instance.

Under normal circumstances I would just create an update query with several iif statements to accommodate this. Or I could create a table that has each field set to a default of zero and then delete and append new data as needed.

My problem is that I have a table that fluctuates in the number of fields. For instance, in one run I may have Field 1 thru Field 24, while a second run may return Field 1 thru Field 50 (this is due to some cross table queries I generate). So I need a function that will loop through each field and replace null values with a value that I specify. Does anyone have any ideas how this can be accomplished?

Thanks so much in advance...

I'm trying to remove underlines that might exist in a table field (type MEMO, set to RTF, so the user's formatting is preserved). My plan of attack is to use an update query (pointed at the table) whose "update to" calls a VBA function, passing the name of memo field as "InputMemo". Where I hit a dead end is how to implement the UNDERLINE property (i.e., someobject.underline = FALSE). Here's the code that doesn't compile:

Public Function funRemoveUnderlines(InputMemo As Variant) As Variant
Dim FixedMemo As Variant
FixedMemo = InputMemo
FixedMemo.Underline = False
funRemoveUnderlines = FixedMemo
End Function

FixedMemo, which is intended to be a container with the "fixed" (no underlines) version of the InputMemo field, is a variable, not an object, which is why FixedMemo.Underline is an invalid statement (this is my interpretation, anyway).

Any fixes or suggestions for an alternative approach? The fact that I need to modify a table field, rather than a form or report control, makes things more complicated, I think.


I need a function to do:

- Loop through all fields in a table (use tabledef) (use any table – tablename should be a parameter for the function)

- There needs to be a timestamp-field which is set on some records and not on others

- For each textfield (only textfields – ignore all others!) check if there are new values (compared between the ones with time-stamp and without ) without time stamp will be first default entry and with time stamps will be updated entry

- If there are new values add the name of the field and the new values to a log-table

- After looping through all textfields and putting the name of the field and the values into the log-table – export the log table to Excel and show it in Excel. (alternatively you can log directly to Excel)

The end result in Excel should look something like this:




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.



I have a table with several (many) fields and for each field I have some empty values (cells). For now, I made an update-query for each field, for example: IIf(IsNull([Man1Type]),"0",[Man1Type]).

This works, however, as there are many fields I was searching for an automatic code (using VBA?) to replace all empty cells in the large table at once. I tried to use the function Nz() but without succes (you also need to do it for each field in a query wich comes to the same as the solution above).

Does someone has an idea how to automate this?

Thanks a lot on forehand!

General browsing on the topic of adding tables to a database throws up a whole lot of really confusing answers. Most stick to CurrentDB which of course does not address creating a persistent table with fields and properties to a database which is NOT the current one!

I have included here a way to choose the database to which you want to add a table using a browse button (all code included)

I have found this very useful when sending updates to clients, very user - friendly so errors have all but disappeared since I began using it.


Firstly let me thankyou for your help because I feel this may be a difficult question.

I have a stock control db that records transactions through purchase orders, which allocates each line item (product) in a purchase order to an employee and a project it is destined for. I have three tables, one for purchase orders, one for transactions and one for products.

I then have a seperate table for stock in and out with a two other look ups for projects and employees.

Now sometimes a product may become a stock item as it is in surplus and not used for the project.

My problem is how do I re-allocate the product as a stock item then when it is used allocated it to another project.

Basically we use an internal unique number for each purchase order and i have a query set up that will recall each product in a purchase order. The reason for this is to save time updating goods recieved when an order turns up as i call all the item in a purchase order in one go rather than going to each product one at a time to update.

I think the db may be to advanced in the set up and feel a restructure of the db is the only solution but hopefully i not. I am no means an advanced user buit have done ok so far in seeting this db up e.g. my knowledge of vba is limited etc.

This has only just come up in what is needed so it is an after thought, however is very important for the operation of the db as a stock control system.

All advice much appreciated in advance.


Good day access programmers,

Greetings from Malta.

I wonder if I can please get some help.

I have a form "Appointments" which has a combo box, two list boxes and various other controls. List 54 updates on the 'after update' event of the combo box using code. This is working well

A value on List 54 is referenced from a textbox (Text62) on the form as follows;


I had to do this indirectly as the SQL statement using the above statement directly did not work.

List 56 is the second list box. It updates on the 'after update' event of List 54 using VBA code which injects an SQL statement into the rowsource property of list 56.

Another textbox control (Text34) contains a date entry which is entered by the user, while (Text64) is another textbox entering time. Both are formatted as Date/Time entries.

I have added a command button which on click should generate code which among other things injects the following SQL statement so that list56 will update depending the date entry in Text34. I want the rows of the list sorted out according to date and time in that order.

The following is the SQL statement which is injected;

SELECT Query3.Appl, Query3.clName, Query3.Surname, Query3.cltIdcard, Query3.meta, Query3.hin, Query3.cltTel1, Query3.ID, Query3.confirm
FROM Query3
WHERE (((Query3.meta)=[Forms]![Appointments]![Text34]) AND ((Query3.dctidcard)=[Forms]![Appointments]![Text62]))
ORDER BY Query3.meta, Query3.hin;

For clarity sake 'meta' is the date field and 'hin' is the time field in a table.

This SQL statement is not updating the list when the command button is clicked. I suspect that it has to do with how I am writing the SQL statement as it should include something like # # for the date criteria and probably also for the time criteria.

Can someone please enlighten me on this problem ?


Hi I have a form with various text boxes/combo boxes check boxes representing the fields in a table, on save I want to add the record to the table thus using dmax +1 for the autonumber ensuring no issues with multiple users getting the same number. the following wont work.

Second question is do I need to do a character check on each field to ensure the user has not entered too many characters?


Private Sub Command66_Click()
Dim dbNCW6b As DAO.Database
Dim rsttblInvestigations As DAO.Recordset
Set dbNCW6b = CurrentDb
Set rsttbInvestigations = dbNCW6b.OpenRecordset("tblInvestigations")
rsttblInvestigations("No").Value = Me.No
rsttblInvestigations("Due Date").Value = Me.Due_Date
rsttblInvestigations("Notes").Value = Me.Notes
rsttblInvestigations("Date Logged").Value = Me.Date_Logged
rsttblInvestigations("Source").Value = Me.Source
rsttblInvestigations("Investigator").Value = Me.Investigator
rsttblInvestigations("Section").Value = Me!Section
rsttblInvestigations("Method").Value = Me.Method
rsttblInvestigations("Ref1").Value = Me.Ref1
rsttblInvestigations("Ref2").Value = Me.Ref2
rsttblInvestigations("Evidence Only").Value = Me.Evidence_Only
rsttblInvestigations("Issue").Value = Me.Issue
End Sub

Access Forum Help Screenprints 11.28.12.zipOk, please bare with me because I am a pretty new at Access and VBA programming/etc and I am trying to build what I thought was a relatively simple database for my company's in-house mechanic's shop for tracking repairs and maintenance on our vehicles/equipment.

I have a Repair and Maintenance Job table that tracks the jobs by JobNumber (autoassigned PK) and a part table for which I can track individual parts by CJBPartNumber (auto assigned PK). I have a one (Jobs) to many (parts) relationship based on JobNumber (foreign key in part table). I foresee that the users will need to add parts to jobs in two different ways: 1) sometimes they order parts specifically to do a job and 2) sometimes they will already have parts in stock that they previously entered into the system and use them on jobs. So I have two buttons on a Job Entry form that allow them to add new parts directly to jobs and also add parts that are already in the system (i.e. stock or inventory parts) to jobs. I have a pretty simple form that takes care of the first task of adding any new part to any particular job. I also have a good idea of how I want the user to be able to add existing parts to jobs but I am not sure how to go about executing it. First, I created an unmatched records query (unmatchedparts) that pulls the records of parts with no associated job number. Then I created a form and added a List Box based on the unmatchedparts query (used as Row Source) which allows multiple selections so that the user can view all parts that are in stock not associated to a job yet.

What I want the user to be able to do is select a part or part(s) from that list box and assign the job number they were working on to it. I was thinking they could click a a command button that says something like "Add Selected Part(s) to current Job" and the On Click event code would go to those parts in the parts table (I am assuming based off the Primary Key CJBPartNumber?) and input the associated JobNumber into the JobNumber field in the Parts Table - thereby assigning those parts to that job. The Job Number would come from the previous form (which is actually a Job Entry Subform that is part of the Vehicle and Equipment form)

As I said, I have a pretty good idea of what I want the code to do and no clue on how to write it. Any help with the VBA coding necessary to do what I am trying to do would be greatly appreciated. I have looked everywhere and typically I can find something close to what I need done, tweak it, and it work out but I have had no such luck with doing this.Attachment 196895

I have also attached a word document that should atleast help you visualize what I am trying to do with my forms. Please let me know if you need additional information.

Not finding an answer? Try a Google search.