PDA

View Full Version : Count increment revise number if same item number



apple
09-11-2007, 10:46 PM
Hello,

Can anybody help me as below:

1. User key in this item number E1234 the frist time
2. The second time key in E1234 for other purpose, it will auto
E1234-Revise1. It's will auto top up the revise number if the user still key in the same part for third time . I want to increase the revise number for each time user fill in same item number.How to write in VB script? Thanks

Bob Phillips
09-12-2007, 12:55 AM
[vba] Private Sub Worksheet_Change(ByVal Target As Range) Const WS_RANGE As String = "H1" '

apple
09-12-2007, 08:33 AM
Please do explain as i not understand. Thanks

Bob Phillips
09-12-2007, 10:44 AM
I was having problems with my posting this morning. It should have been this worksheet event code



Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1" '<== change to suit
Static mpPrev
Dim RevNum As Long

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If mpPrev <> "" Then
If Len(mpPrev) = Len(Replace(mpPrev, "Revise", "")) Then
.Value = .Value & "-Revise1"
Else
RevNum = Right$(mpPrev, Len(mpPrev) - InStr(mpPrev, "-") - 6)
RevNum = RevNum + 1
.Value = Left$(mpPrev, InStr(mpPrev, "-")) & "Revise" & RevNum
End If
End If
mpPrev = .Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

apple
09-12-2007, 05:32 PM
Hi,

Thank you for your help.

apple
09-12-2007, 10:36 PM
Hi,

Please attach the below coding in frontpage. Then i will explain more details:


<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>New Page 3</title>
</head>
<body>
<font face="Arial">
<b><font color=red>٭</font>
</TD>
<td width="16" height="26"></td>
<td bgcolor="#f4f4f4" height="26"><font size="2" face="Arial"> Part Number</font></td>
<td bgcolor="#f4f4f4" height="26">:
<input name="part" type="text" size="20" id="part" onchange="upperCase(this.id)" maxlength="10">
<script type="text/javascript">

</body>
</html>
</b></font>
<p><b><font size="2" face="Arial"> Type</font></b></td>
<td bgcolor="#f4f4f4"><!--<font face="Arial"><input name="type" size=25 value="New_Request"></font><b><font size="2" color="#0033CC" face="Arial"> </font></b></td>-->
<b><font face="Arial" size="2">:</font>
<!--<font face="Arial" size="2" color="#0000FF" onmouseover=this.style.color='red' onmouseout=this.style.color='#0000FF'> New Item</font></b>-->
<!--<input name="typedia" type="text" readonly id="typedia" value="New Item" onmouseover=this.style.color='red' onmouseout=this.style.color='#0000FF' style="font-weight: bold; color:#0000FF">-->
<select name="typedia" style="text-align: center; width:165" size="1">
<option value="">Please select type</option>
<option value="Revised Item">Revised Request</option>
<option value="New Item" selected>New Request</option>
</select></b></p>
<p><input type=submit value=" Next >> " onclick="this.disabled=true;this.value='Going to next...'; this.form.submit();"></p>

apple
09-12-2007, 10:40 PM
This is another file2 which continue from the above file1. Please attach new sheet in frontpage for below coding. Then i will explain more details

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>result</title>
</head>
<body>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft FrontPage 6.0">
<TITLE></TITLE>
</HEAD>
<body>
<FORM class="form" action="send.asp" method="Post" name=Form>
<%
set connOpen = Server.CreateObject("ADODB.connection")
SconnOpen = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & Server.MapPath("guestbooks.mdb") & ";" & _
"Persist Security Info=False"
connOpen.Open(SconnOpen)
Set rsotOpen = Server.CreateObject("ADODB.Recordset")
rsotOpen.Open "SELECT * FROM books WHERE status IN ('Open')",connOpen, 3%>
<%
response.write "<TABLE width=""120%"" bgcolor=""#F4F4F4""><TR>"
response.write "<TD width=""50%""><FONT SIZE=""3"" COLOR=""003366"">&nbsp;<B>OPEN REQUEST</B></FONT></TD>"
response.write "<TD width=""30%"" align=right>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<font face='arial' size=2 color=black> Total Open Request: </font><b><font face='arial' size=2 color=black>"& rsotOpen.recordcount &" </font></b></TD>"

response.write "<TABLE><TR>"
rsotOpen.Close
Dim connOpen2,rsOpen,intCounter,intMod,rcolor
set connOpen2=Server.CreateObject("ADODB.Connection")
connOpen2.Provider="Microsoft.Jet.OLEDB.4.0"
connOpen2.Open(Server.Mappath("guestbooks.mdb"))
set rsOpen = Server.CreateObject("ADODB.recordset")

