Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2012
    Posts
    14

    Unanswered: Editing First Entry Found Auto Data

    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
    Attached Files Attached Files

  2. #2
    Join Date
    Jan 2012
    Posts
    14
    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

  3. #3
    Join Date
    Jan 2012
    Posts
    14
    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.

  4. #4
    Join Date
    Jan 2012
    Posts
    14
    Anyone got any ideas on this one?

  5. #5
    Join Date
    Jan 2012
    Posts
    14
    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...

  6. #6
    Join Date
    Jan 2012
    Posts
    14
    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.
    Attached Files Attached Files

  7. #7
    Join Date
    Jan 2012
    Posts
    14
    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?

  8. #8
    Join Date
    Jan 2012
    Posts
    14
    Really bashing my head against the desk with this one now.... any help would be muchly appreciated

  9. #9
    Join Date
    Jan 2012
    Posts
    14
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •