How to move focus from data sheet sub-form???

[WB786]

It is a very simple form with a sub-form in data sheet view. The user needs to fill out all of the 4 fields in this data sheet. After wards they will press the Send Order button which will validate to make sure all required fields have been entered. And now here is the problem:

I am getting error that the QTY fields etc on the sub-form are blank. When they are not. After much trouble shooting I noticed that if there is an arrow next to the empty record the validation fails. But if there is a STAR next to the empty record the validation passes. So when the user (in this case I) tab through this sub-form the new record goes into edit mode even if I don't type anything - the only way is to click on the record above it to make the new record back to the NEW record.

I have designed quiet a few of these small DB before and never had any problem. What am I missing here?

Thanks,

WB

 

[TheAceMan1]

How are ya WB786 . . .

Post the code in the button! . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[WB786]

Here is the code:

Private Sub SendOrder_Click()

Me.frmParts.Requery 'sub-form in datasheet view

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

If IsNull([VendorName]) Then msg = msg & vbCrLf & vbTab & "- Vendor Name"
If IsNull([VendorAdd1]) Then msg = msg & vbCrLf & vbTab & "- Vendor Add1"
If IsNull([VendorCity]) Then msg = msg & vbCrLf & vbTab & "- Vendor City"
If IsNull([VendorState]) Then msg = msg & vbCrLf & vbTab & "- Vendor State"
If IsNull([VendorZipCode]) Then msg = msg & vbCrLf & vbTab & "- Vendor Zip Code"
If IsNull([ShipToDiv]) Then msg = msg & vbCrLf & vbTab & "- Store Number"
If IsNull([ShipToName]) Then msg = msg & vbCrLf & vbTab & "- Ship To Name"
If IsNull([ShipAdd1]) Then msg = msg & vbCrLf & vbTab & "- Ship Add1"
If IsNull([ShipCity]) Then msg = msg & vbCrLf & vbTab & "- Ship City"
If IsNull([ShipState]) Then msg = msg & vbCrLf & vbTab & "- Ship State"
If IsNull([ShipZip]) Then msg = msg & vbCrLf & vbTab & "- Ship Zip"
If frmParts.Form![PartQty] = 0 Then msg = msg & vbCrLf & vbTab & "- QTY"
If IsNull([frmParts].Form![PartQty]) Then msg = msg & vbCrLf & vbTab & "- QTY"
If IsNull([frmParts].Form![PartNumber]) Then msg = msg & vbCrLf & vbTab & "- Part Number"
If IsNull([frmParts].Form![PartDesc]) Then msg = msg & vbCrLf & vbTab & "- Part Description"
If frmParts.Form![PartUnitPrice] = 0 Then msg = msg & vbCrLf & vbTab & "- Part Unit Price"
If IsNull([RequestBy]) Then msg = msg & vbCrLf & vbTab & "- Requested By"

If msg <> "" Then
MsgBox "One or more of the required fields are blank." & msg, vbExclamation, "CAN NOT SEND PARTS ORDER!"
Else

msg = "You Will Not Be Able To Make Any Changes After The Order Has Been Sent! " & vbCrLf & "YES To Send, NO To Cancel?"
If MsgBox(msg, vbYesNo, "Are You Sure You Want To Send This Part(s) Order?") = vbYes Then
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Dim stDocName As String

stDocName = "rptPartsRequestPrint"
DoCmd.OpenReport stDocName, acViewNormal

stDocName = "rptPartsRequest"
DoCmd.OpenReport stDocName, acViewNormal

Dim safemail As Variant
Dim myOlApp
Dim myItem
Dim myRecipient
Dim myBody
Dim myfolder
Dim mynamespace
Dim myAttachments

Set myOlApp = CreateObject("Outlook.Application.12")
'Set myOlApp = CreateObject("Outlook.Application.10")
Set myItem = myOlApp.CreateItem(0)
Set safemail = CreateObject("Redemption.SafeMailItem")
Set safemail.Item = myItem
'Set myRecipient = safemail.Recipients.Add("Tool Inident Managers")
Set myRecipient = safemail.Recipients.Add("Waheed Beg")
Set mynamespace = myOlApp.GetNamespace("MAPI")
Set myfolder = mynamespace.GetDefaultFolder(5)
safemail.Subject = "NEW PARTS ORDER - " & " " & Now()
safemail.Body = "A Parts Order Has Been Printed To Your Printer and Needs Your Immediate Attention!" & Chr(13) & Chr(13) _
& "Parts Order Number: " & [PartsReqID] & Chr(13) _
& "Store Number: " & [ShipToDiv]
safemail.ReadReceiptRequested = False
safemail.OriginatorDeliveryReportRequested = False
safemail.Send
Set myOlApp = Nothing
Set safemail = Nothing
'----------------------------------------------------------

MsgBox "Your Parts Order Has Been Sent!", vbExclamation, "Email Sent"
Me.Submitted.Value = True
Me.Exit.SetFocus
Me.SendOrder.Enabled = False
DoCmd.GoToRecord , , acNewRec
End If
End If
End Sub

WB

 

[TheAceMan1]

WB786 . . .

Is the button on the mainform or subform?

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[WB786]

It is on the Main Form. It can't be on the sub-form.

WB

 

[TheAceMan1]

OK WB786 . . .

It appears you intent is to send newly saved records (correct me if I'm wrong).

In any case you requery the form before you save a new record:

[blue]Me.frmParts.Requery 'sub-form in datasheet view

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70[/blue]

When you requery a form focus goes to the 1st record! Your subsequent save record saves that record and not the newly added.

At the very least you need to reverse the two:

[blue]   DoCmd.RunCommand acCmdSaveRecord
   [green]'rest of the code here[/green]
   Me.frmParts.Requery[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[WB786]

No that didn't work either.

I think it might be a "flaw" in Access 2003. If I manually click back to the first record then the focus comes off the new record. And it works fine. If I just simply tab through the new record without typing anything in any of the fields it is still making that record locked for editing and so the validation fails because it see that record not finished even though all I want to do is just tab through the new record and go to the next field which is on the main form. In this example I am only making a single record entry in this sub-form. Or even if I do make more then one record it still makes the next new record locked for editing.

I hope this makes sense to you.

Thanks for the help so far.

WB