Copy value in Combo Box(es) & Text Box to table with Macro
Hello Experts,
I would like to use a command button to copy values from a set of combo boxes and text boxes. In this set, I have 2 combo boxes and 1 text box. Once I have selected the required values, I would like to utilise a command button to copy these values to an independent table, say tbl_values. Each time I press the command button, I would require each set of these three values to be copied across to the tbl_values.
However, as a safeguard, I would like to include a message saying "Are you sure you would like to create this combined value of 'cbo_1' with 'cbo_2' on the 'txt_value'? Yes/No."
If the user selects Yes, a message would say "The following values where copied 'cbo_1', 'cbo_2' on the 'txt_value'.
Warmest Regards
LK
I would like to use a command button to copy values from a set of combo boxes and text boxes. In this set, I have 2 combo boxes and 1 text box. Once I have selected the required values, I would like to utilise a command button to copy these values to an independent table, say tbl_values. Each time I press the command button, I would require each set of these three values to be copied across to the tbl_values.
However, as a safeguard, I would like to include a message saying "Are you sure you would like to create this combined value of 'cbo_1' with 'cbo_2' on the 'txt_value'? Yes/No."
If the user selects Yes, a message would say "The following values where copied 'cbo_1', 'cbo_2' on the 'txt_value'.
Warmest Regards
LK
ASKER
Thank you Ed. My primary question relates to the copying of values. Any ideas? :)
Add some strings to Ed's code:
Dim SQL As String
SQL = "INSERT INTO tbl_values ( cbo_1, cbo_2, txt_value )SELECT " & Me.cbo_1 & " AS 1, " & Me.cbo_2 & " AS 2, " & Me.txt_value & " AS txt;"
DoCmd.RunSQL SQL
ASKER
Hey again experts,
Have combined your views in relation to my question.
cbo_1 = cbo_Campaign_Region
cbo_2 = cbo_Campaign_Trial
txt_value = txt_Campaign_Date
The message appears as requested. However, it doesn't show the values from the combo box that I selected.
Secondly, a run time error occurs when the YES is selected.
Further thoughts please? :)
Regards
LK
Have combined your views in relation to my question.
cbo_1 = cbo_Campaign_Region
cbo_2 = cbo_Campaign_Trial
txt_value = txt_Campaign_Date
The message appears as requested. However, it doesn't show the values from the combo box that I selected.
Secondly, a run time error occurs when the YES is selected.
Further thoughts please? :)
Regards
LK
Private Sub cmd_Create_Campaign_Click()
If MsgBox("Are you sure you would like to create this combined value of 'cbo_Campaign_Region' with 'cbo_Campaign_Trial' on the 'txt_value'?", vbYesNo, " ") = vbYes Then
Dim SQL As String
SQL = "INSERT INTO tbl_values ( cbo_Campaign_Region, cbo_Campaign_Trial, txt_Campaign_Date )SELECT " & Me.cbo_Campaign_Region & " AS 1, " & Me.cbo_Campaign_Trial & " AS 2, " & Me.txt_Campaign_Date & " AS txt;"
DoCmd.RunSQL SQL
Else
DoCmd.CancelEvent
End If
End Sub
Please clarify. You say that you want to take the values and copy them to a table. Do you mean a table in MS Access or a table in Excel?
Getting values from the combo boxes and text box and then verifying with the user is trivial as Ed pointed out.
als315's code is SQL for database insertion. If this is not what you are looking for or you need help tying this together, please let use know.
Getting values from the combo boxes and text box and then verifying with the user is trivial as Ed pointed out.
als315's code is SQL for database insertion. If this is not what you are looking for or you need help tying this together, please let use know.
NVM, saw new post
Here is a code snippet for the first part. I cleaned up Ed's code a little. I think it will make a little more sense for what you are doing.
Private Sub button1_Click()
If MsgBox("Are you sure you would like copy the values " & cbo_1 & " and " & cbo_2 & _
" and " & txt_Value & "on the 'txt_value'?", vbYesNo, "Title") = vbYes Then
MsgBox ("cbo_1: " & cbo_1 & "; cbo_2: " & cbo_2 & "; txt_Value: " & txt_Value)
Else
MsgBox ("No Copy")
End If
End Sub
Do you already have a working connection to a database? Or are you actually outputting to a database or to another place in Excel?
Private Sub button1_Click()
If MsgBox("Are you sure you would like copy the values " & cbo_1 & " and " & cbo_2 & _
" and " & txt_Value & "on the 'txt_value'?", vbYesNo, "Title") = vbYes Then
MsgBox ("cbo_1: " & cbo_1 & "; cbo_2: " & cbo_2 & "; txt_Value: " & txt_Value)
Else
MsgBox ("No Copy")
End If
End Sub
Do you already have a working connection to a database? Or are you actually outputting to a database or to another place in Excel?
On what line is error? Move Dim to second line
Add
docmd.setwarnings = false
before runSQL and = true after
Private Sub cmd_Create_Campaign_Click()
Dim SQL As String
If MsgBox("Are you sure you would like to create this combined value of 'cbo_Campaign_Region' with 'cbo_Campaign_Trial' on the 'txt_value'?", vbYesNo, " ") = vbYes Then
SQL = "INSERT INTO tbl_values ( cbo_Campaign_Region, cbo_Campaign_Trial, txt_Campaign_Date )SELECT " & Me.cbo_Campaign_Region & " AS 1, " & Me.cbo_Campaign_Trial & " AS 2, " & Me.txt_Campaign_Date & " AS txt;"
DoCmd.RunSQL SQL
Else
DoCmd.CancelEvent
End If
End Sub
Add
docmd.setwarnings = false
before runSQL and = true after
ASKER
Hello Experts.
rfportilla - I am outputting to a table in the database, being tbl_values
als315 - I run the code, and the error is occuring within the Docmd.RunSQL SQL
Regards
LK
rfportilla - I am outputting to a table in the database, being tbl_values
als315 - I run the code, and the error is occuring within the Docmd.RunSQL SQL
Regards
LK
It seems there is some error in SQL sentence. May be some fields in your DB have other names. Could you upload an example of you DB with this form and table?
You can also check SQL string in debugger. Try to copy it to query (in SQL mode) and run. Query should append one string.
You can also check SQL string in debugger. Try to copy it to query (in SQL mode) and run. Query should append one string.
ASKER
als315: Quickly before I do that, should the field names within tbl_values be of any particular format or name or should there be an ID etc etc?
If there are any index fields (not autonumber), they should also be filled.
Yo can try it manually adding new record with only your three fields filled. If it is enough, you will do it without problems.
Yo can try it manually adding new record with only your three fields filled. If it is enough, you will do it without problems.
I hope fields names in table are: cbo_Campaign_Region, cbo_Campaign_Trial, txt_Campaign_Date
ASKER
Experts,
Please find attached the required file. Ultimately, a UNIQUE ID should also be within the table so as well.
LK
Example-1.mdb
Please find attached the required file. Ultimately, a UNIQUE ID should also be within the table so as well.
LK
Example-1.mdb
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Change SQL string to:
Sorry, code was not attached.
SQL = "INSERT INTO tbl_values ( cbo_Campaign_Region, cbo_Campaign_Trial, txt_Campaign_Date )SELECT [Forms]![frm_Campaign]![cbo_Campaign_Region] AS 1, [Forms]![frm_Campaign]![cbo_Campaign_Trial] AS 2, [Forms]![frm_Campaign]![txt_Campaign_Date] AS txt;"
ASKER
Great Ed, works perfectly, although took out the part combining the value for the Me.txt_Campaign_Date after the Then statement.
Two small questions:
1) How can I populate the Message with the currently selected values i.e. "Are you sure you would like to create this combined value of Sydney with Wine on the 12/08/2010?
2) Before the DoCmd.Cancel Event, clear the values in the combo boxes?
Regards
LK
Two small questions:
1) How can I populate the Message with the currently selected values i.e. "Are you sure you would like to create this combined value of Sydney with Wine on the 12/08/2010?
2) Before the DoCmd.Cancel Event, clear the values in the combo boxes?
Regards
LK
Private Sub cmd_Create_Campaign_Click()
Dim db As DAO.Database, strMyValue As String, strMyValue2 As String, strMyValue3 As String
If MsgBox("Are you sure you would like to create this combined value of 'cbo_Campaign_Region' with 'cbo_Campaign_Trial' on the 'txt_value'?", vbYesNo, " ") = vbYes Then
strMyValue = Me.cbo_Campaign_Region
strMyValue2 = Me.cbo_Campaign_Trial
strMyValue3 = Me.txt_Campaign_Date
Set db = CurrentDb
db.Execute "Insert into tbl_Values (cbo_campaign_region, cbo_campaign_trial, txt_campaign_date) VALUES ('" & strMyValue & "','" & strMyValue2 & "','" & strMyValue3 & "')"
Set db = Nothing
Else
DoCmd.CancelEvent
End If
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
And before the DoCmd.CancelEvent place this:
Me.cbo_Campaign_Region = Null
Me.cbo_Campaign_Trial = Null
Ed
Me.cbo_Campaign_Region = Null
Me.cbo_Campaign_Trial = Null
Ed
ASKER
Thank you to all Experts for the answer. Awarded majority to MINDSUPERB has it answered the question most efficiently.
If MsgBox ("Are you sure you would like to create this combined value of 'cbo_1' with 'cbo_2' on the 'txt_value'?",vbYesNo, "Title") = vbYes Then
Me.txt_Value=Me.cbo_1& " " & Me.cbo_2
Else
DoCmd.CancelEvent
End If
Sincerely,
Ed