Adding value on form to table

[p4nny]

Hi

I would like to insert the value on a form (textbox) to a defined table.

The textbox has an expression within it.

Many thanks

 

[ranman256]

You can set the form to connect directly to the table and enter straight into the field.
if its a calculated field, show this calc in an UNBOUND text box click a button to
save that value straight to the bound text box.
on button click:

txtBoxBoundFld = txtCalculatedField.

 

[p4nny]

Thanks for this.. Apologies, I'm completely new to Access.

Where would I enter this calculation?

text box on form is called currentTime
Field on table is called Timestamp
Form is called Tracker

Button is called Add Record

 

[strive4peace]

You can run an APPEND QUERY to add a record to another table like this:

INSERT INTO Tablename (field1, field2, etc )
SELECT value1, value2, etc

so translating this to your values...

   dim sSQL as string

   SSQL = "INSERT INTO [Tablename] (Timestamp) SELECT #" & me.currentTime & "#"

   currentdb.execute sSQL

   msgbox currentdb.recordsaffected & " Records affected"

This code could go on the CLICK event of a command button ... but seeing what it is, I am wondering if you want it to automatically run?

Also, do you want to specify other values when the TimeStamp record is created?

 

[p4nny]

Many thanks for the reply... On this occasion I would a click event command.. I've tried your code on another field but I keep getting a Syntax error.

So i would to add the field name "tbfullname" from the form called "Tracker"... to the "LC1_tbl" in the "L1 Reviewer" field

Private Sub cmd_addRec_Click()
Dim sSQL As String
sSQL = "INSERT INTO [lc1_tbl] (L1 Reviewer) SELECT #" & Me.tbFullName & "#"
CurrentDb.Execute sSQL
End Sub

much appreciated

 

[strive4peace]

you're welcome

# is the delimiter for dates. For text, you have to use " or '

If you use " inside a string that uses " as a delimiter, you need 2 of them

sSQL = "INSERT INTO [lc1_tbl] (L1 Reviewer) SELECT """ & Me.tbFullName & """"

 

[p4nny]

Hi

I'm getting the syntax error.

the error highlights " CurrentDb.Execute sSQL "

I've checked the name of the table and field and all is correct.

This is my code:

Private Sub cmd_addRec_Click()

Dim sSQL As String
sSQL = "INSERT INTO [lc1_tbl] (L1 Reviewer) SELECT """ & Me.tbFullName & """"
CurrentDb.Execute sSQL

End Sub

Many thanks again

 

[strive4peace]

When you construct SQL in code, it is a good idea to include:

debug.print sSQL

until the program is working -- then you can delete this or comment it

** debug.print ***

debug.print sSQL

--> this prints a copy of the SQL statement to the debug window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)
(or right-click on a blank area in the query design and choose --> SQL View)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL