textbox populated from combobox not storing value

[pukhton]

I am having a problem with my Access database, since I am new to this, I have no idea what is goign on????

okay here is the problem.

I have a database which has Meds tables, and has two fields.
1- Medication_Name
2-Concentration

On the form I have a combo box for Medication_Name (it has all the medication names form the Medication_Name field), and textbox for Concentration. I want my User when they pick the specific medication, the concentration textbox should get populated by itself. So, I was playing around with it, i got that part working, but the problem I am having is when the textfield for Concentration gets populated, its not saving in the main table. Is there a way we could do that?

please help me.

Thanks

[AccessIdiot]

What's the control source for the textbox?

[pukhton]

thanks for the reply. If i put the name of the field in the control source It will save in the table, but if i put this =DLookUp("Conce ntration","medq uery1") under control source I will the result, but it will not save into the table.

its kind of complicated to explain..


take a look of this too, it migh help you.

Private Sub Medication_Name _Change()
' DoCmd.OpenQuery "medquery1"
Me!Concentratio n.RequeryEnd Sub

and thats what i have under Query

SELECT Meds.Concentrat ion
FROM Meds
WHERE (((Meds.Medicat ion_Name)=[Forms]![MainTable1]![Medication Name]));

[pukhton]

please I need HELP regarding this..

[FishVal]

thanks for the reply. If i put the name of the field in the control source It will save in the table, but if i put this =DLookUp("Conce ntration","medq uery1") under control source I will the result, but it will not save into the table.

its kind of complicated to explain..


take a look of this too, it migh help you.

Private Sub Medication_Name _Change()
' DoCmd.OpenQuery "medquery1"
Me!Concentratio n.RequeryEnd Sub

and thats what i have under Query

SELECT Meds.Concentrat ion
FROM Meds
WHERE (((Meds.Medicat ion_Name)=[Forms]![MainTable1]![Medication Name]));

Something like this.

 
Private Sub Medication_Name_AfterUpdate()
 
    Me!Concentration = DLookup("[Concentration]", "[Meds]", _
        "[Medication_Name] = '" & Me![Medication_Name]) & "'"
 
End Sub
 
 

[pukhton]

can u plz explain this little bit more, because i am still not getting the right answer.

[FishVal]

can u plz explain this little bit more, because i am still not getting the right answer.

This sub is fired on AfterUpdate event in your combo and change the value of the text box to Concentration that matches Medical_Name in you table.

Assumed that
1) the type of Meds.Medical_Na me is text
2) combo Medication_Name .BoundColumn is a column corresponding to Meds.Medical_Na me

Take into account that DLookup function returns the first matching value only.

Good luck.

[pukhton]

This sub is fired on AfterUpdate event in your combo and change the value of the text box to Concentration that matches Medical_Name in you table.

Assumed that
1) the type of Meds.Medical_Na me is text
2) combo Medication_Name .BoundColumn is a column corresponding to Meds.Medical_Na me

Take into account that DLookup function returns the first matching value only.

Good luck.

I just copy this into the Medication_Name "after update event" but i am getting this error, i am really sorry if i am getting pain the neck

It says Microsoft Access cant find the field 'Medication_Nam e" reffered to in your expression.
I am sure I do have this field name Under Meds table.

Any help please???

[FishVal]

I just copy this into the Medication_Name "after update event" but i am getting this error, i am really sorry if i am getting pain the neck

It says Microsoft Access cant find the field 'Medication_Nam e" reffered to in your expression.
I am sure I do have this field name Under Meds table.

Any help please???

I can suggest you the following another way.

1) change in design view the following Medication_Name combo properties
.RowSource="SEL ECT Meds.Concentrat ion, Meds.Medication _Name FROM Meds; "
.BoundColumn=2
.ColumnCount=2
.ColumnWidths=0 ;10

2) write another AfterUpdate event handler

Private Sub Medication_Name_AfterUpdate()
 
    With Me.Medication_Name
        Me.Concentration = .Column(0, .ListIndex)
    End With
 
End Sub
 

I've checked this. It works fine.

Good Luck.

PS. If your combo is not bound to table, the code will be even more simple.

[pukhton]

I can suggest you the following another way.

1) change in design view the following Medication_Name combo properties
.RowSource="SEL ECT Meds.Concentrat ion, Meds.Medication _Name FROM Meds; "
.BoundColumn=2
.ColumnCount=2
.ColumnWidths=0 ;10

2) write another AfterUpdate event handler

Private Sub Medication_Name_AfterUpdate()
 
    With Me.Medication_Name
        Me.Concentration = .Column(0, .ListIndex)
    End With
 
End Sub
 

I've checked this. It works fine.

Good Luck.

PS. If your combo is not bound to table, the code will be even more simple.

sorry I tried that, but still nothing...i am sure its me. I am not sure how to fix this crap. I will appreciate it if i can send yout the file on your email id, take a look, and see what am i doing wrong?

thanks
let me know plz.