Button to add data to table

[156sting]

Hi there,

i have a form with some data and what i want to do is create a button which takes some of that data and enters that information into another table within the database.

to me the concept sounds easy i just do not know where to start!

hope someone can help!

 

[arnelgp]

if you want to add record to another table, create Append Query.
if you just need to update the record, create an Update Query.

in Append Query:

1. on Ribbon->Create->Query Design.
2. choose the table you want to append to.
3. select fields to be appended to. dont include the autonumber field.
4. again on Ribbon->Append
5. choose same table.
on the Field list replace with your form fields, example say you have UserID on your table:

Fields: UserID
Table: yourTableName
Sort:
Append To: UserID

after inserting your form fields, it will look something like this:

Fields: Forms!yourFormName!UserID
Table: <remove the table name>
Sort:
Append To: UserID


in Update Query:
follow steps 1-3
4. again on Ribbon->Update

on (Update To type your form's corresponding text fields (Forms!yourForm!UserID, etc.)


Save your query.

on the click event of your button you can either run your query on Macro builder or Code Builder:
on Macro builder, choose OpenQuery. type your query name.
on Code Builder:

Private Sub button_Click()
DoCmd.RunSQL "yourQueryName"
End Sub

 

[156sting]

thanks for your reply but i dont quite understand what you are trying to get me to do.

i have an existing query for the form, i want to have a button on this form which when i click, takes the information i need it to take and enter it into a separate table.

the example below is a test i been messing around with before i implement it on my database

Private Sub btnAddRecord_Click()

Dim FirstName
Dim LastName
Dim UserEmail

FirstName = "Chuck"
LastName = "Norris"
UserEmail = "chuckyboy@gmail.com"

INSERT INTO tUsers (fname, lname, email) VALUES(FirstName, LastName, UserEmail);

End Sub

this is a simple version of what i want the button to do... keeps telling me syntax error...

 

[Auntiejack56]

Hey Stingo, you are nearly there:


Dim strSQL as string

strSQL = "INSERT INTO tUsers(fname, lname, email) VALUES ('" & FirstName & "', '" & LastName & "', '" & UserEmail & "');"
DoCmd.RunSQL strSQL

Be careful with the quotes around the names.

However, this suggestion comes with a warning: the first thing that will go wrong is that a user will double-click the button, and you'll have two records in your table instead of one. Try identifying the record clearly, then insert a check in the above code - something like:


Dim strSQL as string

If IsNull(DLookup("ID","tUsers","fname = '" & FirstName & "' AND lname = '" & LastName & "'") then
strSQL = "INSERT INTO tUsers(fname, lname, email) VALUES ('" & FirstName & "', '" & LastName & "', '" & UserEmail & "');"
DoCmd.RunSQL strSQL
End If

Jack

 

[Auntiejack56]

Actually, comes with 2 warnings: When a Mr O'Riordan or Mrs O'Reilly comes along, it will confuse the SQL, so you'll need to escape the quotes.
Try: Replace(lastname, "'","''") instead of using lastname on its own.
Jack

 

[156sting]

thanks soo much!

that exactly what i was after..

thank you for the warnings. the code i done above is just an example. i will not be using names in the final product!

bloody legend

 

[156sting]

further to my question above, i've implemented the the code thanks to AuntiJack,

Private Sub btnCharge60Day_Click()

Dim TenementNumber As String
Dim TenementProspect As String
Dim ClientToCharge
Dim TheChargeDate As Date
Dim ChargeDescription As String
Dim TimeCharge As Long
Dim PhotocopyCharge As Currency

TenementNumber = Me.TheTenement
TenementProspect = Me.TheProspect
ClientToCharge = Me.TheClientDB
TimeCharge = "1.25"
PhotocopyCharge = "1.00"
TheChargeDate = Me.TheDate
ChargeDescription = "60 DAY MAINTENANCE LETTER"

Dim strSQL As String
strSQL = "INSERT INTO [Charge Data](Date, Code, Number, Prospect, Detail, Time, Photocopy) VALUES ('" & TheChargeDate & "', '" & ClientToCharge & "', '" & TenementNumber & "', '" & TenementProspect & "', '" & ChargeDescription & "', '" & TimeCharge & "', '" & PhotocopyCharge & "');"
DoCmd.RunSQL strSQL

End Sub

now when i click the button to run the code i get Runtime Error 3134 Syntax errer in INTO statement..

i have a feeling it has something to do with the date, but it has me befuddled!

thank you guys for all your patience with me

 

[pbaldy]

Date/time values must be surrounded by # rather than '. They must also be in US format when in code like this, if your format is otherwise.

 

[Minty]

Also Date, Detail, Time, Code are all reserved words in access and will come and bite you. Change them to something more specific e.g. EntryDate, TimeEntered, CodeText etc etc.

http://allenbrowne.com/AppIssueBadWord.html is a list of words to try and avoid. Ditto with spaces and special characters in table and field names.

 

[156sting]

i changed to this from your advice, but still giving me syntax errors

TheChargeDate = Format(Now(), "MM/dd/yyyy")

Dim strSQL As String
strSQL = "INSERT INTO [Charge Data](Date, Code, Number, Prospect, Detail, Time, Photocopy) VALUES (#" & TheChargeDate & "#, '" & ClientToCharge & "', '" & TenementNumber & "', '" & TenementProspect & "', '" & ChargeDescription & "', '" & TimeCharge & "', '" & PhotocopyCharge & "');"
DoCmd.RunSQL strSQL

also this database was not created by me, so the database colum Date has been created by someone else and is used over the entire database... i dont want to change its name just incase it changes something elsewhere

 

[Auntiejack56]

Hey Sting,

This is what I would try next - 2 things:
Put square brackets around all those deeply terrible Field names that you have inherited.

If that doesn't work, before your bad line (that is, the DoCmd.RunSQL), enter Debug.Print strSQL

Run the code again, open the Immediate Window (ctrl-G if you're in a hurry) and you'll see the exact string that the DoCmd is trying to execute. Now
i) copy the string
ii) go back to your DB screen and Create / Query Design
iii) when the table dialog pops up, close it
iv) select SQL
v) paste in your SQL
vi) Run (use the exclamation mark)

