VBA Script - Find First Query Matching & Edit

[Jamz]

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:

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]

Right so the code is kinda working now

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]

Anyone any ideas - this is puzzling me now

 

[tehNellie]

Your code seems overly complex for what it does. Why step through the columns looking for [productID] when you know it's there?

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

You could be a bit smarter about handling the possbility of NULL and Zero Length Strings and you could tidy up the layout a bit more on this to reduce the amount of typing etc, but for clarity I've just simplified it a bit and put what you've asked for in.

 

[Jamz]

Like I say, im new to coding in VBA, so was just following tutorials and what not and puttin peices together to make the code work.

tehNevllie - You see where the values "9" and "47" are, i would like these to be linked to the form, ie when you click on "65w Power Pack" which has ID of "9" it puts in 9, where if the user clicks "Dell Optiplex 380" link - it puts in "10", which is the ID key for that product.

Then where it says "47" this is the ID for the current work request the user is viewing, any way to add this into the code?

Oh and I only want it to use the first entry it finds, not all of them that match the case - ie stop after the first record it finds.

 

[spikepl]

You are going about it the hard way.

A form can have a record source (table/query), where the controls on the form (textboxes/combos etc) are bound to the fields in the underlying record source.In this way, ALL you have to do is type/select the value, and it winds up in the underlying table/query, when the record is updated (by moving off the current record in the form).

Do a tutorial on Access form basics - you get a lot of functionality built in.

 

[tehNellie]

Not making judgements, just trying to help

If you're new to it, first things first, add

Option Explicit

At the top of each module. This forces you to declare variables and will throw errors when you typo them, try to assign the wrong values to them or use a variable that you haven't declared, this will make your life MUCH easier when trying to find out why that bit of code isn't working.

Also I changed the original declaration from "object" to DAO.database/recordset. There are valid reasons to leave the declaration as "object" but initially and certainly for DAO it is worth using explicit declaration, if nothing else you get the benefit of intellisense "hints" when using the objects which can be very helpful.

Yes you can get the values off the form, if you had a combobox (drop down list) for your products, for example, then as the user selects "Power Drill", the relevant ID value can be taken from the control.

e.g.

if rstRequests("ProductID").value = me.cboProducts.value then
  
  [i]do stuff[/i]

end if

You can do likewise for the work request ID depending on what you're doing with the data when the form loads as per spikepl's suggestion.

 

[Jamz]

You are going about it the hard way.

A form can have a record source (table/query), where the controls on the form (textboxes/combos etc) are bound to the fields in the underlying record source.In this way, ALL you have to do is type/select the value, and it winds up in the underlying table/query, when the record is updated (by moving off the current record in the form).

Do a tutorial on Access form basics - you get a lot of functionality built in.

Probably my fault as im used to coding in PHP and HTML and what not, so usually take the coding route then the "easy" options, as this was the only way i could see it doing what i required.

Thing is, i want/need most things hidden, and do not have the time to take a course before I need to get this all sorted.

 

[Jamz]

Not making judgements, just trying to help

Not at all - help is muchly appriciated

 

[Jamz]

Yes you can get the values off the form, if you had a combobox (drop down list) for your products, for example, then as the user selects "Power Drill", the relevant ID value can be taken from the control.

e.g.

if rstRequests("ProductID").value = me.cboProducts.value then
 
  [I]do stuff[/I]
 
end if

Are you saying the way I am doing it via code, and a link for each on the actual database, I cannot assing the ID to this? (ie on php link i could do the link test.php?id=1 and so on, and call the ID from that link.

I think i kinda have gone the long way around on this from my php background, just couldnt figure out the drop down menu and a button to select say "laptop" and click the button to add the first record it finds to the work request.

 

[spikepl]

PHP/Html can give you unnecessary habits. There are no "server-client roundtrips" in Access, and the best invention is a bound form - you tie the form to the datasource and that's the end of it.

AS to "not having time to take a course" is not a good decision. To create unbound forms and managing your data yourself throws away 90 % of the advantages and time savings, and relegates you to do a lot of coding (and errors). As to a "course" .. a 1 hour tutorial about forms ( video or written) is hardly a "course" and would set you straight, since you already have coding experience.

UPdate: you also have the form/subform construct - you can in 2 minutes make a structure like Order form with multiple lines of OrderDetails - and tying it together is all done by Access.

 

[Jamz]

I have a feeling deep down if I use the bound option, i would be re-writing the whole database I already have set out.

I'm not saying its not a good idea (i will probably have a sit down some time and actually re-learn Access - did it all at college years ago, and have forgotten most of it now)

 

[tehNellie]

Spikepl's talking a lot of sense. you can do everything in code if you really need to, but the bound forms and even the combobox wizard take a lot of the gruntwork out of getting the data you need, where you need it without having to fanny around coding.

Bind the controls to queries and then you've got the necessary separation from application and tables, there should be no reason to need to redesign a decent underlying database to cater for the forms/controls.

 

[Jamz]

Cheers guys,

Thing is, i feel im pretty much nearly there with what I have currently done - this was the last part that I needed to get working really.

 

[Jamz]

I'm thinking i will need to incoporate FindFirst in there somewhere.

Also, sticking with the coding route, how can I get from the name of the item clicked, to get the id from that table, to then post the ID to the other table - if that makes sense

 

[Jamz]

Right guys, im getting there with this

Option Compare Database
Option Explicit
Private Sub ProductName_Click()
    Dim Blanket_Orders As DAO.Database
    Dim rstRequests As DAO.Recordset
    Dim Count As Long
    Dim ProdID As String
 
    Set Blanket_Orders = CurrentDb
    Set rstRequests = Blanket_Orders.OpenRecordset("Requests")
 
    ProdID = "SELECT products.[ID] FROM products WHERE products.[ProductName]= '" & Me.ProductName & "';"
 
    ' 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 = ProdID 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
 
End Sub

I just need a bit of help with the sql ive wrote - when I click the link, it doesnt actually do anything, so i think im missing something somewhere - maybe to do with the .value of ProdID?

The SQL should be making a query that matches the link with the entry in the database, and outputs just the id number, which is then used further down.

 

[tehNellie]

As it stands, ProdID is a string containing your query text rather than the results of the query itself.

Product ID 32 is never going to = "Select ID from etc etc etc."

 

[Jamz]

How do I get the VBA to run the script and just output the value the SQL finds?

 

[tehNellie]

There's no equivalent to executescalar in VBA/DAO that I'm aware of...

You've got your SQL that you want to execute to get some data back, so what are you already using in your code that holds returned data from the database?

 

[Jamz]

When you click on the "hyperlinked" text - it will return say "24inch Monitor" the products table holds id, and product name.

Now the sql searches the products table and finds "24inch Monitor" and returns the id "4" which I then want to populate a variable which i can use further down the code.

I know ive got myself in a right pickle here, but its kinda working, just this last bit and then its finished.