Command Button Question

[DataDiva via AccessMonster.com]

Hello All,
I've been working on a database in Access 2007. It is used to track quality
data for Printed Circuit Boards that are serialized. The form that I am
working on: FrmBoardInspection is where general info about the board is
entered. This form has three command buttons, they are:

CmdRecordDefects - Opens up form to record defects related to PCB's on form.

CmdNewRecord - Saves record and re-populates the Assembly_Number, Process and
any other info that the user would otherwise have to enter for the next board,
assuming it was from the same job.

This button uses an embedded macro.

Action = OnError
Action = GoToRecord
Condition = [MacroError]<>0 Action = MsgBox Message = [MacroError].
[Description]

I am using this code in the form's On Current event:

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![cboOp2] = .Fields("Op2_Number")
Me![CboOp3] = .Fields("Op3_Number")
Me![CboOp3] = .Fields("Op4_Number")
Me![JobFunction] = .Fields("Job_Function")
Me![Process] = .Fields("Process")
End With
If Me.NewRecord Then
Me.SerialNumber.SetFocus
Else
Me.TodaysDate.SetFocus
End If
If Me.NewRecord Then
Me.cboOp2.Enabled = False
Me.CboOp3.Enabled = False
Me.CboOp4.Enabled = False
End If
End If

End Sub


cmdNewInfo - This button clears the old info that has been re-populated by
cmdNewRecord once the user is ready to enter a new set of information.

I am using this code in the On Click event:

Private Sub CmdNewInfo_Click()
If Me.NewRecord Then
Me.Undo
End If
End Sub

What I would like to do is disable this cmdNewInfo until the user has saved
the previous record to prevent them from clearing information before saving
it. My coding skills are pretty amateur so any help would be appreciated.
Hope I have provided enough clear information.

Thanks,

Courtney

 

[Jeanette Cunningham]

Hi DataDiva,
the button CmdNewInfo needs to force the current record to save, then move
the form to the next record.

To save, use
If Me.Dirty Then
Me.Dirty = False
End if

To go to the next record
Docmd.GoToRecord , ,acNext

So the button code looks like this-->
Private Sub CmdNewInfo_Click()

If Me.Dirty Then
Me.Dirty = False
End If

If Me.NewRecord Then
'do nothing
Else
Docmd.GoToRecord , ,acNext
End If

the above is untested air code.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[DataDiva via AccessMonster.com]

Hi Jeanette,

I appreciate your quick reply! I ended up using some of the code that you
provided and combined it with code that I found in another post. The button
now saves and clears the record as I had hoped it would. I used this code:

Private Sub CmdNewRecord_Click()
If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew
If Me.NewRecord Then
Me.Undo
End If
End Sub

Thanks for all the help!

Courtney

 

[Jeanette Cunningham]

I had a look at your posted code and there are 2 lines that are nonsense.

These are the lines with the problem

If Me.NewRecord Then
Me.Undo

I suggest you rewrite the code like this

Private Sub CmdNewRecord_Click()
If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew
End If
End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jeanette Cunningham]

.... and remove the now redundant End If

modified code-->
Private Sub CmdNewRecord_Click()
If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew
End Sub

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I had a look at your posted code and there are 2 lines that are nonsense.

These are the lines with the problem

If Me.NewRecord Then
Me.Undo

I suggest you rewrite the code like this

Private Sub CmdNewRecord_Click()
If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew
End If
End Sub


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[John Spencer MVP]

Pardon me, but doesn't saving a record make it no longer a new record?

I believe that is always the case. If you need to do what you are doing based
on the NewRecord property I think you need something like the following.

Private Sub CmdNewRecord_Click()
Dim tfNew as Boolean
tfNew = Me.NewRecord

If Me.Dirty Then Me.Dirty = False
If Not tfNew Then RunCommand acCmdRecordsGoToNew
End Sub

Otherwise, I think you are always going execute RunCommand acCmdRecordsGoToNew
- which may be the desired outcome.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[DataDiva via AccessMonster.com]

Hi,
Thank you both for your insight. I’d like to try to provide a better
explanation for what I’d like this code to do. I also have a few questions in
regards to your posts.


I have tried both of your codes and they seem to work but they reproducing
the effect that my other command button (CmdNewRecord) has on the form. The
codes are repopulating the information that would otherwise have to be
reentered for a new board in the same lot.

What I want the CmdNewInfo button to do is save the record and not repopulate
the form so the user can enter information about a new lot of boards. That is
why I was using:

If Me.NewRecord Then Me.Undo.

The problem I am having with the code below is that if the user accidentally
hits the CmdNewRecord button but really intended to clear the form, the
record is saved and the form repopulates. The only way to clear the form is
to exit without saving the unwanted record and then reopen the form. That is
no good.


Private Sub CmdNewInfo_Click()
If Me.Dirty Then Me.Dirty = False
If Not Me.NewRecord Then RunCommand acCmdRecordsGoToNew
If Me.NewRecord Then
Me.Undo
End If

I’m not sure how to achieve the outcome that I am looking for. As you can
guess, my knowledge of code is really lacking. Should I be using something
other than the NewRecord property? Am I way off? Any suggestions are greatly
appreciated!

Thanks,

Courtney

 

[Jeanette Cunningham]

The tables set up and relationships is affecting what happens when you go to
a new record.
Would you post the details of the relevant tables.

Table name
Primary key
Foreign key
Related tables or table.

The query the form is based on.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[DataDiva via AccessMonster.com]

TblMain

Todays_Date
Job_Number (PK)
Customer
Assembly_Number
RoHS
Rev
Order_Qty
ID

TblBoardInspection

Todays_Date
Department
Job_Number (FK)
Serial_Number
Inspector_ID
Previous_Operator
Operator_2
Op2_Number
Operator_3
Op3_Number
Operator_4
Op4_Number
Job_Function
Process
Defects_Present
ID (PK)

There is a one-to-many relationship set up between tblMain and
tblBoardInspection by the Job_Number field.



FrmBoardInspection is based on a query
qryBoardInspection

Todays_Date – tblBoardInspection
Job_Number – tblBoardInspection
Department – tblBoardInspection
Quantity – tblBoardInspection
Customer – tblmain
Assembly_Number - tblMain
RoHS – tblMain
Serial_Number – tblBoardInspection
Inspector_ID – tblBoardInspection
Previous_Operator – tblBoardInspection
Operator_2 – tblBoardInspection
Op2_Number – tblBoardInspection
Operator_3 – tblBoardInspection
Op3_Number – tblBoardInspection
Operator_4 – tblBoardInspection
Job_Function – tblBoardInspection
Process – tblBoardInspection
Defects_Present – tblBoardInspection

 

[Jeanette Cunningham]

This looks like it should be simple to setup.
Use a main form for TblMain and a subform for TblBoardInspection.
The link master and child fields will automatically manage using the correct
Job no for both forms.
The info relating to TblMain stays on the main form and is always there to
read, there is no need to bother with trying to prepopulate the form for
inspections.
You can simply use the form for inspections to just record data about the
inspection.
This should be a lot simpler to set up and manage than the present
inspection form you are using, which is based on 2 tables.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia