Microsoft Access "Event Procedure"

[songreg]

I am using Srch code to find a record to my form!!!
my error code after running is "Compile error" Variable not found"



Private Sub Command5_Click()
sonny:
Select Case Field6

Case 1
Srch = "SELECT DISTINCTROW Product_Information_Q-Clad_Table1.id,Product_Information_Q-Clad_Table1.number,Product_Information_Q-Clad_Table1.Tech,Product_Information_Q-Clad_Table1.customer,Product_Information_Q-Clad_Table1.customer_partnumber,Product_Information_Q-Clad_Table1.cq_number,Product_Information_Q-Clad_Table1.bd_number,Product_Information_Q-Clad_Table1.cirqon_snumber,Product_Information_Q-Clad_Table1.customer_number,Product_Information_Q-Clad_Table1.quote_number,Product_Information_Q-Clad_Table1.last_quoted_date,Product_Information_Q-Clad_Table1.recent_wip,Product_Information_Q-Clad_Table1.last_pull_date,Product_Information_Q-Clad_Table1.part_status,Product_Information_Q-Clad_Table1.customer_service_rep,Product_Information_Q-Clad_Table1.manufactures_rep FROM Product_Information_Q-Clad_Table1 WHERE ((Product_Information_Q-Clad_Table1.bd_number=[Forms]![Find_a_record_form]![Field12]));"
Case 2
Srch = "SELECT DISTINCTROW Product_Information_Q-Clad_Table1.id,Product_Information_Q-Clad_Table1.number,Product_Information_Q-Clad_Table1.Tech,Product_Information_Q-Clad_Table1.customer,Product_Information_Q-Clad_Table1.customer_partnumber,Product_Information_Q-Clad_Table1.cq_number,Product_Information_Q-Clad_Table1.bd_number,Product_Information_Q-Clad_Table1.cirqon_snumber,Product_Information_Q-Clad_Table1.customer_number,Product_Information_Q-Clad_Table1.quote_number,Product_Information_Q-Clad_Table1.last_quoted_date,Product_Information_Q-Clad_Table1.recent_wip,Product_Information_Q-Clad_Table1.last_pull_date,Product_Information_Q-Clad_Table1.part_status,Product_Information_Q-Clad_Table1.customer_service_rep,Product_Information_Q-Clad_Table1.manufactures_rep FROM Product_Information_Q-Clad_Table1 WHERE ((Product_Information_Q-Clad_Table1.cq_number=[Forms]![Find_a_record_form]![Field12]));"

Case Else
GoTo sonny
End Select
Forms!Find_a_record_form.RecordSource = Srch
Forms!Find_a_record_form.Visible = False

End Sub

 

[Rolliee]

Suggest you put a msgbox field6 before any other command in the proc and see what you are telling the program that field6 is.

Rollie E

 

[Garridon]

It sounds like you might need to declare a variable before the Select Case statement ... basically, a temporary holding place for the search information.

Linda Adams
Linda Adams/Emory Hackman Official Web site

http://www.hackman-adams.com

Official web site for actor David Hedison:

http://www.david-hedison.com

 

[TrojanRabbit]

Where is the error occurring? If it's in VB code, Debug should tell you which variable is not found. If it happens when it tries to use the SQL code, you may need to put [Forms]![Find_a_record_form]![Field12] outside the quote marks.
"bd_number=" & [Forms]![Find_a_record_form]![Field12] & &quot);"