Adding multiple items to table SQL question

Question

Hi. I have a bunch of items I want to add to a table. There are some choices I have in a form to determine what place I'm assigning them. And I have a formula to figure out the expiration date off the serial number. But could someone help me with what code I'd need to be able to take a list that pastes in like this:

14020606

14070501

14110543

18100015

18100020

18100025

18100052

18100053

18100064

18100065

18100066

And I guess parse them out to add in with one button press? I was thinking some way of counting the number of entries, then basically running the SQL entry command I have setup for each number. But I don't know how to do the counting and separating into individual serial numbers.

Answer 1

It's just a text file. The location (in no normalized order) at the top, and a list of serial numbers going down. I was having problems with how to get it to recognize that a new row was a different serial number, and then thought about just finding what character Access uses for a return/new line.

This is the code I came up with. It seems to be working so far. This is meant to be a temporary solution until I get all the files processed with items into the database, and logged into their correct location. I have forms set up that are meant for the actual users to add/edit things.

    Dim sSerial As String, sDate As String, sSN As String 

    Dim dExpr As Date, dDate As Date 

    Dim iType As Integer, iContainer As Integer, iItem As Integer, iTransaction As Integer, i As Integer, iCount As Integer, iLength As Integer, iCheck As Integer 

    Set dbs = CurrentDb 

    dDate = Now 

    iContainer = Me.cboContainerID.Value 

    sSerial = Me.txtSerial.Value 

    sSerial = Replace(sSerial, Chr(13) & Chr(10), "") 

    iLength = Len(sSerial) 

    iCheck = iLength Mod 8 

    If iCheck <> 0 Then Exit Sub 

    iCount = (iLength \ 8) - 1 

    iTransaction = 2 

    iType = Me.grpItemTypeID.Value 

    For i = 0 To iCount 

        If i = 0 Then 

            sSN = Left(sSerial, 8) 

        Else 

            sSN = Mid(sSerial, (i * 8) + 1, 8) 

        End If 

        Debug.Print "i is " & i 

        Debug.Print "sSN is " & sSN 

        sDate = Mid(sSN, 3, 2) & "-01-" & Left(sSN, 2) 

        dExpr = CDate(sDate) 

        iItem = Nz(DLookup("ItemID", "tblItems", "SerialNumber = '" & sSN & "'"), 0) 

        If iItem = 0 Then 

            sSQL = "INSERT INTO tblItems (ItemTypeID, SerialNumber, ExpirationDate) VALUES (" & iType & ", '" & sSN & "' ," & Format(dExpr, "\#mm-dd-yyyy hh:mm:ss\#") & ");" 

            dbs.Execute sSQL, dbFailOnError 

        End If 

        iItem = Nz(DLookup("ItemID", "tblItems", "SerialNumber = '" & sSN & "'"), 0) 

        If iItem <> 0 Then 

            sSQL = "INSERT INTO tblTransactions (ItemID, TransactionTypeID, ContainerID, TransactionDate) VALUES (" & iItem & ", " & iTransaction & ", " & iContainer & ", " & Format(dDate, "\#mm-dd-yyyy hh:mm:ss\#") & ");" 

            dbs.Execute sSQL

Answer 2

What we need to know is what form this list is currently in. If its a linked table then you would use something like:

INSERT INTO table (field list)

SELECT field list FROM linked table;

You could use a calculation in the field list to add the Expiration date

Answer 3

I'm just not sure how to "process" the raw list of serial numbers. I think I'm missing something obvious.

Hi Rocky,

In my applications I work with a few generalized forms: it makes no difference what table it is, or what fields.

To handle this I work quite a lot with "lists":

this_list = "<>item1<>item2<>item3<>item4<>item5 ..."

To make this list available for further processing, I use the Split-function to convert the list to an array:

this_array = Split(this_list,"<>")

For x = 1 to Ubound(this_array)

Do\_something\_with\_this\_item this\_array(x)

Next

The good thing of starting a list with the separator means that you don"t have to remove it, AND your counting starts with 1: very handy for further automation.

Is this something you are looking for?

Imb.

Answer 4

Very sorry, it's been a long day. I try to be more coherent normally. Whether I succeed or not is for history to judge me on.

I just have a list of the serial numbers and a place they're at (like a physical place I mean). I'm adding them to a table.

tblItems (pk ItemID autonumber, SerialNumber string, ExpirationDate date)

The Expiration date is figured out through a formula run on a serial number (first two characters are the year, second two are a month).

Once the items are added to tblItems, I'm adding them to another table, tblTransactions, that show them being assigned to a specific inventory location.

tblTransactions (pk TransactionID, ItemID fk, ContainerID fk from a table of containers)

The transaction marks the item as assigned to storage (the transaction type) and in a specific container (the container ID)

What I was hoping to do is just paste in the list of serial numbers that go with a particular container. Select the container on a form. Then hit a button and have the items added to tblItems then the transaction assigning them to the container.

I'm just not sure how to "process" the raw list of serial numbers. I think I'm missing something obvious.

Answer 5

Hi Rocky,

I'm not quite sure what your final result would look like. Is your "bunch" just these items or is there a table or other type of list of values? We have no idea what you mean by assigning places, serial numbers, and expiration dates.