combine code for one button

H

hisham

I want to combine code 3 button to 1 button.
Pls help me how to combine. Below is coding for 3 button.
*************************************************
Private Sub Command23_Click()
Dim dbs As Database, rst As Recordset
Dim strSelect As String


Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst![Item1] = rst![Item2] Then
rst![StatusItem] = "0"
Else
rst![StatusItem] = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for item done!!!")
End Sub

Private Sub Command24_Click()
Dim dbs As Database, rst As Recordset
Dim strSelect As String


Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst![Qty1] = rst![Qty2] Then
rst![StatusQty] = "0"

Else
rst![StatusQty] = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for quantity done!!!")
End Sub

Private Sub Command25_Click()
Dim dbs As Database, rst As Recordset
Dim strSelect As String


Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst![UoM1] = rst![UoM2] Then
rst![StatusUoM] = "0"
Else
rst![StatusUoM] = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for UoM done!!!")
End Sub
 
M

Marshall Barton

hisham said:
I want to combine code 3 button to 1 button.
Pls help me how to combine. Below is coding for 3 button.
*************************************************
Private Sub Command23_Click()
Dim dbs As Database, rst As Recordset
Dim strSelect As String


Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst![Item1] = rst![Item2] Then
rst![StatusItem] = "0"
Else
rst![StatusItem] = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for item done!!!")
End Sub

Private Sub Command24_Click()
Dim dbs As Database, rst As Recordset
Dim strSelect As String


Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst![Qty1] = rst![Qty2] Then
rst![StatusQty] = "0"

Else
rst![StatusQty] = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for quantity done!!!")
End Sub

Private Sub Command25_Click()
Dim dbs As Database, rst As Recordset
Dim strSelect As String


Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst![UoM1] = rst![UoM2] Then
rst![StatusUoM] = "0"
Else
rst![StatusUoM] = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for UoM done!!!")
End Sub
Click to expand...


Sub SetStatus(strField As String)
Dim dbs As Database, rst As Recordset
Dim strSelect As String

Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst(strField & "1") = rst(strField & "2") Then
rst("Status" & strField) = "0"
Else
rst("Status" & strField) = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for " & strField & "done!!!")
End Sub

Then call the procedure using:

SetStatus "Item"
Or
SetStatus "Qty"
Or
SetStatus "UoM"
 
H

hisham

Marshall, pls teach me how to call procedure;

Then call the procedure using:
SetStatus "Item"
Or
SetStatus "Qty"
Or
SetStatus "UoM"
Click to expand...

Marshall Barton said:
hisham said:
I want to combine code 3 button to 1 button.
Pls help me how to combine. Below is coding for 3 button.
*************************************************
Private Sub Command23_Click()
Dim dbs As Database, rst As Recordset
Dim strSelect As String


Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst![Item1] = rst![Item2] Then
rst![StatusItem] = "0"
Else
rst![StatusItem] = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for item done!!!")
End Sub

Private Sub Command24_Click()
Dim dbs As Database, rst As Recordset
Dim strSelect As String


Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst![Qty1] = rst![Qty2] Then
rst![StatusQty] = "0"

Else
rst![StatusQty] = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for quantity done!!!")
End Sub

Private Sub Command25_Click()
Dim dbs As Database, rst As Recordset
Dim strSelect As String


Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst![UoM1] = rst![UoM2] Then
rst![StatusUoM] = "0"
Else
rst![StatusUoM] = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for UoM done!!!")
End Sub
Click to expand...


Sub SetStatus(strField As String)
Dim dbs As Database, rst As Recordset
Dim strSelect As String

Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst(strField & "1") = rst(strField & "2") Then
rst("Status" & strField) = "0"
Else
rst("Status" & strField) = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for " & strField & "done!!!")
End Sub

Then call the procedure using:

SetStatus "Item"
Or
SetStatus "Qty"
Or
SetStatus "UoM"
Click to expand...
 
M

Marshall Barton

Just replace all your original code in the button Click
events with the appropriate one line. For example:

Private Sub Command23_Click()
SetStatus "Item"
End Sub
--
Marsh
MVP [MS Access]

Marshall, pls teach me how to call procedure;

Then call the procedure using:
SetStatus "Item"
Or
SetStatus "Qty"
Or
SetStatus "UoM"
Click to expand...

Marshall Barton said:
hisham said:
I want to combine code 3 button to 1 button.
Pls help me how to combine. Below is coding for 3 button.
*************************************************
Private Sub Command23_Click()
Dim dbs As Database, rst As Recordset
Dim strSelect As String


Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst![Item1] = rst![Item2] Then
rst![StatusItem] = "0"
Else
rst![StatusItem] = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for item done!!!")
End Sub

