Command Button bound to Table

[tmdrake]

I have three (3) command buttons on a form that perform various task, is there a way to record the button choosen by the user to a table? Any help is greatly appreciated.

 

[pr2-eugin]

You can use the <controlName>.Name property to obtain the Button(control) name.. It depends how you wish to update..

Do the three button call a Same procedure?

 

[nschroeder]

It also depends on if the form is bound to the table you're updating, and if you can only click one button or several. If it's a bound form, and only one button can be clicked, then you may want to switch from command buttons to option buttons in a frame control, and bind the frame control to the field in your table.

If you still need command buttons, then put the code in your Click events. If you can only click one of them, and you want the result stored in a single field:

Sub Command1_Click()
    Call ButtonClicked(1)
End Sub
Sub Command2_Click()
    Call ButtonClicked(2)
End Sub
Sub Command2_Click()
    Call ButtonClicked(3)
End Sub

Private Sub ButtonClicked(iButton As Integer)
    ' ... update table field with iButton value
End Sub

If each button has it's own unique field, then skip the subroutine and just handle each click event separately.

 

[tmdrake]

No the buttons have different functions, below is the codes for the buttons:

"Good" Button

[COLOR=black][FONT=Times New Roman][SIZE=3][FONT=Times New Roman] [/FONT][/SIZE][/FONT][/COLOR][COLOR=black][FONT=Times New Roman][SIZE=3][FONT=Times New Roman]Private Sub GOOD_Click()
If Not IsNull(Me.SN.Value) Then
    LastPosSN = Me.SN.Value
End If
If PosPullTestCount = Me.PullTestNum.Value Then
    MsgBox "Time to perform a pull-test on a scrap cell!"
    PosPullTestCount = 1
    Me.Text30.Value = Me.PullTestNum.Value
    DoCmd.OpenForm "GeneralTabPullTest"
       
Else
    PosPullTestCount = PosPullTestCount + 1
    Me.Text30.Value = Me.PullTestNum.Value
End If[/FONT][/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3][FONT=Times New Roman]DoCmd.GoToRecord , , acNewRec
Me.CreationDate = Now()
Forms!GeneralTabPullTest!LotNumber = Me.LotNumber[/FONT][/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3][FONT=Times New Roman]End Sub[/FONT][/SIZE][/FONT][/COLOR]

"POP" button

[COLOR=black][FONT=Times New Roman][SIZE=3][FONT=Times New Roman]POP Button[/FONT][/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][FONT=Times New Roman][SIZE=3]Private Sub POP_Click()
DoCmd.OpenForm "POPResults"
 
End Sub[/SIZE][/FONT][/FONT][/COLOR]

"Other" Button

[COLOR=black][FONT=Times New Roman][SIZE=3][FONT=Times New Roman]Private Sub Other_Click()
DoCmd.OpenForm "OtherComments"
DoCmd.GoToRecord , , acNewRec
Me.CreationDate = Now()[/FONT][/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3][FONT=Times New Roman]End Sub[/FONT][/SIZE][/FONT][/COLOR]

I thought that Access automatically saves, but I guess not.

Thanks

 

[nschroeder]

Automatically saves what? If data is entered into a record and you Tab or Return out of that record (or close the form), the data is saved automatically.

You didn't answer if they can click more than one button, but I assume they can. You can either have a separate field in the table for each button, and in the click event, update that button's associated field, or if you want more of an event log effect, use the subroutine method to add a log record to the table for each button clicked, maybe with a time stamp.

 

[tmdrake]

I'm sorry, they can only click one button per record.

 

[nschroeder]

So are you still unsure how to proceed, or do you have it? Are you trying to put the click results in the table associated with the current form, or in a different table? What type of results do you want stored?

 

[tmdrake]

I'm still not sure where to put the code among the code already there. I am trying to put the click results in the table bound to the form where the button is located. I just want the name of the button to be stored in the table, ie "Good", "POP, "Good".

The code already there is on the function "On Click" property, do I put the code there or in another property line.

Thanks so much for your assistance.

 

[nschroeder]

Assuming you have a text field in your table for that purpose, add a textbox bound to the field to some random place on your form, and set the Visible property to No. Then modify the code:

Private Sub Good_Click()
    ... your code ...
    Call ButtonClicked("Good")
End Sub
Private Sub Pop_Click()
    ... your code ...
    Call ButtonClicked("Pop")
End Sub
Private Sub _Click()
    ... your code ...
    Call ButtonClicked("Other")
End Sub

Private Sub ButtonClicked(strButton As String)
    formfield.Value = strButton
End Sub

You could also just put formfield.Value = "Good", etc., in your click events, but I like the above because it allows for additional code to be added/maintained at a single location instead of three.

 

[tmdrake]

Did what you suggested; however I recived an error "Runtime error '424': object required. Here is where I place the code you suggested. What am I doing wrong?

[COLOR=black][FONT=Times New Roman][SIZE=3][FONT=Times New Roman]Private Sub GOOD_Click()
DoCmd.RunCommand acCmdSaveRecord[/FONT][/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][SIZE=3][FONT=Times New Roman]If Not IsNull(Me.SN.Value) Then
    LastPosSN = Me.SN.Value
