Text boxs and tables

[echo0001]

Hello

I need the code for taking the value entred into a text box and then adding it to a new record in a table. this will happen on the OnClick event of a command button.

The text box is named "Text40"
The field the value will be added to is named "Allocated To"
The Table the field is located in is named "Allocated To"

Thank you for your time.

 

[nIGHTmAYOR]

in on click event to your button add :

If Not IsNull(Me![Text40]) Then
Dim stSQL
stSQL = "insert into [Allocated To] ([Allocated To].[Allocated To])" &_
" Values (" & Me![Text40] & ")"
DoCmd.RunSQL stSQL
End If

 

[echo0001]

Its coming back with complie error invalid character then its highlighting _

any help.

 

[maxmangion]

if on the OnClick event you are adding this value via another form try searching this forum for OpenArgs

 

[boblarson]

Why not just bind the table to the form and set the control source of the text box to the field? Then, no code necessary, unless you want to validate.

 

[echo0001]

now when i click the button it brings up an error and asks me to look at the DoCmd.RunSQL stSQL part of the code and debug it

 

[boblarson]

Just a thought - it isn't good to have the table name the same as the field name (Allocated To). It might be wise to rename one of them slightly so that there is a difference.

 

[echo0001]

table allocated to has been renamed to Allocated

 

[boblarson]

table allocated to has been renamed to Allocated

Sorry, but in Access World that is the same thing (there is no Case Sensitivity with object names)

 

[echo0001]

yeah i know that i have changed it from "Allocated To" to "Allocated"

 

[echo0001]

are there any references i have to add

 

[boblarson]

are there any references i have to add

No, but if your text box is text (not numeric or date then it would need to have quotes):

stSQL = "insert into [Allocated] ([Allocated].[Allocated To])" & _
" Values ('" & Me![Text40] & "')"

 

[nIGHTmAYOR]

If Not IsNull(Me![Text40]) Then
Dim stSQL
stSQL = "insert into [Allocated To] ([Allocated To].[Allocated To])" & _
" Values ('" & Me![Text40] & "')"
DoCmd.RunSQL stSQL
End If

this one is sure to run

 

[nIGHTmAYOR]

You were faster on this one Bob

 

[echo0001]

now i need the the opposite, to be able to remove a record using the same method.
any help?

 

[nIGHTmAYOR]

If Not IsNull(Me![Text40]) Then
Dim stSQL
stSQL = "delete from [Allocated To] where [Allocated To].[Allocated To]=" & _
"'" & Me![Text40] & "'"
DoCmd.RunSQL stSQL
End If

I recommend that you read a little about sql if database programing is of an interest to you.

 

[echo0001]

dont worry, i will. i have managed to get it to work using another method.