I have this form with a couple of subforms. The link is the OCA number, and then there is the Nbr for each item of the subforms. I am encountering two problems. The first problem is that I go to a record (with an OCA number), and would like to add an item to my Arrest subform. I enter the Nbr, and I get an error message. Microsoft Visual Basic, Compile error: User-defined type not defined. It point to the "Dim dbs As DAO.Database" part of the code. I click okay, and close the VB compile form, and then I am allowed to enter the Nbr until I try to enter another one. The process repeats itself as long as I am entering a new one. Once I get the error message, I am allowed to enter the Nbr. This Arrest subform allows me to see the arrest for this particular OCA number.
Public Function CreateNewOCANumber(ByVal varCaseOCANumber As Variant) As Integer
On Error GoTo Err_CreateNewOCANumber
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
CreateNewOCANumber = -1
If IsNull(varCaseOCANumber) Then
'// No case log number passed to function.
MsgBox "An OCA number can not be assigned to this item because no ""Case OCA#"" was given for the item.", vbExclamation + vbOKOnly, "System Error"
Exit Function
End If
'// Construct SQL string to return the largest item number for
'// the giving case log #.
strSQL = "SELECT Max([D1 Revised Format].Nbr) AS MaxOCANumber"
strSQL = strSQL & " FROM [D1 Revised Format]"
strSQL = strSQL & " GROUP BY [D1 Revised Format].[OCA]"
strSQL = strSQL & " HAVING ((([D1 Revised Format].[OCA])=""" & varCaseOCANumber & """));"
'// Return the largest item number for the giving case log #.
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
If rst.RecordCount > 0 Then
'// The value was returned.
'// Populate the recordset.
rst.MoveLast
rst.MoveFirst
CreateNewOCANumber = rst![MaxOCANumber] + 1
Else
'// No value returned so, this means that this is the first
'// item for this case log #.
CreateNewOCANumber = 1
End If
'// Close objects.
rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing
Exit_CreateNewOCANumber:
Exit Function
Err_CreateNewOCANumber:
MsgBox Err.Description
Resume Exit_CreateNewOCANumber
End Function
My other problem is that I have a command button that when I click on it, I open another form which should display the Nbr numbers (along with the rest of the information that I entered) so that I can select one of them, and enter the additional info for that subform. I do not see that list of items.
Private Sub List24_AfterUpdate()
On Error GoTo Err_List24__AfterUpdate
If IsNull(List24_.Value) Then Exit Sub
'// If record is dirty, then attempt to save first before moving
'// to another record.
If Me.Dirty Then
If IsValidRecord Then
'// Record is valid for saving. Attempt to save record.
On Error Resume Next
DoCmd.RunCommand acCmdSaveRecord
If Err And Err <> 2501 Then
'// Record did not save. Do not move to another record.
MsgBox Err.Description
Err.Clear
List24_.Value = CStr(mvarOCANumber & mvarNbrNumber & "")
Exit Sub
End If
Else
'// Record is not valid for saving. Do not move to another record.
List24_.Value = CStr(mvarOCANumber & mvarNbrNumber & "")
Exit Sub
End If
End If
On Error GoTo Err_List24__AfterUpdate
'// Display the selected record.
Call SelectNbr(List24_.Column(1), List24_.Column(2))
Exit_List24__AfterUpdate:
Exit Sub
Err_List24__AfterUpdate:
MsgBox Err.Description
Resume Exit_List24__AfterUpdate
End Sub
Can anyone suggest what my problem is? I am not good with VB, and am not too good at following the code.
Thank you for any help.
Public Function CreateNewOCANumber(ByVal varCaseOCANumber As Variant) As Integer
On Error GoTo Err_CreateNewOCANumber
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
CreateNewOCANumber = -1
If IsNull(varCaseOCANumber) Then
'// No case log number passed to function.
MsgBox "An OCA number can not be assigned to this item because no ""Case OCA#"" was given for the item.", vbExclamation + vbOKOnly, "System Error"
Exit Function
End If
'// Construct SQL string to return the largest item number for
'// the giving case log #.
strSQL = "SELECT Max([D1 Revised Format].Nbr) AS MaxOCANumber"
strSQL = strSQL & " FROM [D1 Revised Format]"
strSQL = strSQL & " GROUP BY [D1 Revised Format].[OCA]"
strSQL = strSQL & " HAVING ((([D1 Revised Format].[OCA])=""" & varCaseOCANumber & """));"
'// Return the largest item number for the giving case log #.
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot)
If rst.RecordCount > 0 Then
'// The value was returned.
'// Populate the recordset.
rst.MoveLast
rst.MoveFirst
CreateNewOCANumber = rst![MaxOCANumber] + 1
Else
'// No value returned so, this means that this is the first
'// item for this case log #.
CreateNewOCANumber = 1
End If
'// Close objects.
rst.Close
dbs.Close
Set rst = Nothing
Set dbs = Nothing
Exit_CreateNewOCANumber:
Exit Function
Err_CreateNewOCANumber:
MsgBox Err.Description
Resume Exit_CreateNewOCANumber
End Function
My other problem is that I have a command button that when I click on it, I open another form which should display the Nbr numbers (along with the rest of the information that I entered) so that I can select one of them, and enter the additional info for that subform. I do not see that list of items.
Private Sub List24_AfterUpdate()
On Error GoTo Err_List24__AfterUpdate
If IsNull(List24_.Value) Then Exit Sub
'// If record is dirty, then attempt to save first before moving
'// to another record.
If Me.Dirty Then
If IsValidRecord Then
'// Record is valid for saving. Attempt to save record.
On Error Resume Next
DoCmd.RunCommand acCmdSaveRecord
If Err And Err <> 2501 Then
'// Record did not save. Do not move to another record.
MsgBox Err.Description
Err.Clear
List24_.Value = CStr(mvarOCANumber & mvarNbrNumber & "")
Exit Sub
End If
Else
'// Record is not valid for saving. Do not move to another record.
List24_.Value = CStr(mvarOCANumber & mvarNbrNumber & "")
Exit Sub
End If
End If
On Error GoTo Err_List24__AfterUpdate
'// Display the selected record.
Call SelectNbr(List24_.Column(1), List24_.Column(2))
Exit_List24__AfterUpdate:
Exit Sub
Err_List24__AfterUpdate:
MsgBox Err.Description
Resume Exit_List24__AfterUpdate
End Sub
Can anyone suggest what my problem is? I am not good with VB, and am not too good at following the code.
Thank you for any help.
