updating textbox value onchange from combobox

[brad78]

Hey everyone,
I have a pretty good question this time.

I have a combobox on a form that lists serveral different content types (categories). These content types have different review periods and when the content type changes in the combobox, I am trying to requery and update the text box with the appropriate review period value but could use some help. I don't know how to retrieve the review period value from the query and assign it to the textbox value. Any help would be appreciated.

My Table Structure.
general 6 months
overview 6 months
guidelines 1 year


Code I'm working on.

Private Sub content_type_Change()
 
Dim period As String
Dim content_type_var As String
Dim selected_type As String
Dim strSQLSelType As String
 
'content_type_var is current value from combobox
content_type_var = Me.content_type 
 
'build query    
strSQLSelType = "select as selected_type(content_review_period) from content_type where content_type = 'content_type_var'"
 
DoCmd.RunSQL (strSQLSelType)
 
MsgBox (selected_type)
 
Me.content_review_period.Value = selected_type
End Sub

Thanks in advance

 

[WayneRyan]

Brad,

Don't use the OnChange event, that's only for keyboard entry and will fire
for EACH character typed.

Use the BeforeUpdate event of your combobox:

Me.content_review_period = Nz(DLookUP("[Selected_type]", "Content_type", "[content_type] = '" & Me.content_type & "'"), "No Content")

Me.content_review_period is your textbox.
[Selected_type] is the field in your content_type table to assign to your textbox.
"Content_type" is your table.
Me.content_type is your combobox.

Wayne

 

[brad78]

Thanks WR, I didn't realize that onchange was for a keyboard event. I'll give this a try. Thanks for the help!
Brad

Brad,

Don't use the OnChange event, that's only for keyboard entry and will fire
for EACH character typed.

Use the BeforeUpdate event of your combobox:

Me.content_review_period = Nz(DLookUP("[Selected_type]", "Content_type", "[content_type] = '" & Me.content_type & "'"), "No Content")

Me.content_review_period is your textbox.
[Selected_type] is the field in your content_type table to assign to your textbox.
"Content_type" is your table.
Me.content_type is your combobox.

Wayne