Duplicate Values (1 Viewer)

[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

 

[Pat Hartman]

my problem is that after scanning the code the form currently updates any matching records.

I'm not sure what you mean by this, The barcode should be scanned into a new, empty record if you are adding stock. You can have code in the BeforeUpdate event of the barcode control that does a dCount() to see if the ISBN exists already in the database.

Are you using an API to look up info using the ISBN from a specialized website? Is that what you are saying is causing a mass update?

You probably need to post the database or at least the code in the form for us to look at.

 

[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.

 

[Pat Hartman]

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.

 

[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 ??

 

[Pat Hartman]

(often takes so long to get it right ive forgotten which one im meant to be deleting)

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.

 

[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.

 

[Pat Hartman]

Here's where you find it. At this point, I would clean up first. There is the same option when you have a form in design mode and I think that converts ALL the embedded macros in that one form. For individually created macros, you need to convert them one at a time. If you try this, do it in a copy of your database. It will create a new module for each macro.

I made a copy of the first macro and converted it and this is what I got. The problem with a mass conversion after the fact is that you would need to manually reconnect everything and that will be a nightmare.

There is a great tool called Total Access Analyzer at www.FMSINC.com. It would be a big help with finding unused objects but it is ~ $400 but time is money and you have to weigh the cost against the time it will save you.

Access offers some tools for object dependencies but it doesn't include macros. To use it, turn it for the first time, open a table in design view. on the Table Design tab of the Ribbon, choose Object Dependencies. Access will turn on NameAutoCorrect and log all objects. Then to see an individual table/query/form/report, open any object in design view and press the Object Dependencies button in the Ribbon again. Once the feature is "open", just click on a new object and press refresh to see the properties. There is a picture after the code.

Option Compare Database
Option Explicit

'------------------------------------------------------------
' Copy_Of_allstock_formpic
'
'------------------------------------------------------------
Function Copy_Of_allstock_formpic()
On Error GoTo Copy_Of_allstock_formpic_Err

    If (Eval("[imagepath] Is Null And [picture_name] Is Null")) Then
        Beep
        MsgBox "You need to specify the record path and image name with suffix", vbOKOnly, ""
        DoCmd.CancelEvent
    End If
    If (Eval("[imagepath] Is Null And [picture_name] Is Not Null")) Then
        Beep
        MsgBox "You need to specify the path for the image", vbOKOnly, ""
        DoCmd.CancelEvent
    End If
    If (Eval("[picture_name] Is Null And [imagepath] Is Not Null")) Then
        Beep
        MsgBox "You need to specify the name of the picture with suffix", vbOKOnly, ""
        DoCmd.CancelEvent
    End If
    If (Eval("[imagepath] Is Not Null And [picture_name] Is Not Null")) Then
        Forms!allstockrecord!Picture.Picture = Forms!allstockrecord!Imagepath & "\" & Forms!allstockrecord!picture_name & ".jpg"
            End If


Copy_Of_allstock_formpic_Exit:
    Exit Function

Copy_Of_allstock_formpic_Err:
    MsgBox Error$
    Resume Copy_Of_allstock_formpic_Exit

End Function


'------------------------------------------------------------
' Copy_Of_allstock_reppic
'
'------------------------------------------------------------
Function Copy_Of_allstock_reppic()
On Error GoTo Copy_Of_allstock_reppic_Err

    If (Eval("[Picture] Is Not Null")) Then
        Reports![select record r]!reppic.Picture = Reports![select record r]!Picture
            End If


Copy_Of_allstock_reppic_Exit:
    Exit Function

Copy_Of_allstock_reppic_Err:
    MsgBox Error$
    Resume Copy_Of_allstock_reppic_Exit

End Function


'------------------------------------------------------------
' Copy_Of_allstock_Newpic
'
'------------------------------------------------------------
Function Copy_Of_allstock_Newpic()
On Error GoTo Copy_Of_allstock_Newpic_Err

    If (Eval("[imagepath] Is Null And [picture_name] Is Null")) Then
        Beep
        MsgBox "You need to specify the record path and image name with suffix", vbOKOnly, ""
        DoCmd.CancelEvent
    End If
    If (Eval("[imagepath] Is Null And [picture_name] Is Not Null")) Then
        Beep
        MsgBox "You need to specify the path for the image", vbOKOnly, ""
        DoCmd.CancelEvent
    End If
    If (Eval("[picture_name] Is Null And [imagepath] Is Not Null")) Then
        Beep
        MsgBox "You need to specify the name of the picture with suffix", vbOKOnly, ""
        DoCmd.CancelEvent
    End If
    If (Eval("[imagepath] Is Not Null And [picture_name] Is Not Null")) Then
        Forms![new Record f]!Picture.Picture = Forms![new Record f]!Imagepath & "\" & Forms![new Record f]!picture_name
            End If


Copy_Of_allstock_Newpic_Exit:
    Exit Function

Copy_Of_allstock_Newpic_Err:
    MsgBox Error$
    Resume Copy_Of_allstock_Newpic_Exit

End Function


'------------------------------------------------------------
' Copy_Of_allstock_reset
'
'------------------------------------------------------------
Function Copy_Of_allstock_reset()
On Error GoTo Copy_Of_allstock_reset_Err

    DoCmd.SetWarnings False
    DoCmd.OpenQuery "reset select q", acViewNormal, acEdit


Copy_Of_allstock_reset_Exit:
    Exit Function

Copy_Of_allstock_reset_Err:
    MsgBox Error$
    Resume Copy_Of_allstock_reset_Exit

End Function

 

[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!