Return Previous Record 1

[heeleyj]

2nd question of the day..
I have a form that is used to record adjustments made to items in an inventory file. I use the DLookup function to pull data from my inventory file into the form with a primary key on the item number. Once the form is completed it is then recorded into a table and given a record number (AutoNumber) if the item has had an adjustment. What I need is a text box or two on the form that will auto-update with information stating it was previously updated (Yes/No) and the last record it was previously updated on. How would I write this function in VBA?

Thanks in advance for your expertise!

 

[RickSpr]

It sounds as if what you want is the most recent previous adjustment. DLookup can't get you that; if there are multiple adjustments, you'll get an arbitrary one. To be sure you get the most recent one, you'll have to have a date in the adjustments table, and you'll have to use a recordset that retrieves records by descending date. Something like this:

    Dim dbs As Database, rst As Recordset
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("SELECT RecNumber FROM InventoryAdjustments WHERE ItemNumber = '" & Me.txtItemNumber & "' ORDER BY AdjDate DESC;")
    If rst.EOF Then
        ' there was no previous adjustment
        Me.txtMessage = ""
        Me.txtPrevRec = ""
    Else
        ' there was a previous adjustment
        Me.txtMessage = "Record has previous adjustment"
        Me.txtPrevRec = rst!RecNumber
    End If
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

In this example, the InventoryAdjustments table has a RecNumber field (the Autonumber field), an AdjDate field that gives the date the adjustment was entered, and an ItemNumber field that holds the inventory item number for the item that was adjusted. Rick Sprague

 

[heeleyj]

Yes! I do want the most recent adjustment. I do have a date field in the adjustments table. However, I have a couple of questions about this. I need to have the response populate two text boxes on the form. The first text box is "PreviousAdjustment" which should return "Yes" or "No" dependant on the outcome. The second text box is "Record" number which returns the record number it was previously adjusted on. There is not a need to have and message stating no previous adjustment. Should I still use the same code? Where do I place this code? Thank you for your help so far!!

 

[RickSpr]

Yes, this code is just what you need. However, I can't tell you where to put it because you haven't described how the form is opened, or where the item number to be adjusted comes from. But basically, once you get the item number from somewhere you can run (an appropriately modified version of) this code.

With the additional information you've given, I can now partially revise the code:

    Dim dbs As Database, rst As Recordset
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("SELECT RecNumber FROM InventoryAdjustments WHERE ItemNumber = '" & Me.txtItemNumber & "' ORDER BY AdjDate DESC;")
    If rst.EOF Then
        ' there was no previous adjustment
        Me.PreviousAdjustment = "No"
        Me.Record = ""
    Else
        ' there was a previous adjustment
        Me.PreviousAdjustment = "Yes"
        Me.Record = rst!RecNumber
    End If
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

I meant this only as sample code. You didn't give me any actual table, field, form, or control names, so I had to invent some. I meant for you to make substitutions. But I guess you haven't written VBA code before, is that it? Rick Sprague

 

[heeleyj]

My apologies for the lack of info...

Form Name: "UPC Adjustment Form"
Contains text box "PreviousAdjustments" control source set to "PreviousAdjustments" and textbox "PA Record" control source set to "Sheet" ( "Sheet" would be the previous record number)

Adjusted records are stored in: "Table1"
Contains "Date" "ItemNumber" and "Record" feilds

I know I'm not consistently using naming rules, but I've learned allot since I started this module.

When you enter an item number in the "ItemNumber" textbox on the form it fires a DLookup function that pulls data from another table with static data called "stockstatus". Then proceeds to auto-populate other fields such as catalog number, description, unit cost and so on. I'm looking to have the PreviousAdjustment textbox and the Sheet textbox auto populate with the info described in my previous post. Does this make sense? I'm not sure where to plug the code. Would it be in afterupdate on the ItemNumber textbox? My DLookup function is in the afterupdate section now, would this affect that?

 

[RickSpr]

Yes, this code would also go in the ItemNumber_AfterUpdate event procedure. You'll have to put the Dim line before the
first executable statement (which would probably be the statement with the DLookup() function in it). The rest can come before or after the DLookup(); it doesn't matter.

Here is the revised code, using the names you gave me.

    Dim dbs As Database, rst As Recordset
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("SELECT Record FROM Table1 WHERE ItemNumber = '" & Me.ItemNumber & "' ORDER BY Date DESC;")
    If rst.EOF Then
        ' there was no previous adjustment
        Me.PreviousAdjustment = "No"
        Me.[PA Record] = ""
    Else
        ' there was a previous adjustment
        Me.PreviousAdjustment = "Yes"
        Me.[PA Record] = rst!Record
    End If
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing

Your details this time were complete, so I can say this should be the exact code you need.

Your design sounds appropriate, but is maybe not the best possible. Let me explain. The details you're autopopulating into this record are all obtained by looking up the ItemNumber. If that's the case, why store them in Table1 at all? You could save just the ItemNumber and use a "join" (a relationship between 2 tables, in this case Table1 and stockstatus) to look them up every time you need to display them. That would save space, but more importantly, any item data that changes some time later would automatically be reflected when you look at adjustments. If you actually copy the fields, the copies in Table1 either would be inaccurate, or would have to be corrected when changes to stockstatus are made. If you use a join, the accuracy isn't compromised.

It might be, though, that you want Table1 to hold the item data as it appeared at the time of the adjustment, so that it doesn't automatically reflect changes in stockstatus. In that case, your design is the correct one, and that's why I said maybe it's not the best possible. But the question of whether to copy or to join comes up all the time in relational database design, and you should keep it in mind for the future. When you can (that is, when you're looking for current and not historical information), you should avoid copying fields. Rick Sprague

 

[heeleyj]

Thanks so far this last post was a great help in understanding. It also worked for the first record entered in to the database! However, I am now getting a "Run-Time Error: Too few parameters. Expected1" Here is all of the code in the after update event of the ItemNumber Text Box:

Private Sub ITEM_NUMBER_AfterUpdate()
Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("SELECT Record FROM Table1 WHERE ItemNumber = '" & Me.ItemNumber & "' ORDER BY Date DESC;&quot
If rst.EOF Then
' there was no previous adjustment
Me.[PriorAdjustments] = "No"
Me.[PA RECORD] = ""
Else
' there was a previous adjustment
Me.[PriorAdjustment] = "Yes"
Me.[PA RECORD] = rst!Record
End If
rst.Close
Set rst = Nothing
Set dbs = Nothing

CATALOG_NUMBER = DLookup("[CatalogNumber]", "StockStatus", "ItemNumber = " & [ITEM_NUMBER])
DoCmd.Close acTable, "StockStatus"
Description = DLookup("[Description]", "StockStatus", "ItemNumber = " & [ITEM_NUMBER])
DoCmd.Close acTable, "StockStatus"
SYSTEM_QOH_UM = DLookup("[UM3]", "StockStatus", "ItemNumber = " & [ITEM_NUMBER])
DoCmd.Close acTable, "StockStatus"
PHYSICAL_COUNT_UM = DLookup("[UM3]", "StockStatus", "ItemNumber = " & [ITEM_NUMBER])
DoCmd.Close acTable, "StockStatus"
QTY_ADJUSTED_UM = DLookup("[UM3]", "StockStatus", "ItemNumber = " & [ITEM_NUMBER])
DoCmd.Close acTable, "StockStatus"
UnitCost = DLookup("[UnitCost]", "StockStatus", "ItemNumber = " & [ITEM_NUMBER])
DoCmd.Close acTable, "StockStatus"

I think I may be making a mess of things. Can you help with this? The Dlookup function was working fine until I added the latest code. Any thoughts?

PS "PreviousAdjustments was supposed to be "PriorAdjustments" My error!

 

[RickSpr]

I see ItemNumber should have been ITEM_NUMBER as well.

I want to explain a couple things about your code. First, you don't need the DoCmd.Close call after using DLookup; the function creates its own recordset and closes it before it returns to your code. DoCmd.Close, the way you have used it, would close the window you get when you open the table from the Database Window. The table probably wasn't opened anyway, so the DoCmd.Close did nothing.

Also, using multiple DLookups for several fields in the same table with the same criteria is very inefficient. You're pretty much executing the same query again and again with each DLookup function, which is quite slow. This is a situation where you really need another recordset, like the 'rst' I used. In fact, since my code has finished using 'rst' before your DLookups start, we can just reuse the same 'rst' variable. Here's a rewrite of the procedure:

Private Sub ITEM_NUMBER_AfterUpdate()
    Dim dbs As Database, rst As Recordset
    Set dbs = CurrentDb()
    Set rst = dbs.OpenRecordset("SELECT Record FROM Table1 WHERE ItemNumber = '" & [ITEM_NUMBER] & "' ORDER BY Date DESC;")
    If rst.EOF Then
        ' there was no previous adjustment
        Me.[PriorAdjustments] = "No"
        Me.[PA RECORD] = ""
    Else
        ' there was a previous adjustment
        Me.[PriorAdjustment] = "Yes"
        Me.[PA RECORD] = rst!Record
    End If
    rst.Close

    Set rst = dbs.OpenRecordset("SELECT * FROM StockStatus WHERE ItemNumber = " & [ITEM_NUMBER])
    rst.MoveFirst
    CATALOG_NUMBER = rst![CatalogNumber]
    Description = rst![Description]
    SYSTEM_QOH_UM = rst![UM3]
    PHYSICAL_COUNT_UM = rst![UM3]
    QTY_ADJUSTED_UM = rst![UM3]
    UnitCost = rst![UnitCost]
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
End Sub

What's happening here is that we're reading the whole StockStatus record for the item number, then picking the fields we need out of it.

Now for your error message. It could be because you've given me another wrong name. Verify or correct these facts about the table that contains the prior adjustments:
1. Its name is 'Table1'.
2. Its Autonumber field is named 'Record'.
3. It has a field named 'ItemNumber'.
4. It has a field named 'Date'.
If any of these is false, it might be causing the error message.

If these statements are all correct, run it again. When you get the message box, it might have a Debug button on it. If so, click it. Access will display a code window, with the statement causing the error highlighted in yellow. Tell me which line it is. Rick Sprague

 

[heeleyj]

Okay, first of all thank you for your patience...
I have renamed all of my fields to follow the naming rules so bear with me. (I think this was getting me in trouble and could still be the problem!) I checked the four selections as you requested and they are accurate:

1. Its name is 'Table1'.
2. Its Autonumber field is named 'Record'.
3. It has a field named 'ItemNumber'.
4. It has a field named 'Date'.

The above is all true.

Below is the highlighted code selected by the debugger:

Set rst = dbs.OpenRecordset("SELECT Record FROM Table1 WHERE ItemNumber = '" & [ItemNumber] & "' ORDER BY Date DESC;&quot


Below is the entire code with my naming revisions:

Private Sub ItemNumber_AfterUpdate()
Dim dbs As Database, rst As Recordset
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("SELECT Record FROM Table1 WHERE ItemNumber = '" & [ItemNumber] & "' ORDER BY Date DESC;&quot
If rst.EOF Then
' there was no previous adjustment
Me.[PriorAdjustments] = "No"
Me.[Sheet] = ""
Else
' there was a previous adjustment
Me.[PriorAdjustments] = "Yes"
Me.[Sheet] = rst!Record
End If
rst.Close

Set rst = dbs.OpenRecordset("SELECT * FROM StockStatus WHERE ItemNumber = " & [ItemNumber])
rst.MoveFirst
CatalogNumber = rst![CatalogNumber]
Description = rst![Description]
SystemQOHUOM = rst![UM3]
PhysicalCountUOM = rst![UM3]
QTYAdjustedUOM = rst![UM3]
UnitCost = rst![UnitCost]
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub


Please help this is my last resort! Thanks, John

 

[RickSpr]

It really threw me when you said you had "renamed all of [your] fields". I finally figured out that you meant you renamed your controls. Controls aren't fields. A field is a column in your table or query definition. The questions I asked were mostly about the names of fields, so when I read that you had renamed your fields, I thought, "Why in the world did he do that? Now I don't know what they're named any more!"

Actually, it might have been better in this instance if you had left the control names alone, too. Having controls and fields with the same name can be confusing in code. It's also easier to debug a problem when you make only one change at a time. Don't go back and undo your work, though.

The only thing I see that might be causing the error is that in the first query, we're surrounding the item number with apostrophes:
...ItemNumber = '" & [ItemNumber] & "' ORDER...
Try taking the apostrophes out. When I first gave you this code, I didn't know whether ItemNumber was text or numeric. I assumed text because inventory item numbers usually are. But your DLookup calls make it clear that you're using numeric item numbers. When you put numeric values into an SQL statement, you don't use the apostrophes. I forgot to take them out. So change the statement to:
...ItemNumber = " & [ItemNumber] & " ORDER...
and see if that fixes it. Rick Sprague

 

[heeleyj]

YES!!! Thank You Thank You Thank YOU! I hope others can learn from my trip down this road. I know I have! Thanks for your willingness to see it through!!!

John

 

[MichaelRed]

Rick,

The query MIGHT run a bit faster with tthe TOP predicate. Since John only needs the most recent record, ther is little or no need to get/return all records.


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[RickSpr]

Michael, that sounds like a good idea! I never would have thought of it.

John, if your form is sluggish filling in the item information, change that first SQL statement from "SELECT Record" to "SELECT TOP 1 Record". This will tell the Jet engine to forget any other records after it has found the latest one. To get the maximum effect, you should also define an index on Table1 over the combination of Date and Record. It doesn't have to be a unique index, and shouldn't be if there is a possibility of one item having more than one adjustment on a given date. Rick Sprague