MRV reuses number

[Leslie]

In my DB we track all non-conforming material (stock items from vendors) and
RGA's (Return Goods-field returns from our Customers/Distributors). We use
Access 2003.

My problem is that I have a form that is ran using a query and then a
command button to print the report to be shipped with the part back to the
vendor that we use to create MRV's (Material Return to Vendors).

When I create the first MRV for a particular vendor (ABC) it gives me the
new number (113-F) and shows the 3 parts to be returned to that vendor on the
sub-form. That is connected to the MRV primary form using the vendor number.
The MRV with all vendor information is the primary form and the sub-form
(child) is the part information. The report is created in the same manner
using a primary form (all vendor information) and a sub-form (all part
information).

Now when I go to do a second MRV to the same vendor (ABC) and I have 4 parts
to return this time, I get the 4 parts plus the original 3 parts and even
though I have requested a new number and the form gives me a new number when
the report prints it uses the original MRV number. I am at a loss as to how
to get this to work. It doesn't seem to hold the fact that the first ones
were processed on the MRV 113-F and a new number and new form/report with
only the 4 parts needs to be generated.

The MRV number is generated as an auto number in the database and a second
field adds the -F or -S for field or stock depending on the parts being
returned. And I have not concantenated the 2 fields together.

I thought about the inovice but that doesn't seem to be quite what we are
trying to do either, any help would be appreciated.

Thanks,

 

[strive4peace]

ShowFields
---


Hi Leslie,

are you STORING the generated number or simply showing it? What is the
structure of your information?

In order to better help you, we need to know your data structure. Here
is something you can do to document that for us:

create a new general module

paste in this code:

'~~~~~~~~~~~~~~~~~~
'NEEDS REFERENCE to Microsoft DAO library
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'TO DOCUMENT -->
'--- click your mouse into the appropriate Sub below
'--- press F5 to run

Sub RunShowFieldsForTable()
'edit this line for the tablename you wish to document
ShowFields "Your tablename"
End Sub

Sub RunShowFieldsForAllTables()
Dim i As Integer _
, mTablename As String
For i = 0 To CurrentDb.TableDefs.Count - 1
mTablename = CurrentDb.TableDefs(i).Name
If Left(mTablename, 4) <> "Msys" Then
Debug.Print 'blank line
ShowFields mTablename
End If
Next i
End Sub
'~~~~~~~~~~~~~~~~~~
Sub ShowFields(pstrTable As String)
'by DuaneHookom
'modified by Crystal

Dim fld As DAO.Field
Dim tbl As DAO.TableDef
Dim db As DAO.Database

Set db = CurrentDb
Set tbl = db.TableDefs(pstrTable)

Debug.Print tbl.Name
Debug.Print "=========================="

For Each fld In tbl.Fields
'modified by Crystal
Debug.Print fld.OrdinalPosition & " " & fld.Name _
& ", " & fld.Type & " (" & GetDataType(fld.Type) & ")" _
& ", " & fld.Size
Next

'release object variables
set fld = nothing
set tbl = nothing
set db = nothing

End Sub
'~~~~~~~~~~~~~~~~~~
Function GetDataType(pDatType) As String
'by Crystal
Select Case pDatType
Case 1: GetDataType = "Boolean"
Case 2: GetDataType = "Byte"
Case 3: GetDataType = "Integer"
Case 4: GetDataType = "Long"
Case 5: GetDataType = "Currency"
Case 6: GetDataType = "Single"
Case 7: GetDataType = "Double"
Case 8: GetDataType = "Date"
Case 10: GetDataType = "Text"
Case 12: GetDataType = "Memo"
Case Else: GetDataType = Format(Nz(pDatType), "0")
End Select
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~

then
Debug, compile

click in the RunShowFieldsForAllTables sub
press the F5 key to run

then press CTRL-G to show the debug window

copy the results and paste into a Reply to this thread

'~~~~~~~~~~~~~~~~~~

in addition to this output, please tell us how you are generating your
MRV and what field, if any, it is being stored in.

one thing to keep in mind is that all data is stored in tables; forms
only give a convenient way to display and edit table information

Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*