rsOpen.Open "SELECT * from books WHERE STATUS IN ('Open') AND author IN ('" & Session("username") &"') order by sku DESC", connOpen2
%>
<table align=center width="120%" bgcolor="#F4F4F4">
<tr><td align=right></td>
</tr>
</table>
<!--<table bgcolor="#F4F4F4" align=center width="120%" border="1" cellpadding="0" cellspacing="0" height="30" style="border-collapse: collapse" bordercolor="#006699">-->
<%if rsOpen.EOF then Response.Write "<p align=center><font face=arial size=2 color=red><b>No Open Request for "&(Session("username"))&"</b></font>"
%>
<TABLE WIDTH="52%" BORDER=1 CELLSPACING=1 CELLPADDING=1 name="rsTable" id=rsTable cols=14>
<tr>
<th align="left" bgcolor=#006699 height="13" bordercolor="#000000" bordercolorlight="#006699" bordercolordark="#006699">
<b><!--<input type="checkbox" name="C1" value="ON">-->
<A href="javascript:sortTable(0, rsTable);" title="Click ID to sort by ID">
<font color="#FFFFFF" FACE="arial" SIZE="2">ID</font><img border="0" src="combo_arrow.gif" width="8" height="8"></A></b></th>

<th align="center" bgcolor=#006699 height="13" bordercolor="#000000" bordercolorlight="#006699" bordercolordark="#006699">
<A href="javascript:sortTable(1, rsTable);" title="Click Name to sort by name">
<b><font SIZE="2" FACE="arial" color="#FFFFFF">Name</font></b>
<img border="0" src="combo_arrow.gif" width="8" height="8"></A></th>

<th align="left" bgcolor=#006699 height="13" bordercolor="#000000" bordercolorlight="#006699" bordercolordark="#006699">
<p align="center"><A href="javascript:sortTable(2, rsTable);" title="Click Topic to sort by topic">
<b><font SIZE="2" FACE="arial" color="#FFFFFF">Topic</font></b>
<img border="0" src="combo_arrow.gif" width="8" height="8"></A></th>

</tr>

<%intCounter=0%>
<%do until rsOpen.EOF%>
<%
intCounter=intCounter+1
intMod=intcounter mod 2
if intmod=0 then
rcolor = "#F4F4F4"
elseif intmod=1 then
rcolor = "#CCCCCC"
end if
%>
<tr>
<td align=left bgcolor=#FFFFFF title="Click ID to edit"><font SIZE="2" FACE="arial" COLOR="BLACK">
<width=35%>
<!--<Input Type=CheckBox Name=Delete Value=<%=rsOpen("sku")%>>-->
<span style="text-decoration: none"><font SIZE="2" FACE="arial" COLOR="BLACK">
<%if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %>
<!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">-->
<%end if%>
<%=rsOpen("sku")%><!--</a>-->1</font></span>

<td align="left" bgcolor=#FFFFFF width="216" height="20" title="REQUESTOR"><%
if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %><!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">--><%end if%>
<font SIZE="2" FACE="arial" COLOR="BLACK">
<span style="text-decoration: none">&nbsp;User Name: Mary<%Response.Write UCASE(rsOpen("author"))%>&nbsp;</span></font><!--</a>-->
<td align="left" bgcolor=#FFFFFF width="211" height="20" title="TOPIC"><%
if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %><!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">--><%end if%>
<font face="arial" size="2">Item_Number: 1234</font></td>

<%else%>
<font SIZE="1" FACE="arial" COLOR="BLACK"><span title="This item not edited yet" style="text-decoration: none"><font color=red>&nbsp;N/A&nbsp;</font></span></font>
<%end if%></td>


<!--<td align=center bgcolor=#FFFFFF title="Edit Attachment File"><!--<a href="delete.asp?sku=<%=rsOpen("sku")%> "><img border="0" src="icon_config.gif" width="15" height="15"></a>-->
<!--<%
if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then
%>
<A HREF="editattachment.asp?sku=<%=rsOpen("sku")%>">
<img border="0" src="pics/editfile.gif" width="15" height="15"></A>
<%
else
%><img onClick='alert("You have no access to \nCANCEL <%=orang%> part number request.")' border="0" src="stop.gif" width="15" height="15" title="Only <%Response.Write(rsOpen("author"))%> can cancel this <%Response.Write(rsOpen("part"))%>">
<%
end if
%></td>-->

<%rsOpen.MoveNext%>

</tr>
<%loop
SET rsOpen=nothing
set connOpen2=nothing
%>
<tr>
<td align=left bgcolor=#FFFFFF title="Click ID to edit"><font SIZE="2" FACE="arial" COLOR="BLACK">
<width=35%>
<!--<Input Type=CheckBox Name=Delete Value=<%=rsOpen("sku")%>>-->
<span style="text-decoration: none">
<%if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %>
<!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">-->
<%end if%>
<%=rsOpen("sku")%><!--</a>-->2</span>

