Renumber field

M

MikeJohnB

I have asked this question before but no response, guess I didn't explain
myself very well. I am trying to re-number a control on a form if a line item
is deleted from a quote programme. I have tried the following code and
several other variations on MoveFirst MoveNext manipulation of the recordset.
Can anyone see why this does not work?

Private Sub Line_Item_No__GotFocus()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim i As Integer
strSQL = "SELECT * From Tbl_Quotation ORDER BY Line_Item_No_"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)<<<<<Fails here
rst.MoveFirst
i = 1
Do
rst.Edit
rst![Line_Item_No_] = i
Debug.Print Me.Line_Item_No_
rst.Update
rst.MoveNext
Loop Until rst.EOF
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

The Error is
Runtime Error 3061
Too few Parameter Expected 1.

Table Tbl_Quotation
Field Line_Item_No_

SQL Showing Table Structure which works to isolate the Line Items for the
current Quotaion base on a control on the top level form


SELECT Tbl_Quotation.Quotation_Ref, Tbl_Quotation.[Line_Item_No:]
FROM Tbl_Quotation
WHERE (((Tbl_Quotation.Quotation_Ref)=[Forms]![Frm_Main_Data]![Quote_Ref]));

i hope someone can assist, really stuck on the syntax for this one and
getting no where fast.

Regards

--
 
T

Tom van Stiphout

On Wed, 3 Sep 2008 14:35:00 -0700, MikeJohnB

Hmmm, I'm interested in this case. Your select statement seems
innocent enough. Curious column name "Line_Item_No_" but that
shouldn't pose problems.
I tried it in A2007's northwind.accdb, adding such field to the
Customers table, and your code worked normally.

Is tbl_quotation a query, perhaps? Or Line_Item_No_ is misspelled?

-Tom.
Microsoft Access MVP

I have asked this question before but no response, guess I didn't explain
myself very well. I am trying to re-number a control on a form if a line item
is deleted from a quote programme. I have tried the following code and
several other variations on MoveFirst MoveNext manipulation of the recordset.
Can anyone see why this does not work?

Private Sub Line_Item_No__GotFocus()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim i As Integer
strSQL = "SELECT * From Tbl_Quotation ORDER BY Line_Item_No_"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)<<<<<Fails here
rst.MoveFirst
i = 1
Do
rst.Edit
rst![Line_Item_No_] = i
Debug.Print Me.Line_Item_No_
rst.Update
rst.MoveNext
Loop Until rst.EOF
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

The Error is
Runtime Error 3061
Too few Parameter Expected 1.

Table Tbl_Quotation
Field Line_Item_No_

SQL Showing Table Structure which works to isolate the Line Items for the
current Quotaion base on a control on the top level form


SELECT Tbl_Quotation.Quotation_Ref, Tbl_Quotation.[Line_Item_No:]
FROM Tbl_Quotation
WHERE (((Tbl_Quotation.Quotation_Ref)=[Forms]![Frm_Main_Data]![Quote_Ref]));

i hope someone can assist, really stuck on the syntax for this one and
getting no where fast.

Regards
Click to expand...
 
M

MikeJohnB

Tom, thanks for taking an interest in this issue, I am programming usin
Access 2007 but unconverted at the company and in 2k3 at home. The platform
where this will be used is current on 2k3 hence the reason for programming in
2k3 apart from the fact that I cant afford to upgrade my system at the moment.

I have written a Quotaions database which all works very well apart from
this one issue.

1.The Line_Item_No_ Naming convention apears to be Access, if you type Me.
The option avaiable is Line_Iem_No_ but the field in the table is
Line_Item_No (I never leave gaps in the naming convention.)
2. Tbl_Quotation is a Table, I always prefix tables with Tbl and Queries
with Qry and Form Frm, Rpr etc to prevent my confusion.
3. I have converted the database from A2k3 to A2007 and run it this morning,
I get the same error.
4. I have tried re-naming the Line_Item_No_ to Line_Item_No, still the same
issue
5. I guess it could be related to the SQL Selection but I am really lost.

What I am trying to do:

Tbl_Main_Data Tbl_Quotation
Field Quote_Ref >>>> Field Quotation_Ref
Field Unique_No>>>>V
Field Line_Item_No V
V
Tbl_Price_BreakField Ref_No

One Quote_Ref Relates to Many Line Items which relates to many Price Breaks

I Hope that the resizing of this page doesn't screw that up?

I am trying to select all line_Items_No_ from the Tbl_Quotaion Where
Tbl_Quotation.Qutation_Ref = the Quote_Ref control on an active form.

(((Tbl_Quotation.Quotation_Ref)=[Forms]![Frm_Main_Data]![Quote_Ref]));


If a line item has been deleted, I will get a report that does not run
sequentially like

Line Item 1 (Price Quote lead time etc)
Line Item 3 (Price Quote Lead Time etc)
Line Item 4 (Price Quote Lead Time etc)

To be re-numbered to Line 1, Line 2, Line 3 etc

I have used similar code to intially generate the line item (MoveLast if
Line_Item_No_ = 0 or "" add a line item)
That works really well, I have tried to adapt that to this issue but I am
stuck

Sorry for the protrated response but I hope you see what I am tryind to do
now?

The Very Kindest Regards Tom and Thanks again

--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


Tom van Stiphout said:
On Wed, 3 Sep 2008 14:35:00 -0700, MikeJohnB

Hmmm, I'm interested in this case. Your select statement seems
innocent enough. Curious column name "Line_Item_No_" but that
shouldn't pose problems.
I tried it in A2007's northwind.accdb, adding such field to the
Customers table, and your code worked normally.

Is tbl_quotation a query, perhaps? Or Line_Item_No_ is misspelled?

-Tom.
Microsoft Access MVP

I have asked this question before but no response, guess I didn't explain
myself very well. I am trying to re-number a control on a form if a line item
is deleted from a quote programme. I have tried the following code and
several other variations on MoveFirst MoveNext manipulation of the recordset.
Can anyone see why this does not work?

Private Sub Line_Item_No__GotFocus()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim i As Integer
strSQL = "SELECT * From Tbl_Quotation ORDER BY Line_Item_No_"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)<<<<<Fails here
rst.MoveFirst
i = 1
Do
rst.Edit
rst![Line_Item_No_] = i
Debug.Print Me.Line_Item_No_
rst.Update
rst.MoveNext
Loop Until rst.EOF
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

The Error is
Runtime Error 3061
Too few Parameter Expected 1.

Table Tbl_Quotation
Field Line_Item_No_

SQL Showing Table Structure which works to isolate the Line Items for the
current Quotaion base on a control on the top level form


SELECT Tbl_Quotation.Quotation_Ref, Tbl_Quotation.[Line_Item_No:]
FROM Tbl_Quotation
WHERE (((Tbl_Quotation.Quotation_Ref)=[Forms]![Frm_Main_Data]![Quote_Ref]));

i hope someone can assist, really stuck on the syntax for this one and
getting no where fast.

Regards
Click to expand...
Click to expand...
 
M

MikeJohnB

Tom I have also tried the following with the same error

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim i As Integer
'strSQL = "SELECT * From Tbl_Quotation ORDER BY
[Tbl_Quotation].[Line_Item_No_]"
strSQL = "SELECT [Tbl_Quotation].[Quotation_Ref],
[Tbl_Quotation].[Line_Item_No:]"
strSQL = strSQL & "FROM Tbl_Quotation WHERE
[Tbl_Quotation].[Quotation_Ref]=[Forms]![Frm_Main_Data]![Quote_Ref];"
Set db = CurrentDb
Debug.Print strSQL
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

rst.MoveFirst
i = 1
Do
rst.Edit
rst![Line_Item_No_] = i
Debug.Print Me.Line_Item_No_
rst.Update
rst.MoveNext
Loop Until rst.EOF
rst.Close
Set rst = Nothing
Set db = Nothing
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


MikeJohnB said:
Tom, thanks for taking an interest in this issue, I am programming usin
Access 2007 but unconverted at the company and in 2k3 at home. The platform
where this will be used is current on 2k3 hence the reason for programming in
2k3 apart from the fact that I cant afford to upgrade my system at the moment.

I have written a Quotaions database which all works very well apart from
this one issue.

1.The Line_Item_No_ Naming convention apears to be Access, if you type Me.
The option avaiable is Line_Iem_No_ but the field in the table is
Line_Item_No (I never leave gaps in the naming convention.)
2. Tbl_Quotation is a Table, I always prefix tables with Tbl and Queries
with Qry and Form Frm, Rpr etc to prevent my confusion.
3. I have converted the database from A2k3 to A2007 and run it this morning,
I get the same error.
4. I have tried re-naming the Line_Item_No_ to Line_Item_No, still the same
issue
5. I guess it could be related to the SQL Selection but I am really lost.

What I am trying to do:

Tbl_Main_Data Tbl_Quotation
Field Quote_Ref >>>> Field Quotation_Ref
Field Unique_No>>>>V
Field Line_Item_No V
V
Tbl_Price_BreakField Ref_No

One Quote_Ref Relates to Many Line Items which relates to many Price Breaks

I Hope that the resizing of this page doesn't screw that up?

I am trying to select all line_Items_No_ from the Tbl_Quotaion Where
Tbl_Quotation.Qutation_Ref = the Quote_Ref control on an active form.

(((Tbl_Quotation.Quotation_Ref)=[Forms]![Frm_Main_Data]![Quote_Ref]));


If a line item has been deleted, I will get a report that does not run
sequentially like

Line Item 1 (Price Quote lead time etc)
Line Item 3 (Price Quote Lead Time etc)
Line Item 4 (Price Quote Lead Time etc)

To be re-numbered to Line 1, Line 2, Line 3 etc

I have used similar code to intially generate the line item (MoveLast if
Line_Item_No_ = 0 or "" add a line item)
That works really well, I have tried to adapt that to this issue but I am
stuck

Sorry for the protrated response but I hope you see what I am tryind to do
now?

The Very Kindest Regards Tom and Thanks again

--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


Tom van Stiphout said:
On Wed, 3 Sep 2008 14:35:00 -0700, MikeJohnB

Hmmm, I'm interested in this case. Your select statement seems
innocent enough. Curious column name "Line_Item_No_" but that
shouldn't pose problems.
I tried it in A2007's northwind.accdb, adding such field to the
Customers table, and your code worked normally.

Is tbl_quotation a query, perhaps? Or Line_Item_No_ is misspelled?

-Tom.
Microsoft Access MVP

I have asked this question before but no response, guess I didn't explain
myself very well. I am trying to re-number a control on a form if a line item
is deleted from a quote programme. I have tried the following code and
several other variations on MoveFirst MoveNext manipulation of the recordset.
Can anyone see why this does not work?

Private Sub Line_Item_No__GotFocus()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim i As Integer
strSQL = "SELECT * From Tbl_Quotation ORDER BY Line_Item_No_"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)<<<<<Fails here
rst.MoveFirst
i = 1
Do
rst.Edit
rst![Line_Item_No_] = i
Debug.Print Me.Line_Item_No_
rst.Update
rst.MoveNext
Loop Until rst.EOF
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

The Error is
Runtime Error 3061
Too few Parameter Expected 1.

Table Tbl_Quotation
Field Line_Item_No_

SQL Showing Table Structure which works to isolate the Line Items for the
current Quotaion base on a control on the top level form


SELECT Tbl_Quotation.Quotation_Ref, Tbl_Quotation.[Line_Item_No:]
FROM Tbl_Quotation
WHERE (((Tbl_Quotation.Quotation_Ref)=[Forms]![Frm_Main_Data]![Quote_Ref]));

i hope someone can assist, really stuck on the syntax for this one and
getting no where fast.

Regards
Click to expand...
Click to expand...
Click to expand...
 
M

MikeJohnB

You will notice the Debug.Pring strSQL. I have pasted the output of this into
a new query and then ran the query. The result is that the correct fields are
returned based on a control on the main form.

The other point of note is that the dynaset shows 2 when hovering over the
statement in debug mode. Not sure what this relates to at the moment because
in the trial, the line item contains 15 lines.
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


MikeJohnB said:
Tom I have also tried the following with the same error

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim i As Integer
'strSQL = "SELECT * From Tbl_Quotation ORDER BY
[Tbl_Quotation].[Line_Item_No_]"
strSQL = "SELECT [Tbl_Quotation].[Quotation_Ref],
[Tbl_Quotation].[Line_Item_No:]"
strSQL = strSQL & "FROM Tbl_Quotation WHERE
[Tbl_Quotation].[Quotation_Ref]=[Forms]![Frm_Main_Data]![Quote_Ref];"
Set db = CurrentDb
Debug.Print strSQL
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

rst.MoveFirst
i = 1
Do
rst.Edit
rst![Line_Item_No_] = i
Debug.Print Me.Line_Item_No_
rst.Update
rst.MoveNext
Loop Until rst.EOF
rst.Close
Set rst = Nothing
Set db = Nothing
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


MikeJohnB said:
Tom, thanks for taking an interest in this issue, I am programming usin
Access 2007 but unconverted at the company and in 2k3 at home. The platform
where this will be used is current on 2k3 hence the reason for programming in
2k3 apart from the fact that I cant afford to upgrade my system at the moment.

I have written a Quotaions database which all works very well apart from
this one issue.

1.The Line_Item_No_ Naming convention apears to be Access, if you type Me.
The option avaiable is Line_Iem_No_ but the field in the table is
Line_Item_No (I never leave gaps in the naming convention.)
2. Tbl_Quotation is a Table, I always prefix tables with Tbl and Queries
with Qry and Form Frm, Rpr etc to prevent my confusion.
3. I have converted the database from A2k3 to A2007 and run it this morning,
I get the same error.
4. I have tried re-naming the Line_Item_No_ to Line_Item_No, still the same
issue
5. I guess it could be related to the SQL Selection but I am really lost.

What I am trying to do:

Tbl_Main_Data Tbl_Quotation
Field Quote_Ref >>>> Field Quotation_Ref
Field Unique_No>>>>V
Field Line_Item_No V
V
Tbl_Price_BreakField Ref_No

One Quote_Ref Relates to Many Line Items which relates to many Price Breaks

I Hope that the resizing of this page doesn't screw that up?

I am trying to select all line_Items_No_ from the Tbl_Quotaion Where
Tbl_Quotation.Qutation_Ref = the Quote_Ref control on an active form.

(((Tbl_Quotation.Quotation_Ref)=[Forms]![Frm_Main_Data]![Quote_Ref]));


If a line item has been deleted, I will get a report that does not run
sequentially like

Line Item 1 (Price Quote lead time etc)
Line Item 3 (Price Quote Lead Time etc)
Line Item 4 (Price Quote Lead Time etc)

To be re-numbered to Line 1, Line 2, Line 3 etc

I have used similar code to intially generate the line item (MoveLast if
Line_Item_No_ = 0 or "" add a line item)
That works really well, I have tried to adapt that to this issue but I am
stuck

Sorry for the protrated response but I hope you see what I am tryind to do
now?

The Very Kindest Regards Tom and Thanks again

--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


Tom van Stiphout said:
On Wed, 3 Sep 2008 14:35:00 -0700, MikeJohnB

Hmmm, I'm interested in this case. Your select statement seems
innocent enough. Curious column name "Line_Item_No_" but that
shouldn't pose problems.
I tried it in A2007's northwind.accdb, adding such field to the
Customers table, and your code worked normally.

Is tbl_quotation a query, perhaps? Or Line_Item_No_ is misspelled?

-Tom.
Microsoft Access MVP


I have asked this question before but no response, guess I didn't explain
myself very well. I am trying to re-number a control on a form if a line item
is deleted from a quote programme. I have tried the following code and
several other variations on MoveFirst MoveNext manipulation of the recordset.
Can anyone see why this does not work?

Private Sub Line_Item_No__GotFocus()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim i As Integer
strSQL = "SELECT * From Tbl_Quotation ORDER BY Line_Item_No_"
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)<<<<<Fails here
rst.MoveFirst
i = 1
Do
rst.Edit
rst![Line_Item_No_] = i
Debug.Print Me.Line_Item_No_
rst.Update
rst.MoveNext
Loop Until rst.EOF
rst.Close
Set rst = Nothing
Set db = Nothing
End Sub

The Error is
Runtime Error 3061
Too few Parameter Expected 1.

Table Tbl_Quotation
Field Line_Item_No_

SQL Showing Table Structure which works to isolate the Line Items for the
current Quotaion base on a control on the top level form


SELECT Tbl_Quotation.Quotation_Ref, Tbl_Quotation.[Line_Item_No:]
FROM Tbl_Quotation
WHERE (((Tbl_Quotation.Quotation_Ref)=[Forms]![Frm_Main_Data]![Quote_Ref]));

i hope someone can assist, really stuck on the syntax for this one and
getting no where fast.

Regards
Click to expand...
Click to expand...
Click to expand...
 
T

Tom van Stiphout

On Thu, 4 Sep 2008 06:06:11 -0700, MikeJohnB

Can you zip and email your database, stripped down to the bare
necessities to demonstrate this problem, with a little readme text for
me to get up to speed?
I'm sure you can figure out my email address.

--Tom.
Microsoft Access MVP

Tom I have also tried the following with the same error

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim i As Integer
'strSQL = "SELECT * From Tbl_Quotation ORDER BY
[Tbl_Quotation].[Line_Item_No_]"
strSQL = "SELECT [Tbl_Quotation].[Quotation_Ref],
[Tbl_Quotation].[Line_Item_No:]"
strSQL = strSQL & "FROM Tbl_Quotation WHERE
[Tbl_Quotation].[Quotation_Ref]=[Forms]![Frm_Main_Data]![Quote_Ref];"
Set db = CurrentDb
Debug.Print strSQL
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

rst.MoveFirst
i = 1
Do
rst.Edit
rst![Line_Item_No_] = i
Debug.Print Me.Line_Item_No_
rst.Update
rst.MoveNext
Loop Until rst.EOF
rst.Close
Set rst = Nothing
Set db = Nothing
Click to expand...
 
M

MikeJohnB

Tom

name at hotmail dot com

Address topic Access, will reply
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


Tom van Stiphout said:
On Thu, 4 Sep 2008 06:06:11 -0700, MikeJohnB

Can you zip and email your database, stripped down to the bare
necessities to demonstrate this problem, with a little readme text for
me to get up to speed?
I'm sure you can figure out my email address.

--Tom.
Microsoft Access MVP

Tom I have also tried the following with the same error

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim i As Integer
'strSQL = "SELECT * From Tbl_Quotation ORDER BY
[Tbl_Quotation].[Line_Item_No_]"
strSQL = "SELECT [Tbl_Quotation].[Quotation_Ref],
[Tbl_Quotation].[Line_Item_No:]"
strSQL = strSQL & "FROM Tbl_Quotation WHERE
[Tbl_Quotation].[Quotation_Ref]=[Forms]![Frm_Main_Data]![Quote_Ref];"
Set db = CurrentDb
Debug.Print strSQL
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

rst.MoveFirst
i = 1
Do
rst.Edit
rst![Line_Item_No_] = i
Debug.Print Me.Line_Item_No_
rst.Update
rst.MoveNext
Loop Until rst.EOF
rst.Close
Set rst = Nothing
Set db = Nothing
Click to expand...
Click to expand...
 
M

MikeJohnB

Hi Tom
I have overcome the problem but now have a different problem. I will send
you the database if I can work our your addy. However, I have pointed you to
my addy being the postname on here at hotmail.com Not the name at the bottom
of this message. Here is the latest code (Note SQL now corrected which was
the original problem)

Private Sub ButtonDeleteLine_DblClick(Cancel As Integer)

'DoCmd.RunCommand acCmdSelectRecord '(Add this line when Development
Complete)
'DoCmd.RunCommand acCmdDeleteRecord '(Add this line when Development
Complete)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL, FrmViewer As String
Dim i As Integer

FrmViewer =
[Forms]![Frm_Main_Data]![Frm_Quotation_Item].[Form]![Quotation_Ref]

strSQL = "SELECT Tbl_Quotation.[Line_Item_No:] FROM Tbl_Quotation "
strSQL = strSQL & "WHERE [Tbl_Quotation].[Quotation_Ref] = '" &
FrmViewer & "'"
strSQL = strSQL & " ORDER BY Tbl_Quotation.[Line_Item_No:];"

Set db = CurrentDb
Debug.Print strSQL 'Check the SQL Path
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
'Set rst = db.OpenRecordset(strSQL)'Trial Alternative
rst.MoveFirst
i = 1
Do
rst.Edit
rst![Line_Item_No_] = i <<<<<<<<Falls over here Runtime Error 3625

Debug.Print Me.Line_Item_No_ 'Check the value of Line Item
rst.Update
rst.MoveNext
Debug.Print i
i = i + 1
Loop Until rst.EOF
rst.Close
Set rst = Nothing
Set db = Nothing

Exit_ButtonDeleteLine_Click:
Exit Sub

Err_ButtonDeleteLine_Click:
MsgBox Err.Description
Resume Exit_ButtonDeleteLine_Click
End Sub

I have a feeling that the line item is not incrementing to the next item,
The rst.movenext is working correctly as it loops 15 times for 15 line items.

I am lost a little because I have never used this method in this context
before but I have a feeling that I need to make the line_Item_No_ field no
equal the rst. However, Line_Item_No_ Debug Print is always = to 1 (it does
not increment with the rst)

Sorry to be a pain on this one though but I feel I am nearly there now, or
perhaps I'm a million Miles Away?

Kindest regards





--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


MikeJohnB said:
Tom

name at hotmail dot com

Address topic Access, will reply
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


Tom van Stiphout said:
On Thu, 4 Sep 2008 06:06:11 -0700, MikeJohnB

Can you zip and email your database, stripped down to the bare
necessities to demonstrate this problem, with a little readme text for
me to get up to speed?
I'm sure you can figure out my email address.

--Tom.
Microsoft Access MVP

Tom I have also tried the following with the same error

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim i As Integer
'strSQL = "SELECT * From Tbl_Quotation ORDER BY
[Tbl_Quotation].[Line_Item_No_]"
strSQL = "SELECT [Tbl_Quotation].[Quotation_Ref],
[Tbl_Quotation].[Line_Item_No:]"
strSQL = strSQL & "FROM Tbl_Quotation WHERE
[Tbl_Quotation].[Quotation_Ref]=[Forms]![Frm_Main_Data]![Quote_Ref];"
Set db = CurrentDb
Debug.Print strSQL
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

rst.MoveFirst
i = 1
Do
rst.Edit
rst![Line_Item_No_] = i
Debug.Print Me.Line_Item_No_
rst.Update
rst.MoveNext
Loop Until rst.EOF
rst.Close
Set rst = Nothing
Set db = Nothing
Click to expand...
Click to expand...
Click to expand...
 
M

MikeJohnB

Hi Again Tom, I have figured out the full code now, its taken an age to
understand what was but all works as it should now.

I have attached the code that works here just in case someone is interested.
However, thanks for your help, much appreciated, cheers

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL, FrmViewer As String
Dim i As Integer

FrmViewer =
[Forms]![Frm_Main_Data]![Frm_Quotation_Item].[Form]![Quotation_Ref]

strSQL = "SELECT Tbl_Quotation.[Line_Item_No:] As Line FROM
Tbl_Quotation "
strSQL = strSQL & "WHERE [Tbl_Quotation].[Quotation_Ref] = '" &
FrmViewer & "'"
strSQL = strSQL & " ORDER BY Tbl_Quotation.[Line_Item_No:];"

Set db = CurrentDb
'Debug.Print strSQL 'Debugging Only
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
rst.MoveFirst
i = 1 'Set New Line Item Nuber
Do ' Loop Until End Of File EOF
rst.Edit
rst![Line] = i ' Line Item Old Number Replace with New Number
rst.Update
rst.MoveNext ' Get the Next Line Number
'Debug.Print i ' Debugging Only
i = i + 1
Loop Until rst.EOF
rst.Close ' Close the Recordset
Set rst = Nothing 'tidy Up
Set db = Nothing 'tidy up