End If
If PosPullTestCount = Me.PullTestNum.Value Then
    MsgBox "Time to perform a pull-test on a scrap cell!"
    PosPullTestCount = 1
    Me.Text30.Value = Me.PullTestNum.Value
    DoCmd.OpenForm "GeneralTabPullTest"
    DoCmd.GoToRecord , , acNewRec
    Me.CreationDate = Now()
    Forms!GeneralTabPullTest!LotNumber = Me.LotNumber[/FONT][/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman]Else
    PosPullTestCount = PosPullTestCount + 1
    Me.Text30.Value = Me.PullTestNum.Value
End If
    Call ButtonClicked("Good")
    
End Sub
Private Sub ButtonClicked(strButton As String)
    Formfield.Value = "GOOD"
    
End Sub[/FONT][/COLOR]

There error appears to be of the "Private Sub ButtonClicked (strButton As String)
FormField.value = "Good"

Tried it both ways and received error each time.

Thanks

 

[nschroeder]

I just threw "formfield" in there. You need to replace it with the actual name of the hidden textbox on the form.

 

[tmdrake]

It worked perfectly when I put the code in the first button "Good", however when I put in the OnClick for the second button 'POP" I got the following error: "Ambiguous name detected: ButtonClicked"

this is the code:

[COLOR=black][FONT=Times New Roman][SIZE=3][FONT=Times New Roman]Private Sub POP_Click()
DoCmd.RunCommand acCmdSaveRecord
DoCmd.OpenForm "POPResults"[/FONT][/SIZE][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][FONT=Times New Roman][SIZE=3]Call ButtonClicked("POP")
 End Sub
[/SIZE][/FONT][/FONT][/COLOR]
[COLOR=black][FONT=Times New Roman][FONT=Times New Roman][SIZE=3][/SIZE][/FONT][/FONT][/COLOR] 
[COLOR=black][FONT=Times New Roman][FONT=Times New Roman][SIZE=3]Private Sub ButtonClicked(strButton As String)
    Me.Text52.Value = "POP"
    
End Sub[/SIZE][/FONT]


[/FONT][/COLOR]

I not sure why this didn't work.

Thanks

 

[nschroeder]

Please refer back to my earlier post containing the code. The ButtonClicked subroutine should only be there once. And it needs to use the strButton parameter, rather than a hard-coded "POP" value, or there's no purpose in having a subroutine.

 

[tmdrake]

Okay, I been trying to figure this out with bothering you and appearing to be stupid, but I can't get it. You assistance is truly appreaciated. This is the code for all three buttons and the invisible text box. Please tell what I'm doing wrong. The code for all three is located on the "on click" event.

"Good" Button

Private Sub Command57_Click()
DoCmd.RunCommand acCmdSaveRecord
If Not IsNull(Me.SN.Value) Then
    LastPosSN = Me.SN.Value
End If
If PosPullTestCount = Me.PullTestNum.Value Then
    MsgBox "Time to perform a pull-test on a scrap cell!"
    PosPullTestCount = 1
    Me.Text30.Value = Me.PullTestNum.Value
    DoCmd.OpenForm "GeneralTabPullTest"
    DoCmd.GoToRecord , , acNewRec
    Forms!GeneralTabPullTest!LotNumber = Me.LotNumber
    Forms!GeneralTabPullTest!CreationDate = Now()
Else
    PosPullTestCount = PosPullTestCount + 1
    Me.Text30.Value = Me.PullTestNum.Value
End If
Call ButtonClicked("Good")
End Sub

"POP" button

Private Sub POP_Click()
DoCmd.RunCommand acCmdSaveRecord
MsgBox "Clean Electrodes NOW"
If Not IsNull(Me.SN.Value) Then
    LastPosSN = Me.SN.Value
End If
If PosPullTestCount = Me.PullTestNum.Value Then
    MsgBox "Time to perform a pull-test on a scrap cell!"
    PosPullTestCount = 1
    Me.Text30.Value = Me.PullTestNum.Value
    DoCmd.OpenForm "GeneralTabPullTest"
    DoCmd.GoToRecord , , acNewRec
    Forms!GeneralTabPullTest!LotNumber = Me.LotNumber
    Forms!GeneralTabPullTest!CreationDate = Now()
Else
    PosPullTestCount = PosPullTestCount + 1
    Me.Text30.Value = Me.PullTestNum.Value
End If
Call ButtonClicked("POP")
End Sub

"Other" button

Private Sub Other_Click()
DoCmd.OpenForm "OtherComments"
DoCmd.GoToRecord , , acNewRec
Me.CreationDate = Now()
Call ButtonClicked("Other")
End Sub

Invisible Text Box

Private Sub Text55_Click()
Private Sub ButtonClicked(strButton As String)
    Me.Text55.Value = strButton
End Sub

I'm not a programmer, so please excuse my ignorance.

Thank you so much.

 

[nschroeder]

For future reference, please be specific when something doesn't work. We have no idea what that means. Did it blow up? If so, when, and what was the message? If the results not as expected, please explain.

In this case, I'm assuming it failed because you have a subroutine (Private Sub Text55_Click) started with no End Sub to follow. Not sure where that line came from, because it would be rare to put code in the click event of a text box, and impossible for the event to occur in this case because Text55 is hidden. Delete that line for starters.