<td align="left" bgcolor=#FFFFFF width="216" height="20" title="REQUESTOR"><%
if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %><!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">--><%end if%>
<font SIZE="2" FACE="arial" COLOR="BLACK">
<span style="text-decoration: none">&nbsp;User Name<%Response.Write UCASE(rsOpen("author"))%>&nbsp;</span></font><!--</a>-->:
Sindy<td align="left" bgcolor=#FFFFFF width="211" height="20" title="TOPIC"><%
if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %><!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">--><%end if%>
<font face="arial" size="2">Item_Number: 1234-Revised 1</font></td>

</tr>
</table>
<TABLE WIDTH="52%" BORDER=1 CELLSPACING=1 CELLPADDING=1 name="rsTable" id=table1 cols=14>
<tr>
<td align=left bgcolor=#FFFFFF title="Click ID to edit"><font SIZE="2" FACE="arial" COLOR="BLACK">
<width=35%>
<!--<Input Type=CheckBox Name=Delete Value=<%=rsOpen("sku")%>>-->
<span style="text-decoration: none">
<%if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %>
<!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">-->
<%end if%>
<%=rsOpen("sku")%><!--</a>-->3</span>

<td align="left" bgcolor=#FFFFFF width="216" height="20" title="REQUESTOR"><%
if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %><!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">--><%end if%>
<font SIZE="2" FACE="arial" COLOR="BLACK">
<span style="text-decoration: none">&nbsp;User Name<%Response.Write UCASE(rsOpen("author"))%>&nbsp;</span></font><!--</a>-->:
John<td align="left" bgcolor=#FFFFFF width="211" height="20" title="TOPIC"><%
if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %><!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">--><%end if%>
<font face="arial" size="2">Item_Number: 8710</font></td>

</tr>
</table>
<TABLE WIDTH="52%" BORDER=1 CELLSPACING=1 CELLPADDING=1 name="rsTable" id=table2 cols=14>
<tr>
<td align=left bgcolor=#FFFFFF title="Click ID to edit"><font SIZE="2" FACE="arial" COLOR="BLACK">
<width=35%>
<!--<Input Type=CheckBox Name=Delete Value=<%=rsOpen("sku")%>>-->
<span style="text-decoration: none">
<%if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %>
<!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">-->
<%end if%>
<%=rsOpen("sku")%><!--</a>-->3</span>

<td align="left" bgcolor=#FFFFFF width="216" height="20" title="REQUESTOR"><%
if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %><!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">--><%end if%>
<font SIZE="2" FACE="arial" COLOR="BLACK">
<span style="text-decoration: none">&nbsp;User Name<%Response.Write UCASE(rsOpen("author"))%>&nbsp;</span></font><!--</a>-->:
Michael<td align="left" bgcolor=#FFFFFF width="211" height="20" title="TOPIC"><%
if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %><!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">--><%end if%>
<font face="arial" size="2">Item_Number: 8710-Revised1</font></td>

</tr>
</table>
<TABLE WIDTH="52%" BORDER=1 CELLSPACING=1 CELLPADDING=1 name="rsTable" id=table3 cols=14>
<tr>
<td align=left bgcolor=#FFFFFF title="Click ID to edit"><font SIZE="2" FACE="arial" COLOR="BLACK">
<width=35%>
<!--<Input Type=CheckBox Name=Delete Value=<%=rsOpen("sku")%>>-->
<span style="text-decoration: none">
<%if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %>
<!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">-->
<%end if%>
<%=rsOpen("sku")%><!--</a>-->3</span>

<td align="left" bgcolor=#FFFFFF width="216" height="20" title="REQUESTOR"><%
if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %><!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">--><%end if%>
<font SIZE="2" FACE="arial" COLOR="BLACK">
<span style="text-decoration: none">&nbsp;User Name<%Response.Write UCASE(rsOpen("author"))%>&nbsp;</span></font><!--</a>-->:
Peter<td align="left" bgcolor=#FFFFFF width="211" height="20" title="TOPIC"><%
if UCASE(trim(rsOpen("author")))= UCASE(Session("username")) then %><!--<a href="edititem.asp?sku=<%=rsOpen("sku")%>">--><%end if%>
<font face="arial" size="2">Item_Number: 8710-Revised 2</font></td>

</tr>
</table>

</font>
<br>
</body>
</HTML>
</body>
</html>

apple
09-12-2007, 10:47 PM
Hi,

Let's me explain for file 1 and file 2.

File 1:

1. User key in item number and if the item number is same, user will select
list box Revised Request.

2. Press NEXT button

File 2

1. Display the details that fill in file1

My request: I want to count Revised Request item that is same item number fill in by user. Please see file 2.output. If same item number, it will auto revised-1. If other user still apply same item number, it will auto revised 2 and so on. Can anybody help me. Thanks