Do Until Loop Help!

[Constantine AI]

Hello, i am wanting a Loop procedure to check details of all the multiple rows with the following D-LOOKUP procedures;

[*]StkID.Value = DLookup("[StkID]", "stkmas", "[StkShortDesc] = Forms![frmPreSOLine]![StkShortDesc]")[*]Price.Value = DLookup("[SalePrice1]", "primas", "[StkID] = Forms![frmPreSOLine]![StkID]")

For the Loop procedure to work i have used the following code:

Dim dbs As Database
Dim rst As Recordset
Dim Reply As String
Dim strSQL As String
 
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM preordlin")
strSQL = "INSERT INTO stkmas ([StkShortDesc],[Width],[Depth],[Height]) SELECT [StkShortDesc],[Width],[Depth],[Height] FROM preordlin WHERE IsNull(StkID)"
 
Do Until rst.EOF
    StkID.Value = DLookup("[StkID]", "stkmas", "[StkShortDesc] = Forms![frmPreSOLine]![StkShortDesc]")
    Price.Value = DLookup("[SalePrice1]", "primas", "[StkID] = Forms![frmPreSOLine]![StkID]")
    rst.MoveNext
Loop
 
If IsNull(StkID) Then
    Reply = MsgBox("There are Stock Items that present that do not belong to our existing Stock files. Would you like to ADD then?", vbYesNo, "None Existent Stock Details!")
    If Reply = vbYes Then
        DoCmd.RunSQL strSQL
    Else
    End If
Else
End If

The only problem is when i click on the button to execute this procedure, it works but only works on 1 row at a time and you have to keep clicking the button on all the rows of data. Is there a way that does it for all of them at once?

Please help!

Thanks in advance.

Chris

[ADezii]

Just subscribing, I took the liberty of rewriting your code for the sake of clarity. I realize that you are new to this, but in the future, kindly use the Code Tags for obvious reasons. I really don't have the time now to help you with a Reply to this Thread, but I'll check back later and in the meantime, I'm sure someone will provide you with assistance.

• StkID.Value = DLookup("[StkID]", "stkmas", "[StkShortDesc] = Forms![frmPreSOLine]![StkShortDesc]")
• Price.Value = DLookup("[SalePrice1]", "primas", "[StkID] = Forms![frmPreSOLine]![StkID]")

'For the Loop procedure to work i have used the following code:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim Reply As String
Dim strSQL As String
 
Reply = "There are Stock Items that present that do not belong to our " & _
        "existing Stock files. Would you like to ADD then?"
 
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT * FROM preordlin")
strSQL = "INSERT INTO stkmas ([StkShortDesc],[Width],[Depth],[Height]) SELECT " & _
         "[StkShortDesc],[Width],[Depth],[Height] FROM preordlin WHERE IsNull(StkID)"
 
Do Until rst.EOF
  StkID.Value = DLookup("[StkID]", "stkmas", "[StkShortDesc] = Forms![frmPreSOLine]![StkShortDesc]")
  Price.Value = DLookup("[SalePrice1]", "primas", "[StkID] = Forms![frmPreSOLine]![StkID]")
    rst.MoveNext
Loop
 
If IsNull(StkID) Then
  Reply = MsgBox(Reply, vbYesNo, "None Existent Stock Details!")
  If Reply = vbYes Then
    DoCmd.RunSQL strSQL
  Else
  End If
Else
End If

[NeoPa]

It's not clear what you're trying to achieve, but if you look at lines #10 through #14 (Do Until ... Loop) you will see that they assign values to a couple of items which are neither explained nor referred to elsewhere.

As the first thing you do after assigning them once is to assign them again with (presumably) different values, it's clear to see that this loop is not doing anything for you. If these objects are form controls then there will be no time to see them before they disappear again. Only the last record will remain visible. If they are intended to reference fields in a recordset somewhere (they don't) then apart from that they are never written away anyway (No rst.Edit & rst.Update are ever called).

Hopefully these pointers will help you find your problems. I'm afraid your question is otherwise too unclear to help further.