By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,222 Members | 1,067 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,222 IT Pros & Developers. It's quick & easy.

Small rst.Edit Loop Glitch (Can anyone Help!)

100+
P: 129
Hi i have some details below and i want to update the table by inputting the values where i have detailed 'Blank'.

OrderNo StkID StkShortDesc Qty Price
28 Blank 300 DL 1 Blank
28 Blank 600 SA TU 1 Blank

The coding i have used is as follows:

Expand|Select|Wrap|Line Numbers
  1. Dim strSQL As String
  2.     Dim Reply As String
  3.     Dim db As Database
  4.     Dim rst As Recordset
  5.  
  6.     DoCmd.SetWarnings False
  7.  
  8.     Set db = CurrentDb()
  9.     Set rst = db.OpenRecordset("SELECT * FROM preordlin")
  10.  
  11.     Do
  12.         rst.Edit
  13.         rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] = Forms![frmPreSOLine]![StkShortDesc]")
  14.         rst!Price = DLookup("[SalePrice1]", "primas", "[StkID] = Forms![frmPreSOLine]![StkID]")
  15.         rst.Update
  16.         rst.MoveNext
  17.     Loop Until rst.EOF
  18.     rst.Close
  19.     Me.Requery
  20.     Set rst = Nothing
  21.  
  22.     DoCmd.SetWarnings True
The coding to some degree works it looks at both records and assigns details to them, however it places the details for one record to both entries. For example the outcome looks like this:

OrderNo StkID StkShortDesc Qty Price
28 201 300 DL 1 Blank
28 201 600 SA TU 1 38.00

It assigned the first values to the second row of details. The outcome should look like this:

OrderNo StkID StkShortDesc Qty Price
28 201 300 DL 1 38.00
28 280 600 SA TU 1 58.00

Could anyone see where i am going wrong in my coding?

Regards

Chris
Aug 23 '08 #1
Share this Question
Share on Google+
18 Replies


nico5038
Expert 2.5K+
P: 3,072
Guess you need to check upon the loop again.
I'm puzzled why you use the form fields like "Forms![frmPreSOLine]![StkID]", as they will be "stable" and don't change in the loop.

Moreover, storing a price in an order detail row that's also recorded in a product table is against normalization rules. When needed you JOIN your tables and that way you get the actual price.

Nic;o)
Aug 23 '08 #2

100+
P: 129
Guess you need to check upon the loop again.
I'm puzzled why you use the form fields like "Forms![frmPreSOLine]![StkID]", as they will be "stable" and don't change in the loop.

Moreover, storing a price in an order detail row that's also recorded in a product table is against normalization rules. When needed you JOIN your tables and that way you get the actual price.

Nic;o)
Yeah sorry i am not much cop with coding. I have changed the coding to what you sent me last time and i have the following:

Expand|Select|Wrap|Line Numbers
  1.     DoCmd.SetWarnings False
  2.  
  3.     Set db = CurrentDb()
  4.     Set rst = db.OpenRecordset("SELECT * FROM preordlin")
  5.  
  6.     Do
  7.         rst.Edit
  8.         rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] =" & rst!StkShortDesc)
  9.         rst!Price = DLookup("[SalePrice1]", "primas", "[StkID] =" & rst!StkID)
  10.         rst.Update
  11.         rst.MoveNext
  12.     Loop Until rst.EOF
  13.     rst.Close
  14.     Me.Requery
  15.     Set rst = Nothing
  16.  
  17.     'StkID.value = DLookup("[StkID]", "stkmas", "[StkShortDesc] = Forms![frmPreSOLine]![StkShortDesc]")
  18.     'Price.value = DLookup("[SalePrice1]", "primas", "[StkID] = Forms![frmPreSOLine]![StkID]")
  19.  
  20.     DoCmd.SetWarnings True
However i receive an error on line 8

Run-time error '3075':
Syntax error (missing operator) in query expression '[StkShortDesc] = 300 DL'.
Aug 23 '08 #3

nico5038
Expert 2.5K+
P: 3,072
For text fields you'll need to embed the string in single quotes like:
Expand|Select|Wrap|Line Numbers
  1. rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] ='" & rst!StkShortDesc & "'")
  2.  
