Error Message when a record does not exist.

[HillTJ]

People, I have a form that has the following recordsource:

SELECT QuotesTBL.QUOTEID, QuotesTBL.JimCode, QuotesTBL.FreightID, QuotesTBL.QuoteValidity, QuotesTBL.[Delivery Location], QuotesTBL.Delivery_Address, QuotesTBL.CustcontactID, QuotesTBL.Date_Quoted, QuotesTBL.Quote_Description, QuotesTBL.Quoted_Freight, QuotesTBL.Cost_RM_STD, QuotesTBL.GP_Percentage, QuotesTBL.Cost_RM_LS, QuotesTBL.Cost_RM_Clr, QuotesTBL.Cost_MFG, QuotesTBL.Cost_RM_HV, QuotesTBL.EmployeeRef, QuotesTBL.Quote_Status, QuotesTBL.Quote_Status_Date, QuotesTBL.Cust_Order, QuotesTBL.Pronto_Sales_Order, QuotesTBL.[Quote_Accepted(Y/N)], TBLFreight.Freight_Rate, CompaniesTBL.Rebate_Percentage, CompaniesTBL.[Quote_Validity(Days)], QuotesTBL.Cost_RM_Stripe, QuotesTBL.Cost_RM_BLK, QuotesTBL.[AIM_GP ($)]
FROM CompaniesTBL INNER JOIN (TBLFreight INNER JOIN QuotesTBL ON TBLFreight.FreightID = QuotesTBL.FreightID) ON CompaniesTBL.JimCode = QuotesTBL.JimCode
WHERE (((QuotesTBL.EmployeeRef)=GetUserName()) AND ((GetUserLevel())=1)) OR (((GetUserLevel())<>1))
ORDER BY QuotesTBL.QUOTEID;

The concept is that only the records that are filtered by 'GetUserName' and 'GetUserLevel' are returned (so that the user only sees their own work) . This works fine for existing records. However, when a 'new' user opens the form, clearly there is no existing record to filter & display. Hence, an error message. The user can accept the error, then the form opens for data entry, but I'd rather not have the error message displayed.

How's it best to tackle this?

 

[The_Doc_Man]

There is such a thing as a Form_Error event which can intercept and handle messages.

Form.Error event (Access)

Office VBA reference topic

docs.microsoft.com

In essence, you take an error and the form triggers an error event. It passes in the error information and you can choose how to respond to it - with no message at all, a message, or a forced form exit, or whatever else you think is right. If you want a custom error message or something more forceful than a simple message, you have to program that on your own.

 

[theDBguy]

Where is the error coming from? The record source by itself shouldn't produce an error message.

 

[arnelgp]

use a form so on BeforeInsert event of the form, you supply EmployeeRef with the currentusername.

 

[HillTJ]

Gents, error appears to occur upon opening the form for a new user where no record exists. Once a record is saved for that user, next time they open the form all is ok with no error message. Will investigate the 'form_error' event.

 

[arnelgp]

maybe the error is in GetUserLevel()?

also the condition on your query does not make sense:

((GetUserLevel())=1)) OR (((GetUserLevel())<>1))

it has No condition at all.

 

[HillTJ]

Gents error is '3021' 'No Current Record'. I've Trapped it with my Error Trapping Routine, but would rather it not occur at all. Will continue....

 

[The_Doc_Man]

Gents error is '3021' 'No Current Record'. I've Trapped it with my Error Trapping Routine, but would rather it not occur at all. Will continue....

For it to not occur at all when opening the form given the conditions you describe, that form would have to be unbound - which kind of kills the intent of the form. Trap it silently and go forward from there.

 

[Gasman]

So check the recordcount of form?

 

[Pat Hartman]

What is the error message? Add error trapping if you have none so we can find out what is causing the error.

Technically, you should not get an error just because a record does not exist. You must have code that is causing the error. For example, opening the form to a "new" record but issuing the command to go to a new record.

 

[HillTJ]

People, I've tracked down the offending code, See below. Also see my error trapping. This works. To recap, the error appears to only be triggered when opening the form when no record exists for that user. Once a record is added for that user, all is well.

Appreciate any assistance

Private Sub Form_Current()
Combo20.Requery
Combo23.Requery

On Error GoTo ErrorHandler

Dim rst As DAO.Recordset
Dim lngCounter As Long

Set rst = Me.RecordsetClone

With rst
.MoveFirst
.MoveLast
lngCounter = .RecordCount
End With

Me.txtRecordNo = "Quote " & Me.CurrentRecord & " of " & lngCounter

Me.JIMCBO_FOR_Quote.RowSource = "Table/Query"
Me.Combo23.RowSource = "Table/Query"
Me.Combo20.RowSource = "Table/Query"
Me.Combo27.RowSource = "Table/Query"

If Me.NewRecord Then

Call MaterialCost
Me.JIMCBO_FOR_Quote.RowSource = "Companies_QryActive"
Me.Combo23.RowSource = "QryDelivery_Address_For_Quotes_Active"
Me.Combo20.RowSource = "QryContactsTBL_Quotes_Active"
Me.Combo27.RowSource = "QRYFreight_Active"

Else
Me.JIMCBO_FOR_Quote.RowSource = "Companies_QRYAll"
Me.Combo23.RowSource = "QryDelivery_Address_For_Quotes_All"
Me.Combo20.RowSource = "QryContactsTBL_Quotes_All"
Me.Combo27.RowSource = "QRYFreight_All"

End If

ExitError:
Exit Sub

ErrorHandler:
Select Case Err.Number
Case 3021
MsgBox ("No Record Found Select 'OK' to Continue") 'This is to trap the error and ignore it.
Resume Next
Case 999
Resume Next
Case Else
Call LogError(Err.Number, Err.Description, "Quote Form Record Counter", , True)
Resume ExitError
End Select

End Sub

 

[MajP]

If a recordset returns no records you will throw an error if you try to move within it.
This will always throw an error if no records returned

Set rst = Me.RecordsetClone

With rst
.MoveFirst
.MoveLast
lngCounter = .RecordCount
End With

Should be

Set rst = Me.RecordsetClone

With rst
  if not .eof then
      .Movelast
      .MoveFirst
      lngCounter = .RecordCount
  end if
End With

 

[HillTJ]

@MajP , I suspected something like this. Really appreciate it. Will incorporate you amendment into my code & let you know.
To everyone else, thanks for supporting me. Cheers.

 

[HillTJ]

Friends, Works! All Good.

Thanks.