Error 3021

[Marianna_Air]

When I select the add button on the lower tool bar I now get a "GetNextSN Error 3021 no current record" when I go to debug it highlites the code string in red below, thanks for any help anyone can give me

Private Function IsEditable() As Integer
'if (this is a earlier Rev than last) then don't let users change it.
Dim dbsCurrent As DAO.Database
Dim rstTmp As DAO.Recordset
Dim strTmpID As String
Set dbsCurrent = CurrentDb
Dim isRev As Boolean
strTmpID = Me!RequestforQuotesID
If (Mid(Me!RequestforQuotesID, Len(Me!RequestforQuotesID) - 1, 1) = "^") Then
strTmpID = Left(Me!RequestforQuotesID, Len(Me!RequestforQuotesID) - 2)
isRev = True


Else
isRev = False
End If

Set rstTmp = dbsCurrent.OpenRecordset("SELECT requestforquotesID " _
& "FROM [request for quotes] " _
& "WHERE requestforquotesID = """ & strTmpID & """ " _
& "OR requestforquotesID LIKE """ & strTmpID & "^*"" " _
& "ORDER BY requestforquotesID;")

If (rstTmp.RecordCount > 0) Then
rstTmp.MoveLast

If (Me!RequestforQuotesID <> rstTmp!RequestforQuotesID) Then
IsEditable = False
Else
IsEditable = True
End If
Else
IsEditable = True
End If '(rstTmp.RecordCount > 0)
rstTmp.Close
dbsCurrent.Close
Set dbsCurrent = Nothing
End Function

[ssanfu]

I changed your code a little. Try this:
What does the msgbox display?

Private Function IsEditable() As Integer
'if (this is a earlier Rev than last) then don't let users change it.
   Dim dbsCurrent As DAO.Database
   Dim rstTmp As DAO.Recordset
   Dim strTmpID As String
   Dim isRev As Boolean

   Set dbsCurrent = CurrentDb
   
   isRev = False  'initalize boolean
   IsEditable = True  'set default return value
   
   
   strTmpID = Me!RequestforQuotesID
MsgBox strTmpID


   If (Mid(strTmpID, Len(strTmpID) - 1, 1) = "^") Then
      strTmpID = Left(strTmpID, Len(strTmpID) - 2)
      isRev = True
   End If

   Set rstTmp = dbsCurrent.OpenRecordset("SELECT requestforquotesID " _
                                         & "FROM [request for quotes] " _
                                         & "WHERE requestforquotesID = """ & strTmpID & """ " _
                                         & "OR requestforquotesID LIKE """ & strTmpID & "^*"" " _
                                         & "ORDER BY requestforquotesID;")


   If (rstTmp.RecordCount > 0) Then
      rstTmp.MoveLast

      If (Me!RequestforQuotesID <> rstTmp!RequestforQuotesID) Then
         IsEditable = False
         '      Else
         '         IsEditable = True
      End If
      '   Else
      '      IsEditable = True
   End If   '(rstTmp.RecordCount > 0)

   
   rstTmp.Close
   Set rstTmp = Nothing


   ' didn't open it, so don't close it
   'dbsCurrent.Close

   Set dbsCurrent = Nothing
End Function

[Marianna_Air]

debug points to the string in bold now, but it seems to be trying to work now, it shows the last record number in a box before failing


Private Function IsEditable() As Integer
'if (this is a earlier Rev than last) then don't let users change it.
Dim dbsCurrent As DAO.Database
Dim rstTmp As DAO.Recordset
Dim strTmpID As String
Dim isRev As Boolean
Set dbsCurrent = CurrentDb

isRev = False 'initalize boolean
IsEditable = True 'set default return value


strTmpID = Me!RequestforQuotesID
MsgBox strTmpID

If (Mid(strTmpID, Len(strTmpID) - 1, 1) = "^") Then
strTmpID = Left(strTmpID, Len(strTmpID) - 2)
isRev = True
End If
Set rstTmp = dbsCurrent.OpenRecordset("SELECT requestforquotesID " _
& "FROM [request for quotes] " _
& "WHERE requestforquotesID = """ & strTmpID & """ " _
& "OR requestforquotesID LIKE """ & strTmpID & "^*"" " _
& "ORDER BY requestforquotesID;")

If (rstTmp.RecordCount > 0) Then
rstTmp.MoveLast
If (Me!RequestforQuotesID <> rstTmp!RequestforQuotesID) Then
IsEditable = False
' Else
' IsEditable = True
End If
' Else
' IsEditable = True
End If '(rstTmp.RecordCount > 0)

rstTmp.Close
Set rstTmp = Nothing

' didn't open it, so don't close it
'dbsCurrent.Close
Set dbsCurrent = Nothing
End Function

[ssanfu]

"RequestforQuotesID" is a bound control on a form?
That form is open?
What does the data in the control "RequestforQuotesID" look like?

[Marianna_Air]

RequestforQuotesID" is a bound control on a form? Yes it is
That form is open? Yes it is open
What does the data in the control "RequestforQuotesID" look like? "RequestforQuotesID" is a field in my "Request For Quotes" table and looks like RFQ12345

[ssanfu]

Add/change these lines to the function:

   strTmpID = Me!RequestforQuotesID

MsgBox "control Value = " & strTmpID
MsgBox "Mid Value = " & Mid(strTmpID, Len(strTmpID) - 1, 1)
MsgBox "Equals '^': " & Mid(strTmpID, Len(strTmpID) - 1, 1) = "^"

Exit Function

What are the results?

[Marianna_Air]

Did I put it in the right location?

Private Function IsEditable() As Integer
'if (this is a earlier Rev than last) then don't let users change it.
Dim dbsCurrent As DAO.Database
Dim rstTmp As DAO.Recordset
Dim strTmpID As String
Dim isRev As Boolean
Set dbsCurrent = CurrentDb

isRev = False 'initalize boolean
IsEditable = True 'set default return value


strTmpID = Me!RequestforQuotesID
MsgBox "control Value = " & strTmpID
MsgBox "Mid Value = " & Mid(strTmpID, Len(strTmpID) - 1, 1)
MsgBox "Equals '^': " & Mid(strTmpID, Len(strTmpID) - 1, 1) = "^"
Exit Function

If (Mid(strTmpID, Len(strTmpID) - 1, 1) = "^") Then
strTmpID = Left(strTmpID, Len(strTmpID) - 2)
isRev = True
End If
Set rstTmp = dbsCurrent.OpenRecordset("SELECT requestforquotesID " _
& "FROM [request for quotes] " _
& "WHERE requestforquotesID = """ & strTmpID & """ " _
& "OR requestforquotesID LIKE """ & strTmpID & "^*"" " _
& "ORDER BY requestforquotesID;")

If (rstTmp.RecordCount > 0) Then
rstTmp.MoveLast
If (Me!RequestforQuotesID <> rstTmp!RequestforQuotesID) Then
IsEditable = False
' Else
' IsEditable = True
End If
' Else
' IsEditable = True
End If '(rstTmp.RecordCount > 0)

rstTmp.Close
Set rstTmp = Nothing

' didn't open it, so don't close it
'dbsCurrent.Close
Set dbsCurrent = Nothing
End Function

[Marianna_Air]

If I did I'm getting three popup boxes that say

1:Control Value= RFQ13445 One of my records
2:Mid Value=4
3:False

I thank you for taking the time to help me with this, I really appreciate it

Kevin

[ssanfu]

Did I put it in the right location?

Perfect. You can delete the 3 message box lines and the Exit function line

So the first message box showed the value from the control.
The second message box showed the result of the Mid() function.
The third message box showed the result of the comparison.

All of that is working correctly.



Does the code compile without errors?
Have you set a break point and single stepped through the code?

[Marianna_Air]

No its not running the compile without errors it stops

Private Sub cmdCopyCurrentPOID_Click()
Me.requestforquotesIDCopy = Me!RequestforQuotesID
End Sub

and I also had it stop herebut I don't think that is part of my issue at this time, this may be an issue with it being a part of the database I'm cannibalizing to build this one with. But I could be wrong so I'm including it

Private Sub txtItemType_AfterUpdate()

Dim prodItemType As String

If (Me.txtItemType = "Expense") Then
Me.ProductID = "99999999999"
ProductID_AfterUpdate
Else
If Not (IsNull(Me.ProductID)) Then
prodItemType = Nz(DLookup("[ItemType]", "Products", "[ProductID] = """ & ReplaceString(Me.ProductID, """", """""") & """"), "Non-Inventory")

If prodItemType = "Inventory Item" Then prodItemType = "Inventory"
If prodItemType = "Non-inventory Item" Then prodItemType = "Non-Inventory"

If Not (prodItemType = Me.txtItemType) Then
Me.txtItemType = prodItemType
End If
End If
End If

End Sub




I'm thinking it may have been easyer to build this from the ground up even with my limited skills. Just so you know I'm building a database to track request for quotes from vendors out of a database we have that writes and tracks our purchase orders, just about all of the data is the same between the two. So I figured it would be an easy thing to change everything from PO or purchase order to RFQ or request for quote and be done with it, man was I wrong

[ssanfu]

VBA does not have a function "ReplaceString". It should be "Replace"

         prodItemType = nz(DLookup("[ItemType]", "Products", "[ProductID] = """ & Replace(Me.ProductID, """", """""") & """"), "Non-Inventory")

Haven't figured out what is happening with all of the quotes (in blue)

[Marianna_Air]

I replaced the replacestring with replace, I wonder how the original database ever even worked in the first place with all the issues I've found sense the start of my endeavor.
I ran a compile and it hangs up at the highlighted string below. I also tried to use my tool bar button to add a new record and I'm still getting the original error


Private Sub Report_Open(Cancel As Integer)
If Not request for quotes(inventory)("request for quotes(inventory)") Then
MsgBox "Open this report using the Preview button on the request for quotes form."
Cancel = True
End If
End Sub

[orange]

You appear to have some invalid naming/bracketing on the highlighted line.

It is a good practice to NOT have embedded spaces in field and object names.

[Marianna_Air]

It used to work, not sure whats going on with it, but my biggest issue right now is adding a new record I get an error and it highlights the following
Private Function IsEditable() As Integer
'if (this is a earlier Rev than last) then don't let users change it.
Dim dbsCurrent As DAO.Database
Dim rstTmp As DAO.Recordset
Dim strTmpID As String
Set dbsCurrent = CurrentDb
Dim isRev As Boolean
strTmpID = Me!RequestforQuotesID
If (Mid(Me!RequestforQuotesID, Len(Me!RequestforQuotesID) - 1, 1) = "^") Then
strTmpID = Left(Me!RequestforQuotesID, Len(Me!RequestforQuotesID) - 2)
isRev = True
Else
isRev = False
End If

[orange]

You seem to have a Function with no input parameter, that returns a Boolean, but you never give the function a return value in your code.

If it use to work, what exactly did it do? What is the purpose of the function? Why not post all the code for the function--there is no End Function statement.