468,316 Members | 2,053 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,316 developers. It's quick & easy.

Selecting field data from another Table

Hi everyone

This is probably really simple, but it has me stumped.
I have a form which gets its data from a table called "Dentistry", this table is linked to another table called "SurgeryInventory"(this table has a list of various Surgery procedures and there related cost value for each procedure).

Each Procedure in the "SurgeryInventory" Table is assigned a CategoryID so i can easily filter the combo's in the form to only list the procedures relevent to the data required for the"Dentistry" information.

When i select a procedure from the combobox on the form i would like the AfterUpdate Event for the combo to select the relevent Cost value (a field in "SurgeryInventory") and display that value in an Unbound TextBox on the Form. Note. there are numerous different procedure combos the user selects on the Dentistry Form and i need to extract the cost value for each.
I tried using a SELECT statement linking the ProcedureID selected on the Form to the ProcedureID in the "SurgeryInventory" to extract the Cost Field Value. I dont know if this is correct as i probably got the Syntax of the SQL SELECT statement wrong.

Any help would be appreciated
Mar 11 '07 #1
4 5577
MSeda
159 Expert 100+
Why not add the cost field as a column to the procedure combo box and set its width to zero (make sure you change the column count as well).
Then in the after update of the combo you can just use the expression
me.ubdText = me.procIDcbo.column(1)

of course change the names to match the ones on your form and remember the column numbering starts at 0 not 1.
Mar 11 '07 #2
Why not add the cost field as a column to the procedure combo box and set its width to zero (make sure you change the column count as well).
Then in the after update of the combo you can just use the expression
me.ubdText = me.procIDcbo.column(1)

of course change the names to match the ones on your form and remember the column numbering starts at 0 not 1.
Thanks for the quick response, wow, but i have found a solution, it was easy as i thought it would be, but only if you know where to look, i still have a lot to learn and only tried this by accident. my solution was :

Expand|Select|Wrap|Line Numbers
  1. Private Sub CPF_Change()
  2.   Me.CPFcost = DLookup("[Cost]", "SurgeryInventory", "[SurgeryID]=" & Me.CPF)
  3.   Me.CPFcost.Requery
  4. End Sub
Mar 11 '07 #3
MSeda
159 Expert 100+
Glad you got it worked out! dlookup is definitely very handy.
Mar 11 '07 #4
NeoPa
32,075 Expert Mod 16PB
Thanks for the quick response, wow, but i have found a solution, it was easy as i thought it would be, but only if you know where to look, i still have a lot to learn and only tried this by accident. my solution was :

Expand|Select|Wrap|Line Numbers
  1. Private Sub CPF_Change()
  2.   Me.CPFcost = DLookup("[Cost]", "SurgeryInventory", "[SurgeryID]=" & Me.CPF)
  3.   Me.CPFcost.Requery
  4. End Sub
While your approach will work, MSeda's idea is better practice. It may not matter much in your current circumstances, but if you're ever working with larger tables, you'll notice the performance hit of using DLookup() as an extra step after already populating the ComboBox anyway.
Mar 12 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

5 posts views Thread by uthuras | last post: by
6 posts views Thread by aaj | last post: by
4 posts views Thread by Jeffrey Davis | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.