By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,948 Members | 900 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,948 IT Pros & Developers. It's quick & easy.

Selecting field data from another Table

P: 5
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
Share this Question
Share on Google+
4 Replies


MSeda
Expert 100+
P: 159
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

P: 5
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
Expert 100+
P: 159
Glad you got it worked out! dlookup is definitely very handy.
Mar 11 '07 #4

NeoPa
Expert Mod 15k+
P: 31,342
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.