Nic;o)
Aug 23 '08 #4

100+
P: 129
For text fields you'll need to embed the string in single quotes like:
Expand|Select|Wrap|Line Numbers
  1. rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] ='" & rst!StkShortDesc & "'")
  2.  
Nic;o)
Thanks Nico that worked like a treat! Cheers again. I was wondering if you could give me some advice on another issue. U solved my Loop problem then as well. I have this code for inserting more details based on the StkID.

Expand|Select|Wrap|Line Numbers
  1.     Dim strSQL As String
  2.     Dim db As Database
  3.     Dim rst As Recordset
  4.  
  5.     DoCmd.SetWarnings False
  6.  
  7.     DoCmd.GoToRecord , , acFirst
  8.  
  9.     Set db = CurrentDb()
  10.     Set rst = db.OpenRecordset("SELECT * FROM preordlin")
  11.  
  12.     Do
  13.         rst.Edit
  14.         strSQL = "INSERT INTO preordlin ([OrderNo],[StkID],[StkShortDesc],[Width],[Depth],[Height],[Qty]) SELECT forms!frmCustomerOrderForm!sfrmSOHeader!OrderNo AS OrderNo,[SubStkID],[stkmas.StkShortDesc],preordlin.Width,preordlin.Depth,preordlin.Height,[Qty] FROM (stkbommas INNER JOIN stkmas ON stkbommas.substkID = stkmas.StkID) WHERE stkbommas.StkID =" & rst!StkID
  15.         DoCmd.RunSQL strSQL
  16.         rst!SuppNo = DLookup("[SuppNo]", "stkmas", "[StkID] = Forms![frmPreSOLine]![StkID]")
  17.         rst.Update
  18.         rst.MoveNext
  19.     Loop Until rst.EOF
  20.     rst.Close
  21.     Me.Requery
  22.     Set rst = Nothing
  23.  
  24.     DoCmd.SetWarnings True
This code works almost perfectly, however the width, depth and height fields stay fixed to the first record and not change based on the rows. I have just tried changing the code from "preordlin.Width,preordlin.Depth,preordlin.Hei ght" to this "rst!Width,rst!Depth,rst!Height" unfortunately i receive another error asking me to input the fields for rst!Width,rst!Depth,rst!Height.

Do you have any advice you could give me please?
Aug 23 '08 #5

nico5038
Expert 2.5K+
P: 3,072
Just stop using form variables like forms!frmCustomerOrderForm!sfrmSOHeader!OrderNo and take the record set as the source.

Nic;o)
Aug 23 '08 #6

100+
P: 129
Just stop using form variables like forms!frmCustomerOrderForm!sfrmSOHeader!OrderNo and take the record set as the source.

Nic;o)
Ok i have started using the recordset more with the issue spoken about. I have the following code:

Expand|Select|Wrap|Line Numbers
  1.     Dim db As Database
  2.     Dim rst As Recordset
  3.     Dim strSQL As String
  4.  
  5.     DoCmd.SetWarnings False
  6.  
  7.     Set db = CurrentDb()
  8.     Set rst = db.OpenRecordset("SELECT * FROM preordlin")
  9.  
  10.     Do
  11.         rst.Edit
  12.         strSQL = "INSERT INTO preordlin ([OrderNo],[StkID],[StkShortDesc],[Width],[Depth],[Height],[Qty]) SELECT forms!frmCustomerOrderForm!sfrmSOHeader!OrderNo AS OrderNo,[SubStkID],[stkmas.StkShortDesc],rst!Width,rst!Depth,rst!Height,[Qty] FROM (stkbommas INNER JOIN stkmas ON stkbommas.substkID = stkmas.StkID) WHERE stkbommas.StkID =" & rst!StkID
  13.         DoCmd.RunSQL strSQL
  14.         rst!SuppNo = DLookup("[SuppNo]", "stkmas", "[StkID] = Forms![frmPreSOLine]![StkID]")
  15.         rst.update
  16.         rst.MoveNext
  17.     Loop Until rst.EOF
  18.     rst.Close
  19.     Me.Requery
  20.     Set rst = Nothing
  21.  
  22.     cmdUpdateInformation.Enabled = False
Upon executing this code it asks me to enter the rst!Width, rst!Depth and rst!Width for each row. Now i thought i needed to tell it where to look so i tried the following code:

Expand|Select|Wrap|Line Numbers
  1.     Set db = CurrentDb()
  2.     Set rst = db.OpenRecordset("SELECT * FROM preordlin")
  3.  
  4.     Do
  5.         rst.Edit
  6.         strSQL = "INSERT INTO preordlin ([OrderNo],[StkID],[StkShortDesc],[Width],[Depth],[Height],[Qty]) SELECT forms!frmCustomerOrderForm!sfrmSOHeader!OrderNo AS OrderNo,[SubStkID],[stkmas.StkShortDesc],rst!Width,rst!Depth,rst!Height,[Qty] FROM (stkbommas INNER JOIN stkmas ON stkbommas.substkID = stkmas.StkID) WHERE stkbommas.StkID =" & rst!StkID
  7.         DoCmd.RunSQL strSQL
  8.  
  9.         rst!Width = Width.value
  10.         rst!Depth = Depth.value
  11.         rst!Height = Height.value
  12.  
  13.         rst!SuppNo = DLookup("[SuppNo]", "stkmas", "[StkID] = Forms![frmPreSOLine]![StkID]")
  14.         rst.update
  15.         rst.MoveNext
  16.     Loop Until rst.EOF
  17.     rst.Close
  18.     Me.Requery
  19.     Set rst = Nothing
Yet it still asks me to input the values, what am i doing wrong?
Aug 24 '08 #7

nico5038
Expert 2.5K+
P: 3,072
Please read Quotes (') and Double-Quotes (") - Where and When to use them on how to build a proper string. The Access database won't be able to find the rst fields as they are in your code and will need to be translated...

Making a string is one of the most important "tricks" when learning to code VBA.

Nic;o)
Aug 24 '08 #8

100+
P: 129
I know your gonna get miffed with me, i have read your link, i understand it a bit more now. I understand the different between rst and Forms!.... way. I understand about the "" and ' vb and sql. From what you said previously about the rst!Width, rst!Depth and rst!Height value need to be translated outside the internal loop. I have tried many ways to do this and looked into many websites. I stll cant fix it.
Aug 24 '08 #9

nico5038
Expert 2.5K+
P: 3,072
When you can use a SELECT in the INSERT INTO statement, then the fields can be selected form the joined tables like:
strSQL = "INSERT INTO preordlin ([OrderNo],[StkID],[StkShortDesc],[Width],[Depth],[Height],[Qty]) SELECT [OrderNo],[StkID],[StkShortDesc],[Width],[Depth],[Height],[Qty] FROM (stkbommas INNER JOIN stkmas ON stkbommas.substkID = stkmas.StkID) WHERE stkbommas.StkID =" & rst!StkID

When using an INSERT INTO with a VALUES clause, then you'll need to string the values into the command like:
strSQL = "INSERT INTO preordlin ([OrderNo],[StkID],[StkShortDesc],[Width],[Depth],[Height],[Qty]) VALUES (" & rst!OrderNo & ", " & rst!SubStkID & ", '" & rst!StkShortDesc & "', ... etc.

Always keep in mind how the strSQL looks like when constructed and in general it's best to test the query in the query editor before creating it in code.

Nic;o)
Aug 24 '08 #10

