Append data from a form to a table (textbox problem)

[Guest]

Hi,

I faced another problem which I can't solve on my own.
I got a table and a form. I want to use the form to append data to the table.
So I made a few textboxes etc at the form and a button with this code:

Private Sub Command32_Click()
On Error GoTo Err_Command32_Click
DoCmd.RunSQL "INSERT INTO tblSpecials ([PcodeId], [ContractId], [CalcCosts],
[CalcSales], [RelCosts], [RelSales], [Chance], [Results]) VALUES (" & Combo2
& " , " & ContractId & " , " & tbCalculatedCosts & " , " & tbCalculatedSales
& " , " & tbRealizedCosts & " , " & tbRealizedSales & " , " & tbChance & " ,
" & tbResults & ")"
Exit_Command32_Click:
Exit Sub
Err_Command32_Click:
MsgBox Err.Description
Resume Exit_Command32_Click
End Sub

This worked out fine for the Number values (the columns that have a "number
data type" in the table). All data is nicely appended.
Q1) Although for text fields it doesn't work. When I want to append
text-fields I receive a pop up screen where I can prompt the text in. When I
do so it does work, but ofcourse this isn't really a solution to the problem.

Q2) When I don't enter a value in one of the fields (text or number) I
receive an error message "syntax error in insert into statement". While the
columns in the table are NOT required (or key columns).

Very strange to me:S
Hopefully someone can help me with this!
I would be very gratefull,

- Onne

 

[Guest]

The text fields need to be enclosed in quotes. Since I don't know which are
text fields, I will use ContractId, Chance, and Results as examples of how to
handle the text fields:

DoCmd.RunSQL "INSERT INTO tblSpecials ([PcodeId], [ContractId], [CalcCosts],
[CalcSales], [RelCosts], [RelSales], [Chance], [Results]) VALUES (" & Combo2
& " , '" & ContractId & "' , " & tbCalculatedCosts & " , " &
tbCalculatedSales
& " , " & tbRealizedCosts & " , " & tbRealizedSales & " , '" & tbChance & "'
,
'" & tbResults & "')"

Also, the Execute method is much faster than RunSQL because it goes directly
to Jet:

CurrentDb.Execute("INSERT INTO tblSpecials ([PcodeId], [ContractId],
[CalcCosts],
[CalcSales], [RelCosts], [RelSales], [Chance], [Results]) VALUES (" & Combo2
& " , '" & ContractId & "' , " & tbCalculatedCosts & " , " &
tbCalculatedSales
& " , " & tbRealizedCosts & " , " & tbRealizedSales & " , '" & tbChance & "'
,
'" & tbResults & "')"), dbFailOnError

And last, why are you not just using a bound form?

 

[Guest]

Hi Klatuu,

thanks a lot for your help!
I've inserted the quotes, that solved the problem.
Besides that, I took over your "CurrentDb.Execute-idea" that worked fine as
well (besides that it's probably faster it also took away the question "are
you sure to append.." I'm happy about that too)
My second question (about the syntax error) is solved by setting the default
value of numberic fields to zero.

I'm really a beginner to access, so I don't know anything about bound forms
(except for the name) that's why I didn't use one of those

Thanks again, greetings Onne

The text fields need to be enclosed in quotes. Since I don't know which are
text fields, I will use ContractId, Chance, and Results as examples of how to
handle the text fields:

DoCmd.RunSQL "INSERT INTO tblSpecials ([PcodeId], [ContractId], [CalcCosts],
[CalcSales], [RelCosts], [RelSales], [Chance], [Results]) VALUES (" & Combo2
& " , '" & ContractId & "' , " & tbCalculatedCosts & " , " &
tbCalculatedSales
& " , " & tbRealizedCosts & " , " & tbRealizedSales & " , '" & tbChance & "'
,
'" & tbResults & "')"

Also, the Execute method is much faster than RunSQL because it goes directly
to Jet:

CurrentDb.Execute("INSERT INTO tblSpecials ([PcodeId], [ContractId],
[CalcCosts],
[CalcSales], [RelCosts], [RelSales], [Chance], [Results]) VALUES (" & Combo2
& " , '" & ContractId & "' , " & tbCalculatedCosts & " , " &
tbCalculatedSales
& " , " & tbRealizedCosts & " , " & tbRealizedSales & " , '" & tbChance & "'
,
'" & tbResults & "')"), dbFailOnError

And last, why are you not just using a bound form?

Hi,

I faced another problem which I can't solve on my own.
I got a table and a form. I want to use the form to append data to the table.
So I made a few textboxes etc at the form and a button with this code:

