How to set form textbox default value by event using INSER INTO statement

[Joe Y]

In Access 2007, I have a form called OrderHeader that displays order details. The record source is based on table DBA_Order. When user clicks a combo box to select customer no, I want the form to call data of "customer payment term" from DBA_customers table as default value of a textbox in current form (which is bound to ordh_pymt_terms field in table OrderHeader) and allow user to modify the data.

In the After Update event, I use the following vba code to execute the routine. I got various syntax errors. I cannot get it right. Please help.

If this approach is wrong for such purpose, please guide the correct method.

Thanks for any helps!

Private Sub ComboSelectCust_AfterUpdate()

Dim dbs As Database

MsgBox (ordh_cust_no)  

dbs.Execute " INSERT INTO DBA_Order " _	
& "(ordh_pymt_terms) " _			
& "SELECT cust_payment_terms " _		
& "FROM DBA_customers " _				
& "WHERE cust_no = ordh_cust_no;"			

MsgBox (cust_payment_terms) 			
dbs.Close
 
End Sub

[Seth Schrock]

Check out the DLookup function. It sounds like this could be what you are looking for. You could then do something like

Me.TxtBox = DLookup({your information})

[Joe Y]

Thank you Seth. This works! I have wasted so many hrs trying to make INSERT INTO statement work for this situation.

[NeoPa]

@Joe Y
Please check out When Posting (VBA or SQL) Code before ever posting again.

I won't delete this again as you already have a solution, but frankly I would have otherwise. You clearly paid no attention to the message last time.