100+
P: 129
Thanks a million nico that worked a treat for that reference problem. Now i was wondering if i could bother you for one more reference problem i have. I created some code to generate a PO Number and to assign that number to numerous rows of data where SuppNo is equal to (Name):

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdGenerate_Click()
  2.  
  3.     Dim db As Database
  4.     Dim rst As Recordset
  5.     Dim rst2 As Recordset
  6.     Dim rst3 As Recordset
  7.     Dim IntPONo As Integer
  8.     Dim strSQL As String
  9.     Dim PO As String
  10.  
  11.     DoCmd.SetWarnings False
  12.  
  13.     Set db = CurrentDb()
  14.     Set rst = db.OpenRecordset("SELECT * FROM pordhdr")
  15.  
  16.     rst.Edit
  17.     strSQL = "INSERT INTO pordhdr ([SuppNo],[PODate],[DueDate]) SELECT forms!frmPOGenerator!txtSuppNo AS SuppNo,[OrderDate],[DueDate] FROM ordhdr WHERE ordhdr.OrderNo = forms!frmPOGenerator!txtOrderNo"
  18.     DoCmd.RunSQL strSQL
  19.     rst.update
  20.     rst.Close
  21.  
  22.     Set rst3 = db.OpenRecordset("SELECT * FROM pordhdr")
  23.  
  24.     rst3.MoveLast
  25.         PO = MsgBox("Purchase Order " & rst3!PONo & " Has Been Created. ", vbOKOnly, "Purchase Order Created")
  26.     rst3.Close
  27.  
  28.     Set rst3 = Nothing
  29.  
  30.     Set db = CurrentDb()
  31.     Set rst2 = db.OpenRecordset("SELECT * FROM ordlin")
  32.  
  33.     rst2.Edit
  34.     strSQL = "UPDATE ordlin SET PONo = rst3!PONo WHERE ordlin.OrderNo = forms!frmPOGenerator!txtOrderNo And ordlin.SuppNo = forms!frmPOGenerator!txtSuppNo"
  35.     DoCmd.RunSQL strSQL
  36.     rst2.update
  37.     rst2.Close
  38.  
  39.     Set rst2 = Nothing
  40.  
  41.     DoCmd.SetWarnings True
  42.  
  43.     lstSuppNoSelect.Requery
  44.  
  45. End Sub
I encounter the problem when i refernce the code:

strSQL = "UPDATE ordlin SET PONo = rst3!PONo WHERE ordlin.OrderNo = forms!frmPOGenerator!txtOrderNo And ordlin.SuppNo = forms!frmPOGenerator!txtSuppNo"

Now i thought it maybe because i closed the rst3 but that didn't make a difference, turning it off. At the moment is asks me to enter a figure which works when i input it but as usual i require referencing fields.

I was wondering if you could suggest something?

Regards

Chris

P.s. I swear this is the last time i am gonna bother you. I really appreciate your help, plus i am learning stuff at the same time. Thanks again.
Aug 25 '08 #11

nico5038
Expert 2.5K+
P: 3,072
1) Please reread my statement about using form fields is a query.
2) Please reread the stringing article as the rst3 won't be recornized.

Nic;o)
Aug 25 '08 #12

100+
P: 129
1) Please reread my statement about using form fields is a query.
2) Please reread the stringing article as the rst3 won't be recornized.

Nic;o)
Hi i have looked through the quotes help again and your forms guide and have re-attempted the code:

Expand|Select|Wrap|Line Numbers
  1.     Dim db As Database
  2.     Dim rst As Recordset
  3.     Dim rst2 As Recordset
  4.     Dim rst3 As Recordset
  5.     Dim IntPONo As Integer
  6.     Dim strSQL As String
  7.     Dim PO As String
  8.  
  9.     DoCmd.SetWarnings False
  10.  
  11.     Set db = CurrentDb()
  12.     Set rst = db.OpenRecordset("SELECT * FROM pordhdr")
  13.  
  14.     rst.Edit
  15.     strSQL = "INSERT INTO pordhdr ([SuppNo],[PODate],[DueDate]) SELECT forms!frmPOGenerator!txtSuppNo AS SuppNo,[OrderDate],[DueDate] FROM ordhdr WHERE ordhdr.OrderNo = forms!frmPOGenerator!txtOrderNo"
  16.     DoCmd.RunSQL strSQL
  17.     rst.update
  18.     rst.Close
  19.  
  20.     Set rst3 = db.OpenRecordset("SELECT * FROM pordhdr")
  21.  
  22.     rst3.MoveLast
  23.         PO = MsgBox("Purchase Order " & rst3!PONo & " Has Been Created. ", vbOKOnly, "Purchase Order Created")
  24.     rst3.Close
  25.  
  26.     Set rst3 = Nothing
  27.  
  28.     Set db = CurrentDb()
  29.     Set rst2 = db.OpenRecordset("SELECT * FROM ordlin")
  30.  
  31.     rst2.Edit
  32.     strSQL = "UPDATE ordlin SET [PONo] = 'rst3!PONo' WHERE ([ordlin.OrderNo] = [forms]![frmPOGenerator]![txtOrderNo] And [ordlin.SuppNo] = [forms]![frmPOGenerator]![txtSuppNo])"
  33.     DoCmd.RunSQL strSQL
  34.     rst2.update
  35.     rst2.Close
  36.  
  37.     Set rst2 = Nothing
  38.  
  39.     DoCmd.SetWarnings True
  40.  
  41.     lstSuppNoSelect.Requery
