VBA help needed

[mephisto794]

Hello people !
i have been working on a small MS Access database (stock maintenance and so...)
i am trying to write a code that allows to insert a new 'article' in the 'article' definition table when creating a purchase voucher.

I used this code :
----------------------------------------

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

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 = DLookup("[MarqueID]", "tblMarque", mrqCriteria)
mdlID = DLookup("[ModeleID]", "tblModele", mdlCriteria)
typID = DLookup("[TypeID]", "tblType", typCriteria)

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

DoCmd.RunSQL sqlArticle
Me.Refresh

End If
End Sub

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

am having the following error message : "Run-time error 3134: Syntax error in INSERT INTO statement"

I tried to insert some debugging messages, it showed up that mrqID, mdlID and typID are returning a null value.

Does anyone has any enlightening suggestion ?
am counting on you guys! don't let me down
thanks in advance !

[lebanese_a]

Originally Posted by mephisto794

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

Hey
first of all sorry, I am sure you expected a reply that solve ur problem but I am 99% sure that mine is not the one coz I am a beginner at Access (wizards anja2) and a beginner at VB.NET bass from what I know la hala2 that putting stuff entre "" means that the app conciders them as text and I see you got the red part entre guillemet so...
Anyways like I said 99% akid I didn't help bass ya3neh! a7ssan mine ma chi!

[mephisto794]

Thanks for the tip Lebanese_a.

It helped a little, not enough to resolve the problem though!

I update the insert statement to become as follows :

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

(Removed the quotes from the ID fields (since they are numeric)

I tried to run the application, no success

A different message though (for a change) : Invalid use of null.

the debugging highlighted the following code :

mrqID = DLookup("[MarqueID]", "tblMarque", mrqCriteria) showing mrqID = 0 (which is not correct)


The message boxes i inserted to show the value of mrqID where not displayed. The system was obviously stopped before reaching them .

Any other suggestions?