ClaraBarton
Registered User.
- Local time
- Today, 01:42
- Joined
- Oct 14, 2019
- Messages
- 427
In the following code all the variables test right, the proper record is created and everything is right BUT the form will not move to the new record.
Code:
Private Sub btnNew_Click()
On Error GoTo btnNew_Click_Err
Dim db As DAO.Database
Set db = CurrentDb
Dim rst As DAO.Recordset
Dim intDoc As Integer
Dim strWhere As String
Dim strFind As String
Dim intDrawer As Integer
Dim intNewID As Integer
Dim strSql As String
Me.FilterOn = False
'get the drawer
intDrawer = PopupValue("popDrawer", "lstLocation")
Set rst = Me.RecordsetClone
strFind = "[InUse] = False And [fDrawerID] =" & intDrawer
If rst.RecordCount <> 0 Then
rst.MoveLast
rst.MoveFirst
End If
'find an empty record to fill in
rst.FindFirst strFind
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
Else
'If there are no empty records, tack them on the end
With rst
.AddNew
![Modified] = Date
![Description] = "<New Item>"
'grab the new ID for the detail table
intNewID = ![ItemID]
.Update
'requery the form to pick up the new record
Me.Requery
End With
rst.Close
Set rst = Nothing
'reposition the form
Set rst = Me.RecordsetClone
strWhere = "ItemID = " & intNewID
If Not rst.EOF Then
rst.FindFirst strWhere
Me.Bookmark = rst.Bookmark
End If
End If
'create a matching record in the detail table
intDoc = Nz(DMax("DocNo", "qryItems1", "[fDrawerID]=" & intDrawer), 0)
intDoc = intDoc + 1
strSql = "INSERT INTO tblItemDetail ([DocNo], [fDrawerID], [fItemID], [InUse]) " & _
"VALUES (" & intDoc & "," & intDrawer & "," & intNewID & "," & -1 & ");"
With CurrentDb
.Execute strSql
End With
On Error Resume Next
rst.Close
Set rst = Nothing
Me.Requery
DoCmd.GoToControl "Item"
btnNew_Click_Exit:
Exit Sub
btnNew_Click_Err:
MsgBox Error$
Resume btnNew_Click_Exit
End Sub