The main part of the code i have used the '' signs as follows:
Expand|Select|Wrap|Line Numbers
  1.     rst2.Edit
  2.     strSQL = "UPDATE ordlin SET [PONo] = 'rst3!PONo' WHERE ([ordlin.OrderNo] = [forms]![frmPOGenerator]![txtOrderNo] And [ordlin.SuppNo] = [forms]![frmPOGenerator]![txtSuppNo])"
  3.     DoCmd.RunSQL strSQL
  4.     rst2.update
  5.     rst2.Close
I did try the various other signs, however this one didn't give me error or any input yet it does nothing. Would i be close or way off the correct answer?

Chris
Aug 25 '08 #13

nico5038
Expert 2.5K+
P: 3,072
Try:

strSQL = "UPDATE ordlin SET [PONo] = '" & rst3!PONo & "' WHERE ([ordlin.OrderNo] = [forms]![frmPOGenerator]![txtOrderNo] And [ordlin.SuppNo] = [forms]![frmPOGenerator]![txtSuppNo])"

When the rst3!PONo is a numeric field, else drop the single quotes (').

Nic;o)
Aug 25 '08 #14

100+
P: 129
Try:

strSQL = "UPDATE ordlin SET [PONo] = '" & rst3!PONo & "' WHERE ([ordlin.OrderNo] = [forms]![frmPOGenerator]![txtOrderNo] And [ordlin.SuppNo] = [forms]![frmPOGenerator]![txtSuppNo])"

When the rst3!PONo is a numeric field, else drop the single quotes (').

Nic;o)
Hi i tried that and received a debug error code 91 stating object variable not defined i looked up the error on microsoft which tells me i am not referencing a valid object. Which to me i thought i was. I hate errors!

Expand|Select|Wrap|Line Numbers
  1.     Set rst3 = db.OpenRecordset("SELECT * FROM pordhdr")
  2.  
  3.     rst3.MoveLast
  4.         PO = MsgBox("Purchase Order " & rst3!PONo & " Has Been Created. ", vbOKOnly, "Purchase Order Created")
  5.     rst3.Close
  6.  
  7.     Set rst3 = Nothing
  8.  
  9.     Set db = CurrentDb()
  10.     Set rst2 = db.OpenRecordset("SELECT * FROM ordlin")
  11.  
  12.     rst2.Edit
  13.     strSQL = "UPDATE ordlin SET [PONo] = '" & rst3!PONo & "' WHERE ([ordlin.OrderNo] = [forms]![frmPOGenerator]![txtOrderNo] And [ordlin.SuppNo] = [forms]![frmPOGenerator]![txtSuppNo])"
  14.     DoCmd.RunSQL strSQL
  15.     rst2.update
  16.     rst2.Close
  17.  
  18.     Set rst2 = Nothing
Aug 25 '08 #15

nico5038
Expert 2.5K+
P: 3,072
Drop the "SET db" line and try:

Set rst3 = currentdb.OpenRecordset("SELECT * FROM pordhdr")

Nic;o)
Aug 25 '08 #16

100+
P: 129
Drop the "SET db" line and try:

Set rst3 = currentdb.OpenRecordset("SELECT * FROM pordhdr")

Nic;o)
Brilliant what you said was right, all i had to do extra was change the recordset to movelast. Thankyou very much your help has been much appreciated!

Regards

Chris
Aug 25 '08 #17

100+
P: 129
How do i close this thread?
Aug 25 '08 #18

Expert Mod 2.5K+
P: 2,545
You can't close the thread - like all our threads it remains open after your questions have been answered to your satisfaction. We only close threads (debar further posts) if one of the site rules has been infringed.

MODERATOR
Aug 25 '08 #19

Post your reply

Sign in to post your reply or Sign up for a free account.