I need a text field get updated when an item is picked from the combo box (1 Viewer)

[shan_vba]

Hello all!
This is my problem I am currently having.... I have a table called "Summary" and it contains a fields called "Drug" and "Quantity" where I created a form by using the wizard and changed the text field of the drugs to a combo box.... and it loads the drugs in it... and there is another text box created for quantity.. but when I change the item in the combo box the value of the quantity field remains the same. I need it to be changed accordingly when the combo box value is changed. I tried to assign control source property of the quantity text field in the onChange event of the combo box, but I don't know how to do it correctly... Please help.. I have attached the db file here

 

[Ranman256]

they query in the combo box would hold 2 fields from tSummary: Drug, Qty
in the combo properties, set the :
column count = 2
you can hide the 2nd column from users by setting col widths: 1 ; 0
bound column is still 1

then in the AFTERUPDATE event of the combo box ,fill in the Qty txt box.
(select combo, show properies , Events tab, in Afterupdate event, select Event Procudure, click the elipsis button at the end of the event combo box)

'NOTE: in vb, columns begin with 0, so column 2 will be column(1) in vb code:

Private Sub cboDrug_AfterUpdate()
   txtQty = cboDrug.column(1)
End Sub

 

[June7]

ControlSource property cannot be set to an SQL statement.

Not really making sense that Summary is RecordSource for form as well as RowSource for combobox.

 

[shan_vba]

Thanks for the reply I tried the code below but still the qty text box becomes #Name?

Code Tags Added by UG

Private Sub Drug_Change()

Dim Dbs As Database
Dim Rst As Recordset
Dim MyString As String
Dim valId As String

Set Dbs = CurrentDb()
valId = Me.Drug.Value
MyString = "SELECT Quantity FROM Summary WHERE Drug =" + "'" + valId + "';"

Set Rst = Dbs.OpenRecordset(MyString)
Rst.Requery

MsgBox (MyString)
Me.qty.ControlSource = MyString

Me.qty.Requery

As you said if the control source can't be set to an SQL then how to set the retrieved value there please....

I tried this as Runman said (Thank you) but it gives an error

Private Sub cboDrug_AfterUpdate()
   txtQty = cboDrug.column(1)
End Sub

End Sub

 

[June7]

Why is there only the one table? Why is there not a table of Drugs as source for combobox? What is purpose of Summary table?

You get an error because you don't have a field or control named txtQty.

Why are you using Change event? That was not recommended.

 

[shan_vba]

Why is there only the one table? Why is there not a table of Drugs as source for combobox? What is purpose of Summary table?

You get an error because you don't have a field or control named txtQty.

No in that I changed the names to match the names in my form.. so the error is not due to that.....

Why can't I do it with one table ? The purpose of this very simple application is to have two forms one is to enter data to the Summary table and the other is to In OUT operation where user can take drugs our or put drugs in after selecting the drug with the combo box (Or later on I may need to have a like search in a text box to retrieve values to a list box and select from that) so once taken drugs out the quantity should be updated... I'm totally new to VBA and Access in this level.. Please help

 

[shan_vba]

Thank you all for the support

Code Tags Added by UG

Private Sub Drug_Change()
Dim Dbs As Database
Dim Rst As Recordset
Dim MyString As String
Dim valId As String

Set Dbs = CurrentDb()
valId = Me.Drug.Value
MyString = "SELECT Quantity FROM Summary WHERE Drug =" + "'" + valId + "';"

Set Rst = Dbs.OpenRecordset(MyString)

Me.qty.Value = Rst.Fields(0).Value

End Sub

This worked

 

[arnelgp]

firstly, table Summary is not normalized.
you should at least have 3 tables there:

tblDrug
DrugID (autonumber, PK)
DrugName (text)

tblPharma
PharmaID (autonumber, PK)
PharmaName (text, pharmacy name)

tblTrans (In /Out table)
ID (autonumber)
DrugID (Long, FK)
PharmaID (Long, FK)
Quantity (double) (negative values as Out)

"Summary" can be achieved by making a Query (Total Query) against table tblTrans.

 

[June7]

As arnelgp post describes, your In/Out activity should be recording transaction records, not changing a saved value. If you don't use transactions there is no way to validate the saved value - there is no 'history' to base this number on. The balance should be calculated based on transactions. Review http://allenbrowne.com/AppInventory.html