Errors generated by populating fields

HugH
HugH used Ask the Experts™
on
Have a form that I have city state and zip self populate as well as box id no. populating 3 different serial numbers in a string. Added error trap and now every time enter zip code get 3021 error "No Current Record" tbl appears to be updating ok.  Believe problem here is TB.Seek line in my error trapping.  Other problem is with box id if mistake is made and cboBox ID number is deleted receive 3075 error.  For this one need a sub routine to allow deletions and corrections.  Any way to rid myself of these two errors and get this program into the main database?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Commented:
I would make the combo Box have Multiple columns and then update from those columns.

private sub cbo_afterupdate

if len(me.cbo.column(1)) > 0 then
   me.txtcity = me.cbo.column(1)
   me.txtst = me.cbo.column(2)
end if
End sub


HTH

Jag5x5

Author

Commented:
You had a good idea there and I may employ it in the fields for cbo id fields vs. serial numbers, but not permitted to change the city state zip fields, following is sub routine I am now using for after update of zip.  Every time all fields are populated but this is constantly followed by error 3021.  Believe problem resides with TB.Seek in my global ErrorHandler found below.  I am pretty new at VB, last programming I did was COBOL many years ago.

Private Sub cboZip_AfterUpdate()
Dim DB As DAO.Database, TB As DAO.Recordset
 
Static sFunction As String
Static iError As Integer
Static iResumeNext As Integer
Static iInteger As Integer

On Error GoTo TestError
sFunction = "tblZips"

 Dim State, City As String
   
    State = DLookup("[State]", "tblZips", "[Zip] =" & cboZip)
    City = DLookup("[City]", "tblZips", "[Zip] =" & cboZip)
   
    txtCity = City
    txtState = State
   
    Me.Refresh

'==============
TestError:
'==============
iError = Err
iResumeNext = ErrorHandler(sFunction, iError)
If iResumeNext = -1 Then
  Resume Next
Else
  Close
  Exit Sub
End If
=======================================
Function ErrorHandler(sFunction As String, _
                       iError As Integer) As Integer
                       
Dim DB As DAO.Database
Dim TB As DAO.Recordset
Dim TBLOG As DAO.Recordset

Static iResumeNextFlag As Integer
Static iBeepFlag As Integer
Static iErrorLog As Integer
Static iMsgBoxFlag As Integer
Static iNoOccurrences As Long
Static vNow As Variant
Static sMessage As String
Static iMessage As Integer
Static sMsgTitle As String
 
ErrorHandler = 0
vNow = Now
iMessage = 16    'STOP icon for non-trapped errors
sMsgTitle = "FEI: " & sFunction & " ( )"

On Error GoTo FatalError

Set DB = CurrentDb()
Set TB = DB.OpenRecordset("systblErrorTrapping")

If TB.EOF Then
  GoTo GenericMessage
End If

TB.Index = "PrimaryKey"
TB.Seek "=", sFunction, iError

sMessage = TB![Description]
iMessage = TB![MsgBoxIcon]
iResumeNextFlag = TB![ResumeNextFlag]
iBeepFlag = TB![BeepFlag]
iErrorLog = TB![ErrorLogFlag]
iMsgBoxFlag = TB![MsgBoxFlag]
iNoOccurrences = TB![NoOccurrences]

TB.Edit
TB![NoOccurrences] = iNoOccurrences + 1
TB![LastOccurrence] = vNow
TB![UserName] = CurrentUser()
TB.Update
TB.Close

If iBeepFlag = -1 Then
  Beep
End If

If iMsgBoxFlag = -1 Then
  sMsgTitle = "FN: " & sFunction & " ( )"
  MsgBox sMessage, iMessage, sMsgTitle
End If

ErrorHandler = iResumeNextFlag

'--------------------------
' Check to see if entry is in <systblErrorLog>
'--------------------------

If iErrorLog = -1 Then
  Set TBLOG = DB.OpenRecordset("systblErrorLog")
  TBLOG.AddNew
  TBLOG![FunctionName] = sFunction
  TBLOG![ErrorNumber] = iError
  TBLOG![TimeOfError] = vNow
  TBLOG![UserName] = CurrentUser()
  TBLOG.Update
  TBLOG.Close
End If

Close
Exit Function

GenericMessage:

Beep
sMessage = "Error #" & iError & ": " & Error(iError)
MsgBox sMessage, iMessage, sMsgTitle

Set TBLOG = DB.OpenRecordset("systblErrorLog")
  TBLOG.AddNew
  TBLOG![FunctionName] = sFunction
  TBLOG![ErrorNumber] = iError
  TBLOG![TimeOfError] = vNow
  TBLOG![UserName] = CurrentUser()
  TBLOG.Update
  TBLOG.Close
 
Close
Exit Function

FatalError:

Beep
sMessage = "Error #" & Err & ": " & Error(Err)
sMsgTitle = "FN: ErrorHandler ( )"
MsgBox sMessage, iMessage, sMsgTitle

Set TBLOG = DB.OpenRecordset("systblErrorLog")
  TBLOG.AddNew
  TBLOG![FunctionName] = "ErrorHandler"
  TBLOG![ErrorNumber] = Err
  TBLOG![TimeOfError] = Now
  TBLOG![UserName] = CurrentUser()
  TBLOG.Update
  TBLOG.Close

Close

Author

Commented:
Decided to try out your suggestion all works fine for populating the fields unless you type in zip code which causes form to close and move to next form, any suggestions?? Pressure coming down to get the bugs out and put form in main database & I am stuck.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
BTW Still getting Error 3021 msg after update, something about my error trapping code is the snag

Commented:
I use the notinlist event to pop up another box to let them enter in values not in the list.

Dont know what error 3021 is but if it is an error trapping error I would change that code and post a question about that.

Sorry
Jag5x5

Author

Commented:
Thanks for the assist. The VB code u gave seems to be more efficient the the dlookup

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial