Update Field records from a form

Steve67

Registered User.
Local time
Today, 08:12
Joined
Oct 2, 2007
Messages
21
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.”

Form.jpg


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
 
Actually any of them could work (more than 1 way to skin a cat), but each has a syntax error or two. Try this:

DoCmd.RunSQL “UPDATE tblMoldSetUpSheet SET Material = '" & Me.txtNewMaterial & "' WHERE Material = '" & Me.cmbCurrentMaterial & "'"

Text values are treated differently than numeric values; they need to be surrounded by single quotes.
 
Thanks.. Will give it a shot
 
Still getting a compile error, and it highlights the tblMoldSetUpSheet. The error says Expected: end of statement
 
What exactly is your code now?
 
I copied what you had and put in the on click event

Private Sub cmdUpdate_Click()

DoCmd.RunSQL “UPDATE tblMoldSetUpSheet SET Material = '" & Me.txtNewMaterial & "' WHERE Material = '" & Me.cmdCurrentMaterial & "'"

End Sub

I got a compilie error in VBE when I tried to enter the code. Right now I changed things up a little bit and have it partly working right now. The event goes through the motions and I don't get any errors, but it doesn't update anything and tells me that there are 0 records to update. Below is what I am change to code to.


Private Sub cmdUpdate_Click()

'update material
Dim mySQL As String

mySQL = "UPDATE tblMoldSetUpSheet"
mySQL = mySQL + " SET tblMoldSetUpSheet.Material = '" & Me.txtNewMaterial & "'"
mySQL = mySQL + " WHERE Material = '" & Me.cmbCurrentMaterial & "'"

DoCmd.RunSQL mySQL

End Sub
 
I note that you have 2 different names, so one of them is wrong:

cmdCurrentMaterial
cmbCurrentMaterial

That might be causing the compile error. The second one basically creates the same SQL. Is it possible the update happened once and now there are no records to update? If not, add this line after the string is set and we can examine the final SQL:

Debug.Print mySQL
 
Yeah I seen the miss-spelling of the cmbCurrentMaterial. That was just a mistype on my part in the thread. It is spelled right in the code.

I didn't notice any updates to the table when the event ran. Will try the debug print and see what it comes up with..

Thanks
 
Here is what comes out on the Immediate Window

UPDATE tblMoldSetUpSheet SET tblMoldSetUpSheet.Material = 'Trial' WHERE tblMoldSetUpSheet.Material = '1428-2'

The Set function is right but the Where function is wrong. The 1428-2 is the part number field, not the Material field. For some reason the SQL statement is looking up the Part Number field not the Material field it looks like
 
Well I managed to get it working. I took a different approach. Instead of making the form based off the table, I made the form based off a query with just the material field listed. Then made the combo box from that query and the update worked. Now I just have to have get the combo box to re-populate itself with the new update.

Thanks for all your help
 
Fixed while I slept; excellent! Presumably the bound column of the combo was different than the visible column. Probably could have fixed it by using the column property of the combo to grab the correct column.
 

Users who are viewing this thread

Back
Top Bottom