iif and dlookp! (1 Viewer)

[Sonya_G]

My name is Sonya Geary, ad I have a question for you. With this letter is my form (doc1) and my query, I am using access. Ii have a insulin type combo box and two following text boxes for ratio and corr_ratio. I wan my users to choose a insulin type an click on the choice they want to make. From there the program takes over and looks up data the ratio and corr_ratio from the insulin query. I did the two look ups with dlookup and it works, however when I choose a different choice other than the first row of the table it doesn’t work.

What I would like to see is that the user chooses a insulin type (say NPH) an the lookup pulls the correct ratio and corr_ratio. I would like this say if they choose wrong the first time and had to choose again. I am wondering if a nested iif statement is the way to go? Now a iif statement has two parts, a true part and a false part, I am having trouble with the iif statement. Am I on the right track or is there another way?

 

[June7]

Really don't need DLookup. Review http://baldyweb.com/Autofill.htm

 

[Micron]

Not trying to suggest any other method, but this

when I choose a different choice other than the first row of the table it doesn’t work.

likely means you have no criteria in your lookup. That will give you the first record from a table or query. In the future you might want to consider posting what you tried (verbatim - copy & paste) otherwise we'll have to guess and probably will guess wrong.

 

[arnelgp]

your combobox RowSource should be based on your insulin Query.

select [insulin type], [ratio], [corr_ratio] from yourInsulineTableName;

you add code to combobox AfterUpdate Event to show Ratio and Corr_Ratio to corresponding Textboxes:

Private Sub cboInsuline_AfterUpdate()
If cboInsuline.ListIndex > -1 Then
     Me.RatioTextbox = Me.Combobox1.Column(1)
     Me.Corr_RatioTextbox = Me.cboInsuline(2)
Else
     Me.RatioTextbox = ""
     Me.Corr_RatioTextbox = ""
End If
End Sub

 

[June7]

VBA only needed if you need to save values. No need to save unless values for that particular insulin type would be changed in future.

 

[Sonya_G]

your combobox RowSource should be based on your insulin Query.

select [insulin type], [ratio], [corr_ratio] from yourInsulineTableName;

you add code to combobox AfterUpdate Event to show Ratio and Corr_Ratio to corresponding Textboxes:

Private Sub cboInsuline_AfterUpdate()
If cboInsuline.ListIndex > -1 Then
     Me.RatioTextbox = Me.Combobox1.Column(1)
     Me.Corr_RatioTextbox = Me.cboInsuline(2)
Else
     Me.RatioTextbox = ""
     Me.Corr_RatioTextbox = ""
End If
End Sub

Thanks, this code works!