Run-Time Error 91 trying to create recordset in VBA (1 Viewer)

mrmozambique

Registered User.
Local time
Today, 13:22
Joined
May 20, 2009
Messages
16
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?



Code:
'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

Registered User.
Local time
Today, 06:22
Joined
Oct 1, 2012
Messages
170
Having a quick look at the code it seems fine, I would think your problem lies within the query.
 

pr2-eugin

Super Moderator
Local time
Today, 06:22
Joined
Nov 30, 2011
Messages
8,494
Try this as your SQL statement..
Code:
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

Registered User.
Local time
Today, 13:22
Joined
May 20, 2009
Messages
16
Try this as your SQL statement..
Code:
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

Super Moderator
Local time
Today, 06:22
Joined
Nov 30, 2011
Messages
8,494
Okay I did not know they were Numbers, in that case.. Try removing the single quotes..
Code:
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

New member
Local time
Yesterday, 23:22
Joined
May 1, 2018
Messages
1
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

Immoderate Moderator
Staff member
Local time
Today, 01:22
Joined
Feb 28, 2001
Messages
27,001
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.
 

Users who are viewing this thread

Top Bottom