Hello All-
There may be a very simple fix for this but I'm very new to the code world and don't completely understand what's going wrong.
I have a data entry form in a survey-like database. One subform is used to enter in responses for each of 12 criteria (equivalent to "questions"). The 12 criteria are featured in a combobox which then filters a multiselect listbox (they can choose more than 1 response under each criteria)to contain only the potential answers related to that particular criteria. This then passes each selection to a separate record in the response table. Its set up so that when the person presses the next criteria button it moves to the next criteria on the list.
The problem comes when you get to the 12th and last criteria. I'm not sure how to tell the form that after responses have been entered for the 12th criteria, save that record(s), and then move on to next control (which is another subform.
I'd added in another button "end criteria" which would move the focus to the next control but it often doesn't save the records associated with the last criteria. And it would be great if the second button wasn't necessary.
Here's the code:
Option Compare Database
Option Explicit
Private Sub cboCriteriaid_AfterUpdate()
Me.OptionID.Requery
End Sub
Private Sub Command10_Click()
'Dim OptionID As Control
Dim MyControl As Control
Dim varItm As Variant
Dim intI As Integer
Dim strPick As String
Set MyControl = Forms!frmApplication!fsubResponse!cboCriteriaid
For Each varItm In OptionID.ItemsSelected 'now process selected items...
If varItm >= 0 Then 'selected item?
strPick = "Insert into Response(HabitatID, Criteriaid, OptionID, Answercom) Values (" & Forms!frmApplication!fsubResponse!HabitatID & "," & Me.cboCriteriaid.Column(0) & "," & OptionID.Column(0, varItm) & ",'" & Me.AnswerCom & "');"
'MsgBox strPick 'To view what numbers are being saved for troubleshooting
xSql strPick
End If
Next
'clear selected items
For Each varItm In OptionID.ItemsSelected
If varItm >= 0 Then 'is this one selected
OptionID.Selected(varItm) = False
End If
Next
Me.cboCriteriaid = MyControl + 1
Me.AnswerCom = ""
cboCriteriaid_AfterUpdate
End Sub
Private Sub Command11_Click()
Forms!frmApplication!fsubHowDoYouEnjoy2!enjoyCode.SetFocus
End Sub
And I've attached a screen shot of the form.
Thanks for any assistance!
Stephanie
There may be a very simple fix for this but I'm very new to the code world and don't completely understand what's going wrong.
I have a data entry form in a survey-like database. One subform is used to enter in responses for each of 12 criteria (equivalent to "questions"). The 12 criteria are featured in a combobox which then filters a multiselect listbox (they can choose more than 1 response under each criteria)to contain only the potential answers related to that particular criteria. This then passes each selection to a separate record in the response table. Its set up so that when the person presses the next criteria button it moves to the next criteria on the list.
The problem comes when you get to the 12th and last criteria. I'm not sure how to tell the form that after responses have been entered for the 12th criteria, save that record(s), and then move on to next control (which is another subform.
I'd added in another button "end criteria" which would move the focus to the next control but it often doesn't save the records associated with the last criteria. And it would be great if the second button wasn't necessary.
Here's the code:
Option Compare Database
Option Explicit
Private Sub cboCriteriaid_AfterUpdate()
Me.OptionID.Requery
End Sub
Private Sub Command10_Click()
'Dim OptionID As Control
Dim MyControl As Control
Dim varItm As Variant
Dim intI As Integer
Dim strPick As String
Set MyControl = Forms!frmApplication!fsubResponse!cboCriteriaid
For Each varItm In OptionID.ItemsSelected 'now process selected items...
If varItm >= 0 Then 'selected item?
strPick = "Insert into Response(HabitatID, Criteriaid, OptionID, Answercom) Values (" & Forms!frmApplication!fsubResponse!HabitatID & "," & Me.cboCriteriaid.Column(0) & "," & OptionID.Column(0, varItm) & ",'" & Me.AnswerCom & "');"
'MsgBox strPick 'To view what numbers are being saved for troubleshooting
xSql strPick
End If
Next
'clear selected items
For Each varItm In OptionID.ItemsSelected
If varItm >= 0 Then 'is this one selected
OptionID.Selected(varItm) = False
End If
Next
Me.cboCriteriaid = MyControl + 1
Me.AnswerCom = ""
cboCriteriaid_AfterUpdate
End Sub
Private Sub Command11_Click()
Forms!frmApplication!fsubHowDoYouEnjoy2!enjoyCode.SetFocus
End Sub
And I've attached a screen shot of the form.
Thanks for any assistance!
Stephanie
