combo box / SQL insert into

[Fred]

I have a combo box that look up patient’s names. If the patient’s name is on
the list and chosen it fills in the corresponding boxes. This function work
fine, but if the patient is not listed I would like the ability to add the
patient name to the table. I have added the following to the “NotInList†in
the events tab but when you run the procedure an error message “syntax error
in INSERT INTO statement†shows up. Can you please find where my mistake is?

Table Name: MedicalTable
Table field Name: reference number
Table field Name: patient

Private Sub cboNames_NotInList(NewData As String, Response As Integer)

Dim ctl As Control
Dim strSQL As String
Set ctl = Me!cboNames

If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then

Response = acDataErrAdded

strSQL = "INSERT INTO MedicalTable(reference number,Patient)VALUES("
strSQL = strSQL &(DMax("[reference number]",+1)& "NewData" & ");"

CurrentDb.Execute strSQL

Else

Response = acDataErrContinue
ctl.Undo
End If

End Sub

 

[Dennis]

You are missing the Domain in your DMax function.
Set a variable first like
Dim nNextNum as Integer
nNextNum = DMax("[reference number]","MedicalTable") + 1

strSQL = "INSERT INTO MedicalTable (reference number,Patient) VALUES("
strSQL = strSQL & nNextNum & "," & NewData & ");"

 

[Fred]

Dennis

I knew I forgot something, Thanks for getting me back on track!

You are missing the Domain in your DMax function.
Set a variable first like
Dim nNextNum as Integer
nNextNum = DMax("[reference number]","MedicalTable") + 1

strSQL = "INSERT INTO MedicalTable (reference number,Patient) VALUES("
strSQL = strSQL & nNextNum & "," & NewData & ");"

I have a combo box that look up patient’s names. If the patient’s name is on
the list and chosen it fills in the corresponding boxes. This function work
fine, but if the patient is not listed I would like the ability to add the
patient name to the table. I have added the following to the “NotInList†in
the events tab but when you run the procedure an error message “syntax error
in INSERT INTO statement†shows up. Can you please find where my mistake is?

Table Name: MedicalTable
Table field Name: reference number
Table field Name: patient

Private Sub cboNames_NotInList(NewData As String, Response As Integer)

Dim ctl As Control
Dim strSQL As String
Set ctl = Me!cboNames

If MsgBox("Value is not in list. Add it?", _
vbOKCancel) = vbOK Then

Response = acDataErrAdded

strSQL = "INSERT INTO MedicalTable(reference number,Patient)VALUES("
strSQL = strSQL &(DMax("[reference number]",+1)& "NewData" & ");"

CurrentDb.Execute strSQL

Else

Response = acDataErrContinue
ctl.Undo
End If

End Sub