Help with SQL string in VBA (1 Viewer)

[mort]

Hello all,

I am writing av event in the NotInList event of a combobox. The combobox does a lookup in another table and returns the value to a textbox in my main form.

The lookuptable is tblSvarTypeSporprøve. It has 3 columns:
- ID
- Type
- Category

Its purpose is to show the answer of something that has been done to the item selected in a different listbox. For example if a car XX (selected item in listbox) has been sent to repairs. The type could be "engine repaired" and the category would then be "car workshop".

There are about 5 different categories with 3-5 different types in each. The combobox is filtered to only show the types that are stored in the corresponding category of that item. The types belonging in the category "veterinary" would not show up if the selected item was a car, but it would show up if the item was a cat. The types for each item can only be selected from 1 category. Hope you understand.

I have succesfully written a code that allows the user to enter the new type, and that type is stored in its table based on the following SQL:

strSQL = "INSERT INTO tblSvarTypeSporprøve([Svartype]) " & _
"VALUES ('" & NewData & "');"

The problem is that with each type there is also a corresponding category. When the user enter a new type it is successfully stored in the tblSvarType... but the category field is empty.

I need help to also Insert the category in the tblSvarType...




I have pasted the complete code below if anyone wants to see.

Private Sub cboSvartype_NotInList(NewData As String, Response As Integer)
On Error GoTo Err_cboSvartype_NotInList
Dim intAnswer As Integer
Dim strSQL As String
intAnswer = MsgBox("Svartypen " & Chr(34) & NewData & _
Chr(34) & " er ikke oppført på listen." & vbCrLf & _
"Vil du legge denne til som svartype i listen?" _
, vbQuestion + vbYesNo, "Endring av verdiliste")
If intAnswer = vbYes Then
strSQL = "INSERT INTO tblSvarTypeSporprøve([Svartype]) " & _
"VALUES ('" & NewData & "');"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
MsgBox "Den nye svartypen er lagt til i listen." _
, vbInformation, "Vellykket registrering"
Response = acDataErrAdded
Else
MsgBox "Velg svartype fra listen." _
, vbInformation, "Informasjon"
Response = acDataErrContinue
End If
cboSvartype_NotInList_Exit:
Exit Sub
Err_cboSvartype_NotInList:
MsgBox Err.Description, vbCritical, "Error"
Resume cboSvartype_NotInList_Exit
End Sub

 

[Minty]

If the category is available from the preceding combo you can include that in your code.

strSQL = "INSERT INTO tblSvarTypeSporprøve([Svartype], [Category]) " & _
"VALUES ('" & NewData & "' , " & Me.cmbCategory &   ");"

This assumes your category is a number field, if not add the single quotes around it. Change cmbCategory to the name of you category combo control.

 

[Cronk]

If the user has to enter the extra information to be saved with the new value in the combo, use an input box and use the insert sql to add it as well.

eg strExtraInfo = inputbox("Enter info")

 

[mort]

Thanks Minty - your solution worked