syntax error (missing operator) in query expression (1 Viewer)

[LukeH]

Hi all,
Im new to programming on Access and have hit a brick wall with this, im trying to select a value from a table, but i keep getting the error runtime error '3075': syntax error(missing operator) in query expression. any help fixing my mistakes would be great.

strSQL1 = "SELECT[Stock]FROM[tbl_barstock]WHERE[Traceability Code]='" & Me.txt_traceability_code.Value & "'"

 

[Ranman256]

DONT use the .value.
(cant tell if you have spaces)

strSQL1 = "SELECT [Stock] FROM [tbl_barstock] WHERE [Traceability Code]='" & Me.txt_traceability_code & "'"

and theres no need to write sql, just use a query (no syntax errors) :
SELECT [Stock] FROM [tbl_barstock] WHERE [Traceability Code]=forms!fMyForm!txt_traceability_code

 

[gemma-the-husky]

is txt_traceability_code a number or a string. That will make a difference.

 

[LukeH]

Sorry guys, ive figured out what was doing it. Its because i was doing that query and then trying to use strSQL1 as a value for insert into. Would you happen to no of a way i could find the number that would direct me to and insert it to a different table. Thanks for your replies

 

[The_Doc_Man]

Here is the syntax for an INSERT INTO query.

INSERT INTO statement (Microsoft Access SQL)

Office developer client VBA reference documentation

docs.microsoft.com

From your question, I'm going to assume you want to, in essence, copy [Stock] to another table. If that is the only thing you have in that table, then you really COULD use your strSQL1 as follows:

strSQL2 = "INSERT INTO myothertable (stock) " & strSQL1 & ";"

HOWEVER - if the other table has more fields than just this stock then you might have to do a little bit more. I won't burden you with the extra stuff unless you tell us what ELSE you need.

Just remember to assure that you have spaces between elements. A very common error is to leave things jammed together without spaces, and that usually leads to some type of syntax error.

 

[LukeH]

Thanks for your reply, ill save that info for when i may need it in the future.

What im trying to do is i have a form set to pop up that i can put a Traceability Code into and other information.
Then input that into a table, but i would like to get it to also use the traceability code i type into the pop up form to find the stock number related to it from table1 which contains both of that information and then input it into table 2 also on the same row as the traceability code.

i hope i have explained that good enough.

 

[The_Doc_Man]

If you have multiple fields to insert, the syntax would vaguely resemble:

INSERT INTO myothertable (Stock, field2, field3, ...) 
SELECT Stock, constant1, constant2, ... FROM tbl_barstock
WHERE WHERE [Traceability Code]='" & Me.txt_traceability_code & "';"

Substitute the correct names for myothertable, field2, field3, constant1, constnat2 as needed.

However, "on the same row as the traceability code" makes me stop and ask if there is something else going on here that you haven't told us yet. That requirement seems odd in some way. Perhaps I just didn't understand it. I've been known to get confused.

 

[LukeH]

I do apologise, i am pretty poor at explaining things (I normally get on with google and patience trying different things).

The picture attached is 3 cropped screen shots the top 1 is a row of information from the first table where the (stock) is each row of information has a unique (traceability code).

The second is my pop up form that i fill out to insert information to my table which is the third one.

so far i have got the form to input the information i have typed into it to the table, what i am trying to do now is get the stock value from the first table which is on the same line as the traceability code i have typed into my pop up and insert it into my other table on the same line as the traceabilty code. so i can then change the value of (stock) on the first table with a sum of (original length(M))-Length on the second table.

I thought if i attacked it one thing at a time and break it up it would be alot easier, but i seem to have jumped into one huge pothole and confused myself with code

Sorry if i sound abit dumb right now but thanks so much for your time and help

 

[LukeH]

Hi I figured it out i needed to use the update function rather than insert.

"UPDATE tbl_booked_out INNER JOIN tbl_barstock" & vbCrLf & _
"ON tbl_booked_out.[Traceability Code] = tbl_barstock.[Traceability Code]" & vbCrLf & _
"SET tbl_booked_out.[Original Length(M)] = [tbl_barstock].[Stock]"