how to bring value of query to table?

[jerry28ph]

Hi all,

I'd like to ask how to bring the value of query into table. I have 2 subforms on my main form and I would like to update 2 fields value calculated from my main form. can you please send me the syntax or sample how to save or update my table with my query value.
Please help.

Regards,
JE

 

[CameronM]

Firstly, have a look at the information on how to refer to subforms, check out this recent post http://www.access-programmers.co.uk/forums/showthread.php?t=163782

From your post it is not clear where the value for the field on the main form is coming from. I see two possible options.

Option 1:
If the field on your main form is calculated from the query, as your posting suggests, then why can't you modify the queries under your subform to include that field also?

If you are unable to modify the query, then you would place the code below in the Form_Current event, which would load the value into the subform everytime the form is opened.

Option2:
If the value is entered by the user in the main form or you already have the field you require on the main form, you should be able use VBA such as Me.[SubFormName].Form![ControlOnSubForm] = Me.[ControlOnMainForm]

For example, if you had a field on the main form where the user entered a refund amount, you could could copy this to the subform using the OnClick event of a button, or the AfterUpdate event of the field.

Private Sub Command5_Click()
Me.frmTransactions.Form!txtRefundAmount = Me.txtRefundAmount
End Sub

 

[jerry28ph]

hi CameronM,

main problem: see my title
it goes: i have my main form [frmLogs], 2 subforms [qryTotalDirect subform] and [qryTotalQueue subform] i used =count(*) on each subform, and i could see the count of each subform on my main form. I have a tbl_operator, and i want to update 2 fields to my tbl_operator from 2 subforms. Using command button, how could i write a script or query that will execute to automatically add the value of my 2 subforms and other field supplied by the user on my mainform to tbl_operators.
I hope you understand my problem.

for better understanding, if you dont mind, i can attach my db and send it to you.

Thank you and more power.
Regards,
Jerry

Firstly, have a look at the information on how to refer to subforms, check out this recent post http://www.access-programmers.co.uk/forums/showthread.php?t=163782

From your post it is not clear where the value for the field on the main form is coming from. I see two possible options.

Option 1:
If the field on your main form is calculated from the query, as your posting suggests, then why can't you modify the queries under your subform to include that field also?

If you are unable to modify the query, then you would place the code below in the Form_Current event, which would load the value into the subform everytime the form is opened.

Option2:
If the value is entered by the user in the main form or you already have the field you require on the main form, you should be able use VBA such as Me.[SubFormName].Form![ControlOnSubForm] = Me.[ControlOnMainForm]

For example, if you had a field on the main form where the user entered a refund amount, you could could copy this to the subform using the OnClick event of a button, or the AfterUpdate event of the field.

Private Sub Command5_Click()
Me.frmTransactions.Form!txtRefundAmount = Me.txtRefundAmount
End Sub

 

[CameronM]

Hi,

You can no doubt create an Append Query to do this but my preference is to build the query string in VBA code and tie it into the OnClick event. That way you can add any code to check values prior to inserting into your new table.

    Dim strSQL As String
 
    'build the string including the field names as required
    strSQL = "INSERT INTO tblOperator (Field1, Field2, Field3)
        VALUES (" & Me.[FieldOnMainForm] & ",
  " & Me.[qryTotalDirect subform].Form![FieldOnSubForm1]& ",
  " & Me.[qryTotalQueue subform].Form![FieldOnSubForm2]& ")"
 
    'execute the SQL string to insert the values
    DoCmd.RunSQL strSQL

 

[jerry28ph]

Hi CameronM,

I tried to add a command button and put the code that you gave me, but when I saved it, the "VALUES" line turned into red, I know this code belongs to VB, how am i going to write this code into Access script, please bear with coz im really newbie in access.

Regards,
Jerry

PS: thanks for the code.

Hi,

You can no doubt create an Append Query to do this but my preference is to build the query string in VBA code and tie it into the OnClick event. That way you can add any code to check values prior to inserting into your new table.

    Dim strSQL As String
 
    'build the string including the field names as required
    strSQL = "INSERT INTO tblOperator (Field1, Field2, Field3)
        VALUES (" & Me.[FieldOnMainForm] & ",
  " & Me.[qryTotalDirect subform].Form![FieldOnSubForm1]& ",
  " & Me.[qryTotalQueue subform].Form![FieldOnSubForm2]& ")"
 
    'execute the SQL string to insert the values
    DoCmd.RunSQL strSQL

 

[jerry28ph]

Hi,

Prior to my reply here's the script that I wrote once I click the command button:

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


... the color red lines shows when I m going to save the script. Text295 came from main form, and text2's are from sub forms.

Thanks.

Hi CameronM,

I tried to add a command button and put the code that you gave me, but when I saved it, the "VALUES" line turned into red, I know this code belongs to VB, how am i going to write this code into Access script, please bear with coz im really newbie in access.

Regards,
Jerry

PS: thanks for the code.

 

[CameronM]

Sorry, I should have clarified that it was only pseudo-code. Your query string does not contain any line break so it needs to either be all on one line, or you can add the line breaks to improve readability. Also make sure you use the syntax " & me.[Fieldname] & " when referring to the fields in you query.

[COLOR=black]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] & ")"[/COLOR]

Or Better still use " & _ to define line breaks so you can read the string:

 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] & ")"