Private Sub Command32_Click()
On Error GoTo Err_Command32_Click
DoCmd.RunSQL "INSERT INTO tblSpecials ([PcodeId], [ContractId], [CalcCosts],
[CalcSales], [RelCosts], [RelSales], [Chance], [Results]) VALUES (" & Combo2
& " , " & ContractId & " , " & tbCalculatedCosts & " , " & tbCalculatedSales
& " , " & tbRealizedCosts & " , " & tbRealizedSales & " , " & tbChance & " ,
" & tbResults & ")"
Exit_Command32_Click:
Exit Sub
Err_Command32_Click:
MsgBox Err.Description
Resume Exit_Command32_Click
End Sub

This worked out fine for the Number values (the columns that have a "number
data type" in the table). All data is nicely appended.
Q1) Although for text fields it doesn't work. When I want to append
text-fields I receive a pop up screen where I can prompt the text in. When I
do so it does work, but ofcourse this isn't really a solution to the problem.

Q2) When I don't enter a value in one of the fields (text or number) I
receive an error message "syntax error in insert into statement". While the
columns in the table are NOT required (or key columns).

Very strange to me:S
Hopefully someone can help me with this!
I would be very gratefull,

- Onne

 

[Guest]

Sorry, I forgot to answer the second question.
The default value, unless otherwise specified, for any text box is Null. To
avoid that in your situation, you can use the Nz function with converts a
Null or Zero value to some other value. For example, in the VALUES portion
of your SQL, you could do this:
& Nz(tbCalculatedCosts,0) &

So any time tbCalculatedCosts is not filled in, it will put 0 in the field.

A bound form in the natural way to do data entry in Access. For starters,
use the Form Wizard to create a form. It will ask you what table or query
you want to base your form on, then you will get a list of fields in the
table, and you can drag each on to the form. One of the advantages of this
approach is that you don't have to worry about use SQL to update your table.
That will happen automatically.

Since you already have a form built, you could go into design mode. click
on properties and select the form. Select the data tab and you can select
the table from there. Then, select each field, the data tab, and the Control
Source property will be where you put the name of the field.

Hi Klatuu,

thanks a lot for your help!
I've inserted the quotes, that solved the problem.
Besides that, I took over your "CurrentDb.Execute-idea" that worked fine as
well (besides that it's probably faster it also took away the question "are
you sure to append.." I'm happy about that too)
My second question (about the syntax error) is solved by setting the default
value of numberic fields to zero.

I'm really a beginner to access, so I don't know anything about bound forms
(except for the name) that's why I didn't use one of those

Thanks again, greetings Onne

The text fields need to be enclosed in quotes. Since I don't know which are
text fields, I will use ContractId, Chance, and Results as examples of how to
handle the text fields:

DoCmd.RunSQL "INSERT INTO tblSpecials ([PcodeId], [ContractId], [CalcCosts],
[CalcSales], [RelCosts], [RelSales], [Chance], [Results]) VALUES (" & Combo2
& " , '" & ContractId & "' , " & tbCalculatedCosts & " , " &
tbCalculatedSales
& " , " & tbRealizedCosts & " , " & tbRealizedSales & " , '" & tbChance & "'
,
'" & tbResults & "')"

Also, the Execute method is much faster than RunSQL because it goes directly
to Jet:

CurrentDb.Execute("INSERT INTO tblSpecials ([PcodeId], [ContractId],
[CalcCosts],
[CalcSales], [RelCosts], [RelSales], [Chance], [Results]) VALUES (" & Combo2
& " , '" & ContractId & "' , " & tbCalculatedCosts & " , " &
tbCalculatedSales
& " , " & tbRealizedCosts & " , " & tbRealizedSales & " , '" & tbChance & "'
,
'" & tbResults & "')"), dbFailOnError

And last, why are you not just using a bound form?

Hi,

I faced another problem which I can't solve on my own.
I got a table and a form. I want to use the form to append data to the table.
So I made a few textboxes etc at the form and a button with this code:

Private Sub Command32_Click()
On Error GoTo Err_Command32_Click
DoCmd.RunSQL "INSERT INTO tblSpecials ([PcodeId], [ContractId], [CalcCosts],
[CalcSales], [RelCosts], [RelSales], [Chance], [Results]) VALUES (" & Combo2
& " , " & ContractId & " , " & tbCalculatedCosts & " , " & tbCalculatedSales
& " , " & tbRealizedCosts & " , " & tbRealizedSales & " , " & tbChance & " ,
" & tbResults & ")"
Exit_Command32_Click:
Exit Sub
Err_Command32_Click:
MsgBox Err.Description
Resume Exit_Command32_Click
End Sub

This worked out fine for the Number values (the columns that have a "number
data type" in the table). All data is nicely appended.
Q1) Although for text fields it doesn't work. When I want to append
text-fields I receive a pop up screen where I can prompt the text in. When I
do so it does work, but ofcourse this isn't really a solution to the problem.

Q2) When I don't enter a value in one of the fields (text or number) I
receive an error message "syntax error in insert into statement". While the
columns in the table are NOT required (or key columns).

