insert into using command button (1 Viewer)

J

jerry28ph

jerry
Local time
Today, 02:45
Joined
Nov 16, 2008
Messages
141
Hi All,

I trying to run a query using command button on my main form, but it seems i have an error in writing this code. I want to save the two values from my sub forms, and a value supplied by user from my main form.


Private Sub Command351_Click()
Dim strSQL As String

'build the string including the field names as required
strSQL = "INSERT INTO tab_Summary (cs_datefrom, in_int_ext, in_ext_caller)"

VALUES ("Me.[text295]",
"Me.[Smdr Qry_Total_Direct subform1].Form![text2]",
"Me.[Smdr Qry_Total_Queue subform].Form![text2]")"

'execute the SQL string to insert the values
DoCmd.RunSQL strSQL
End Sub

Please if you an idea, what's wrong with this script, im asking your help guys.

Thanks and more power.
 
A

Alc

Registered User.
Local time
Today, 05:45
Joined
Mar 23, 2007
Messages
2,407
Would this work?
Code: 
Private Sub Command351_Click()
Dim strSQL As String
 
strSQL = "INSERT INTO tab_Summary (cs_datefrom, in_int_ext, in_ext_caller) " & _
[COLOR=black]       "VALUES ('" & Me.[text295] & "', " & _[/COLOR]
[COLOR=black]       "'" & Me.[Smdr Qry_Total_Direct subform1].Form![text2] & "', " & _[/COLOR]
[COLOR=black]       "'" & Me.[Smdr Qry_Total_Queue subform].Form![text2] & "');"[/COLOR]
 
DoCmd.RunSQL strSQL
End Sub
 
J

jerry28ph

jerry
Local time
Today, 02:45
Joined
Nov 16, 2008
Messages
141
hi ALC,

i ran the my form and clicked the button with this script, and here' the error msg:

Runtime error: 2465

Microsoft Office Access can't find the field "|" referred in your expression.
I set the data type for all 3 fields as Text.

Please advise.
Thanks.




Alc said:
Would this work?
Code: 
Private Sub Command351_Click()
Dim strSQL As String
 
strSQL = "INSERT INTO tab_Summary (cs_datefrom, in_int_ext, in_ext_caller) " & _
[COLOR=black]      "VALUES ('" & Me.[text295] & "', " & _[/COLOR]
[COLOR=black]      "'" & Me.[Smdr Qry_Total_Direct subform1].Form![text2] & "', " & _[/COLOR]
[COLOR=black]      "'" & Me.[Smdr Qry_Total_Queue subform].Form![text2] & "');"[/COLOR]
 
DoCmd.RunSQL strSQL
End Sub
Click to expand...
 
A

Alc

Registered User.
Local time
Today, 05:45
Joined
Mar 23, 2007
Messages
2,407
Try adding the following, to work out where the problem lies.
Code: 
'If one of these errors, the problem is in setting the value(s)
Msgbox Me.[text295]
Msgbox Me.[Smdr Qry_Total_Direct subform1].Form![text2]
Msgbox Me.[Smdr Qry_Total_Queue subform].Form![text2]
 
strSQL = "INSERT INTO tab_Summary (cs_datefrom, in_int_ext, in_ext_caller) " & _
[COLOR=black]      "VALUES ('" & Me.[text295] & "', " & _[/COLOR]
[COLOR=black]      "'" & Me.[Smdr Qry_Total_Direct subform1].Form![text2] & "', " & _[/COLOR]
[COLOR=black]      "'" & Me.[Smdr Qry_Total_Queue subform].Form![text2] & "');"[/COLOR]
'Check if the sytax looks correct when this is displayed
Msgbox strSQL
 
J

jerry28ph

jerry
Local time
Today, 02:45
Joined
Nov 16, 2008
Messages
141
Hi Alc,

Thank you so much it works now. You're such a nice person more power to you.

If you dont mind, I have 2 last question for you. Im sure you know this one.

1. The script that you gave me is how to append new record, how about if you jus want to update some fields of a record, I mean i dont want to append record, all i need is to search the current record in my table, and updates some fields that I calculated from my subforms and main forms, lets say the same fields that we appended. can I use UPDATE, or is there any additional script i have to code? I know it's too much, and would say you're one of the genius on this forum.

2. on my main form, I have 2 subforms and everytime I ran this script, a window for my query results (in details) appears on the screen. Is there any way how to display the value of my subform, instead of showing another window for the detailed value of my query?

heres the code:

Private Sub Command234_Click()
On Error GoTo Err_RnRfrshQry_Click
Dim stDocName As String
stDocName = "Smdr Qry_Total_Direct"
DoCmd.SetWarnings False

DoCmd.OpenQuery stDocName, acNormal, acReadOnly
[Smdr Qry_Total_Direct subform1].Form.Requery

DoCmd.SetWarnings True
Exit_RnRfrshQry_Click:
Exit Sub
Err_RnRfrshQry_Click:
MsgBox Err.Description
Resume Exit_RnRfrshQry_Click

End Sub



Thanks, you're really the BEST.

Regards,
Jerry



Alc said:
Try adding the following, to work out where the problem lies.
Code: 
'If one of these errors, the problem is in setting the value(s)
Msgbox Me.[text295]
Msgbox Me.[Smdr Qry_Total_Direct subform1].Form![text2]
Msgbox Me.[Smdr Qry_Total_Queue subform].Form![text2]
 
strSQL = "INSERT INTO tab_Summary (cs_datefrom, in_int_ext, in_ext_caller) " & _
[COLOR=black]     "VALUES ('" & Me.[text295] & "', " & _[/COLOR]
[COLOR=black]     "'" & Me.[Smdr Qry_Total_Direct subform1].Form![text2] & "', " & _[/COLOR]
[COLOR=black]     "'" & Me.[Smdr Qry_Total_Queue subform].Form![text2] & "');"[/COLOR]
'Check if the sytax looks correct when this is displayed
Msgbox strSQL
Click to expand...
 
A

Alc

Registered User.
Local time
Today, 05:45
Joined
Mar 23, 2007
Messages
2,407
jerry28ph said:
1. The script that you gave me is how to append new record, how about if you jus want to update some fields of a record, I mean i dont want to append record, all i need is to search the current record in my table, and updates some fields that I calculated from my subforms and main forms, lets say the same fields that we appended. can I use UPDATE, or is there any additional script i have to code?
Click to expand...
Yep, you can use Update, Delete, Insert or whatever.
I usually build the query in the Queries window then copy and paste it into code, adding the quotation marks as I go.

you're one of the genius on this forum.
Click to expand...
If only :D. Case in point being I'm not sure what you're describing here?
on my main form, I have 2 subforms and everytime I ran this script, a window for my query results (in details) appears on the screen. Is there any way how to display the value of my subform, instead of showing another window for the detailed value of my query?
Click to expand...
I'm probably just being dense, but what exactly are you seeing and what exactly do you want to see?
 
J

jerry28ph

jerry
Local time
Today, 02:45
Joined
Nov 16, 2008
Messages
141
hi,

for my question #2
these 2 subforms are part of my main form, i put command button on each and the content is:

On Error GoTo Err_RnRfrshQry_Click
Dim stDocName As String
stDocName = "Smdr Qry_Total_Direct"
DoCmd.SetWarnings False

DoCmd.OpenQuery stDocName, acNormal, acReadOnly
[Smdr Qry_Total_Direct subform1].Form.Requery

DoCmd.SetWarnings True
Exit_RnRfrshQry_Click:
Exit Sub
Err_RnRfrshQry_Click:
MsgBox Err.Description
Resume Exit_RnRfrshQry_Click


i have to run this script to get the value of my subform, and i want to display the value of this subform into my text box located on my main form too. But everytime I click the button, a new window will appear showing query results of my subform, so i have to put DoCmd.Close in order to close that window and go back to main form. Is there any way not to show this window, and just show the value of subform into text box?

Again, thanks for your time and help. I really appreciated.

Regards,
Jerry


Alc said:
Yep, you can use Update, Delete, Insert or whatever.
I usually build the query in the Queries window then copy and paste it into code, adding the quotation marks as I go.


If only :D. Case in point being I'm not sure what you're describing here?

I'm probably just being dense, but what exactly are you seeing and what exactly do you want to see?
Click to expand...
 
A

Alc

Registered User.
Local time
Today, 05:45
Joined
Mar 23, 2007
Messages
2,407
I'm sure there is.
What's the name of the text box into which you want to put the value?
 
J

jerry28ph

jerry
Local time
Today, 02:45
Joined
Nov 16, 2008
Messages
141
the name of the Text box is Text235 and to be able to get the value of my subform and put it in Text235,
I set the controlSource for Text235=[Smdr Qry_Total_Direct subform1].Form!Text2.





