By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,083 Members | 1,608 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,083 IT Pros & Developers. It's quick & easy.

Adding a Duplicate Record

P: n/a
Hi,

I've created a stocktaking database using Access XP. This is indexed by two
fields - part number and shelf location.

I am currently inputting all the data via a form. When I have entered a
record such as:

part number 202354-001
location C1-01
quantity 2

and then I find later that there is another one of this part number in the
same location and try to enter this as a new record I get the expected error
3022 (Duplicate value in index etc).

What I would like is for a message to pop up alerting me to this, but then
for the quantity to be amended to reflect that I have found another part in
the same location.

I found a few bits of information on trapping the error first (didn't seem
to work - something about a problem accessing the OLE object) - but I am
lost with the second part - adding the new quantity to the old.

Any help would be greatly appreciated - I've got to have this stocktake
complete and consolidated in a week and a half :-(

Cheers
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Hi!

I kind of giggled to myself in regards to you comment "I've got to have this
stocktake complete and consolidated in a week and a half :-("
Good luck with that one... I've been messing around with my inventory
program since the Access 2.0 version that I created in about 1994, and it
*still* isn't "finished":-)

Anyway ... I'd suggest a second table named "tblLocation" and use a
one-to-many join on PartID. That's what I did and it has worked quite well
for me.
Be sure to look at Allen Browne's inventory database suggestions at
http://members.iinet.net.au/~allenbr...Inventory.html. It will save you
days of work later if you implement this at the beginning.

--
HTH,
Don
=============================
Use My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================
"6thirty" <pl****@dontspam.com> wrote in message
news:1G********************@front-1.news.blueyonder.co.uk...
Hi,

I've created a stocktaking database using Access XP. This is indexed by two fields - part number and shelf location.

I am currently inputting all the data via a form. When I have entered a
record such as:

part number 202354-001
location C1-01
quantity 2

and then I find later that there is another one of this part number in the
same location and try to enter this as a new record I get the expected error 3022 (Duplicate value in index etc).

What I would like is for a message to pop up alerting me to this, but then
for the quantity to be amended to reflect that I have found another part in the same location.

I found a few bits of information on trapping the error first (didn't seem
to work - something about a problem accessing the OLE object) - but I am
lost with the second part - adding the new quantity to the old.

Any help would be greatly appreciated - I've got to have this stocktake
complete and consolidated in a week and a half :-(

Cheers

Nov 13 '05 #2

P: n/a
Thanks for the input Don. I do actually have a seperate table as you
suggest - I was trying to simplify the description (more for myself to be
able to explain the problem than anything else!). I have seperate tables for
parts, locations, counted parts. I think I got the relationships set up
about right because all my reports make sense and everything kinda works.

Having the one-to-many works well for a part being in different locations,
but my problem lies slightly differently. I'll try to explain:

1. I do the stocktake, going round counting everything and writing down on
count sheets.
2. This data is input into the database.
3. During consolidation I find one more of a part (lying around or whatever)
which needs to go into it's correct location, so I open up the database and
try to enter a new record.
4. Because there is already a record for that part in that location Access
throws up an error about a duplicate entry (quite rightly)
5. All I need is for it to give me a quick warning of what is about to
happen then to add the recently found quantity to the original quantity in
that location.

Now, the easy way to do it is to simply open the underlying table and amend
the quantity manually like that, but I'm trying to get this user friendly so
it's not just me stuck with doing the damn stocktake every six months :-p

On a lighter note - inventory databases do seem to grow arms and legs, but I
am quite proud of this one. Because our "official" stores system is based on
a head office Alphaserver and is worse than useless for any sort of
accounting I decided to write this thing myself to make the whole stocktake
easier. Seems to do the job - after importing what the official system says
we should be holding I can do some nice queries to show what we are missing,
what we are surplus and I'm working on something to try and consolidate the
two based on keywords in the description of the part (like officially we
should have a 40Gb DLT Tape Drive but I can only find a 35Gb AIT Tape drive
and it flags this as a possible match)
"Don Leverton" <le****************@telusplanet.net> wrote in message
news:_aGBc.9$933.5@clgrps12...
Hi!

I kind of giggled to myself in regards to you comment "I've got to have this stocktake complete and consolidated in a week and a half :-("
Good luck with that one... I've been messing around with my inventory
program since the Access 2.0 version that I created in about 1994, and it
*still* isn't "finished":-)

Anyway ... I'd suggest a second table named "tblLocation" and use a
one-to-many join on PartID. That's what I did and it has worked quite well
for me.
Be sure to look at Allen Browne's inventory database suggestions at
http://members.iinet.net.au/~allenbr...Inventory.html. It will save you days of work later if you implement this at the beginning.

--
HTH,
Don

Nov 13 '05 #3

P: n/a

"6thirty" <pl****@dontspam.com> wrote in message
news:1G********************@front-1.news.blueyonder.co.uk...
Hi,

I've created a stocktaking database using Access XP. This is indexed by two fields - part number and shelf location.

I am currently inputting all the data via a form. When I have entered a
record such as:

part number 202354-001
location C1-01
quantity 2

and then I find later that there is another one of this part number in the
same location and try to enter this as a new record I get the expected error 3022 (Duplicate value in index etc).

What I would like is for a message to pop up alerting me to this, but then
for the quantity to be amended to reflect that I have found another part in the same location.

I found a few bits of information on trapping the error first (didn't seem
to work - something about a problem accessing the OLE object) - but I am
lost with the second part - adding the new quantity to the old.

Any help would be greatly appreciated - I've got to have this stocktake
complete and consolidated in a week and a half :-(

Cheers


Enter your data on an unattached form. It can check for a duplicate part
number then use a query to adjust quantity or add the record.
Nov 13 '05 #4

P: n/a
That sounds like a good idea - I'll give it a try tomorrow. Thank you.
"paii, Ron" <pa**@packairinc.com> wrote in message
news:10*************@corp.supernews.com...
Enter your data on an unattached form. It can check for a duplicate part
number then use a query to adjust quantity or add the record.

Nov 13 '05 #5

P: n/a
Hi,

Let me tell you how I do it. Then tell me what you think. Collaboration is a
good thing! :-)

Main data entry and display
----------------------------
I have a main form called "frmInventoryAdjustments"
I use a combo-box (cboPartNumber) to find and display the record for that
part number.
The data from tblLocations (PartID, Loc, Qty, CountDate) is displayed and
edited as nessecary in a datasheet style subform called sbfLocations, which
is linked to the main form on PartID. As different parts are selected on the
main form, their corresponding locations and qty's are displayed in the
subform.

This approach is very flexible, because it allows the creation of new
records (for that part that you found on the shelf at location C01, for
example) as well as editing both the location and/or qty. If I move a part
from C01 to C02, its a simple matter of just changing its location right on
this form.
sbfLocations allows me to record the qty counted at each location, and the
total on-hand qty is the calculated sum of all qtys in all locations.

Stock-Taking
------------------
I print out a report called rptStockCheckByLocation, which is sorted and
grouped by Location, Line (3-chracter Mfr code -- i.e AUT for Autolite
spark plugs), and Part number.
This report includes the "assumed" total qty on hand at the time of
printing, followed by a blank underlined space for handwritten corrections.
As I wander around the stock area, beginning with location A01, I count the
qty of each part number. If the qty count at this location is the same as
the assumed count for this location ... I circle the quantity, indicated
that it has been counted and tat it is correct. If the total qty found at
this location is different, I handwrite the new qty in the blank
"corrections" field.

Posting the Adjustments
-------------------------
When the entire stock room has be counted, I can readily identify which
changes must be posted ... they are the items with the hand-written qtys!
"Missed counts" are also fairly obvious ... they are the ones that are
neither circled nor handwritten. I go back and check these, and "zero" them
if they just aren't there anymore.

This method saves me a lot of time, and disk space ... as I only have to
enter the exceptions, as well as modifying an existing record rather than
adding a new one.
When I am satisfied with the count, I am ready to post the changes using
the previously mentioned frmInventoryAdjustments.

Oh yeah ... let's say that I found a part on the shelf in the location that
I was counting, but there is no record of it at that location on the report.
I handwrite these at the bottom of the sheet. When doing my posting, I
discover that this part is not even recorded into the inventory system yet.
I know this because the combo-box ("cboPartNumber") displays a message
telling me that, and offers to add the part number for me. cboPartNumber
uses the NotInList event to display this message, as well as saving me the
work of re-typing the part number again in a new record. The other scenario
is that the part *was already in inventory*, and that it had just been put
away in the "wrong" location.When this happens, I can choose to move it
where it belongs, or count it where it lays. (I usually will put it where it
belongs, except if it is being reserved for a customer.)

Sales and Re-Stocking of Inventory Items
------------------------------------------
This is the part that I am in the process of presently revising. I was of
the mind that I *needed* to perform calculations, and store the results
because of the complexities involved with multiple locations. I had tried to
implement Allen's method previously, but never seemed to have the time to
change my whole structure. What I have come up with is a table that records
transactions (tblTransactions of course) and have modified Allen's code to
suit.

*SO FAR*, it appears to be working, but I need to do further testing before
I give this code my total endorsement.
FYI: I have also just posted this code in a seperate thread, requesting some
feedback.
************************************************** **************************
**********

Function OnHand(vProductID As Variant, Optional vAsOfDate As Variant) As
Long

'This has been re-written to include my table / field names...

'Purpose: Return the quantity-on-hand for a product.
'Arguments: vProductID = the product to report on.
' vAsOfDate = the date at which quantity is to be calculated.
' If missing, all transactions are included.
'Return: Quantity on hand. Zero on error.
Dim db As DAO.Database 'CurrentDb()
Dim rs As DAO.Recordset 'Various recordsets.
Dim lngProduct As Long 'vProductID as a long.
Dim strAsOf As String 'vAsOfDate as a string.
Dim strSTDateLast As String 'Last Stock Take Date as a string.
Dim strDateClause As String 'Date clause to use in SQL statement.
Dim strSQL As String 'SQL statement.
Dim lngQtyLast As Long 'Quantity at last stocktake.
Dim lngQtyAcq As Long 'Quantity acquired since stocktake.
Dim lngQtyUsed As Long 'Quantity used since stocktake.

If Not IsNull(vProductID) Then
'Initialize: Validate and convert parameters.
Set db = CurrentDb()
lngProduct = vProductID
If IsDate(vAsOfDate) Then
strAsOf = "#" & Format$(vAsOfDate, "mm\/dd\/yyyy") & "#"
End If

'Get the last stocktake date and quantity for this product.
'-------------------------------------------------------------------------
If Len(strAsOf) > 0 Then
'strDateClause = " AND (StockTakeDate <= " & strAsOf & ")"
strDateClause = " AND (CountDate <= " & strAsOf & ")"
End If

'strSQL = "SELECT TOP 1 StockTakeDate, Quantity FROM tblStockTake "
& _
"WHERE ((ProductID = " & lngProduct & ")" & strDateClause & _
") ORDER BY StockTakeDate DESC;"

strSQL = ""
strSQL = strSQL & "SELECT Sum(Nz([tblLocation]![Qty],0)) AS Count,
tblLocation.PartID, "
strSQL = strSQL & "tblLocation.CountDate "
strSQL = strSQL & "FROM tblLocation "
strSQL = strSQL & "WHERE ((PartID = " & lngProduct & ")" &
strDateClause
strSQL = strSQL & ") GROUP BY tblLocation.PartID,
tblLocation.CountDate; "

'Debug.Print strSQL
Set rs = db.OpenRecordset(strSQL)
With rs
If .RecordCount > 0 Then
'strSTDateLast = "#" & Format$(!StockTakeDate,
"mm\/dd\/yyyy") & "#"
strSTDateLast = "#" & Format$(!CountDate, "mm\/dd\/yyyy") &
"#"
'lngQtyLast = Nz(!Quantity, 0)
lngQtyLast = Nz(!Count, 0)
End If
End With
rs.Close

'Build the Date clause
If Len(strSTDateLast) > 0 Then
If Len(strAsOf) > 0 Then
strDateClause = " Between " & strSTDateLast & " And " &
strAsOf
Else
strDateClause = " >= " & strSTDateLast
End If
Else
If Len(strAsOf) > 0 Then
strDateClause = " <= " & strAsOf
Else
strDateClause = vbNullString
End If
End If

'Get the quantity acquired since then.
'-------------------------------------------------------------------------
'strSQL = "SELECT Sum(tblAcqDetail.Quantity) AS QuantityAcq " & _
"FROM tblAcq INNER JOIN tblAcqDetail ON tblAcq.AcqID =
tblAcqDetail.AcqID " & _
"WHERE ((tblAcqDetail.ProductID = " & lngProduct & ")"
strSQL = ""
strSQL = strSQL & "SELECT Sum(IIf([tblTransactions]![TransType] Like
"
strSQL = strSQL & ""
strSQL = strSQL & chr$(34) & "*Receipt*"
strSQL = strSQL & chr$(34) & ",[TransQty],0)) AS QuantityAcq "
strSQL = strSQL & "FROM tblTransactions "
strSQL = strSQL & "WHERE ((tblTransactions.PartID = " & lngProduct &
")"

If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
'strSQL = strSQL & " AND (tblAcq.AcqDate " & strDateClause &
"));"
strSQL = strSQL & " AND (tblTransactions.TransDate " &
strDateClause & "));"
End If

Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
lngQtyAcq = Nz(rs!QuantityAcq, 0)
End If
rs.Close

'Get the quantity used since then.
'-------------------------------------------------------------------------
'strSQL = "SELECT Sum(tblInvoiceDetail.Quantity) AS QuantityUsed " &
_
"FROM tblInvoice INNER JOIN tblInvoiceDetail ON " & _
"tblInvoice.InvoiceID = tblInvoiceDetail.InvoiceID " & _
"WHERE ((tblInvoiceDetail.ProductID = " & lngProduct & ")"
strSQL = ""
strSQL = strSQL & "SELECT Sum(IIf([tblTransactions]![TransType] Like
"
strSQL = strSQL & chr$(34) & "*Sale*"
strSQL = strSQL & chr$(34) & ",[TransQty],0)) AS QuantityUsed "
strSQL = strSQL & "FROM tblTransactions "
strSQL = strSQL & "WHERE ((tblTransactions.PartID = " & lngProduct &
")"

If Len(strDateClause) = 0 Then
strSQL = strSQL & ");"
Else
'strSQL = strSQL & " AND (tblInvoice.InvoiceDate " &
strDateClause & "));"
strSQL = strSQL & " AND (tblTransactions.TransDate " &
strDateClause & "))"
End If

Set rs = db.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
lngQtyUsed = Nz(rs!QuantityUsed, 0)

End If
rs.Close

'Assign the return value
OnHand = lngQtyLast + lngQtyAcq - lngQtyUsed
End If

Set rs = Nothing
Set db = Nothing
Exit Function
End Function

--
HTH,
Don
=============================
Use My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================
<previous threads snipped>
Nov 13 '05 #6

P: n/a
Hi,

You actually managed to answer my question in this bit:

"I use a combo-box (cboPartNumber) to find and display the record for that
part number.
The data from tblLocations (PartID, Loc, Qty, CountDate) is displayed and
edited as nessecary in a datasheet style subform "

I have a similar form for when you want to search for a parts location, but
the subform is locked so edits cannnot be done. It was designed for the
non-storepeople to be able to find a part without messing up any
quantities - but with a bit of adjustment it could be exactly what I need.

I think the problem was that I was aiming for rapid data entry when the
count had been done - I'm used to just typing away and barely looking at the
screen, so the frmCount doesn't display a subform like this for each part
number entered.

It isn't quite the solution I expected but sometimes all you need is a fresh
pair of eyes to look at the problem.

It would be nice to be able to follow your example of
"rptStockCheckByLocation", however, our official system does not hold
details of shelf location so I have to go around and write down part
numbers, locations and quantities "blind".

A purist would say that this is the correct way to carry out a stocktake -
not knowing what the quantity should be, but what do they know eh ;-)

I actually work in a Computer Field Service centre and the reason my life
gets so complicated with these stocktakes is that engineers swap out faulty
parts of different types without making a record of it (such as giving a
customer a 40Gb drive and taking away their faulty 20Gb). This means that
when I come to consolidate, my figures are all over the place, and also why
it would be great if I could get the keyword comparison thing up and
running.

Anyway, enough of my whinging - I'll give your solution a try. It looks like
it will work.

Many thanks.

"Don Leverton" <le****************@telusplanet.net> wrote in message
news:NeOBc.6227$mm3.937@clgrps13...
Hi,

Let me tell you how I do it. Then tell me what you think. Collaboration is a good thing! :-)

Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.