Run-Time Error 91 trying to create recordset in VBA

[mrmozambique]

Hi all. Searched everywhere, but I don't seem to understand the meaning of this error. I'm trying to create a warning message box if the user tries to enter a duplicate record (but still allow it). My code is below. The error occurs when I try to set rcst equal to the recordset of rslt (code: Set rcst = dbs.OpenRecordset(rslt, dbOpenSnapshot)). Any ideas?

'Check to see if Facility has already had shipment this cycle.  If so, give a warning.
Dim dbs As DAO.Database
Dim rslt As String
Dim answer As Integer
Dim rcst As DAO.Recordset

Set dbs = CurrentDb
rslt = "SELECT eDeliveries.DispCycle, eDeliveries.FacilityID FROM eDeliveries WHERE (((eDeliveries.DispCycle)=[Forms]![fDispatch]![fDispatchsub]![txtDispCycle]) AND ((eDeliveries.FacilityID)=[Forms]![fDispatch]![fDispatchsub]![cbFacilityID]));"

Set rcst = dbs.OpenRecordset(rslt, dbOpenSnapshot) ' [COLOR=DarkOrange]THIS IS WHERE THE ERROR OCCURS[/COLOR]
If rcst.EOF Then
      answer = MsgBox("This facility already had a shipment this cycle. Do you wish to continue?", vbYesNo)
      If answer = vbNo Then
         Cancel = True
         Me.cbFacilityID.SetFocus
      End If
Else
End If

 

[pwbrown]

Having a quick look at the code it seems fine, I would think your problem lies within the query.

 

[pr2-eugin]

Try this as your SQL statement..

rslt = "SELECT eDeliveries.DispCycle, eDeliveries.FacilityID " & _
         "FROM eDeliveries " & _
         "WHERE (((eDeliveries.DispCycle)=[B][COLOR=Red] '" & [/COLOR][/B][Forms]![fDispatch]![fDispatchsub]![txtDispCycle][B][COLOR=Red] & "'[/COLOR][/B]) AND ((eDeliveries.FacilityID)= [COLOR=Red][B]'" & [/B][/COLOR][Forms]![fDispatch]![fDispatchsub]![cbFacilityID][B][COLOR=Red] & "'[/COLOR][/B]));"

You need to properly concatenate variables when generating SQL.. Also remember to use Debugging technique..

 

[mrmozambique]

Try this as your SQL statement..

rslt = "SELECT eDeliveries.DispCycle, eDeliveries.FacilityID " & _
         "FROM eDeliveries " & _
         "WHERE (((eDeliveries.DispCycle)=[B][COLOR=Red] '" & [/COLOR][/B][Forms]![fDispatch]![fDispatchsub]![txtDispCycle][B][COLOR=Red] & "'[/COLOR][/B]) AND ((eDeliveries.FacilityID)= [COLOR=Red][B]'" & [/B][/COLOR][Forms]![fDispatch]![fDispatchsub]![cbFacilityID][B][COLOR=Red] & "'[/COLOR][/B]));"

You need to properly concatenate variables when generating SQL.. Also remember to use Debugging technique..

Thanks for the responses, guys. I'm getting a type mismatch error now on the Set rcst = dbs.OpenRecordSet line. I always struggle with this in VBA. All form objects (e.g. txtDispCycle and cbFacilityID) are integers as are db fields (e.g. DispCycle, FacilityID). How will that change where the quotes are?

I inserted the Debug Print command, but nothing happened. Not sure what the

 

[pr2-eugin]

Okay I did not know they were Numbers, in that case.. Try removing the single quotes..

rslt = "SELECT eDeliveries.DispCycle, eDeliveries.FacilityID " & _
       "FROM eDeliveries " & _
       "WHERE (((eDeliveries.DispCycle)= " & [Forms]![fDispatch]![fDispatchsub]![txtDispCycle] & ") AND ((eDeliveries.FacilityID)= " & [Forms]![fDispatch]![fDispatchsub]![cbFacilityID] & "));"

Go to the VBA Editor (ALT+F11), then hit CTRL+G which will open the Immediate window.. it will show the generated SQL.. once you Run the code..

Or when the error throws up click Debug.. and then Ctrl+G to see the window..

 

[WendyRussell858Ba]

i want to share what i found on this website
www errorsolutions.tech/error/vba-runtime-error-91

it actually works for me they have step by step guide with pictures

 

[The_Doc_Man]

1. Thanks for your contribution Wendy, but were you aware you were responding to a thread over 5 years old?

2. The link didn't work for me, though there IS still a viable www.errorsolutions.tech site.