Hello,

I created a database maintain stock transactions of a company.

The scenario goes like so:
The user opens the purchase screen,
The function works fine for an item already defined in the database.

But, if use enters a new item (that does not exist yet in the database) the following error is raised:

"run-time error '3075': Syntax error (missing operator) in query expression []"
The debugger takes me to the code line in red

Below is the code:

Private Sub ArticleID_NotInList(NewData As String, Response As Integer)
'Suppress the default error message.
Response = acDataErrContinue
' Prompt user to verify if they wish to add a new value.

If MsgBox("L'Article " & NewData & " n'existe pas. Voulez-vous le créer?", vbYesNo) = vbYes Then
' Set Response argument to indicate that data is being added.
a = Split(NewData, "-", , 5)

Dim mrq As String, mdl As String, typ As String, ref As String, coul As String
mrq = a(0)
mdl = a(1)
typ = a(2)
ref = a(3)
coul = a(4)

mrqCriteria = "[MarqueCode] = '" & mrq & "'"
mdlCriteria = "[ModeleCode] = '" & mdl & "'"
typCriteria = "[TypeCode] = '" & typ & "'"

Dim mrqID As Long, mdlID As Long, typID As Long
mrqID = Nz(DLookup("[MarqueID]", "tblMarque", mrqCriteria), 1)
mdlID = Nz(DLookup("[ModeleID]", "tblModele", mdlCriteria), 1)
typID = Nz(DLookup("[TypeID]", "tblType", typCriteria), 1)

MsgBox "Marque code = " & mrq & " Marque ID = " & mrqID
MsgBox "Modele code = " & mdl & " Modele ID = " & mdlID
MsgBox "Type code = " & typ & " Type ID = " & typID

sqlArticle = "insert into tblArticle (MarqueID, ModeleID, TypeID, Reference, Couleur) values ( " & mrqID & ", " & mdlID & ", " & typID & " , " & ref & ", " & coul & ") "
DoCmd.RunSQL sqlArticle

'Inform the combo box that the desired item has been added to the list.
Response = acDataErrAdded
'rstArticle.Close 'Close the recordset

Me.Refresh

End If
End Sub

Thanks in advance for the support.

Try:
sqlArticle = "insert into tblArticle (MarqueID, ModeleID, TypeID, Reference, Couleur) values ( '" & mrqID & "', '" & mdlID & "', '" & typID & "' , '" & ref & "', '" & coul & "') "

Try:
sqlArticle = "insert into tblArticle (MarqueID, ModeleID, TypeID, Reference, Couleur) values ( '" & mrqID & "', '" & mdlID & "', '" & typID & "' , '" & ref & "', '" & coul & "') "


Thanks for the tip, i totally forgot about the single quotes. I had to add them only to "ref" and "coul" since the other 3 variable are of type integer.


There seems to be another bug in this code :o

The following line is not storing the looked up value, but the 'if null' value.

mrqID = Nz(DLookup("[MarqueID]", "tblMarque", mrqCriteria), 1)

Put a breakpoint on that line of code and see what all the values are.

go back to this

mrqCriteria = "[MarqueCode] = '" & mrq & "'"

this will just translate into

mrqCriteria = "[MarqueCode] = & mrq & ", which is nonsense.

i think you ought to use a breakpoint and see what is happening, or check/display your data at appropriate points with msgbox es

i find cgr(34) easier to use than sgle/dble quotes

so in the above you should have

mrqCriteria = "[MarqueCode] = " & chr(34) & mrq & chr(34) , which is not the same as you had. the mrq is NOT within codes, and will be replaced by the variable mrq.

First, i wanna say sorry for not replying sooner (very abrut from me). It is just that i have been widley overhelmed at office (new project kick off)

