Is there anybody that could assist with this bit of code, I've picked it up from this forum but I am struggling to get it to do what I want.
Basically, my combo box (in a subform, which is linked to a control source workdesc_id) picks up it's data depending on another control's setting in the main form - this works fine.
The not in list won't let me add new data and I think it's because of the where clause in the combo's details, which were on the row source, but I've since transferred it to vba as I thought this was the problem.
The Combo is set as follows:-
Dim sSQL As String
'This function sets the RowSource of WorkDesc, based on the
'value in Code_ID.
sSQL = "SELECT Tbl_workdesc.WorkDesc_ID, Tbl_workdesc.WDesc, Tbl_workdesc.Code_ID" _
& " FROM Tbl_workdesc WHERE Tbl_workdesc.Code_ID = " & Forms!Frm_Raise_all_Quote.Code_ID _
& " ORDER BY Tbl_workdesc.WDesc;"
The not in list is as follows:-
Private Sub Work_Description_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim ctl As Control
Set ctl = Screen.ActiveControl
Dim Msg As String
'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub
Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Work Description...")
If i = vbYes Then
strSQL = "Insert Into Tbl_WorkDesc ([WDesc]) VALUES('" & NewData & "', & '" & Me.Code_ID = Forms!Frm_Raise_all_Quote.Code_ID & "')"
CurrentDb.Execute strSQL
Response = acDataErrAdded
Else
ctl.Undo
Response = acDataErrContinue
End If
End Sub
The error message MS gives me is that it can't find the input table or query 'False' - within the debug, the CurrentDb.Execute strSQL is highlighted but if you hover over it, it is set as False and so is the strSQL (when you hover over it)
I have tried this without the code_id setting and still get the same error, I have an after update set for the code_setting. If I take the where clause out of the equation, it works fine, problem is it gives me a huge list of all work descriptions.
Sorry this is so long, hope some one can help
Basically, my combo box (in a subform, which is linked to a control source workdesc_id) picks up it's data depending on another control's setting in the main form - this works fine.
The not in list won't let me add new data and I think it's because of the where clause in the combo's details, which were on the row source, but I've since transferred it to vba as I thought this was the problem.
The Combo is set as follows:-
Dim sSQL As String
'This function sets the RowSource of WorkDesc, based on the
'value in Code_ID.
sSQL = "SELECT Tbl_workdesc.WorkDesc_ID, Tbl_workdesc.WDesc, Tbl_workdesc.Code_ID" _
& " FROM Tbl_workdesc WHERE Tbl_workdesc.Code_ID = " & Forms!Frm_Raise_all_Quote.Code_ID _
& " ORDER BY Tbl_workdesc.WDesc;"
The not in list is as follows:-
Private Sub Work_Description_NotInList(NewData As String, Response As Integer)
Dim strSQL As String
Dim i As Integer
Dim ctl As Control
Set ctl = Screen.ActiveControl
Dim Msg As String
'Exit this sub if the combo box is cleared
If NewData = "" Then Exit Sub
Msg = "'" & NewData & "' is not currently in the list." & vbCr & vbCr
Msg = Msg & "Do you want to add it?"
i = MsgBox(Msg, vbQuestion + vbYesNo, "Unknown Work Description...")
If i = vbYes Then
strSQL = "Insert Into Tbl_WorkDesc ([WDesc]) VALUES('" & NewData & "', & '" & Me.Code_ID = Forms!Frm_Raise_all_Quote.Code_ID & "')"
CurrentDb.Execute strSQL
Response = acDataErrAdded
Else
ctl.Undo
Response = acDataErrContinue
End If
End Sub
The error message MS gives me is that it can't find the input table or query 'False' - within the debug, the CurrentDb.Execute strSQL is highlighted but if you hover over it, it is set as False and so is the strSQL (when you hover over it)
I have tried this without the code_id setting and still get the same error, I have an after update set for the code_setting. If I take the where clause out of the equation, it works fine, problem is it gives me a huge list of all work descriptions.
Sorry this is so long, hope some one can help
