Updating Old Records Based on New Records

[lzook88]

@ItsMe



The Nest seemed right. Since for each J there could be 1 to how ever many the quantity text field has.

Thanks for the help

[ItsMe]

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

Does not work for a couple of reasons. The first is because NewLocation is a Lookup. So you need to include a number here. I suggest getting rid of the lookup and using a separate table. Store the PK value from the new table in the Foreign Key.

The second reason is Prod is a number. So you probably want something closer to
= DLookup("TrackingID", "tblTransfers", strWhere)


Beyond that, your loop is not doing anything, well anything productive. In the sample you uploaded the RS is still open but you need to use .FindFirst before .Edit.

So maybe something like

            With rs
                For j = 1 To Me.Controls("quan" & i).Value
                
                rs.FindFirst "[MyField] = " & j
                
                    .Edit

...

I was not able top wrap my head around the append and update process you are attempting. It seems you are working with one table, tblTransfers. So why you would do an append process to then update records is not jumping out at me. I guess one table is acting like an inventory table and the new records need to show where the stuff is now and the old records need to indicate not active.

Maybe your form is accomplishing this but I wonder if the User should be selecting from stuff that is actually available to be moved.

[ItsMe]

I have some meetings to attend so I am out of the building for a while ...

[ssanfu]

I looked at your dB. Echoing Itsme, I don't understand the transfer form/code.
How can you transfer Antiseptic Wipes from STN to a vehicle M44185, if there are no Antiseptic Wipes in STN?

I can add Antiseptic Wipes to the transfer table, but where do that come from?

[lzook88]

This is the code that I just tried using the find first and removing the dlookup since the findfirst does that basically.. im still getting the type mismatch because of the productname is on a lookup.. I know what you mean by changing the way it looks up... but it will mess up all the reports. so is there another way to get it find the product id and then find the name or something of that sort??

If Me.txtFrom.Value = "Annex" Then
Else
    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
                    .FindFirst ("[ProductName] = " & Me.Controls("item" & i) And "[NewLocation] = " & Me.txtFrom.Value)
                    .Edit
                    !Used = True
                    .Update
                Next j
            End With
        End If
    Next i
    
End If

[ItsMe]

I took a closer look at your lookup field in your table. It is based on another table, a lookup table. This is the lessor of two evils compared to basing your lookup on a Value List where the data is stored in the lookup.

I would still look to fixing your entire database in a way that the Primary Key value is stored in your tblTransfers, as a Foreign Key. I would use the LocationID field in tblLocations as the Primary key and avoid storing the value of PLocation in any other table. Store the value of LocationID in other tables as a Foreign Key.

What ever you decide to do, you are going to have to adjust the properties of your combobox named txtFrom. Something like the following might get you started.

RowSource:

SELECT tblLocations.LocationID, tblLocations.PLocation FROM tblLocations ORDER BY tblLocations.PLocation;

And to accommodate the new Rowsource ...
Under the Format Tab
Column Count: 2
Column Widths: 0";2"
Under the data tab
Bound Column: 2

After you make the above adjustments to your combo, you will affect what data is retrieved. Rather than retrieving one column, you will retrieve two columns. Now when you reference the combo via VBA, you would use the following as a reference.

Data from the first column ...
Me.txtFrom.Column(0)

Data from the second column ...
Me.txtFrom.Column(1)
OR
Me.txtFrom.Value '.Value is affected by the Bound Column property

[lzook88]

Okay I did everything you said.. However I am still getting a type mismatch on the .findfirst search criteria.

If Me.txtFrom.Value = "Annex" Then

Else
    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
                    StrCriteria = "[ProductName] = '" & Me.Controls("item" & i) & "'" And "[NewLocation] = '" & Me.txtFrom.Column(0) & "'" And "[Used] = '" & 0 & "'"
                    .FindFirst StrCriteria
                    .Edit
                    !Used = -1
                    .Update
                Next j
            End With
        End If
    Next i
    
End If

the section in blue throws an error of Type Mismatch

[ItsMe]

Try adding a debug.print line for your strcriteria. There are a couple of syntactic errors within strcriteria.

                    StrCriteria = "[ProductName] = '" & Me.Controls("item" & i) & "'" And "[NewLocation] = '" & Me.txtFrom.Column(0) & "'" And "[Used] = '" & 0 & "'"
                    Debug.Print StrCriteria
                    .FindFirst StrCriteria

You can view the immediate window and your Print via the keyboard shortcut, Ctrl + G

You should notice that you are including single quotes for a Number type. So ='24' is not correct and it should be =24.

"[NewLocation] = '" & Me.txtFrom.Column(0) & "'"

Instead something like

"[NewLocation] = " & Me.txtFrom.Column(0) & " AND [Used] =" &

If it is text that your are evaluating or assigning then ='Foo'

Also, you want to include your AND operator within the quotes. Variables like Me.txtFrom.Column(0) need to be concatenated but Operators that are part of the SQL statement need to be included within the string.

StrCriteria = "[ProductName] = '" & Me.Controls("item" & i) & "' AND [NewLocation] = " &

[lzook88]

You are amazing sir. I just removed the single quotes and it works perfectly now! Thank you so much sir!

SOLVED!

[ItsMe]

Okay, don't forget the quotes around And

You might get some benefit from Relational database tutorials. Also, if you are going to be working with ComboBox Controls, you might want to understand the various properties of Combo Controls. I created some tutorials that highlight a few of the properties. Working through the tutorials is not something that can be completed in a few minutes. There are several hours of screencasts available for download.
https://www.accessforums.net/tutoria...ers-52741.html