It seems that there is problem is when fetching the value of mrqID, mdlID and typID (in debugging mode, they all return 1 as value, whereas they are supposed to return the ID of the (already existing) paramater they are given:

mrqID = Nz(DLookup("[MarqueID]", "tblMarque", mrqCriteria), 1)
mdlID = Nz(DLookup("[ModeleID]", "tblModele", mdlCriteria), 1)
typID = Nz(DLookup("[TypeID]", "tblType", typCriteria), 1)


Now the weird thing is that the new values are stored (despite the wrong values of the above values), but the function enters an endless loop!



gemma-the-husky,
I didn't get quite well what chr(34) stands for in this context :o , and how it would differ from the other syntaxes.
Would you care elaborate it pls?

sorry, mephisto,

on first glance i thought the expression for mrqCriteria

mrqCriteria = "[MarqueCode] = '" & mrq & "'" was punctuated incorrectly, but I don't think it is now, looking at it again.

as the idea is to end up with something that looks like the following (eg in your example when the variable a(0) repesents say a string of AB1234)

either
[MarqueCode] = 'AB1234' or
[MarqueCode] = "AB1234"

rather than trying to use double double quotes, or embed single quotes within double quotes, i find it much easier to use the ascii char for double quotes which is chr(34) and say

[MarqueCode] = chr(34) & a(0) & chr(34) which is the same as saying
[MarqueCode] = chr(34) & "AB1234" & chr(34)

As I say I thought (wrongly) that your original sysntax was faulty, but I don't now think it was, on reflection.

------------------------------------------------

if you are still getting problems though, I would be inclined to put a breakpoint where you have the "split" statement, and step through the code from that point on.

Thanks for the clarification, gemma_the_husky.

The splitted values are correct, I also inserted debugging messages to make sure that they are !

Do you think i should 'commit' or 'refresh' the new values somewhere?

are you still getting the code not exiting at all - i can't see any looping statements in there ot do that. perhaps you are calling it incorrectly from outside the sub.

I think that you may need Me.Requery rather than Me.Refresh.
Refesh does not call up the data you have added

Peter

I think that you may need Me.Requery rather than Me.Refresh.
Refesh does not call up the data you have added

Peter

Thanks for the tip Peter. I tried it with no further success though !

You are welcome to play with this which is a way I do things just wondering are the values added to a() able to be on the form in some form Is so you could try sending the values into this function for adding items just add the fields to the function and the call plus update the m_rst(StrField) = StrItem Adding a new one for each item.
this is a global function for adding one item to a table from any combo box Hope it helps have a playUnder Option Explicit In a Module Add
Private Const Strfrm As String = "MsgfrmLinkingTables"
Private m_rst As Recordset
Private m_IntNew As Integer
Private Const m_strMsg As String = " Is Not An Item In The List Would You Like To Add It"
Private Const m_strUndoMsg As String = " Is Not In the List Undoing Entry"
Private Const m_strTitle As String = "Undoing Invalid Entry"
Public Function FillListsOne(StrItem As String, StrTable As String, _
StrField As String) As Integer
On Error GoTo Err_HandleErr
m_IntNew = MsgBox(StrItem & m_strMsg _
, vbInformation + vbYesNo, "Item Not In List")
If m_IntNew = vbYes Then
Set m_rst = CurrentDb.OpenRecordset(StrTable)
m_rst.AddNew
m_rst(StrField) = StrItem
m_rst.Update
FillListsOne = acDataErrAdded
Else
MsgBox StrItem & m_strUndoMsg, , m_strTitle
FillListsOne = acDataErrContinue
End If

Exit_HandleErr:
Exit Function

Err_HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error: " & Err.Description & " (" & Err.Number & ")"
Resume Exit_HandleErr
Resume
End Select

End Function


Call Function LikePrivate Sub LabelID_NotInList(NewData As String, Response As Integer)
Response = FillListsOne(NewData, "tblRecordingLabels", "RecordingLabelName")
If Response = acDataErrContinue Then
DoCmd.RunCommand acCmdUndo
End If
End Sub