Importing inventory transactions (using MS "Desktop inventory" template)

[iceoln]

Using Access 2013, downloaded "Desktop inventory" template.

Have successfully added inventory items to database (so all my item numbers are known).

I'm trying to import an XLS containing inventory transactions, so goods in/out of my stock can be scanned with barcode to an Excel sheet first (scanned barcode need to be stripped for extra digits before import). I simply cannot make it work!
I've tried with a blank new database, changed format on columns in my XLS, named colums differently etc. Either I get a "Subscript out of range" or "Property not found" error.
I would be grateful if someone could try to create a new database using the same template and then see why it's not possible to import an XLS via the "External Data" import button on the ribbon.

Thanks a million for your help - it's driving me insane!
And I guess there is no need to say I'm not Access experienced in any way! :-)

[June7]

Was the blank new database NOT the template, but one you created?

Want to provide the Excel file for analysis?

[iceoln]

Hi June7

Sorry if I did not describe properly. The "blank" db was indeed a new db created by the template. So just starting a new db, selecting the template.

I've attached a copy of my database as well as a sample import XLS sheet. Can't figure out what is the requirement for a proper import of "transactions" so I can scan barcodes to an Excel sheet and then import the data afterwards when I've modified them a bit.

Appreciate any help you might be able to give.

Thanks
OleInventory.ziptransaction inventory import.zip

[June7]

Change the column header in the spreadsheet to Transaction Item.

I thought the issue could be the indexes set up on the table. However, removing them (except for the PK) did not help.

Then I thought issue could Transaction Type field was set as Required. Changing that also did not help.

Access continues to insist there are key violations.

So I tried copy/paste. Now I get message that related record is required in Inventory. Remove the Relationships link between Inventory and Inventory Transactions and the import works.

[iceoln]

Thanks for your suggestion. I changed the column header and deleted the relationship between Inventory and Transaction Items as suggested. Then you are correct that it's possible to import the list.

However, after this import, I cannot see any changed stock levels or transactions in any of the forms or reports. Maybe this is due to the broken relationship? I even tried to make the relations ship afterwards again, but I cannot make it with "Enforce Referential Integrity" set as it was, and if I ignore this setting it still makes no difference.

The Inventory List will keep a complete company spare parts list, and then transactions items would be used when any of these parts are put into a local stock (or taken away). The idea was to scan the barcodes to Excel and then import them on a weekly basis and be able to follow stock levels.
I could do the scan of barcodes directly to the database, however some of our barcodes contain more than 7-digits (which is the spare part number) and the Inventory Transaction List will search for the whole string when entered (not only 7 or until match). If this could be set for only looking at the first 7 digits and then ignore any extra digits then all would be good. I just can see how that is possible....

Thanks

[iceoln]

And one more thing - there will always be a related record in "Inventory" as it will be a gross list of all possible parts.

[June7]

Yes, can build search that uses only the first 7 digits. One way is to construct a field in query that extracts the 7 digits and apply filter criteria to that field.

WHERE Left([barcode], 7) = [Forms]![formname]![comboboxname]

Another approach is with LIKE and wildcard.

WHERE [barcode] LIKE [Forms]![formname]![comboboxname] & "*"

[iceoln]

Okay, that sounds interesting. But can it really be done so that when entering an item no. in the Transaction Inventory List as ex. 15digits, and press enter, then it simply matches on the first 7 digits and then accept as if I had only entered 7 ?
Unfortunately I really have no idea how to do what you have suggested...if you believe it can be done, would it be possible for you to try to implement it in the sample db I attached for me?

Thanks
Ole

[June7]

Do you mean entering by scanner? Otherwise, why would someone type 15 digits when only need 7? Do you want to save the shorter value into record?

Code can take the input value and truncate it to whatever you want and save that into record. Use the AfterUpdate event of combobox. Give control a name different from the field bound to, like: cbxTransItem.

I use only VBA:
Private Sub cbxTransItem_AfterUpdate()
Me!TransactionItem = Left(Me.cbxTransItem, 7)
End If

However, there is macro equivalent. It is the SetValue method.

I suggest you attempt code.
Otherwise, just save whatever is input and deal with the values as already described in post 7.

Recommend no spaces or special characters/punctuation (underscore is exception) in naming convention.

[iceoln]

Yes exactly, I want to enter by scanner, that's the whole problem. I don't need to save rest of digits, only use the first 7.
Can do?

[June7]

Then try the suggested code.

[iceoln]

Yes...unfortunately I'm not sure how to actually do what you suggested - sorry :-)
Do I understand correctly that I should add a new ComboBox, not link this to anything and add an AfterUpdate event with the code you suggest?
What do I do with the existing "Transaction Item" field?

Sorry I'm very basic at this...as suggested before, I would be grateful if you could try to implement it for me in the DB I attached.

Thanks

[June7]

Even if I wanted to, I cannot work with Access until Mar 23. The combobox would still be bound to the field and yes, code would be in the AfterUpdate event.

Again, options are to save the 15 digits and deal with them later in query to extract the first 7 or use suggested code to actually save only the 7 digits.

What exactly do you not understand about creating event procedure VBA code?