Using Variables (original) (raw)

Hi Gang -

I am trying to learn VBA within Microsoft Access.

I have a form with a drop-down box on it. After the user select from the drop down box I want to query a different table, and than re-paint the form with the new data.
(eg Users selects madison Square Garden from Drop-down box and gets address of 7ave/31St NYC).
When I run the code below however I get a message box asking me for the tempptno variable value.

What am I missing???

Thanks!
MY CODE:

Private Sub destination_AfterUpdate()
DoCmd.SetWarnings False
Set TEMPpt_no = [pt_no]
DoCmd.Close acForm, "by__pt_no", acPrompt
'DoCmd.RunSQL "UPDATE HCF INNER JOIN ins_pidx2 ON HCF.HCF_name = ins_pidx2.destination SET ins_pidx2.xport_to_line1 = [HCF]![hcf_add_1], ins_pidx2.xport_to_line2 = [HCF]![hcf_add_2], ins_pidx2.xport_to_city = [HCF]![hcf_city], ins_pidx2.xport_to_state = [HCF]![hcf_state], ins_pidx2.xport_to_phone = [HCF]![hcf_phone] WHERE ins_pidx2.destination=[HCF]![HCF_name]"
DoCmd.RunSQL "UPDATE HCF INNER JOIN ins_pidx2 ON HCF.HCF_name = ins_pidx2.destination SET ins_pidx2.xport_to_line1 = [HCF]![hcf_add_1], ins_pidx2.xport_to_line2 = [HCF]![hcf_add_2], ins_pidx2.xport_to_city = [HCF]![hcf_city], ins_pidx2.xport_to_state = [HCF]![hcf_state], ins_pidx2.xport_to_phone = [HCF]![hcf_phone] WHERE ins_pidx2.destination=[HCF]![HCF_name] AND INS_PIDX2.PT_NO= TEMPPTNO"
DoCmd.OpenForm "by__pt_no"