All I have to do now is to tidy the rest of the programme up
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


MikeJohnB said:
Hi Tom
I have overcome the problem but now have a different problem. I will send
you the database if I can work our your addy. However, I have pointed you to
my addy being the postname on here at hotmail.com Not the name at the bottom
of this message. Here is the latest code (Note SQL now corrected which was
the original problem)

Private Sub ButtonDeleteLine_DblClick(Cancel As Integer)

'DoCmd.RunCommand acCmdSelectRecord '(Add this line when Development
Complete)
'DoCmd.RunCommand acCmdDeleteRecord '(Add this line when Development
Complete)

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL, FrmViewer As String
Dim i As Integer

FrmViewer =
[Forms]![Frm_Main_Data]![Frm_Quotation_Item].[Form]![Quotation_Ref]

strSQL = "SELECT Tbl_Quotation.[Line_Item_No:] FROM Tbl_Quotation "
strSQL = strSQL & "WHERE [Tbl_Quotation].[Quotation_Ref] = '" &
FrmViewer & "'"
strSQL = strSQL & " ORDER BY Tbl_Quotation.[Line_Item_No:];"

Set db = CurrentDb
Debug.Print strSQL 'Check the SQL Path
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
'Set rst = db.OpenRecordset(strSQL)'Trial Alternative
rst.MoveFirst
i = 1
Do
rst.Edit
rst![Line_Item_No_] = i <<<<<<<<Falls over here Runtime Error 3625

Debug.Print Me.Line_Item_No_ 'Check the value of Line Item
rst.Update
rst.MoveNext
Debug.Print i
i = i + 1
Loop Until rst.EOF
rst.Close
Set rst = Nothing
Set db = Nothing

Exit_ButtonDeleteLine_Click:
Exit Sub

Err_ButtonDeleteLine_Click:
MsgBox Err.Description
Resume Exit_ButtonDeleteLine_Click
End Sub

I have a feeling that the line item is not incrementing to the next item,
The rst.movenext is working correctly as it loops 15 times for 15 line items.

I am lost a little because I have never used this method in this context
before but I have a feeling that I need to make the line_Item_No_ field no
equal the rst. However, Line_Item_No_ Debug Print is always = to 1 (it does
not increment with the rst)

Sorry to be a pain on this one though but I feel I am nearly there now, or
perhaps I'm a million Miles Away?

Kindest regards





--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


MikeJohnB said:
Tom

name at hotmail dot com

Address topic Access, will reply
--
Advice to Posters.
Check your post for replies or request for more information.
Consider providing some feed back to the response you have recieved.
Kindest Regards Mike B


Tom van Stiphout said:
On Thu, 4 Sep 2008 06:06:11 -0700, MikeJohnB

Can you zip and email your database, stripped down to the bare
necessities to demonstrate this problem, with a little readme text for
me to get up to speed?
I'm sure you can figure out my email address.

--Tom.
Microsoft Access MVP


Tom I have also tried the following with the same error

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim i As Integer
'strSQL = "SELECT * From Tbl_Quotation ORDER BY
[Tbl_Quotation].[Line_Item_No_]"
strSQL = "SELECT [Tbl_Quotation].[Quotation_Ref],
[Tbl_Quotation].[Line_Item_No:]"
strSQL = strSQL & "FROM Tbl_Quotation WHERE
[Tbl_Quotation].[Quotation_Ref]=[Forms]![Frm_Main_Data]![Quote_Ref];"
Set db = CurrentDb
Debug.Print strSQL
Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)

rst.MoveFirst
i = 1
Do
rst.Edit
rst![Line_Item_No_] = i
Debug.Print Me.Line_Item_No_
rst.Update
rst.MoveNext
Loop Until rst.EOF
rst.Close
Set rst = Nothing
Set db = Nothing
Click to expand...
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

Invalid Use of Null 3
Error message on Treeview 1
Make table from recordset 2
cannot update. DB or object is read-only 3027 4
Help with Type Mismatch Error 2
Working with sequential Data 5
Run-time error 3061 2
Deleting a single field of a single record 3

Top