If Statement Puts Values In Form

[leahmw]

Quick question, new to Access... I have a search form with a subform, and when I double-click an option in the subform list-box, I would like it to fill in the appropriate boxes on another form.

I have three different sets of boxes, and each has a button. Each button calls the same form, because I don't want to have three identical search forms.

I need working code to fill in the appropriate set of boxes, based on the name of the button that called it. Aka my button will be named bttn_occ_search1, the corresponding boxes are named bttn_occ_cd1, bttn_occ_name1.

It *almost* works... I can get it to fill in the form, but unfortunately it fills in all three sets of boxes with the result all the time... and changes all of them when I go to change one. I don't want the boxes to change until I push the specific button to change that specific set. They have to be independent in a sense.

I have an if statement, I'm not sure if this is the most appropriate way to try and do this... help or another method would be fantastic!

If called_by = name_bttn0 Then
[Forms]![contract]![tb_occ_cd0] = lb_trade_results.Column(0)
[Forms]![contract]![tb_occ_name0] = lb_trade_results.Column(1)
End If
If called_by = name_bttn1 Then
[Forms]![contract]![tb_occ_cd1] = lb_trade_results.Column(0)
[Forms]![contract]![tb_occ_name1] = lb_trade_results.Column(1)
End If
If called_by = name_bttn2 Then
[Forms]![contract]![tb_occ_cd2] = lb_trade_results.Column(0)
[Forms]![contract]![tb_occ_name2] = lb_trade_results.Column(1)
End If

 

[lameid]

You could go the other way... Put the code that fills in the values on the button that calls it. That's probably the easiest way.


Perhaps more versatile would be to set a Gloabal variable to what you want to do. This is only more versatile if multiple things need to do the same form and therefore having them on there own click event would be redundant.

Global called_by as String 'in some module somewhere but not in a procedure

'in your procedure assuming something like the on load event

Select Case called_by
Case "name_bttn0"
    [Forms]![contract]![tb_occ_cd0] = lb_trade_results.Column(0)
    [Forms]![contract]![tb_occ_name0] = lb_trade_results.Column(1)

Case "name_bttn1"
    [Forms]![contract]![tb_occ_cd1] = lb_trade_results.Column(0)
    [Forms]![contract]![tb_occ_name1] = lb_trade_results.Column(1)

Case "name_bttn2 Then"
    [Forms]![contract]![tb_occ_cd2] = lb_trade_results.Column(0)
    [Forms]![contract]![tb_occ_name2] = lb_trade_results.Column(1)
End Select

'Don't forget to populate that Global variable.
Sub name_bttn1_Click
    
    called_by = "name_bttn1"
End Sub

Also, there are several Access forums on Tek-tips. Posting Access questions there may get you faster results. I checked this Forum on a lark.