error when compiling

[tiku]

i have this current program modify from another program, both program function quite similar.. when compiling, there are 2 error when i run the access,

1 is it point that there was syntax error on insert statement, but i couldnt any mistake - DoCmd.RunSQL strinsert (program point it mistake to this sentence)

2. when i leave my BL blank, the program should prompt me a msg but instead it pointed that "BL = rs("Bidlog")" has error... it seems fine to me..

any kind soul to assist me?thank u in advance

 

[tehNellie]

String Variables can't be NULL. You don't need to read the recordset values into variables at that point, test them directly.

if isnull(rs!("BL")) Then
 display the message box.
end if

Your message box messages suggest you're tying to get a value from the user but message box is a prompt only, Inputbox will allow the user to get values, but as you only want "na" then you don't need to prompt the user

if isnull(rs!("bidlog")) then
  strBL = "na"
else
  strBL = rs!("bidlog")
endif

Better yet, if NULL isn't a valid value for that column, don't allow NULL in there to begin with.

There are syntax errors in your SQL statement, you're also using LIKE without any wildcards. debug.print the contents of strInsert and paste it into the query designer in SQL view and you'll be able to see what it's doing and where it's going wrong.

I also wouldn't set warnings to FALSE until you're happy that the code is doing what you think it should.


On a general basis, don't stick the code you're having problems with in file attachments. Normally I won't go near them with a 10 foot barge pole and it's extra effort compared to just using [ code] tags in your post so it can be seen (by everyone).

 

[vbaInet]

rs!BL

Or 

rs![BL] ... if you have spaces or special characters

Or

rs.Fields("BL")

 

[tiku]

Thanks tehNellie and vbaInet.. that was a great idea for the If statement, the idea never came across my mind.. now i have no prob with the that part..

alright prob solved..but now im facing a logical error... previously i was tehNellie help me to assign NULL input as na.. i will like the program to work like this--> if user onli keys in CUS (customer name), the program will search all the entries with the same customer name... if user keys in CUS and PROJ(projectname), program will find all those entries which matches both CUS and PROJ..etc.. below is the only comparsion statement to determine what to output... i tried using OR instead of AND, result wasnt correct..

anyone care to share his/her idea on sovling my logical error..

strinsert = "INSERT into PCEfinal (DbDate,Bidlog,FileDate,Customer,ProjectName,MFGPN,CUSPN,Cmdty,Description,Mfg,VndPN,Vnd,PriceUSD,SelPrice,CStatus,Remarks)" & _
"SELECT PCEquoteDB.[DbDate], PCEquoteDB.[Bidlog],PCEquoteDB.[FileDate], PCEquoteDB.[Customer], PCEquoteDB.[ProjectName], PCEquoteDB.[MFGPN], PCEquoteDB.[CUSPN], PCEquoteDB.[Cmdty], PCEquoteDB.[Description], PCEquoteDB.[Mfg], PCEquoteDB.[VndPN], PCEquoteDB.[Vnd], PCEquoteDB.[PriceUSD], PCEquoteDB.[SelPrice], PCEquoteDB.[CStatus], PCEquoteDB.[Remarks]" & _
"FROM PCEquoteDB " & _
"WHERE PCEquoteDB.[Bidlog] LIKE '" & BL & "' or PCEquoteDB.[Customer] = '" & CUS & "' or PCEquoteDB.[ProjectName] = '" & PROJ & "' or PCEquoteDB.[MFGPN]= '" & MFG & "';"

i have some doubts on the following codes found in under strinsert INSERT . i will like to ask will the rest of the comparsion statement (example : PCEquoteDB.[ProjectName] = '" & PROJ & " )works like "LIKE" statement as i only key in once INSERT ??

"WHERE PCEquoteDB.[Bidlog] LIKE '" & BL & "' and PCEquoteDB.[Customer] = '" & CUS & "' and PCEquoteDB.[ProjectName] = '" & PROJ & "' and PCEquoteDB.[MFGPN]= '" & MFG & "';"

 

[vbaInet]

tiku, create a new thread for this new question.