UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Append Data From One Table To Another And Auto-increment Serial Number, Access 2016    
 
   
Vector1
post Jan 23 2018, 12:45 PM
Post#1



Posts: 72
Joined: 9-March 17



Hi,
Can someone help me create a VBA (button) to append data from one table to another. ALSO want to auto-increment serial number where the fields get populated:

Structure:

Table Name: tbl_LastSerialNumber
Fields: PartNo (multiple parts), LastSerialNo (capture last serial number added for a specific part)

Table Name: tbl_ABR_Common
Fields: RotNo, RotRev, RotTask1, RotTask2, RotFormNo, RotTravelerRev, RotTaskActive

Table Name: tbl_ABR
Fields: ID (PK), RotSerialNo, RotNo, RotRev, RotTask1, RotTask2, RotFormNo, RotTravelerRev, Emp1, Emp2, Date1, Date2, Check1, Check2

So the question:
Append data from table "tbl_ABR_Common" to table "tbl_ABR" where "RotTaskActive" is true and lookup last serial number of specific "PartNo" in table "tbl_LastSerialNumber", increment it by 1, and insert in that appended record. OPEN FORM to that PARTICULAR record.
Update table "tbl_LastSerialNumber" with the last serial number for that particular "PartNo".

Thanks a bunch.
Go to the top of the page
 
tina t
post Jan 23 2018, 01:26 PM
Post#2



Posts: 5,614
Joined: 11-November 10
From: SoCal, USA


can you tell us about the business process you're supporting, and the purpose of each table? because just looking at what you've posted, my first thoughts are that you don't need tbl_LastSerialNumber, neither of the other two tables is normalized, and why would you duplicate data from one of those tables to the other?

but we don't know enough about what you're doing, and why, at this point, to know if any of those concerns are valid in your situation.

hth
tina
Go to the top of the page
 
Vector1
post Jan 23 2018, 02:08 PM
Post#3



Posts: 72
Joined: 9-March 17



The business process is basically a shop traveler which contains some common tasks to be performed by operator.
The tbl_ABR_Common contains specific tasks that each record in tbl_ABR has to have when an operator wants to create a new traveler.
The normalization between the two tables is not needed at this point as the tbl_Common has nothing but data needed to be appended to tbl_ABR for the operator to see which tasks to be performed.
Every time an operator creates a new record (traveler) s/he needs to be able to see that information to sign off in the "tbl_ABR".
The only difference is the serial number that separate each traveler.
The "tbl_LastSerialNumber" is just to start off the traveler(s) when it's time to implement the database. Once the first cut off serial number(s) is/are entered, we won't need that table (may be).
Go to the top of the page
 
Vector1
post Jan 23 2018, 03:04 PM
Post#4



Posts: 72
Joined: 9-March 17



Can someone help me with this code? I am getting error 3061 too few parameters expected 1. In NewSerial, GlobalCode

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim intSN As Long
Dim LastRec As Long
Dim strSQL_Insert As String

On Error GoTo NewSerial_Error
LastRec = DMax("LastSerialNo", "tbl_ABR_1SerialLast", "TravelerPart=" & "'" & 1721854 & "'")

Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_ABR_DiodeAssy", dbOpenDynaset)
'increment LastSerialNo
If rs.RecordCount = 0 Then
intSN = LastRec
Else
intSN = LastRec + 1
End If
'create append query
strSQL_Insert = _
"INSERT INTO tbl_ABR_DiodeAssy (RotDiodeSerialNo, RotDiodeNo, RotDiodeRev, RotDiodeTask1, RotDiodeTask2, RotDiodeTask3, RotDiodeFormNo, RotoDiodeTravelerRev) " & _
"SELECT (intSN),tbl_ABR_Diode_Comm.RotDiodeNo, tbl_ABR_Diode_Comm.RotDiodeRev, tbl_ABR_Diode_Comm.RotDiodeTask1, tbl_ABR_Diode_Comm.RotDiodeTask2,
tbl_ABR_Diode_Comm.RotDiodeTask3, tbl_ABR_Diode_Comm.RotDiodeFormNo, tbl_ABR_Diode_Comm.RotoDiodeTravelerRev " & _
"FROM tbl_ABR_Diode_Comm " & _
"WHERE (((tbl_ABR_Diode_Comm.RotDiodeTasksActive)=True))"

db.Execute strSQL_Insert, dbFailOnError

ProcExit:
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Exit Sub

NewSerial_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") " & _
"in NewSerial, GlobalCode"
Resume ProcExit


'Update tbl_ABR_1SerialLast with the last record vlaue

Set db = CurrentDb
Set rs = db.OpenRecordset("tbl_ABR_1SerialLast", dbOpenDynaset)
rs.FindFirst "TravelerPart=" & "'" & 1721854 & "'"
rs.Edit
rs!LastSerialNo = intSN
rs.Update
'me.Requery
End Sub
This post has been edited by Vector1: Jan 23 2018, 03:26 PM
Go to the top of the page
 
Jeff B.
post Jan 23 2018, 04:00 PM
Post#5


UtterAccess VIP
Posts: 10,166
Joined: 30-April 10
From: Pacific NorthWet


It sounds like you may be "welded" to a particular approach. Tina has already pointed out that the structure you're using may be interfering with your/Access' ability to do what you want.

Instead of telling us "how" you want to do something, tell us a little more about "what" that is -- that is, what do you/your users want to accomplish (rather than how you want to do it ...)?
Go to the top of the page
 
Vector1
post Jan 23 2018, 04:31 PM
Post#6



Posts: 72
Joined: 9-March 17



We run shop travelers for every piece we produce. A shop floor traveler may have a number of tasks, ranging from verifying that the task is complete to entering test or measurement values as required by the customer.
So, this particular Traveler has those tasks to be verified by the operator (as depicted in the table "tbl_ABR") for piece produced. The only difference between each piece produced is the "Serial Number".

There are a number of travelers (one for each sub-assembly being built). Each gets its own serial number. Each has its own part number.

Note: we have batch production model (i.e. we produce a number of sub-assemblies of each) {reason being shortage of parts here and there}

At the end of the day, when operator is ready to put the upper level/top assembly (or call it finish good), s/he randomly picks one sub-assembly form each and puts together the finish good which gets it own serial number along with linking up the serial numbers of all the sub-assemblies used to produce the finish good.
(Until the end we don't know which serial numbers will be linked to the finish good serial number)

Let me know if this provides enough background

How should this process be approached in ms-access?
Go to the top of the page
 
tina t
post Jan 23 2018, 07:10 PM
Post#7



Posts: 5,614
Joined: 11-November 10
From: SoCal, USA


okay. i've worked for a manufacturing company for the past 12 years, so i do understand what you're describing. we use travelers for our production process as well.

QUOTE
The normalization between the two tables is not needed at this point as the tbl_Common has nothing but data needed to be appended to tbl_ABR for the operator to see which tasks to be performed.

i don't know what you mean be "between the two tables", but both tables do need to be normalized. repeating fields are a red flag, and a big no-no in relational design. it locks your production process into a specific number of tasks, with no flexibility to support changes to a process.

instead, i'd suggest a table listing every piece you produce. another table listing every task that may be done in production of any piece. these two tables have a many-to-many relationship: one piece may require many tasks, and one task may be assigned to many pieces.

so you also need a junction table, to support the relationship between pieces and tasks. each intersection between a specific piece and a specific task is one record in the table - not one field. this table replaces your tbl_ABR_Common. so far, your setup would look like

tblPieces
pieceID (primary key, autonumber)
pieceName
<other fields that describe a piece; maybe dimensions, maybe material, etc.>

tblTasks
taskID (pk, autonumber)
taskName

tblPieceTasks
ptID (pk, autonumber)
pt_pieceID (foreign key from tblPieces)
pt_taskID (foreign key from tblTasks)

so if piece A requires seven tasks to be completed, then there will be seven records for that piece in tblPieceTasks, one record for each task. if piece B requires 25 tasks to be completed, then there will be 25 records for that piece in tblPieceTasks, one record for each task. and so on...

the above is standard relational design. its' advantage is its' complete flexibility to support changes in the business process by changing records in the tables, not changing the tables' structures. remember that a change in table structure will inevitably require changing the structure of every object that works with that table - queries, forms, reports, and code.

your production table, which tracks specific instances of travelers and the tasks assigned to each one, should be split into two tables also, echoing the design described above.

as for the "last serial number" table, well, i wouldn't bother with it, because you can get the same info from the production piece table, but i also don't see any harm in using it - so long as it's updated at a timely point in the process, so that you don't get conflicts in a multi-user environment.

hth
tina


Go to the top of the page
 
Vector1
post Jan 29 2018, 01:07 PM
Post#8



Posts: 72
Joined: 9-March 17



Tina,
Thank you for providing really good insight...
So, if a piece has specification to be be met e.g. Depth should be 8" +/- 0.5". Is this part of the tblPieces? and the actual measurement recorded by the operator is part of tblPiecesTasks?
What if another task says to "follow certain work instruction" and there is checkbox for operator to confirm and a sign off button that populates employee's name and date?
Please help determine the layout of fields if possible.
Go to the top of the page
 
tina t
post Jan 29 2018, 02:05 PM
Post#9



Posts: 5,614
Joined: 11-November 10
From: SoCal, USA


QUOTE
So, if a piece has specification to be be met e.g. Depth should be 8" +/- 0.5". Is this part of the tblPieces? and the actual measurement recorded by the operator is part of tblPiecesTasks?

no and no.

as i said before:
QUOTE
your production table, which tracks specific instances of travelers and the tasks assigned to each one, should be split into two tables also, echoing the design described above.

go back and re-read my previous post, hon. also, recommend you read up/more on relational design principles, to help you understand the relational design model i've described. there are links on the UA home page, IIRC, to a number of websites that will help you.

hth
tina
Go to the top of the page
 
Vector1
post Mar 6 2018, 06:13 PM
Post#10



Posts: 72
Joined: 9-March 17



Tina,
I did get a little chance to read up m-t-m relationship and Entity Relationship Diagrams. Well, I still consider myself really novice. I hope you can help me a little bit more...

So my junction table tblPieceTasks becomes my tbl_ABR_Common (which carries all the tasks related to all the parts when queried).


I am thinking that my Top level table would look:
tblTopLevel:
TopID
PartNumber? or PtID?
SerialNumber
OperatorName
DateSigned

tblSubLevel:
SubTopID
TopIDFK
ActualReading

Which field link my upper level production tables to the tblPieceTasks?
How do I associate my ActualReading field with each record from tblPiecesTasks? How do I pull common tasks in a from view for the user to see and associate ActualReading field for them to populate?

Please help!
Go to the top of the page
 
tina t
post Mar 6 2018, 06:44 PM
Post#11



Posts: 5,614
Joined: 11-November 10
From: SoCal, USA


hello Vector, just posting to let you know i saw your most recent post, even though it's been awhile. i need time to sit and give this some thought, get my head back in the game a bit, and i don't have that time today. i'll try to take a closer look at the thread and your last post, in the next day or two. or somebody else may step in and give you a hand - we all help out where we can in these forums.

hth
tina
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    14th December 2018 - 09:37 AM