Total new to Access, How do I update data in a table using a from?

[nagiese]

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.

[Robeen]

Do you want to update existing records in multiple tables and put data from your Form into their Year & Period fields?

Are the Year & Period fields already in all the other tables?

[nagiese]

Yes, I want to update existing records in multiple tables with the year and the period. And, yes all of the tables have the year and period field in them. These tables will have data appended to them each month, since the system that we are pulling the data out of does not provide a date field I need to be able to add the period and the year to the appended records each month. But, since I have over 16 tables that need this update I would like to use a form that will update them all with the year and the period. Essentially I would like to use a form to find all the records with “null values” in the Year & Period fields, and then update them with the values entered in the Year & Period text boxes in the form.

[Robeen]

Can you tell me what Data Type your Period filed is in the Table? Is it a Number - or Text . . . ?

[Robeen]

Hi Nagiese,

I just ran this update on a small table I created and it updated all the records with a Null in my TestTimestamp field with the date that was in the StrDate field of my Form.

 
Private Sub Command21_Click()
 
Dim db As Database
 
Set db = CurrentDb
 
db.Execute ("UPDATE TableName SET TableName.TestTimestamp = #" & Me.StrDate & "# WHERE TableName.TestTimestamp Is Null")
 
End Sub

Those rows that already had date values in the TestTimestamp field were not touched.

I still don't know the Data Types of your two fields [Year, Period] - so if you still need help - let me know what the Data Type is for Year and for Period and I'll help with that.

[nagiese]

my data type is number for the year and period fields.

my table name is mtb-STAT3AR1 and my form name is frm-Update Fiscal Yr/Pd

My form is unbound so I'm not sure if that is a problem.

I'm not sure how to translate your code into my database since I've never written any VBA or SQL in access before.

[Robeen]

1.
I don't know the name of the Number field in your Table that has the Year.
In my example below I have called that field YearField.
You can put in the correct name of your field wherever you see 'YearField'.
If you have spaces in your field name -
Eg: Year Field
then put the field name into box brackets - like this: [Year Field].

In my example that would look like this:
[mtb-STAT3AR1].[Year Field]

2.
In my code - you will notice 'Me.StrYear'. The StrYear is the name of the TextBox on my Form that has the YYYY year that will be put into the Year field in the Table.
Replace StrYear with the name of the text box on YOUR Form that has the Year that you want to put into your Table.

You will need a Command Button on your Form.

In my example below - I've called the Command Button cmdUpdate.

Right-Click the Command Button -> Build Event -> Code Builder.
If your command button is named cmdUpdate, you will see a window open with this in it:

 
Private Sub cmdUpdate_Click()
 
End Sub

Now - paste this code:

 
Dim db As Database
 
Set db = CurrentDb
 
db.Execute ("UPDATE mtb-STAT3AR1 SET mtb-STAT3AR1.YearField = " & Me.StrYear & " WHERE mtb-STAT3AR1.YearField Is Null")

into that Sub [Sub Routine] - between those two lines above.

It should end up looking like this:

 
Private Sub cmdUpdate_Click()
 
Dim db As Database
 
Set db = CurrentDb
 
db.Execute ("UPDATE mtb-STAT3AR1 SET mtb-STAT3AR1.YearField = " & Me.StrDate & " WHERE mtb-STAT3AR1.YearField Is Null")
 
End Sub

Now - as long as you have replaced my 'YearField' with the name of the Year field in your Table - that code should put the Year from the text Box on your Form into all the Year fields in your table that are Null.

I hope this helps!

[nagiese]

I followed your steps, but when it came time to enter in the code I right clicked on the comand button and them Build Event and it took me to the macro builder, I did not have an option of Code Builder. How do I get to the Code Builder?

[Robeen]

Right-click the button.
Open the Property Sheet.
Click the Event Tab.
To the extreme right of the On Click row - click the [...] button.
You should be given the option to Build Code.

If you don't get that option:
Put a new command button on your Form.
Cancel when Access tries to automate the button's functionality for you.
Then
Right-click the button.
Open the Property Sheet.
Click the Event Tab.
To the extreme right of the On Click row - click the [...] button.
You should be given the option to Build Code.

I hope this helps!

[nagiese]

Ok, I found it for some reason after I added the VBA Project button on to my quick access tool bar then it opened up when I tryed it again.

[Robeen]

OK!!
Let me know if everything works out fine!

[nagiese]

Sorry that I haven't responded in so long, I've been traveling on business and this is the first chance I've gotten to work on this project. I've followed your instructions and I get a "Syntax error" message in the update statement line. Can you tell me why I'm getting that error and what I need to change to make this work?

I've posted the code below, for your review.

Thank you for your help.
Nena

Private Sub cmdUpdate_Click()

Dim db As Database

Set db = CurrentDb

db.Execute ("UPDATE mtb-STAT3AR1 SET mtb-STAT3AR1.YearField = " & Me.StrYear & " WHERE mtb-STAT3AR1.YearField Is Null")

End Sub

[Robeen]

1. What is the name of the field in your Table that will hold the Year?

2. What is the name of the text box in your Form that has the Year?

[nagiese]

1. YearField
2. StrYear

I changed my field names to match yours to see if the code would run without me changing anything.

[Robeen]

It still throws the error?

Are you able to post a copy of your DB here?