• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 459
  • Last Modified:

Insert with VBA

Hi all,

I've just inserted a new field into an already existent table, and I would like to populate this field which spans over two thousand records. I would like to create a vba script which will allow me to do this.

I'm assuming that I would have to create a new module but I'm not quite sure how to go about it. I am familiar with attaching simple code to buttons etc, but I'm stumped as to how to do this!

grateful for advice.

- My new field is FileCode, the table name is Files and the vale I want to insert is the letter A -
I have tried the following:

Public Sub insert()
Dim db As Database
Set db = CurrentDb()
CurrentDb.Execute "INSERT INTO Files (FileCode) VALUES ("A")"
db.Close
End Sub

and

DoCmd.RunSQL "INSERT INTO Files ([FileCode]) "VALUES ('A')"

0
PipMic
Asked:
PipMic
  • 6
  • 3
1 Solution
 
Patrick MatthewsCommented:
UPDATE Files SET FileCode = "A"
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
PipMic,

Your code is using "INSERT INTO ... " which is an Append query. This adds new records. It does not modify existing records.

As matthewspatrick pointed out you need to use an Update query to modify existing records.

0
 
PipMicAuthor Commented:
Hi all,

Thanks, but what would be the VBA code?


0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
PipMicAuthor Commented:
I'm completely lost..... how can i use an append query to update a new field with the same value??
0
 
PipMicAuthor Commented:
Easy after all
0
 
PipMicAuthor Commented:
Ok soory  i was confusing append with query....thanks all.
0
 
PipMicAuthor Commented:
soorry confusing append with update query!!!
0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
<< ...but what would be the VBA code?>>

Your VBA code is fine. It is the SQL Statement that is the issue. The "INSERT INTO Files (FileCode) VALUES ("A")" is the part that needs changed.

<<m completely lost..... how can i use an append query to update a new field with the same value??>>

You don't. An append query is used to add records. You use an Update query to update a field in an existing record.

Public Sub insert()

     CurrentDb.Execute "UPDATE Files SET FileCode = ""A"";"

End Sub

Open in new window

and

DoCmd.RunSQL "UPDATE Files SET FileCode = ""A"";"

Open in new window


0
 
Boyd (HiTechCoach) Trimmell, Microsoft Access MVPCommented:
You're welcome. Glad I could assist.
0
 
PipMicAuthor Commented:
Thanks HiTech,

For the VBA correction.

:)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now