Alc said:
I'm sure there is.
What's the name of the text box into which you want to put the value?
Click to expand...
 
A

Alc

Registered User.
Local time
Today, 05:45
Joined
Mar 23, 2007
Messages
2,407
I think refreshing the text box should get the data to be displayed, given that you've already set the source.
Code: 
[SIZE=1]On Error GoTo Err_RnRfrshQry_Click
Dim stDocName As String
stDocName = "Smdr Qry_Total_Direct"
DoCmd.SetWarnings False

DoCmd.OpenQuery stDocName, acNormal, acReadOnly
[Smdr Qry_Total_Direct subform1].Form.Requery
[/SIZE]
[SIZE=1][B][I][Smdr Qry_Total_Direct subform1].Form.Refresh[/I][/B][/SIZE]
[SIZE=1]
DoCmd.SetWarnings True
Exit_RnRfrshQry_Click:
Exit Sub
Err_RnRfrshQry_Click:
MsgBox Err.Description
Resume Exit_RnRfrshQry_Click[/SIZE]
If that doesn't work, try this as well
Code: 
[SIZE=1]On Error GoTo Err_RnRfrshQry_Click
Dim stDocName As String
stDocName = "Smdr Qry_Total_Direct"
DoCmd.SetWarnings False

DoCmd.OpenQuery stDocName, acNormal, acReadOnly
[Smdr Qry_Total_Direct subform1].Form.Requery[/SIZE]
[SIZE=1][/SIZE] 
[SIZE=1][B][I]Text235=[Smdr Qry_Total_Direct subform1].Form!Text2[/I][/B]
[/SIZE][SIZE=1][B][I][Smdr Qry_Total_Direct subform1].Form.Refresh[/I][/B][/SIZE]
[SIZE=1]
DoCmd.SetWarnings True
Exit_RnRfrshQry_Click:
Exit Sub
Err_RnRfrshQry_Click:
MsgBox Err.Description
Resume Exit_RnRfrshQry_Click[/SIZE]
If you just want to run the query to refresh the data but not actually display the results to you, try
Code: 
[SIZE=1]DoCmd.OpenQuery stDocName[/SIZE]
Instead of
Code: 
[SIZE=1]DoCmd.OpenQuery stDocName, acNormal, acReadOnly[/SIZE]
I use that syntax on various queries and never see the data that it finds.
 
J

jerry28ph

jerry
Local time
Today, 02:45
Joined
Nov 16, 2008
Messages
141
i tried all the possibilities that you given, but still the detailed result of my query displays (should I say data grid) it looks like spreadsheet. can we set it on properties?

Thanks for your patience.

regards,
Jerry




Alc said:
I think refreshing the text box should get the data to be displayed, given that you've already set the source.
Code: 
[SIZE=1]On Error GoTo Err_RnRfrshQry_Click[/SIZE]
[SIZE=1]Dim stDocName As String[/SIZE]
[SIZE=1]stDocName = "Smdr Qry_Total_Direct"[/SIZE]
[SIZE=1]DoCmd.SetWarnings False[/SIZE]
 
[SIZE=1]DoCmd.OpenQuery stDocName, acNormal, acReadOnly[/SIZE]
[SIZE=1][Smdr Qry_Total_Direct subform1].Form.Requery[/SIZE]
 
[SIZE=1][B][I][Smdr Qry_Total_Direct subform1].Form.Refresh[/I][/B][/SIZE]
 
[SIZE=1]DoCmd.SetWarnings True[/SIZE]
[SIZE=1]Exit_RnRfrshQry_Click:[/SIZE]
[SIZE=1]Exit Sub[/SIZE]
[SIZE=1]Err_RnRfrshQry_Click:[/SIZE]
[SIZE=1]MsgBox Err.Description[/SIZE]
[SIZE=1]Resume Exit_RnRfrshQry_Click[/SIZE]
If that doesn't work, try this as well
Code: 
[SIZE=1]On Error GoTo Err_RnRfrshQry_Click[/SIZE]
[SIZE=1]Dim stDocName As String[/SIZE]
[SIZE=1]stDocName = "Smdr Qry_Total_Direct"[/SIZE]
[SIZE=1]DoCmd.SetWarnings False[/SIZE]
 
