Duplicate Values

[PearlsBooks]

i am developing a database for my secondhand bookshop stock. i am inputting a barcode to a form which adds to the stock. occasionally there will be more than 1 stock line with the same barcode (maybe a signed or unsigned copy) or stock which has somehow made itts way to the shop without being inputted.
my problem is that after scanning the code the form currently updates any matching records.
i am hoping there is a way that, when the code is scanned and it finds multiple entries, that prior to updating the stock, a form opens showing me the different options so that i can select which one to add stock to (or, in the case where the barcode scanned is not in the database, an error notifies me).
i was wondering about a query that counts stock id field (as each stock has unique stock id)
if someone could help that would be much appreciated.
rob

 

[PearlsBooks]

no i have a table with products i have entered and barcode is one of the fields.
as i buy more stock i scan the barcode which looks at the table i have and if there is a match it updates the stock by 1.
the problem is that say i have (or have previously had) a signed copy, and an unsigned copy both of which have been entered in the table and have the same barcode.
so i buy another copy and try to update my stock but it updates both the copies i previously had.
i was hoping that on entering the barcode and comparing it to the values in the table, that if it came up with multiple results it would somehow give me the choice of selecting which one was correct.
i was thinking a continuous form with an image of each item (which again is info i have stored (as a link)) however researching this seems to suggest that only 1 image can come up on a continuous form (the data comes up different but the image is the same.
sorry im pretty much a hobbyist at this.

 

[theDBguy]

Hi Rob. Welcome to AWF!

It would probably make things easier if you're able to post a sample db with test data.

 

[CJ_London]

sounds like your records do not have a primary key and you are relying on the barcode to identify an individual record - which it can't do if there is two or more of them. As DBG says, best to upload a copy of your db. Remove anything sensitive and not relevant to your question, then compact/repair and zip before uploading

 

[PearlsBooks]

thanks - i will put it up now. i left a small sample of stock up some of which have no barcodes and one is doubled up.
i hope its understanable because there are a heap of forms, queries etc that havenmt been used in a long while but i have left on in case something references them.
i had intentions of using the form 'multicode' to show different records but idk how to get the different images to show.

 

[PearlsBooks]

it is saying the database is too big - its 12Mb, compacted from 100 !!. not sure how to get it smaller.

 

[PearlsBooks]

i uploaded it here :-
members.iinet.net.au/~unclemeat/access forum.accdb

 

[PearlsBooks]

if you input this barcode you get 2 results. 9781925533682
this has 1 - 9781875359240
this has none - 5021456187492

 

[PearlsBooks]

sounds like your records do not have a primary key and you are relying on the barcode to identify an individual record - which it can't do if there is two or more of them. As DBG says, best to upload a copy of your db. Remove anything sensitive and not relevant to your question, then compact/repair and zip before uploading

i just checked the primary key and you are right - all records have a unique autonumber but its not allocated a primary key. would that help ?

 

[arnelgp]

see this changes to your barcode form.

 

[PearlsBooks]

Why in the world would you keep all those useless forms? When you decide to start again, either delete the object or add a zzzz prefix so you know you are not going to use it and clean up when you are sure you won't go back to that version.

yeah i dont know - sometimes i copy a form to do changes in case it doesnt work out and then i either forget to delete the original or the new failed one (often takes so long to get it right ive forgotten which one im meant to be deleting)

 

[PearlsBooks]

see this changes to your barcode form.

this wont open for me - it says unrecognised database format ??

 

[PearlsBooks]

Start by coping the form you want to change and name it:
OriginalformName_SAVE

Work on the original version of the form so that all the rest of the app still works. When you are OK with the changes, rename
OriginalformName_SAVE to
zzzOriginalformName_SAVE

If you already have a form with that name, add a suffix or let the new one overlay the old one.

You'd be doing yourself a huge favor if you went through and tried to clean up. Don't delete anything, just add zzzz's to the beginning of the name. That gets them to sort to the end of the list and stay out of the way. Keep them around for a month or two until you are convinced you didn't rename anything important. Then either delete them or export them to a new database if you really think you might need to recover one of them. Then C&R the database to clean up.

I know macros seem easy if you don't code but they really are much harder to work with and they are incredibly hard to modify. If you don't know how to do something, its OK to let the macro builder make it for you but then use the ribbon option to convert the macro to VBA. Eventually, you'll be able to understand the VBA and changes will be easier.

Sorry I'm not being much help but it takes way to long to try to figure out what the macros are doing and which one needs to be changed. If you can identify the one you want to change, it will be easier for someone to do it for you.

il'll do the form thing.
sorry tpo sound ignorant but i dont knoiw what the ribbon option on macros is.

 

[arnelgp]

what version do you have?
here is a modified using A2007.

 

[PearlsBooks]

what version do you have?
here is a modified using A2007.

thank you - that opens. bedtime here but ill check on it in the morning. much appreciated it looks like it works fine.

 

[arnelgp]

goodluck!

 

[PearlsBooks]

goodluck!

just had a p[lay for half hour and its great - thank you so much. im gonna try and understand the code one day - it kinda makes sense when i see it but i am a bunny trying to write it. anyway much appreciated. come and grab a book when youre in Cairns.

 

[arnelgp]

had to quickly google the place and wow what a nice place.
i only do virtual travel.

 

[PearlsBooks]

goodluck!

thanks - hey just one last query - is it possible to have images on that form ? the pictures for each item are stored on a different folder - imagepath, picture name . jpg. i was trying to do that with continuous forms but only 1 picture would display (seemed to be related to oncurrent condition).
Alternatively could i link to a single form which has a picture on it ?
i was just messing with your form but i couldnt seem to put a button that was visible either on the footer or under the list of items.
rob

 

[PearlsBooks]

when i mean click a link i mean click on the product and have a single form with a picture pop up.
so like if there is a match with multiple barcodes then i could see an image by clicking on the relevant item.