Private Sub Command24_Click()
Dim dbs As Database, rst As Recordset
Dim strSelect As String


Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst![Qty1] = rst![Qty2] Then
rst![StatusQty] = "0"

Else
rst![StatusQty] = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for quantity done!!!")
End Sub

Private Sub Command25_Click()
Dim dbs As Database, rst As Recordset
Dim strSelect As String


Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst![UoM1] = rst![UoM2] Then
rst![StatusUoM] = "0"
Else
rst![StatusUoM] = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for UoM done!!!")
End Sub
Click to expand...


Sub SetStatus(strField As String)
Dim dbs As Database, rst As Recordset
Dim strSelect As String

Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst(strField & "1") = rst(strField & "2") Then
rst("Status" & strField) = "0"
Else
rst("Status" & strField) = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for " & strField & "done!!!")
End Sub

Then call the procedure using:

SetStatus "Item"
Or
SetStatus "Qty"
Or
SetStatus "UoM"
Click to expand...
Click to expand...
 
H

hisham

When I run this button, error (item not found in this collection) Arrow in;

---> If rst(strField & "1") = rst(strField & "2") Then..

Why?

Marshall Barton said:
Just replace all your original code in the button Click
events with the appropriate one line. For example:

Private Sub Command23_Click()
SetStatus "Item"
End Sub
--
Marsh
MVP [MS Access]

Marshall, pls teach me how to call procedure;

Then call the procedure using:
SetStatus "Item"
Or
SetStatus "Qty"
Or
SetStatus "UoM"
Click to expand...

Marshall Barton said:
hisham wrote:

I want to combine code 3 button to 1 button.
Pls help me how to combine. Below is coding for 3 button.
*************************************************
Private Sub Command23_Click()
Dim dbs As Database, rst As Recordset
Dim strSelect As String


Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst![Item1] = rst![Item2] Then
rst![StatusItem] = "0"
Else
rst![StatusItem] = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for item done!!!")
End Sub

Private Sub Command24_Click()
Dim dbs As Database, rst As Recordset
Dim strSelect As String


Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst![Qty1] = rst![Qty2] Then
rst![StatusQty] = "0"

Else
rst![StatusQty] = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for quantity done!!!")
End Sub

Private Sub Command25_Click()
Dim dbs As Database, rst As Recordset
Dim strSelect As String


Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst![UoM1] = rst![UoM2] Then
rst![StatusUoM] = "0"
Else
rst![StatusUoM] = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for UoM done!!!")
End Sub


Sub SetStatus(strField As String)
Dim dbs As Database, rst As Recordset
Dim strSelect As String

Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst(strField & "1") = rst(strField & "2") Then
rst("Status" & strField) = "0"
Else
rst("Status" & strField) = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for " & strField & "done!!!")
End Sub

Then call the procedure using:

SetStatus "Item"
Or
SetStatus "Qty"
Or
SetStatus "UoM"
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Do you mean you want to click one button and have all three updates occur?
If so, I would use an update query. It will be faster and will lead to less
bloating of your database.

Private Sub Command23_Click()
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSelect As String

Set dbs = CurrentDb
strSelect = "UPDATE P1Entry " & _
" SET StatusItem = Abs([Item1]<>[Item2])" & _
", StatusQty = Abs([qty1]<>[qty2])" & _
", StatusUOM = Abs([Uom1]<>[UoM2])"
dbs.execute strSelect, dbFailOnError

Msgbox dbs.RecordsAffected & " Records updated"

End Sub

Note that this could have a problem if any of the fields in the comparison
contain Nulls since the comparison will return Null. You can wrap the
comparison in the NZ function to return 0 or 1 depending on your wishes.

strSelect = "UPDATE P1Entry " & _
" SET StatusItem = NZ(Abs([Item1]<>[Item2]),1)" & _
", StatusQty = NZ(Abs([qty1]<>[qty2]),1)" & _
", StatusUOM = NZ(Abs([Uom1]<>[UoM2]),1)"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
M

Marshall Barton

When the error occurs, check the value of strField and make
sure that it agrees with the field name in the table. My
understanding is that you said the P1ENTRY table has fields
named Item1 and Item2, but the error says those are not the
names of the fields in the recordset.
--
Marsh
MVP [MS Access]

When I run this button, error (item not found in this collection) Arrow in;

---> If rst(strField & "1") = rst(strField & "2") Then..

Why?

Marshall Barton said:
Just replace all your original code in the button Click
events with the appropriate one line. For example:

Private Sub Command23_Click()
SetStatus "Item"
End Sub
Marshall, pls teach me how to call procedure;

Then call the procedure using:

SetStatus "Item"
Or
SetStatus "Qty"
Or
SetStatus "UoM"

:

hisham wrote:

I want to combine code 3 button to 1 button.
Pls help me how to combine. Below is coding for 3 button.
*************************************************
Private Sub Command23_Click()
Dim dbs As Database, rst As Recordset
Dim strSelect As String


Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst![Item1] = rst![Item2] Then
rst![StatusItem] = "0"
Else
rst![StatusItem] = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for item done!!!")
End Sub

Private Sub Command24_Click()
Dim dbs As Database, rst As Recordset
Dim strSelect As String


Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst![Qty1] = rst![Qty2] Then
rst![StatusQty] = "0"

Else
rst![StatusQty] = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for quantity done!!!")
End Sub

Private Sub Command25_Click()
Dim dbs As Database, rst As Recordset
Dim strSelect As String


Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst![UoM1] = rst![UoM2] Then
rst![StatusUoM] = "0"
Else
rst![StatusUoM] = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for UoM done!!!")
End Sub


Sub SetStatus(strField As String)
Dim dbs As Database, rst As Recordset
Dim strSelect As String

Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst(strField & "1") = rst(strField & "2") Then
rst("Status" & strField) = "0"
Else
rst("Status" & strField) = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for " & strField & "done!!!")
End Sub

Then call the procedure using:

SetStatus "Item"
Or
SetStatus "Qty"
Or
SetStatus "UoM"
Click to expand...
Click to expand...
Click to expand...
 
H

hisham

Thanks you John and Marshal

John Spencer said:
Do you mean you want to click one button and have all three updates occur?
If so, I would use an update query. It will be faster and will lead to less
bloating of your database.

Private Sub Command23_Click()
Dim dbs As DAO.Database, rst As DAO.Recordset
Dim strSelect As String

Set dbs = CurrentDb
strSelect = "UPDATE P1Entry " & _
" SET StatusItem = Abs([Item1]<>[Item2])" & _
", StatusQty = Abs([qty1]<>[qty2])" & _
", StatusUOM = Abs([Uom1]<>[UoM2])"
dbs.execute strSelect, dbFailOnError

Msgbox dbs.RecordsAffected & " Records updated"

End Sub

Note that this could have a problem if any of the fields in the comparison
contain Nulls since the comparison will return Null. You can wrap the
comparison in the NZ function to return 0 or 1 depending on your wishes.

strSelect = "UPDATE P1Entry " & _
" SET StatusItem = NZ(Abs([Item1]<>[Item2]),1)" & _
", StatusQty = NZ(Abs([qty1]<>[qty2]),1)" & _
", StatusUOM = NZ(Abs([Uom1]<>[UoM2]),1)"

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

hisham said:
I want to combine code 3 button to 1 button.
Pls help me how to combine. Below is coding for 3 button.
*************************************************
Private Sub Command23_Click()
Dim dbs As Database, rst As Recordset
Dim strSelect As String


Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst![Item1] = rst![Item2] Then
rst![StatusItem] = "0"
Else
rst![StatusItem] = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for item done!!!")
End Sub

Private Sub Command24_Click()
Dim dbs As Database, rst As Recordset
Dim strSelect As String


Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst![Qty1] = rst![Qty2] Then
rst![StatusQty] = "0"

Else
rst![StatusQty] = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for quantity done!!!")
End Sub

Private Sub Command25_Click()
Dim dbs As Database, rst As Recordset
Dim strSelect As String


Set dbs = CurrentDb
strSelect = "SELECT * FROM P1ENTRY"
Set rst = dbs.OpenRecordset(strSelect)
Do While Not rst.EOF
rst.Edit
If rst![UoM1] = rst![UoM2] Then
rst![StatusUoM] = "0"
Else
rst![StatusUoM] = "1"
End If
rst.Update
rst.MoveNext
Loop
rst.close
MsgBox ("Checking for UoM done!!!")
End Sub
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

qty in table1 minus qty in table2 equal to result column in table2 2
comparison and get the result 1
Message from Access 2007 on sub function 3
cannot update. DB or object is read-only 3027 4
Use a string to define a field 1
Run Query on Subform Using VBA Macro Button 1
Function not working 3
Access 97 VB 2

Top