Updating field's value via VBA

  • Thread starter Thread starter FA
  • Start date Start date
F

FA

Is there a way to update a field value via VBA code. The following are
two fields in my query, i want to update one of the field's value.
rst!FINDG_NO rst!RSK_ID
PCL20060125-AT001 3
PCL20060125-AT003 2
PCL20060125-AT004 2
PCL20060125-AT002 1

rst!RSK_ID should be descending order and they are coming out fine. The
secondary sort is FINDG_NO asscending order but look at the values they
are not coming out in 001,002,003,004 order. I guess i will have to
update the values or change the values of FINDG_NO so that they come
out in order. They should come out like

PCL20060125-AT001 3
PCL20060125-AT002 2
PCL20060125-AT003 2
PCL20060125-AT004 1

In this example i switch 002 and 003. User do not have problem changing
these values as long as they come in ascending order. Remember,
rst!RSK_ID can not be changed.

If there is a way via VBA code to change or update rst!FINDG_NO, please
let me know.

thanks
Moe
 
Moe,

Try this on a copy of your mdb:

I created a table (tblTest1) with two fields: FINDG_NO (type text) & RSK_ID
(type long)

I created a query (Query1) for a form (Form1). The SQL of the query is

SELECT tblTest1.RSK_ID, tblTest1.FINDG_NO
FROM tblTest1
ORDER BY tblTest1.RSK_ID DESC , tblTest1.FINDG_NO;


On Form1, I display the form header and footer. In the header, I added a combo
box (Combo0) and a button (btnRenumber)

In the detail section there is two text boxes, one for RSK_ID and one for FINDG_NO.


I set Combo0 ROWSOURCE to:

SELECT DISTINCT Left([FINDG_NO],14) AS Expr1 FROM tblTest1;



In the button on click event, paste the following code:

'*****************************************
Private Sub btnRenumber_Click()
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strPrefix As String 'left 14 chars of FINDG_NO from Combo0
Dim RC As Long 'record count
Dim i As Integer

strSQL = "SELECT tblTest1.RSK_ID, tblTest1.FINDG_NO"
strSQL = strSQL & " FROM tblTest1"
strSQL = strSQL & " WHERE left(tblTest1.FINDG_NO,14) = '"
strSQL = strSQL & [Forms]![form1].[Combo0] & "'"
strSQL = strSQL & " ORDER BY tblTest1.RSK_ID DESC;"

Set rst = CurrentDb.OpenRecordset(strSQL)

'if no records, display message
' close rst and and exit sub
If rst.BOF And rst.EOF Then
MsgBox "No records to renumber"
rst.Close
Set rst = Nothing
Exit Sub
End If

strPrefix = [Forms]![form1].[Combo0]

rst.MoveLast
RC = rst.RecordCount
rst.MoveFirst

For i = 1 To RC
rst.Edit
rst!FINDG_NO = strPrefix & Right("000" & i, 3)
rst.Update
rst.MoveNext
Next

Me.Requery
rst.Close
Set rst = Nothing

End Sub
'***********************************


In the Combo0 after update event, paste the following:

'***************************
Private Sub Combo0_AfterUpdate()
'filters the form records
Me.Filter = "Left(FINDG_NO,14) = '" & Combo0 & "'"
Me.FilterOn = True
End Sub
'***************************


This assumes that FINDG_NO will always have the first 14 chars as text and the
last three will be a three digit number that is left padded with zeros.
 
Uzytkownik "FA said:
Is there a way to update a field value via VBA code. The following are
two fields in my query, i want to update one of the field's value.
rst!FINDG_NO rst!RSK_ID
PCL20060125-AT001 3
PCL20060125-AT003 2
PCL20060125-AT004 2
PCL20060125-AT002 1

rst!RSK_ID should be descending order and they are coming out fine. The
secondary sort is FINDG_NO asscending order but look at the values they
are not coming out in 001,002,003,004 order. I guess i will have to
update the values or change the values of FINDG_NO so that they come
out in order. They should come out like

PCL20060125-AT001 3
PCL20060125-AT002 2
PCL20060125-AT003 2
PCL20060125-AT004 1

In this example i switch 002 and 003. User do not have problem changing
these values as long as they come in ascending order. Remember,
rst!RSK_ID can not be changed.

If there is a way via VBA code to change or update rst!FINDG_NO, please
let me know.

thanks
Moe
Click to expand...

============================================================================
================
FULL LEGAL SOFTWARE !!!
Games, video, program, image, chat, questbook, catalog site, arts, news,
and...
This site it is full register and legal software !!!
Please download and you must register software !!!

PLEASE REGISTER SOFTWARE:
http://www.webteam.gsi.pl/rejestracja.htm
DOWNLOAD LEGAL SOFTWARE:
http://www.webteam.gsi.pl

Full question and post: http://www.webteam.gsi.pl

Contact and service and advanced technology:
http://www.webteam.gsi.pl/kontakt.htm
FAQ: http://www.webteam.gsi.pl/naj_czesciej_zadawane_pytania.htm

Please add me URL for you all site and search engines and best friends !!!

Me site:
SERWIS WEBNETI: http://www.webneti.gsi.pl
PORTAL WEBTEAM: http://www.webteam.gsi.pl
LANGUAGE: http://www.webneti.cjb.net
============================================================================
================
 
Thanks millions Steve, it worked just as smooth as i wanted, only one
little problem, the FINDG_NO will not be always 14 chars before the 3
digit numbers. Is there any way u can help me with the variable lenght
of FINDG_NO chars. I mean the last three will always be numbers but
before the three digit numbers are characters which will vary in
lenght.
For one set of FINDG_NO they will be the same lenght, but if other set
of FINDG_NO may be different.
Examples:
For PCLV1 (Application Name) the finding numbers are following
PCLV120060125-AT001
PCLV120060125-AT002
For ADEV1 (Application Name) the finding numbers are following
ADEV120060325-AT001
ADEV120060325-AT002
and so on...
So each application tested has a set of finding numbers, which will
remain the same lenght as far as the characters before the three digit
number concern.

Thanks millions again Steve

Moe
 
Hi Steve,
I got it working just fine but i have another issue came up. The table
that has the FINDG_NO has a one to many relationship with another table
called ScreenShot. So each FINDG_NO which is a PK of the table is
present in table Screenshot. I tried to change the relation ship to
enforce refrential integerity but its not working since i have to
change the FINDG_NO from PK to a regular field and make another
autonumber PK for that table. So now FINDG_NO is not a PK thats why the
refrential integerity is not working. Following it the table structure
now
tblTest1 ScreenShot
ID (PK) ShotID(PK)
FINDG_NO ID (FK)
FINDG_NO

If the FINDG_NO change in tblTest1, i also want them to change in the
table ScreenShot.
Is there a way to achieve this ??
SteveS said:
Moe,

Try this on a copy of your mdb:

I created a table (tblTest1) with two fields: FINDG_NO (type text) & RSK_ID
(type long)

I created a query (Query1) for a form (Form1). The SQL of the query is

SELECT tblTest1.RSK_ID, tblTest1.FINDG_NO
FROM tblTest1
ORDER BY tblTest1.RSK_ID DESC , tblTest1.FINDG_NO;


On Form1, I display the form header and footer. In the header, I added a combo
box (Combo0) and a button (btnRenumber)

In the detail section there is two text boxes, one for RSK_ID and one for FINDG_NO.


I set Combo0 ROWSOURCE to:

SELECT DISTINCT Left([FINDG_NO],14) AS Expr1 FROM tblTest1;



In the button on click event, paste the following code:

'*****************************************
Private Sub btnRenumber_Click()
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strPrefix As String 'left 14 chars of FINDG_NO from Combo0
Dim RC As Long 'record count
Dim i As Integer

strSQL = "SELECT tblTest1.RSK_ID, tblTest1.FINDG_NO"
strSQL = strSQL & " FROM tblTest1"
strSQL = strSQL & " WHERE left(tblTest1.FINDG_NO,14) = '"
strSQL = strSQL & [Forms]![form1].[Combo0] & "'"
strSQL = strSQL & " ORDER BY tblTest1.RSK_ID DESC;"

Set rst = CurrentDb.OpenRecordset(strSQL)

'if no records, display message
' close rst and and exit sub
If rst.BOF And rst.EOF Then
MsgBox "No records to renumber"
rst.Close
Set rst = Nothing
Exit Sub
End If

strPrefix = [Forms]![form1].[Combo0]

rst.MoveLast
RC = rst.RecordCount
rst.MoveFirst

For i = 1 To RC
rst.Edit
rst!FINDG_NO = strPrefix & Right("000" & i, 3)
rst.Update
rst.MoveNext
Next

Me.Requery
rst.Close
Set rst = Nothing

End Sub
'***********************************


In the Combo0 after update event, paste the following:

'***************************
Private Sub Combo0_AfterUpdate()
'filters the form records
Me.Filter = "Left(FINDG_NO,14) = '" & Combo0 & "'"
Me.FilterOn = True
End Sub
'***************************


This assumes that FINDG_NO will always have the first 14 chars as text and the
last three will be a three digit number that is left padded with zeros.


--
HTH
--
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)
Is there a way to update a field value via VBA code. The following are
two fields in my query, i want to update one of the field's value.
rst!FINDG_NO rst!RSK_ID
PCL20060125-AT001 3
PCL20060125-AT003 2
PCL20060125-AT004 2
PCL20060125-AT002 1

rst!RSK_ID should be descending order and they are coming out fine. The
secondary sort is FINDG_NO asscending order but look at the values they
are not coming out in 001,002,003,004 order. I guess i will have to
update the values or change the values of FINDG_NO so that they come
out in order. They should come out like

PCL20060125-AT001 3
PCL20060125-AT002 2
PCL20060125-AT003 2
PCL20060125-AT004 1

In this example i switch 002 and 003. User do not have problem changing
these values as long as they come in ascending order. Remember,
rst!RSK_ID can not be changed.

If there is a way via VBA code to change or update rst!FINDG_NO, please
let me know.

thanks
Moe
Click to expand...
Click to expand...
 
Hi Moe,

You should remove the field [FINDG_NO] from the table "ScreenShot". the two
tables are related by the field [ID].

If you had a table of employee names and a table of jobs completed, would
you have a name field in both tables? Or just in the EmployeeNames table?

Now you don't have to worry about updating the ScreenShot table. <g>

--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


FA said:
Hi Steve,
I got it working just fine but i have another issue came up. The table
that has the FINDG_NO has a one to many relationship with another table
called ScreenShot. So each FINDG_NO which is a PK of the table is
present in table Screenshot. I tried to change the relation ship to
enforce refrential integerity but its not working since i have to
change the FINDG_NO from PK to a regular field and make another
autonumber PK for that table. So now FINDG_NO is not a PK thats why the
refrential integerity is not working. Following it the table structure
now
tblTest1 ScreenShot
ID (PK) ShotID(PK)
FINDG_NO ID (FK)
FINDG_NO

If the FINDG_NO change in tblTest1, i also want them to change in the
table ScreenShot.
Is there a way to achieve this ??
SteveS said:
Moe,

Try this on a copy of your mdb:

I created a table (tblTest1) with two fields: FINDG_NO (type text) & RSK_ID
(type long)

I created a query (Query1) for a form (Form1). The SQL of the query is

SELECT tblTest1.RSK_ID, tblTest1.FINDG_NO
FROM tblTest1
ORDER BY tblTest1.RSK_ID DESC , tblTest1.FINDG_NO;


On Form1, I display the form header and footer. In the header, I added a combo
box (Combo0) and a button (btnRenumber)

In the detail section there is two text boxes, one for RSK_ID and one for FINDG_NO.


I set Combo0 ROWSOURCE to:

SELECT DISTINCT Left([FINDG_NO],14) AS Expr1 FROM tblTest1;



In the button on click event, paste the following code:

'*****************************************
Private Sub btnRenumber_Click()
Dim rst As DAO.Recordset
Dim strSQL As String
Dim strPrefix As String 'left 14 chars of FINDG_NO from Combo0
Dim RC As Long 'record count
Dim i As Integer

strSQL = "SELECT tblTest1.RSK_ID, tblTest1.FINDG_NO"
strSQL = strSQL & " FROM tblTest1"
strSQL = strSQL & " WHERE left(tblTest1.FINDG_NO,14) = '"
strSQL = strSQL & [Forms]![form1].[Combo0] & "'"
strSQL = strSQL & " ORDER BY tblTest1.RSK_ID DESC;"

Set rst = CurrentDb.OpenRecordset(strSQL)

'if no records, display message
' close rst and and exit sub
If rst.BOF And rst.EOF Then
MsgBox "No records to renumber"
rst.Close
Set rst = Nothing
Exit Sub
End If

strPrefix = [Forms]![form1].[Combo0]

rst.MoveLast
RC = rst.RecordCount
rst.MoveFirst

For i = 1 To RC
rst.Edit
rst!FINDG_NO = strPrefix & Right("000" & i, 3)
rst.Update
rst.MoveNext
Next

Me.Requery
rst.Close
Set rst = Nothing

End Sub
'***********************************


In the Combo0 after update event, paste the following:

'***************************
Private Sub Combo0_AfterUpdate()
'filters the form records
Me.Filter = "Left(FINDG_NO,14) = '" & Combo0 & "'"
Me.FilterOn = True
End Sub
'***************************


This assumes that FINDG_NO will always have the first 14 chars as text and the
last three will be a three digit number that is left padded with zeros.


--
HTH
--
Steve S.
--------------------------------
"Veni, Vidi, Velcro"
(I came, I saw, I stuck around.)
Is there a way to update a field value via VBA code. The following are
two fields in my query, i want to update one of the field's value.
rst!FINDG_NO rst!RSK_ID
PCL20060125-AT001 3
PCL20060125-AT003 2
PCL20060125-AT004 2
PCL20060125-AT002 1

rst!RSK_ID should be descending order and they are coming out fine. The
secondary sort is FINDG_NO asscending order but look at the values they
are not coming out in 001,002,003,004 order. I guess i will have to
update the values or change the values of FINDG_NO so that they come
out in order. They should come out like

PCL20060125-AT001 3
PCL20060125-AT002 2
PCL20060125-AT003 2
PCL20060125-AT004 1

In this example i switch 002 and 003. User do not have problem changing
these values as long as they come in ascending order. Remember,
rst!RSK_ID can not be changed.

If there is a way via VBA code to change or update rst!FINDG_NO, please
let me know.

thanks
Moe
Click to expand...
Click to expand...
Click to expand...
 
I should have

Change the Combo box ROWSOURCE to:

SELECT DISTINCT Left(Trim([FINDG_NO]),Len(Trim([FINDG_NO])-3) AS Expr1 FROM
tblTest1;


Change the code in the button click event to: (look at the third line)

strSQL = "SELECT tblTest1.RSK_ID, tblTest1.FINDG_NO"
strSQL = strSQL & " FROM tblTest1"
strSQL = strSQL & " WHERE Left(Trim([FINDG_NO]),Len(Trim([FINDG_NO])-3) = '"
strSQL = strSQL & [Forms]![form1].[Combo0] & "'"
strSQL = strSQL & " ORDER BY tblTest1.RSK_ID DESC;"

The Trim() function removes any leading or trailing spaces, then the number
of chars is is calculated by finding the length of the string, and
subtracting 3 (instead of using the fixed value of 14).
 
Thanks Steve, it worked.
SteveS said:
I should have

Change the Combo box ROWSOURCE to:

SELECT DISTINCT Left(Trim([FINDG_NO]),Len(Trim([FINDG_NO])-3) AS Expr1 FROM
tblTest1;


Change the code in the button click event to: (look at the third line)

strSQL = "SELECT tblTest1.RSK_ID, tblTest1.FINDG_NO"
strSQL = strSQL & " FROM tblTest1"
strSQL = strSQL & " WHERE Left(Trim([FINDG_NO]),Len(Trim([FINDG_NO])-3) = '"
strSQL = strSQL & [Forms]![form1].[Combo0] & "'"
strSQL = strSQL & " ORDER BY tblTest1.RSK_ID DESC;"

The Trim() function removes any leading or trailing spaces, then the number
of chars is is calculated by finding the length of the string, and
subtracting 3 (instead of using the fixed value of 14).


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


FA said:
Thanks millions Steve, it worked just as smooth as i wanted, only one
little problem, the FINDG_NO will not be always 14 chars before the 3
digit numbers. Is there any way u can help me with the variable lenght
of FINDG_NO chars. I mean the last three will always be numbers but
before the three digit numbers are characters which will vary in
lenght.
For one set of FINDG_NO they will be the same lenght, but if other set
of FINDG_NO may be different.
Examples:
For PCLV1 (Application Name) the finding numbers are following
PCLV120060125-AT001
PCLV120060125-AT002
For ADEV1 (Application Name) the finding numbers are following
ADEV120060325-AT001
ADEV120060325-AT002
and so on...
So each application tested has a set of finding numbers, which will
remain the same lenght as far as the characters before the three digit
number concern.

Thanks millions again Steve

Moe
Click to expand...
Click to expand...
 
Back
Top