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

[Constantine AI]

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:

Dim strSQL As String
    Dim Reply As String
    Dim db As Database
    Dim rst As Recordset
 
    DoCmd.SetWarnings False
 
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT * FROM preordlin")
 
    Do
        rst.Edit
        rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] = Forms![frmPreSOLine]![StkShortDesc]")
        rst!Price = DLookup("[SalePrice1]", "primas", "[StkID] = Forms![frmPreSOLine]![StkID]")
        rst.Update
        rst.MoveNext
    Loop Until rst.EOF
    rst.Close
    Me.Requery
    Set rst = Nothing
 
    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

[nico5038]

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)

[Constantine AI]

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:

    DoCmd.SetWarnings False
 
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT * FROM preordlin")
 
    Do
        rst.Edit
        rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] =" & rst!StkShortDesc)
        rst!Price = DLookup("[SalePrice1]", "primas", "[StkID] =" & rst!StkID)
        rst.Update
        rst.MoveNext
    Loop Until rst.EOF
    rst.Close
    Me.Requery
    Set rst = Nothing
 
    'StkID.value = DLookup("[StkID]", "stkmas", "[StkShortDesc] = Forms![frmPreSOLine]![StkShortDesc]")
    'Price.value = DLookup("[SalePrice1]", "primas", "[StkID] = Forms![frmPreSOLine]![StkID]")
 
    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'.

[nico5038]

For text fields you'll need to embed the string in single quotes like:

rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] ='" & rst!StkShortDesc & "'")
 

Nic;o)

[Constantine AI]

For text fields you'll need to embed the string in single quotes like:

rst!StkID = DLookup("[StkID]", "stkmas", "[StkShortDesc] ='" & rst!StkShortDesc & "'")
 

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.

    Dim strSQL As String
    Dim db As Database
    Dim rst As Recordset
 
    DoCmd.SetWarnings False
 
    DoCmd.GoToRecord , , acFirst
 
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT * FROM preordlin")
 
    Do
        rst.Edit
        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
        DoCmd.RunSQL strSQL
        rst!SuppNo = DLookup("[SuppNo]", "stkmas", "[StkID] = Forms![frmPreSOLine]![StkID]")
        rst.Update
        rst.MoveNext
    Loop Until rst.EOF
    rst.Close
    Me.Requery
    Set rst = Nothing
 
    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?

[nico5038]

Just stop using form variables like forms!frmCustomerOrderForm!sfrmSOHeader!OrderNo and take the record set as the source.

Nic;o)

[Constantine AI]

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:

    Dim db As Database
    Dim rst As Recordset
    Dim strSQL As String
 
    DoCmd.SetWarnings False
 
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT * FROM preordlin")
 
    Do
        rst.Edit
        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
        DoCmd.RunSQL strSQL
        rst!SuppNo = DLookup("[SuppNo]", "stkmas", "[StkID] = Forms![frmPreSOLine]![StkID]")
        rst.update
        rst.MoveNext
    Loop Until rst.EOF
    rst.Close
    Me.Requery
    Set rst = Nothing
 
    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:

    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT * FROM preordlin")
 
    Do
        rst.Edit
        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
        DoCmd.RunSQL strSQL
 
        rst!Width = Width.value
        rst!Depth = Depth.value
        rst!Height = Height.value
 
        rst!SuppNo = DLookup("[SuppNo]", "stkmas", "[StkID] = Forms![frmPreSOLine]![StkID]")
        rst.update
        rst.MoveNext
    Loop Until rst.EOF
    rst.Close
    Me.Requery
    Set rst = Nothing

Yet it still asks me to input the values, what am i doing wrong?

[nico5038]

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)

[Constantine AI]

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.

[nico5038]

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)

[Constantine AI]

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):

Private Sub cmdGenerate_Click()
 
    Dim db As Database
    Dim rst As Recordset
    Dim rst2 As Recordset
    Dim rst3 As Recordset
    Dim IntPONo As Integer
    Dim strSQL As String
    Dim PO As String
 
    DoCmd.SetWarnings False
 
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT * FROM pordhdr")
 
    rst.Edit
    strSQL = "INSERT INTO pordhdr ([SuppNo],[PODate],[DueDate]) SELECT forms!frmPOGenerator!txtSuppNo AS SuppNo,[OrderDate],[DueDate] FROM ordhdr WHERE ordhdr.OrderNo = forms!frmPOGenerator!txtOrderNo"
    DoCmd.RunSQL strSQL
    rst.update
    rst.Close
 
    Set rst3 = db.OpenRecordset("SELECT * FROM pordhdr")
 
    rst3.MoveLast
        PO = MsgBox("Purchase Order " & rst3!PONo & " Has Been Created. ", vbOKOnly, "Purchase Order Created")
    rst3.Close
 
    Set rst3 = Nothing
 
    Set db = CurrentDb()
    Set rst2 = db.OpenRecordset("SELECT * FROM ordlin")
 
    rst2.Edit
    strSQL = "UPDATE ordlin SET PONo = rst3!PONo WHERE ordlin.OrderNo = forms!frmPOGenerator!txtOrderNo And ordlin.SuppNo = forms!frmPOGenerator!txtSuppNo"
    DoCmd.RunSQL strSQL
    rst2.update
    rst2.Close
 
    Set rst2 = Nothing
 
    DoCmd.SetWarnings True
 
    lstSuppNoSelect.Requery
 
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.

[nico5038]

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)

[Constantine AI]

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:

    Dim db As Database
    Dim rst As Recordset
    Dim rst2 As Recordset
    Dim rst3 As Recordset
    Dim IntPONo As Integer
    Dim strSQL As String
    Dim PO As String
 
    DoCmd.SetWarnings False
 
    Set db = CurrentDb()
    Set rst = db.OpenRecordset("SELECT * FROM pordhdr")
 
    rst.Edit
    strSQL = "INSERT INTO pordhdr ([SuppNo],[PODate],[DueDate]) SELECT forms!frmPOGenerator!txtSuppNo AS SuppNo,[OrderDate],[DueDate] FROM ordhdr WHERE ordhdr.OrderNo = forms!frmPOGenerator!txtOrderNo"
    DoCmd.RunSQL strSQL
    rst.update
    rst.Close
 
    Set rst3 = db.OpenRecordset("SELECT * FROM pordhdr")
 
    rst3.MoveLast
        PO = MsgBox("Purchase Order " & rst3!PONo & " Has Been Created. ", vbOKOnly, "Purchase Order Created")
    rst3.Close
 
    Set rst3 = Nothing
 
    Set db = CurrentDb()
    Set rst2 = db.OpenRecordset("SELECT * FROM ordlin")
 
    rst2.Edit
    strSQL = "UPDATE ordlin SET [PONo] = 'rst3!PONo' WHERE ([ordlin.OrderNo] = [forms]![frmPOGenerator]![txtOrderNo] And [ordlin.SuppNo] = [forms]![frmPOGenerator]![txtSuppNo])"
    DoCmd.RunSQL strSQL
    rst2.update
    rst2.Close
 
    Set rst2 = Nothing
 
    DoCmd.SetWarnings True
 
    lstSuppNoSelect.Requery

The main part of the code i have used the '' signs as follows:

    rst2.Edit
    strSQL = "UPDATE ordlin SET [PONo] = 'rst3!PONo' WHERE ([ordlin.OrderNo] = [forms]![frmPOGenerator]![txtOrderNo] And [ordlin.SuppNo] = [forms]![frmPOGenerator]![txtSuppNo])"
    DoCmd.RunSQL strSQL
    rst2.update
    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

[nico5038]

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)

[Constantine AI]

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!

    Set rst3 = db.OpenRecordset("SELECT * FROM pordhdr")
 
    rst3.MoveLast
        PO = MsgBox("Purchase Order " & rst3!PONo & " Has Been Created. ", vbOKOnly, "Purchase Order Created")
    rst3.Close
 
    Set rst3 = Nothing
 
    Set db = CurrentDb()
    Set rst2 = db.OpenRecordset("SELECT * FROM ordlin")
 
    rst2.Edit
    strSQL = "UPDATE ordlin SET [PONo] = '" & rst3!PONo & "' WHERE ([ordlin.OrderNo] = [forms]![frmPOGenerator]![txtOrderNo] And [ordlin.SuppNo] = [forms]![frmPOGenerator]![txtSuppNo])"
    DoCmd.RunSQL strSQL
    rst2.update
    rst2.Close
 
    Set rst2 = Nothing

[nico5038]

Drop the "SET db" line and try:

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

Nic;o)

[Constantine AI]

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

[Constantine AI]

How do i close this thread?

[Stewart Ross]

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