Editing First Entry Found Auto Data

Jamz · #1 (**permalink**) 01-12-12, 08:08

Hi Guys,

Helped a lot on my last problem, and this one should be my last issue i would say.

So the tables:
Purchase Orders - Stores PO numbers of products in stock
Work Request - Main Table
Products - Links to Purchase Orders by productID

Right so, each work request can have many PO numebrs under it, PO's are added and can be taken at any time.

Basically on the form, I have a subform with a list of how many of each item is in stock, the subform has a title that is a link - I would like to be able to click on the link once, which would then add the WR number on the first, say Dell Latitude E5420, PO that is found, and refresh the form so it views in the other sub form.

It will be easier if you view the attached database, as you will get an idea - click on "Reserve New Stock" - The subform in the top right is the current stock, and the subform on the bottom of the page will be the reserverd stock under that work request. The user should be able to click any product in the top right subform, and this will add the details required to put the product as reserved (add wr number/id and mark as taken)

Please help

Jamz · #2 (**permalink**) 01-12-12, 10:47

So basically i need a VBA function that

1. Finds the id number of the product the user clicked on
2. Finds a list of po numbers under that product_id the user clicked
3. Takes the first record, and edits the record with the current work request id and mark a check box as "true"

Hope that makes sense

Jamz · #3 (**permalink**) 01-13-12, 03:21

I am thinking something along these lines

Code:

Private Sub ProductName_Click()

    Dim dbBlanket_Orders As Object
    Dim Requests As Object
    Dim fldEnumerator As Object
    Dim fldColumns As Object

    Set dbBlanket_Orders = CurrentDb
    Set rstPO = Blanket_Orders.OpenRecordset("Requests")
    Set fldColumns = rstPO.Fields

    ' Scan the records from beginning to each
    While Not Requests.EOF
        ' Check the current column
        For Each fldEnumerator In rstPO.Fields
            ' If the column is named ProductID
            If fldEnumerator.Name = "ProductID" Then
                ' If the Product ID equals the same as what the user clicked
                If fldEnumerator.Value = [clickvalue] Then
                    ' then change its value
                    rstPO.Edit
                    rstPO"Director").Value = [workrequestid]
                    rstPO.Update
                End If
            End If
        Next
        ' Move to the next record and continue the same approach
        rstPO.MoveNext
    Wend
    
End Sub

But then I'm not sure how i will find out what link the user clicked, as the entrys in the PO database are all ID's, and the user clicks on the actual name of the item.

And also, I would like it to find the first entry it finds (usually one at the top of the database - which will be the oldest entry.

Jamz · #4 (**permalink**) 01-16-12, 06:38

Anyone got any ideas on this one?

Jamz · #5 (**permalink**) 01-16-12, 07:45

Right guys, im getting a bit further with this, after playing around a little

Here's my current code:

Code:

Private Sub ProductName_Click()

    Dim Blanket_Orders As Object
    Dim rstRequests As Object
    Dim fldEnumerator As Object
    Dim fldColumns As Object

    Set Blanket_Orders = CurrentDb
    Set rstRequests = Blanket_Orders.OpenRecordset("Requests")
    Set fldColumns = rstRequests.Fields

    ' Scan the records from beginning to each
    While Not rstRequests.EOF
        ' Check the current column
        For Each fldEnumerator In rstRequests.Fields
            ' If the column is named ProductID
            If fldEnumerator.Name = "ProductID" Then
                ' If the Product ID of the current record is "9"
                If fldEnumerator.Value = "9" Then
                    ' then change its value
                    rstRequests.Edit
                    rstRequests("WRID").Value = "9999"
                    rstRequests.Update
                End If
            End If
        Next
        ' Move to the next record and continue the same approach
        rstRequests.MoveNext
    Wend
    
End Sub

Now the first issue i see is how I select just one record, the first one it finds meeting the criteria.

The next issue is how do I link the click of the item to a ID number that is stored on a linked table...

Jamz · #6 (**permalink**) 01-16-12, 08:45

Hope this makes more sense:

Hi Guys,

Right I would say i need a bit of help with this one - I am quite new to VBA, but have used access DB's for a while now - but i program in other languages so can get my head around things once im shown.

Anyway, I have a database that I have setup, and is mostly working, apart from the major feature i am trying to program now.

I have attached the database as it stands while typing this for people to have a look at.

So bascially, I have a database that is populated with Purchase Order (PO) numbers, these PO numbers are linked to products, which the products each have an ID. Now there are work requests, that can take PO numbers and "claim" these PO numbers under this work request...

For example one work request requires 1x Laptop and 1x Monitor, this would then be linked to this work request, and classed as "taken" so no one else could use this PO number.

So, if you are looking at the database, click any "WR" link in the sub form, and it will open a new form.

On the right hand side is a list of products currently in stock, you will notice these are currently linked. Now what I would like this to do, when you click on the link of the product, it will add this product into this work request (add the work request ID into the PO box, and select the check box within the Purchase Orderes (requests) table.

I would like the first PO found to be added.

I have kinda adapted this code:

Code:

Private Sub ProductName_Click()

    Dim Blanket_Orders As Object
    Dim rstRequests As Object
    Dim fldEnumerator As Object
    Dim fldColumns As Object

    Set Blanket_Orders = CurrentDb
    Set rstRequests = Blanket_Orders.OpenRecordset("Requests")
    Set fldColumns = rstRequests.Fields

    ' Scan the records from beginning to each
    While Not rstRequests.EOF
        ' Check the current column
        For Each fldEnumerator In rstRequests.Fields
            ' If the column is named ProductID
            If fldEnumerator.Name = "ProductID" Then
                ' If the Product ID of the current record is "9"
                If fldEnumerator.Value = "9" Then
                    ' then change its value
                    rstRequests.Edit
                    rstRequests("WRID").Value = "9999"
                    rstRequests.Update
                End If
            End If
        Next
        ' Move to the next record and continue the same approach
        rstRequests.MoveNext
    Wend
    
End Sub

But its not currently working - been trying to figure it out myself, and have come up trumps.

So any advice, help or code would be great.

Jamz · #7 (**permalink**) 01-16-12, 10:55

Right so the code is kinda working now

Code:

Private Sub ProductName_Click()

    Dim Blanket_Orders As Object
    Dim rstRequests As Object
    Dim fldEnumerator As Object
    Dim fldColumns As Object
    
    Set Blanket_Orders = CurrentDb
    Set rstRequests = Blanket_Orders.OpenRecordset("Requests")
    Set fldColumns = rstRequests.Fields

    ' Scan the records from beginning to each
    While Not rstRequests.EOF
        ' Check the current column
        For Each fldEnumerator In rstRequests.Fields
            ' If the column is named ProductID
            If fldEnumerator.Name = "ProductID" Then
                ' If the Product ID of the current record is "9"
                If fldEnumerator.Value = "9" Then
                        ' Then change its value
                        rstRequests.Edit
                        rstRequests("WRID").Value = "47"
                        rstRequests.Update
                        rstRequests.Edit
                        rstRequests("Taken").Value = "True"
                        rstRequests.Update
                    
                End If
            End If
        Next
        ' Move to the next record and continue the same approach
        rstRequests.MoveNext
        
    Wend
    
    Me.Parent.Refresh
    
End Sub

Now, i need to figure out how to find the row that equals say 9, AND the cloumn that WRID IS blank.

Not sure how I can do that?

Jamz · #8 (**permalink**) 01-17-12, 09:30

Really bashing my head against the desk with this one now.... any help would be muchly appreciated

Jamz · #9 (**permalink**) 01-17-12, 10:34

Made a few changes to the code

Code:

Private Sub ProductName_Click()
    Dim Blanket_Orders As DAO.Database
    Dim rstRequests As DAO.Recordset

    Set Blanket_Orders = CurrentDb
    Set rstRequests = Blanket_Orders.OpenRecordset("Requests")
    
    ' Scan the records from beginning to each
    While Not rstRequests.EOF
       
      'not sure if WRID is going to be NULL or a zero length string, so we'll include both.         
      If rstRequests("productID").Value = 9 And (IsNull(rstRequests("WRID").Value) Or rstRequests("WRID").value = "") Then          
          
          ' then update the WRID and Taken values
          rstRequests.Edit
            rstRequests("WRID").Value = 47
            rstRequests("taken").Value = True
          rstRequests.Update
          
      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
    
End Sub

Internet Services

Web Development

Digital Marketing

Consumer Tech