Very strange to me:S
Hopefully someone can help me with this!
I would be very gratefull,

- Onne

 

[Guest]

Thanks again.
I already knew the Nz funtion but didn't think about it to use it here
again. This solution is much more beautifull for the user then setting the
default field-value to zero.

About the bound form, I have tried that in the first place. But because I
had to do some other calculations and cross references between combo boxes
and tables (don't know how to explain it more clearly) I decided that it was
easier to build it up myself from scratch.

You seem to know a lot about access/vba functionalities, maybe you could
give me a hint solving a different problem I faced with access

http://support.microsoft.com/newsgr...-8b9cf33299a5&dglist=&ptlist=&exp=&sloc=en-us

(if the link doesn't work use "nok ok chart colour" as search query)

I have no idea if you have the time and knowledge to look at it, and if
there is a solution to my problem in the first place, but I could give it a
shot!

Greetings Onne

Sorry, I forgot to answer the second question.
The default value, unless otherwise specified, for any text box is Null. To
avoid that in your situation, you can use the Nz function with converts a
Null or Zero value to some other value. For example, in the VALUES portion
of your SQL, you could do this:
& Nz(tbCalculatedCosts,0) &

So any time tbCalculatedCosts is not filled in, it will put 0 in the field.

A bound form in the natural way to do data entry in Access. For starters,
use the Form Wizard to create a form. It will ask you what table or query
you want to base your form on, then you will get a list of fields in the
table, and you can drag each on to the form. One of the advantages of this
approach is that you don't have to worry about use SQL to update your table.
That will happen automatically.

Since you already have a form built, you could go into design mode. click
on properties and select the form. Select the data tab and you can select
the table from there. Then, select each field, the data tab, and the Control
Source property will be where you put the name of the field.

Hi Klatuu,

thanks a lot for your help!
I've inserted the quotes, that solved the problem.
Besides that, I took over your "CurrentDb.Execute-idea" that worked fine as
well (besides that it's probably faster it also took away the question "are
you sure to append.." I'm happy about that too)
My second question (about the syntax error) is solved by setting the default
value of numberic fields to zero.

I'm really a beginner to access, so I don't know anything about bound forms
(except for the name) that's why I didn't use one of those

Thanks again, greetings Onne

The text fields need to be enclosed in quotes. Since I don't know which are
text fields, I will use ContractId, Chance, and Results as examples of how to
handle the text fields:

DoCmd.RunSQL "INSERT INTO tblSpecials ([PcodeId], [ContractId], [CalcCosts],
[CalcSales], [RelCosts], [RelSales], [Chance], [Results]) VALUES (" & Combo2
& " , '" & ContractId & "' , " & tbCalculatedCosts & " , " &
tbCalculatedSales
& " , " & tbRealizedCosts & " , " & tbRealizedSales & " , '" & tbChance & "'
,
'" & tbResults & "')"

Also, the Execute method is much faster than RunSQL because it goes directly
to Jet:

CurrentDb.Execute("INSERT INTO tblSpecials ([PcodeId], [ContractId],
[CalcCosts],
[CalcSales], [RelCosts], [RelSales], [Chance], [Results]) VALUES (" & Combo2
& " , '" & ContractId & "' , " & tbCalculatedCosts & " , " &
tbCalculatedSales
& " , " & tbRealizedCosts & " , " & tbRealizedSales & " , '" & tbChance & "'
,
'" & tbResults & "')"), dbFailOnError

And last, why are you not just using a bound form?


:

Hi,

I faced another problem which I can't solve on my own.
I got a table and a form. I want to use the form to append data to the table.
So I made a few textboxes etc at the form and a button with this code:

Private Sub Command32_Click()
On Error GoTo Err_Command32_Click
DoCmd.RunSQL "INSERT INTO tblSpecials ([PcodeId], [ContractId], [CalcCosts],
[CalcSales], [RelCosts], [RelSales], [Chance], [Results]) VALUES (" & Combo2
& " , " & ContractId & " , " & tbCalculatedCosts & " , " & tbCalculatedSales
& " , " & tbRealizedCosts & " , " & tbRealizedSales & " , " & tbChance & " ,
" & tbResults & ")"
Exit_Command32_Click:
Exit Sub
Err_Command32_Click:
MsgBox Err.Description
Resume Exit_Command32_Click
End Sub

This worked out fine for the Number values (the columns that have a "number
data type" in the table). All data is nicely appended.
Q1) Although for text fields it doesn't work. When I want to append
text-fields I receive a pop up screen where I can prompt the text in. When I
do so it does work, but ofcourse this isn't really a solution to the problem.

Q2) When I don't enter a value in one of the fields (text or number) I
receive an error message "syntax error in insert into statement". While the
columns in the table are NOT required (or key columns).

Very strange to me:S
Hopefully someone can help me with this!
I would be very gratefull,

- Onne