If Statement Hlep

[shankbanta]

I need with making an addendum to some code. This code was created by someone else and being the novice that I am, I know what I want it to do, but am unsure of the code. This is how it currently stands:

Private Sub Download_AS400_Click()
On Error GoTo Err_Download_AS400_Click

DoCmd.SetWarnings False
DoCmd.OpenQuery "Append AS/400"
Me.Refresh
DoCmd.SetWarnings True
Me.RecordSource = "select * from [BTD1];"
Dim R As Recordset
Set R = Me.RecordsetClone
R.FindFirst "[JobNum] = " & Chr(34) _
& Me![JobAS400] & Chr(34)
Me.Bookmark = R.Bookmark
[FindJob] = [JobAS400]
[FindBan] = [BanCode]
[BanCovQty] = 0
If [JobNum] = 0 Then
SendKeys "{ESC}"
End If
DoCmd.GoToControl "BinderOp"

Exit_Download_AS400_Click:
Exit Sub

Err_Download_AS400_Click:
MsgBox Err.Description
Resume Exit_Download_AS400_Click

End Sub


I the [JonNum] is not found in the AS400 database I get the following error:

"Update or CancelUpdate wihtou AddNew or Edit"

So I think I need an If statement for when the job code is not found on the AS400. When the number is not found, I need to make a new record in the BTD1 database.

If [FindJob] Not Equal [JobAS400]
Then AddNew

Thanks for all help.

 

[markgrizzle]

Try testing for no match found before setting the bookmark. The problem is that if no match is found, then there isn't a current bookmark.

You said you needed to add a new record to the table, so you could use an insert statement to add it. I didn't include actual fields in my example.

R.FindFirst "[JobNum] = " & Chr(34) _
& Me![JobAS400] & Chr(34)

if r.nomatch then
currentdb.execute "Insert Into BTD1 (Field1,...) " & _
"Select (Field1,...)
else

Me.Bookmark = R.Bookmark
[FindJob] = [JobAS400]
[FindBan] = [BanCode]
[BanCovQty] = 0
If [JobNum] = 0 Then
SendKeys "{ESC}"
End If
DoCmd.GoToControl "BinderOp"

end if

 

[shankbanta]

Isn't there a way to set the code so that it adds to the fields at the end of the database. Mimicking the >* button and then fill in the fields on the form?

 

[markgrizzle]

The new record is always added to the 'end' of the table. To make the new record appear on the form, try requerying the form's recordsource while filtering on the new record.