Looking for some clarification on how to write some VBA code for a task I am trying to perform. I know this is probable covered numerous times on these forums, when I performed a search I did not come up with anything that was matching to my problem. The bad thing is that this is such a simple task, but I can’t seem to grasp the VBA code concept.
I have a Purchasing agent that likes to change the type of materials we use to manufacture our products for cost reasons. I have a table that stores various information about are parts and one of the fields is a material field. When the Purchasing Agent buys a different brand of material, I have to go in and change all the material in the table that matches the type of new material bought. I can do this easily though and update query, but I am not the person that is supposed to be maintaining the database, another department is. My thought is that if I create a simple form where the other department managers just needs to pick the current material used from a list or combo box then type in the replacement material into an unbound text box, hit and update command button to update all the related records in the material field I can go about doing what I am supposed to do and not be bothered with other peoples work.
I created a simple form called “frmUpdateMaterial” and added a combo box called “cmbCurrentMaterial” that pulls the list of material from the “tblMoldSetUpSheet” table, a unbound text box called “txtNewMaterial” as well as a command button called “cmdUpdate.”
The idea is for the user to select a material that needs to be replaced from the drop down list in the “Current Material” combo box, type the new material in the “New Material” text box, then click on the “Update Material” command button to update all the records in the Material field that correspond to the current material and replace them with the new material. I need to write a VBA code in the “On Click” event that will perform the update. Problem I am running into is that I have various books that show me how to do this, but they all say different things. I tried them all, but don’t get any results but compile errors. Below are the various ways that the books tell me to do this.
Private Sub cmdUpdate_Click()
DoCmd.RunSQL “UPDATE tblMoldSetUpSheet SET tblMoldSetUpSheet.Material = Me.txtNewMaterial
WHERE (((tblMoldSetUpSheet.Material)= Me.cmbCurrentMaterial))
End Sub
Another book has it set up this way:
Private Sub cmdUpdate_Click()
Dim sql As String
Dim rsUpdate As New ADODB.Recordset
sql= SELECT Material From tblMoldSetUpSheet Where Material = “ & Val(Me.cmdCurrentMaterial.Value)
rsUpdate.Open sql, remoteConnecton, , , adCmdText
With rsUpdate
!Material = Me.txtNewMaterial
.Update
.Close
End With
Exit Sub
And yet another book describes using this code
Dim rsUpdate As ADODB.Recordset
Dim strSQL As String
strSQL = UPDATE tblMoldSetUpSheet SET txtNewMaterial = ‘ “ & Me.txtNewMaterial WHERE Material = “ & Me!cmdCurrentMaterial
rs.Update
rs.Close
End Sub
What is the actually way to perform the task I am trying to accomplish? If you need anymore information, please let me know.
Thanks
Steve
I have a Purchasing agent that likes to change the type of materials we use to manufacture our products for cost reasons. I have a table that stores various information about are parts and one of the fields is a material field. When the Purchasing Agent buys a different brand of material, I have to go in and change all the material in the table that matches the type of new material bought. I can do this easily though and update query, but I am not the person that is supposed to be maintaining the database, another department is. My thought is that if I create a simple form where the other department managers just needs to pick the current material used from a list or combo box then type in the replacement material into an unbound text box, hit and update command button to update all the related records in the material field I can go about doing what I am supposed to do and not be bothered with other peoples work.
I created a simple form called “frmUpdateMaterial” and added a combo box called “cmbCurrentMaterial” that pulls the list of material from the “tblMoldSetUpSheet” table, a unbound text box called “txtNewMaterial” as well as a command button called “cmdUpdate.”
The idea is for the user to select a material that needs to be replaced from the drop down list in the “Current Material” combo box, type the new material in the “New Material” text box, then click on the “Update Material” command button to update all the records in the Material field that correspond to the current material and replace them with the new material. I need to write a VBA code in the “On Click” event that will perform the update. Problem I am running into is that I have various books that show me how to do this, but they all say different things. I tried them all, but don’t get any results but compile errors. Below are the various ways that the books tell me to do this.
Private Sub cmdUpdate_Click()
DoCmd.RunSQL “UPDATE tblMoldSetUpSheet SET tblMoldSetUpSheet.Material = Me.txtNewMaterial
WHERE (((tblMoldSetUpSheet.Material)= Me.cmbCurrentMaterial))
End Sub
Another book has it set up this way:
Private Sub cmdUpdate_Click()
Dim sql As String
Dim rsUpdate As New ADODB.Recordset
sql= SELECT Material From tblMoldSetUpSheet Where Material = “ & Val(Me.cmdCurrentMaterial.Value)
rsUpdate.Open sql, remoteConnecton, , , adCmdText
With rsUpdate
!Material = Me.txtNewMaterial
.Update
.Close
End With
Exit Sub
And yet another book describes using this code
Dim rsUpdate As ADODB.Recordset
Dim strSQL As String
strSQL = UPDATE tblMoldSetUpSheet SET txtNewMaterial = ‘ “ & Me.txtNewMaterial WHERE Material = “ & Me!cmdCurrentMaterial
rs.Update
rs.Close
End Sub
What is the actually way to perform the task I am trying to accomplish? If you need anymore information, please let me know.
Thanks
Steve
