Avatar of Hiro 714
Hiro 714
 asked on

How to create a query to generate a record.

I have a query trying to create a record, but it doesn't work.
another question is, how to set a modified date field.
Please take a look on sample file.
Database2.accdb
Microsoft Access

Avatar of undefined
Last Comment
Gustav Brock

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
John Tsioumpris

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Gustav Brock

You need to tell which record you wish to create - in which table and with what data, and if this data are to be input by the user.
Dale Fye

Well a simple insert query would look like the following when run from the SQL view in Access
INSERT INTO yourTableName (Field2integer, Field3Text, Field4Date)
Values (1, "test", #11/21/2019#)

Open in new window

Note that I did not include Field1, which in my case is always an Autonumber and gets created automatically.

if you want to do this from code, you have to make a couple of minor modifications, replacing the quotes with single quotes and wrapping the entire string in quotes, like this:
Dim strSQL as string
strSQL = "INSERT INTO yourTableName (Field2integer, Field3Text, Field4Date) " _
       & "Values (1, 'test', #11/21/2019#)"
Currentdb.Execute strsql, dbfailonerror

Open in new window

You can also insert NULLs into fields which accept them.
INSERT INTO yourTableName (Field2integer, Field3Text, Field4Date, Field5AllowsNulls)
Values (1, "test", #11/21/2019#, NULL)

Open in new window

HTH
Dale
Hiro 714

ASKER
Thank you! Would you edit my file? I would like to add a record to “test data” table
Your help has saved me hundreds of hours of internet surfing.
fblack61
Hiro 714

ASKER
There are 100 users will select test a or b or c, then input result. I need a result and modified date.
Dale Fye

Sorry, don't have time to update the file, did you look at what John gave you?
Gustav Brock

Again, as we can't read your mind: Add a record with what data, and are these data to be input by the user?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.