How to link textboxes to SQL strings ??

[arunb]

Hi,

How do I link the values in a textbox on a form to an entry in the SQL string.

For example:

UPDATE tlbPartsTable SET tlbPartsTable.ItemsInStock =

[tlbPartsTable].[ItemsInStock]-frmReqTransaction.txtQuantity
WHERE ((([tlbPartsTable].[PartID])=frmReqTransaction.txtPartID));

Here frmRequistion is the form (It is not linked to any data source)...
txtQuantity, txtPartID are textboxes...
tlbPartTable is the table where data is retrieved....

thanks
arun

[salvelinus]

' In code
"UPDATE tlbPartsTable SET tlbPartsTable.ItemsInStock = " & _
"[tlbPartsTable].[ItemsInStock]- " & _
Val(frmReqTransaction.txtQuantity.Text) & _
" WHERE ((([tlbPartsTable].[PartID])= " & _
Val(frmReqTransaction.txtPartID)));

This assumes your fields in the table are numeric, which is reasonable since you're subtracting them. The values in your textboxes are string data types, which can't be subtracted, or compared meaningfully, from numeric data types. You have to convert them. You could use CInt(), CDbl(), etc., instead of Val() if needed.
Also make sure of your tablename -tbl is the usual prefix, not tlb.

[arunb]

Can the same code be implemented in Access using the script editor and not VB6. ??

[salvelinus]

If by script editor you mean VBA, yes, that's how I'd do it. You could also create it as a saved query, without the double quotes.

[arunb]

Thanks a lot it worked well.

I changed the string to

UPDATE tblPartsTable SET tblPartsTable.ItemsInStock = [tblPartsTable].[ItemsInStock]-Val([Forms]![frmReqTransaction]![txtQuantity])
WHERE ((([tblPartsTable].[PartID])=[Forms]![frmReqTransaction]![txtPartID]));

It seems the Forms! entry removed the Parameter Dialog box and I could get the values straight away from the textboxes.

Will post further questions if required....

thanks
arunb

[salvelinus]

Great. If this is VBA code in an event procedure of the form, you can replace Forms with Me.

[RiceRocket]

This is because the DBEngine does not recognize what "frmReqTransaction.txtPartID" is.

'Try replacing frmReqTransaction.txtPartID with 
Forms("frmReqTransaction")!txtPartID ' or 
[Form_frmReqTransaction]!txtPartID ' or 
Forms!frmReqTransaction!txtPartID
' all of these reference the same textbox

If this doesn't work, you may have to create a table that is the data source for frmReqTransaction. Because your textbox value may be temporary, storing the textbox value in a table will provide a way for you to access the data via a SQL string. You can even accomplish this by having only one record in that table, and overwriting the values for that one record every time your form is changed (using events).

Hope this helps...

[RiceRocket]

I guess I didn't read your last post closely enough... good job on getting it to work!

[arunb]

I would like to now modify the sql string

UPDATE tblPartsTable SET tblPartsTable.ItemsInStock = [tblPartsTable].[ItemsInStock]-Val([Forms]![frmReqTransaction]![txtQuantity])
WHERE ((([tblPartsTable].[PartID])=[Forms]![frmReqTransaction]![txtPartID]));

so that entries like txtQuantity, txtPartID are inserted in a new record in another table tblRequistions, this table initially contains just one record.

So now if the sql query finds a matching PartID, the entries like txtQuantity, txtPartID are added to a new record in the tblRequistions table. In case of unmatched PartID no entry should be made.

I am not sure if this will work, I checked out some previous threads an there i says that an Insert and Update command cannot be performed in a single sql query string.. Is this so ???

Kindly advise what other methods exist???

thanks
arun