It will error again, but the Query Builder will try to highlight your error by putting the cursor at the name that is causing the error. Fixes my probs every time. And I have so many .

Jack

 

[isladogs]

Just to add to the comments in the last post, you may find this utility useful when switching between SQL & VBA or vice versa:

https://www.access-programmers.co.uk/forums/showthread.php?t=293372

This builds on code originally published by Allen Browne

 

[156sting]

so it seems the putting brackets around horrible name worked, however.. there is still some conversion issue... i get this error now

http://imgur.com/a/GghTP

 

[Auntiejack56]

When I look at your SQL string, it has quotes around every fieldname except the date. So would it be correct to say that those other fields are all text? If any field is actually a number, remove the quotes around it.

If no luck there, try my second suggestion. It seems like a lot of trouble cutting and pasting and whatnot, but it's a winner - another week and you'll be swearing by it.
Jack

 

[156sting]

TimeCharge is a Number - the code i use to set variable as Dim TimeCharge As Integer

and PhotocopyCharge is Currency.

i've removed the quote marks from TimeCharge and PhotocopyCharge as well as just tried it alternate ways... how should i be handling currency and Integer data types ?

when i get some time, i am going to run the debug test you suggested... once again thank you all for your help and patience with my noob level

 

[Auntiejack56]

When you create the SQL string, all the values you are inserting into it get converted to strings, so it doesn't matter what data types you've used in your dim statements.

What is important is that the resultant SQL string is valid. And that depends on the data types in the table, not the data types in your VBA.

In other words, regardless of whether you have 'Dim TimeCharge As Integer' or 'Dim TimeCharge as String', strSQL is just a command which is itself a string.
You just have to decide whether it looks like (in part):
a) VALUES ('" & TimeCharge & "')
or
b) VALUES (" & TimeCharge & ")

and that depends solely on whether TimeCharge is a number or text in the table.

For example, say TimeCharge in your table is Currency.

Then:
Dim TimeCharge as String
TimeCharge = "1"
VALUES (" & TimeCharge & ")

or

Dim TimeCharge as Long
TimeCharge = 1
VALUES (" & TimeCharge & ")

or even

Dim TimeCharge as Boolean
TimeCharge = True
VALUES (" & TimeCharge & ")

are all fine. But
VALUES ('" & TimeCharge & "')

will always fail regardless of the value in it because TimeCharge in the table is Currency.

Jack (obviously in chatty mood).

 

[156sting]

Jack,

thank you soo much for all your help.... i would not have been able to fix the issue.

there were a few errors, but the data type conversion error was not in TimeCharge or PhotocopyCharge, it was in Code where i was trying to put a string into a Number so i made a slight modification and some minor changes and it is working how it is supposed to super excited now

final code is below incase anyone else needs to do this type of thing --

Private Sub btnCharge60Day_Click()

Dim TenementNumber As String
Dim TenementProspect As String
Dim ClientToCharge As Integer
Dim TheChargeDate As Date
Dim ChargeDescription As String
Dim TimeCharge As Double
Dim PhotocopyCharge As Currency

ChargeDescription = "60 DAY MAINTENANCE LETTER"
TenementNumber = Me.TheTenement
TenementProspect = Me.TheProspect
ClientToCharge = Me.txtClientNum
TimeCharge = 1.25
PhotocopyCharge = 1
TheChargeDate = Format(Now(), "dd/mm/yy")

Dim strSQL As String
strSQL = "INSERT INTO [Charge Data]([Date], [Code], [Number], [Prospect], [Detail], [Time], [Photocopy]) VALUES ('" & TheChargeDate & "', " & ClientToCharge & ", '" & TenementNumber & "', '" & TenementProspect & "', '" & ChargeDescription & "', " & TimeCharge & ", '" & PhotocopyCharge & "');"
DoCmd.RunSQL strSQL

End Sub