Updating Old Records Based on New Records

[lzook88]

I am attempting to code the VBA Submit button to add the items to the database (this works). Once the items are added, I want it to go back and look where those items came from and mark them used so we can show depleting stock.



Here is the code:

Option Compare Database

Private Sub Command61_Click()
Dim DBSS As Database
Dim rs As DAO.Recordset
Dim from As String
Dim prod As Variant
Dim i As Integer
Dim j As Integer

Set DBSS = CurrentDb
Set rs = CurrentDb.OpenRecordset("SELECT ProductName, OldLocation, NewLocation, Used FROM tblTransfers")



'this part works!
For i = 1 To 20
    If Me.Controls("item" & i) & "" = "" Then
        Exit For
    Else
        With rs
            For j = 1 To Me.Controls("quan" & i).Value
                .AddNew
                !ProductName = Me.Controls("item" & i).Value
                !OldLocation = Me.txtFrom.Value
                !NewLocation = Me.txtTo.Value
                .Update
            Next j
        End With
    End If
Next i

rs.Close
Set rs = Nothing



'this part does not
For i = 1 To 20
prod = Me.Controls("item" & i).Value
from = DLookup(prod, "tblTransfers", "NewLocation = '" & Me.txtFrom.Value & "'")
    If Me.Controls("item" & i) & "" = "" Then
        Exit For
    Else
        With rs
            For j = 1 To Me.Controls("quan" & i).Value
                !Used = Yes
                .Update
            Next j
        End With
    End If
Next i







DoCmd.Close
End Sub

I get an error with this code:

Run-Time Error '3464':
Data Type mismatch in criteria expression.

[ItsMe]

It could be a couple things but my guess is your code is halting here
!Used = Yes

Is Used a Yes/No data type? Try ...
!Used = True
OR
!Used = -1

[lzook88]

I updated the code to use 0 as used.. and when it throws the error and I hit debug it has this highlighted

from = DLookup(prod, "tblTransfers", "NewLocation = '" & Me.txtFrom.Value & "'")

[ItsMe]

I updated the code to use 0 as used..

I do understand what you mean by this. As for the error in your DLookup, you might try something like ...

dim varFrom as variant
dim strFrom as string

strFrom = Me.txtFrom.Value
debug.print strFrom

varFrom = DLookup(prod, "tblTransfers", "NewLocation = '" & strFrom & "'")

When it throws an error you can check the immediate window or hover over Me.txtFrom.Value to see what the value of this control is.



.

[lzook88]

the first part instead of -1 I tried 0 as well.. my fault. fast typing and not paying attention.

The second part:

the varFrom still threw an error and hovering over it says VarFrom=Empty

[ItsMe]

Correct, varFrom will be empty if the code halts on or before varFrom =

What I believe is happening is there is an issue with the value of txtFrom. So you should be looking in the immediate window (Ctrl + G) and the following lines

strFrom = Me.txtFrom.Value
debug.print strFrom

[lzook88]

So here is what I am getting when hovering

VarForm = Empty
Prod = 11
NewLocation = STN

so the idea here would be that the DLookup would find the record matching Newlocation of STN with product of 11 (Bio-Hazard Bags) and change them from not used to used.


but there could be multiple records that match. because the transaction table is set up that 1 record = 1 physical item

[DrGUI]

In your DLookup statement, prod is a field in table tblTransfers, correct? If so, then try the following:

from = DLookup("prod", "tblTransfers", "NewLocation = '" & Me.txtFrom.Value & "'")

***Note the quote marks around 'prod'

[DrGUI]

Sorry about that. I just noticed that you calculate prod above it. However, the first param in the dLookup function must be of type string. If prod returns a string, then you need to surround it with quotes:

from = DLookup("'" & prod & "'", "tblTransfers", "NewLocation = '" & Me.txtFrom.Value & "'")


Question: you use dLookup to init the 'from' variable but you don't use it. Do you even need the dLookup statement?

[ItsMe]

Sorry about that. I just noticed that you calculate prod above it. However, the first param in the dLookup function must be of type string. If prod returns a string, then you need to surround it with quotes:

from = DLookup("'" & prod & "'", "tblTransfers", "NewLocation = '" & Me.txtFrom.Value & "'")


Question: you use dLookup to init the 'from' variable but you don't use it. Do you even need the dLookup statement?

I did not bother to check MSDN but that simply looks all kinds of wrong.

[ssanfu]

You have several errors.

1) "FROM" is a reserved word in Access
2) These two lines aren't used (not really an error but.... )

prod = Me.Controls("item" & i).Value
from = DLookup(prod, "tblTransfers", "NewLocation = '" & Me.txtFrom.Value & "'")

3) In the part that doesn't work, the "i" in this line should be a "j"

            For j = 1 To Me.Controls("quan" & i).Value

4) The biggest problem is that you closed the recordset "RS", destroyed the reference to it, then tried to reference it.

.
.
.
rs.Close
Set rs = Nothing

'this part does not
For i = 1 To 20
prod = Me.Controls("item" & i).Value
from = DLookup(prod, "tblTransfers", "NewLocation = '" & Me.txtFrom.Value & "'")
    If Me.Controls("item" & i) & "" = "" Then
        Exit For
    Else
        With rs
.
.
.

Looking at the code, if the part that doesn't work HAD worked, you would have randomly updated the "Used" field because you did not select a specific record to update.

[ItsMe]

So here is what I am getting when hovering

VarForm = Empty
Prod = 11
NewLocation = STN

so the idea here would be that the DLookup would find the record matching Newlocation of STN with product of 11 (Bio-Hazard Bags) and change them from not used to used.


but there could be multiple records that match. because the transaction table is set up that 1 record = 1 physical item

DLookup is not going to allow you to edit anything. So I am not sure why you went from a loop to a DLookup. You could either stick with DAO or build an SQL statement to UPDATE records, with a WHERE clause. Let me know. DAO should work OK. SQL may be an issue if you are using variables that change with a loop iteration.

[ItsMe]

I did not look at all of what Steve posted but if the code that you said works does not, you are going to need to address that issue first. I did not look at the code you said worked and Steve did.

[lzook88]

Guys I think I got everyone confused here.. Let me post the database for everyone to look at and see what you can figure out.

The idea is that when the transfer form is used.. it will add the records (first part of the code) and then it will go back for the same records and see where they came from and mark them used (take them away from the current stock in the old location)....

So I have marked the part of the code that works and what doesn't work inside the vba console in the database..

thanks for all the help so far and further on..


Zook

Attachment 22057

[ItsMe]

I will take a look at it. You nested loop looks OK at first glance.
For j = 1 To Me.Controls("quan" & i).Value