VBA Script - Find First Query Matching & Edit

[tehNellie]

Actually I lie.

DLookup() should do it.

prodID = dlookup("[ID]","products","[productname]= " & Me.ProductName )

danger of Dlookup is if there is a possiblity that more than one record might be returned.

takes things out of the realm of "coding" and into using Access' inbuilt functionality but why write a bunch of code to do something that Access provides for you?

 

[Jamz]

Just got it working

Option Compare Database
Option Explicit
Private Sub ProductName_Click()
    Dim Blanket_Orders As DAO.Database
    Dim rstRequests As DAO.Recordset
    Dim rst As DAO.Recordset
    Dim Count As Long
    Dim ProdID As String
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim vSql As String
 
Set db = CurrentDb
vSql = "SELECT products.[ID] FROM products WHERE products.[ProductName]= '" & Me.ProductName & "';"
Set rs = db.OpenRecordset(vSql, dbOpenDynaset, dbSeeChanges)
    Set Blanket_Orders = CurrentDb
    Set rstRequests = Blanket_Orders.OpenRecordset("Requests")
 
    ' Scan the records from beginning to each
    While Not rstRequests.EOF
 
      ' As we only need to insert one item at a time start a count
      If Count = 0 Then
        ' Not sure if WRID is going to be NULL or a zero length string, so we'll include both.
        If rstRequests("productID").Value = rs![ID] And (IsNull(rstRequests("WRID").Value) Or rstRequests("WRID").Value = "") Then
 
            ' Then update the WRID and Taken values
            rstRequests.Edit
            rstRequests("WRID").Value = Forms![Manage Request].[ID]
            rstRequests("taken").Value = True
            rstRequests.Update
            Count = Count + 1
 
        End If
      End If
        ' Move to the next record and continue the same approach
        rstRequests.MoveNext
    Wend
 
    ' Tidy up
    rstRequests.Close
    Set rstRequests = Nothing
    Set Blanket_Orders = Nothing
    Me.Parent.Refresh
 
    rs.Close
    db.Close
 
End Sub

Dunno how, but it does lol - hope you can see what i was trying to do from that

 

[tehNellie]

You don't need db AND blanket_orders declared, you can use one of them for both.

As for how, exactly the same as rstrequests essentially. That's just opening the table directly, now you're doing the same thing but executing a query into a recordset instead.