Use SQL as Source of Text Box

[bozic12]

Hi all,

I have an unbound text box, Cutter_No on the form Stackup_Form, which is populated by using a Select_Cutter_Form form. I then use a hidden text box, Cutter_Hidden, which is populated using the dlookup statement to convert this number into the associated primary key of Cutter_Config_Table, which is Cutter_ID for that cutter number. This Cutter_Hidden is what controls the Cutter_ID attribute of Main_Stackup_Form's controlling table, Stackup_Table. I can post code if this is too confusing.

This all works very well, except when the user wants to scroll to that record on the Main_Stackup_Form, the Cutter Number field they see is blank, since it is unbound. Is there a way that I can control that textbox with an SQL statement like:
SELECT [cutter config table].[cutter no] FROM [cutter config table] WHERE ((([cutter config table].[cutter id]) = [forms]![stackup form]![cutterhidden]));

Remembering that cutterhidden is on the form hiding the record's Cutter_ID, which is obvioulsy useless to the end user. I want to use this ID essentially the same way that I created it to populate the Cutter Number textbox (this is the meaningful value to the user)when a user navigates to a particular record.

 

[AlexMidd]

Not sure exactly what you want to do here. The dlookup should serve the function of the SQL that you have mentioned.

Have fun! )

Alex Middleton

 

[AlexMidd]

Have re-read your post and think I understand the problem now. Will think about it and re-post, unless someone gets back first.

Have fun! )

Alex Middleton

 

[AlexMidd]

OK, are Stackup_Form and Main_Stackup_Form the same form? If not, should the field on the Main_Stackup_Form not be bound to the appropriate field so that it will show the correct data or is there some reason that prevents you doing this?

Cheers

Have fun! )

Alex Middleton

 

[bozic12]

Hi AlexMidd,

Thank you for your reply. Yes, I'm sorry for the confusion and my mistyping. Main_Stackup_Form is actually Stackup_Form.

My goal is to use the Cutter_ID of the of the Stackup_Forms current record (in the Stackup_Table), to populate a textbox on the same form with the Cutter_No of that Cutter_ID from the Cutter_Table (i.e., the reverse of how I populated the Cutter_ID for the record from a chosen Cutter_No which was put into this textbox in question).

I guess it might be easy to explain that if it were possible (which I tried and doesn't work), if I could use the dlookup function or an SQL statement as the control source for the textbox. The textbox is originally populated when the record is created by the user either typing in the known Cutter_No, or using a form to chose which Cutter_No they want, and the value is passed through code. I then use the dlookup function to populate a hidden textbox, CutterHidden, to convert this Cutter_no to the associated Cutter_ID, whose control source is the Cutter_ID attribute of the Stackup_Table.

Hope this makes more sense!!!

Thank,

Jeff

 

[cpossamai]

bozic12, Have you got an answer to this post, because I want to do a simular thing. I want to put the data from column 2 and 3 of a combo box into 2 textboxes using the sql statement something like this:
SELECT [Products]![UnitPrice] FROM [Products] WHERE [Products]![ProductID] = [Combo18]
and
SELECT [Products]![Product] FROM [Products] WHERE [Products]![ProductID] = [Combo18]

combo18 holds column 1- ProductID
column 2 - Product
column 3 - UnitPrice

I want to put these statements into the control source property of the 2 text boxes (one each)

Hope someone can help
Thank you