[SIZE=1]DoCmd.OpenQuery stDocName, acNormal, acReadOnly[/SIZE]
[SIZE=1][Smdr Qry_Total_Direct subform1].Form.Requery[/SIZE]
 
[SIZE=1][B][I]Text235=[Smdr Qry_Total_Direct subform1].Form!Text2[/I][/B][/SIZE]
[SIZE=1][B][I][Smdr Qry_Total_Direct subform1].Form.Refresh[/I][/B][/SIZE]
 
[SIZE=1]DoCmd.SetWarnings True[/SIZE]
[SIZE=1]Exit_RnRfrshQry_Click:[/SIZE]
[SIZE=1]Exit Sub[/SIZE]
[SIZE=1]Err_RnRfrshQry_Click:[/SIZE]
[SIZE=1]MsgBox Err.Description[/SIZE]
[SIZE=1]Resume Exit_RnRfrshQry_Click[/SIZE]
If you just want to run the query to refresh the data but not actually display the results to you, try
Code: 
[SIZE=1]DoCmd.OpenQuery stDocName[/SIZE]
Instead of
Code: 
[SIZE=1]DoCmd.OpenQuery stDocName, acNormal, acReadOnly[/SIZE]
I use that syntax on various queries and never see the data that it finds.
Click to expand...
 
A

Alc

Registered User.
Local time
Today, 05:45
Joined
Mar 23, 2007
Messages
2,407
What's the query? Could you posy the SQL behind it?

Also, just to warn you, I'm off work in about twenty-five minutes and have to fly back to the UKfor a funeral tomorrow. I won't be around until next Tuesday, so if we don't get this sorted before I leave, hopefully somebody else will be able to help you.
 
J

jerry28ph

jerry
Local time
Today, 02:45
Joined
Nov 16, 2008
Messages
141
ok here:

Smrdr Qry_Total_Direct - name of query

SELECT [Smdr(backup1-6-2009)].Party1Name, [Smdr(backup1-6-2009)].Dialled_number, [Smdr(backup1-6-2009)].Direction, [Smdr(backup1-6-2009)].[Call duration]
FROM [Smdr(backup1-6-2009)]
WHERE ((([Smdr(backup1-6-2009)].Party1Name)=[forms]![FrmLog]![combo17]) AND (([Smdr(backup1-6-2009)].Dialled_number)=[forms]![FrmLog]![text135]) AND (([Smdr(backup1-6-2009)].Direction)=[forms]![FrmLog]![text30]) AND (([Smdr(backup1-6-2009)].[Call duration])<>#12/30/1899#));




Alc said:
What's the query? Could you posy the SQL behind it?

Also, just to warn you, I'm off work in about twenty-five minutes and have to fly back to the UKfor a funeral tomorrow. I won't be around until next Tuesday, so if we don't get this sorted before I leave, hopefully somebody else will be able to help you.
Click to expand...
 
A

Alc

Registered User.
Local time
Today, 05:45
Joined
Mar 23, 2007
Messages
2,407
Okay, which one is the value you want in the text box?
 
J

jerry28ph

jerry
Local time
Today, 02:45
Joined
Nov 16, 2008
Messages
141
it should be the total no. of records that filtered on this query based on the criteria. That why I created a subform/subreport on my main form, and put the a calculated control under form footer which is Text2=count(*).Can we just get the count of records on my subform and put it on Text235 on main forms without displaying the data grid for the query?


Alc said:
Okay, which one is the value you want in the text box?
Click to expand...
 
A

Alc

Registered User.
Local time
Today, 05:45
Joined
Mar 23, 2007
Messages
2,407
Try setting the data property for the text box to be
=Forms![Smdr Qry_Total_Direct subform1].[name of subform].Form.RecordSet.RecordCount
That should give you the record number.

I'm stumped on the displying of the query, I'm afraid. :confused:

Have to run, so I hope you get it all sorted and sorry I couldn't finish it off for you.
 
You must log in or register to reply here.

Similar threads

mike60smart
Insert
Replies
3
Views
319
Pat Hartman
P
C
Insert into syntax error
Replies
8
Views
394
ClaraBarton
C
M
insert into code
Replies
2
Views
196
mhakim
M
A
Solved Problems inserting records into a table using SQL : maybe an error in my concatenation ?
Replies
5
Views
251
alan2013
A
A
Inserting numeric values into a Table, using SQL : What's the correct VBA code to use in the VALUES part of the code ?
Replies
10
Views
404
ebs17
E

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom