New Record Question

[DataDiva via AccessMonster.com]

I have a form where most of the fields in a new record are auto populated
with the previous record's information. I have used this code in the On
Current event of the form:

Private Sub Form_Current()
Dim rs As Object
Set rs = Me.Recordset.Clone

If rs.EOF Or Not Me.NewRecord Then
' Don’t do anything if there's no records or it is not a new record
Else
With rs

.MoveLast

Me![JobNumber] = .Fields("Job_Number")
Me![cboDepartments] = .Fields("Department")
Me![InspectorID] = .Fields("Inspector_ID")
Me![PrevOperator] = .Fields("Previous_Operator")
Me![JobFunction] = .Fields("Job_Function")
Me![Process] = .Fields("Process")
End With

End If

End Sub

The code is working fine. I know someone will ask "why" I want to store
redundant data so I will just get that answer out of the way Auto
populating makes the users job a lot easier because they will only be working
on one Job at a time, which means that most of the information should be the
same with the exception of the Serial_Number and if there are Defects_Present.
My question is when a user is ready to move to different Job Number with a
new set of information, is there a way to create a button (or something to
that effect) that gives you a new blank record without populating any of the
previous info? I hope this makes some sense! Thank you in advance for any
help that is offered!

Courtney

 

[Dennis]

The REAL answer is to have a subform for the data that changes. The main form
can have all that "redundent" data, and it only appears ONCE. The subform is
bound to a child table which has a PK/FK relationship with the parent table.

At taht point, all a user has to do for a new parent (job) is click the Add
New Record button on the parent form.

Proper database design eliminates a lot of unnecessary work and duplicated
data.

 

[Klatuu]

Well, excluding the fact that you are thinking spreadsheet in a relational
database, you could add this magic button. In its click event, you can put
a value in a module level variable that you could then test before you
populate the fields in the current event. You would also need to reset the
value of the variable in the current event.

As to what you should do.
You need two tables rather than 1. The fields you are prepopulating should
be the parent table and the fields being entered manually should be the
Child table. You should set up a form/subform for the data.

In a spreadsheet is is normal to have a lot of redundant data, but it is
incorrect in a relational database. The Child table only needs a field to
store the primary key field value of its parent record.

 

[John Spencer]

You could have code in a button to UNDO the changes. The following code
snippet would undo all the changes

IF Me.NewRecord Then
Me.Undo
End If

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[DataDiva via AccessMonster.com]

Hello,

Thank you both for your replies! I am still a bit confused. I think I
understand the concept of a form/sub form but I have yet to build one that
works the way that I would like it to. Maybe I could describe my table and
form structure and you guys could make some suggestions as to the best way to
go about this.

tblMain - This is the main table where the originator of a job puts in the
initial job information using a form (frmMain).

Todays_Date - Date/Time
Job_Number - (PK)
Customer - Combo to a lookup table
Assembly_Number - Text
RoHS - Combo to lookup table
REV - Text
Order_Qty - Number
ID - Autonumber

tblBoardInspection - This table is used to store information about the
initial inspection of a PCB board and to determine whether or not any defects
were found. There is a one to many relationship set up from the tblMain to
tblBoardInspection.

Today_Date - Date/Time
Department - (PK) Combo to a lookup table
Job_Number - Text
Serial_Number - (PK) Text
Inspector_ID - Combo to a lookup table
Previous_Operator - Combo to a lookup
Job_Function - (PK) Combo to a lookup table
Defects_Present - Combo to a look up table
ID - Autonumber

The reason I have three PK fields in this table is because each board will
have a unique Serial_Number and will be going through more than one
Department and it is also possible that a board will be sent back to the same
department for a different Job_Function.

The form (frmBoardInspection) is based on a query (qryBoardInspection) that
contains all of the fields from tblBoardInspection except for ID and the
Customer, Assembly_Number, and Rohs fields from tblMain. Looks like this:

Todays_Date
Job_Number - once this is entered it auto populates the fields from tblMain
Department - Combo box
Customer - Unbound text box used to populate information from the tblMain as
a visual aid for user
Assembly_Number - Unbound text box used to populate information from the
tblMain as a visual aid for user
Rohs - Unbound text box used to populate information from the tblMain as a
visual aid for user
Serial_Number - textbox
Inspector_ID - Combo box
Previous_Operator - Combo box
Job_Function - Combo box
Process - Combo box
Defects_Present - Combo box
cmdSave - Saves the record
CmdDefect_table - If Defects are present then this cmdbutton enables for
users to open up the frmBoardDefects
cmdClose -Closes form when user is finished


This is where I feel like it gets really redundant and messy!

tblBoardDefects - This table is used to store information about the defects
if they are found on the boards. There is an indeterminate relationship set
up between the ID field in tblBoardInspection and the ID field in
tblBoardDefects.

Todays_Date - Date/Time
Job_Number - text
Department - textbox
Serial_Number - text
Qty_Defective - Number
Error_Code - Combo box to lookup
Location - text
Comment - text
ID - (PK)

frmBoardDefects - This form will only be used if there are defects found on a
board.

Todays_Date
Job_Number - autopopulated from frmBoardInspection
Department - is autopopulated form frmBoardInspection
Serial_Number - autopopulated form frmBoardInspection
Qty_Defective - text box
Error_Code - is a combo box that is populated based on the department field.
There are different error codes for each department.
Location - textbox
Comment - textbox
cmdSave - saves record
cmdClose - closes form

I know there is a much better way to set this up, I just don’t know how. A
start in the right direction would be very helpful!

Courtney

 

[DataDiva via AccessMonster.com]

John,

Thanks for the little code! I put it in the On Click event of a command
button and it's working great!

Courtney