INSERT INTO

[123]

I hope help:

In this code you see word in capital letter this word my problem:

What I want I want to say to INSERT INTO sentences take the field name from
the active form name…

So I do

Dim FORMNAME

FORMNAME = Screen!ActiveForm!Name

And put the formname as field name for INSERT INTO Sentences

But my code not work

Any idea or help

Hope understand me.

-----------------Code start----------------------------------

Dim FORMNAME

FORMNAME = Screen!ActiveForm!Name

Dim cdl As CommonDlg

Set cdl = New CommonDlg

cdl.CancelError = True

cdl.ShowColor

Screen.ActiveForm.Section(0).BackColor = cdl.color

Dim sql As String

sql = "INSERT INTO tblcolor (FORMNAME) VALUES (" &
Screen.ActiveForm.Section(0).BackColor & ");"

CurrentDb.Execute sql, dbFailOnError



-------------code end

 

[MacDermott]

This suggests that you have a table tblcolor with a field for each form in
your application, in which you store the client's choice of back color for
that form.
In that case, your SQL should look like this:
sql = "INSERT INTO tblcolor (" & FORMNAME & ") VALUES (" &
Screen.ActiveForm.Section(0).BackColor & ");"

However, I'd suggest you consider instead a table with 2 fields, one to hold
the form name and one to hold the color value. This design will be much
simpler to use if you ever change the number or names of forms in your
application.

HTH
- Turtle

 

[123]

Thank you for your help and reply:
I have discoverd big problem in my table Design so I want to use your
solution can you give more information again.
what i understand form your notes that the table will be like this

Your Table Desgin MacDermott
---------------------------------
FormName ColorValue
---------------------------------
Startup 015201
Logo 05888
=================================
This what i have understand from you notice but if this true what i say How
to use this code to insert the backcolor value in field colorvalue =
FormName that user select color on..
Again Hope understand me..
sql = "INSERT INTO tblcolor (" & FORMNAME & ") VALUES (" &
Screen.ActiveForm.Section(0).BackColor & ");"

I will tell you more:
My first table consist of one field for each form so saving color to the
table is very easy but when using your table desgin i don't know how to use
this code to save colorvalue=formname
any Idea

 

[MacDermott]

sql="INSERT INTO tblColor (FormName, ColorValue) VALUES (" & _
chr(34) & FORMNAME & chr(34) & "," &
Screen.ActiveForm.Section(0).BackColor & ");"

Please note that in this case you are actually inserting a new record into
the table.
If you want to *change* an existing record (e.g. change the color for an
existing form), you'll need an UPDATE query instead of INSERT INTO.

HTH
- Turtle

 

[123]

MacDermott
Thank you for your help and answer:
You give me big Idea but I can't do this idea using SQL Update
My question is How to convert INSERT INTO in this Code to UPDATE
-----------code start------------
Dim FormName
FormName = Screen.ActiveForm.Form.Name
Dim cdl As CommonDlg
Set cdl = New CommonDlg
cdl.CancelError = True
cdl.ShowColor
Screen.ActiveForm.Section(0).BackColor = cdl.color
Dim sql As String
sql = "INSERT INTO tblcolor (" & FormName & ") VALUES (" &
Screen.ActiveForm.Section(0).BackColor & ");"
docmd.runsql "sql"
----------Code end-----------------------
Thank you again and i'm sorry because I don't know more information I know
only for example
Update tblcolor set color = 10
and i try myself in previous code but not work I change Insert into to
update and put set and change value to equl but not work
for exampl:
sql = "UPDATE tblcolor set (" & FormName & ") = (" &
Screen.ActiveForm.Section(0).BackColor & ");"
but not work
Thank you

 

[MacDermott]

I see a couple of things:
1. The correct syntax is
DoCmd.RunSQL sql
no need for quotation marks around sql.

2. I notice that you are still using your older syntax, suggesting that
you are still using a table with form names for field names. Is this what
you want?

- Turtle

 

[123]

Thank you for your help and answer:
Mr MacDermott I'm sorry for Using old version because If We Use Update
command Here Retrieve the color from the form name is very easy for me is
just use Dlookup because I don't know how Retrieve data using Your solution.
DLookup ("Frmstartup",tblcolor") this exp is very easy for me
So Please Mr MacDermott If You Could Change the Insert into in this code to
UPDATE This is very (kind,
Generous) from You

Thank you again


-----------code start------------
Dim FormName
FormName = Screen.ActiveForm.Form.Name
Dim cdl As CommonDlg
Set cdl = New CommonDlg
cdl.CancelError = True
cdl.ShowColor
Screen.ActiveForm.Section(0).BackColor = cdl.color
Dim sql As String
sql = "INSERT INTO tblcolor (" & FormName & ") VALUES (" &
Screen.ActiveForm.Section(0).BackColor & ");"
docmd.runsql sql
----------Code end-----------------------

 

[MacDermott]

The DLookup syntax is quite easy in the table design I suggested, too:
DLookup("Color","tblColor","FormName='" & FORMNAME & "'")

As I commented in my previous post, I don't think there's a problem in your
UPDATE statement.
I think the problem is in the code you use to execute it.

HTH
- Turtle