Select error

[civl_eng]

hi guys, i write a sql code to display a value in table by textbox valuin form, but i get error for this :





weight22 = "select weight from tbl_matter where matter1 = " & Me.matter2 & "

weight22 is field name on my form that i want to display weight from tbl_matter and matter2 is textbox field in the same form too.

can someone provide the correct method to use???

[ranman256]

try not to use sql , instead use queries, text boxes do not get sql, the form does.
but if matter2 is numeric , then you dont need delimiters:

weight22 = "select weight from tbl_matter where matter1 = " & Me.matter2

if it IS text then you need quotes:
weight22 = "select weight from tbl_matter where matter1 = '" & Me.matter2 & "'"

[civl_eng]

thanks for reply, matter2 is text and i run second sql code, but this time i get this error :



matter2 type is text and weight,weight22 are of double type.

[orange]

Show us your table design.
As ranman suggests, use a query.
You can test your logic with a query, before designing to use form.

SELECT  weight from tbl_matter where matter1 =[Enter a matter1 value]

example: entering "c" gives

weight
21.99

with this table

id	matter1	weight
1	a	12.45
2	b	34
3	c	21.99
4	d	157.59
5	e	34.91

[June7]

Can't pull a value into textbox by referencing an SQL string. All it does is try to pull the string into textbox. Why are you doing this? If you need to pull value from another table, options:

1. a multi-column combobox that includes the field then code can reference column of combobox

2. include the table in the form's RecordSource to retrieve the related data, bind textbox to the weight field and set it Locked Yes and TabStop No

3. DLookup() expression in textbox or in VBA: weight22 = DLookup("weight", "tbl_matter", "matter1 = '" & Me.matter2 & "'")

Regardless of which option used to display the value, code is required to save to record. But why save the value when it can be retrieved through query join?

[civl_eng]

thanks guys, i have several text box in a form and i wanted to pull string into textbox from another table and i did not want to create query for each table and text box for this reason i used SQL instead query and i got error for this, now i use June7 code (Dlookup) and my problem has been resolved.

sorry for my poor english.
thanks again for your reply.