Error 91 Object variable or with block variable not set

[Guest]

I receive the subject error message when trying to save a record . Using
Version Access 2003. How to locate the error would be very helpful. Debugging
does not produce any error.

 

[Guest]

How are you attempting to save the record? Is this VBA code on a button
click, or just the udpate of the form? Post any germane code for analysis.

 

[Guest]

Thank you for your prompt reply. I am attemting to write a programme that
collects the customer data from the server and deposits into to a tempary
local form. To date the search works find and I can open the record, But it
will not let me save back to the server. I am new to vb and am copy the
coding from Microsof Building access applications. Written by JL Viescas.
This a VBA Code how do i send the germane code and how much.?

 

[Guest]

1. What is the server (Oracle, DB2, SQL, Access file?) & how do you connect
(ODBC or direct Access link)?
2. Is the form bound to the table (i.e. is the RecordSource of the form the
table) with controls on the form bound to fields in the table
(ControlSource), or are these all unbound.

That is, when you "open the record", is your form bound to it, or are you
just looking up various values from the table and assigning them to various
controls on the form?

The code: what do you do to attempt to save the record? Click a button? If
so, send the code from the Click event of the button (e.g. Private
Button1_Click() with the code below.)

 

[Guest]

The server is a linked backed end to my programme and the form is not bound
to the table . Form is populated via a local table on the front end.
The click function on the save button calls the fctCheckedSaved . I have
copied half of the code . If i knew what was not set in the error message it
would be a great hepl.
Public Function fctCheckSaved() As String
' This code checks that the required information is complete and then
' saves the record and flags the linked table for an update
Dim db As DAO.Database, rst As DAO.Recordset
Dim strErrMsg As String, strSQL As String
Dim blnNewRec As Boolean, blnSkipSave As Boolean
Dim lngID As Long

' Set an error trap
On Error GoTo errfctCheckSaved

' If no customer ID yet,
If IsNothing(Me.txtCustomerID) Then
' Set the new record flag
blnNewRec = True
Else
' Have a customer ID - save it
lngID = Me.txtCustomerID
blnNewRec = False
End If

' Make sure the composite Customer Name field is properly filled
Me.txtCustomerName = Me.txtCFirst & " " & Me.txtCLast

'If the current record already saved,
If blnSaved = True Then
' Confirm saved and exit
fctCheckSaved = "Saved"
Else
' Validate the customer data - returns ZLS if successful
strErrMsg = ValidateRecord("frmCustomers", "frmCustomers")
' Add the local validation on addresses and phone numbers
strErrMsg = strErrMsg & fctValidateCustomer()
' If the error string not empty,
If strErrMsg <> "" Then
' Set up the message to display
strErrMsg = "The record could not be saved " & _
"because the following fields are not complete: " & _
vbCrLf & strErrMsg & vbCrLf & "What would you like to do?"
' Display the message - if user clicked cancel,
If CustomError(strErrMsg, OkCancel, "Could not save record", _
Question) = "Cancel Changes" Then
' Return that edit was canceled
fctCheckSaved = "Cancel"
' Clear all data to a new screen
Call fctReLoadRecord
Else
' User clicked - OK, wants to work on it some more
' Return that edit failed, but still dirty
fctCheckSaved = "Ok" 'The user chose to keep working.
' Make sure record not marked saved
fctDirty
End If
Else
' No error messages, so save the record
' But if this is a new record,
If blnNewRec Then
' See if a potential duplicate
' Point to this database
Set db = DBEngine(0)(0)
MsgBox "sql select on fctChecksaved)"
' Open a recordset using Soundex to find duplicates
' and match on city name
' Set up the SQL depending on default address
'THE ERROR MESSAGE APPEARS WHEN I DELETE THE IF STATEMENT I
DONT REQUIRE A BUSINESS _
ADDRESS IN MY PROGRAMME AND THEREFORE WANT TO DELETE THE
COMBO BOX
If Me.cmbDefaultAddress = 1 Then
' Home address
strSQL = "SELECT C.CFirst, C.CLast, " & _
"C.HomeCity As City, C.HomeStateOrProvince As State "
& _
"FROM tblCustomers As C " & _
"WHERE Soundex(C.CLast) = '" & Soundex(Me.txtCLast) & _
"' AND C.HomeCity = '" & Me.txtHomeCity & _
"' AND C.HomeStateOrProvince = '" & _
Me.cmbHomeStateOrProvince & "'"
' Else
' Business address
' strSQL = "SELECT C.CFirst, C.CLast, " & _
' "C.BusinessCity As City, C.BusinessStateOrProvince
As State " & _
' "FROM tblCustomers As C " & _
' "WHERE Soundex(C.CLast) = '" & Soundex(Me.txtCLast)
& _
' "' AND C.BusinessCity = '" & Me.txtBusinessCity &
"'" '_
' "' AND C.BusinessStateOrProvince = '" & _'
' Me.cmbBusinessStateOrProvince & "'"
End If

 

[Guest]

When you compile from within the VBA screen, does it generate the error? It
should, along with highlighting the line where the error occurs. That would
be most helpful info, but here goes a guess.

Since the error appears only when you remove the If statement, then the
culprit is evidently somewhere within the If..Then loop. I am assuming that
it is the loop at the bottom of the function that begins thus:

If Me.cmbDefaultAddress = 1 Then...

Since you are not requiring a business address, does the conrol
cmbDefaultAddress even exist on the form and do you set its value at some
point? If not, or its value has never been 1, then perhaps it has simply been
bypassing the rest of the loop (if the Else was, indeed commented out as you
posted below)

If all the of the above is accurate, then it points to something in the
SQL-building line, and you might try using an explicit reference to your
table name instead of "C", just to clarify the SQL structure. You might also
try creating a query that generates the same SQL, with
[Form]![yourForm]![yourControl] as the criteria of the various fields. The
query builder will pinpoint SQL errors immediately, whereas they can be
difficult to find in VBA (with all those single & double quotes).

Also, although I know what Soundex is, I am not familiar with how it works
in Access; but if it is an add-in, are you sure that you have the add-in
installed?

 

[Guest]

Its quite a complicated programme and the combo box on the form is named
"cmbDefaultaddress" 1 being the home address i could retain the combo box on
the form but as I have no use for a second selection I wished to remove it
from the from. i am assumming the SQL structure is sound as it work fine if
i retained home and Business address. i will take on board your comments and
thank you for your time and efford
Regards Bob

When you compile from within the VBA screen, does it generate the error? It
should, along with highlighting the line where the error occurs. That would
be most helpful info, but here goes a guess.

Since the error appears only when you remove the If statement, then the
culprit is evidently somewhere within the If..Then loop. I am assuming that
it is the loop at the bottom of the function that begins thus:

If Me.cmbDefaultAddress = 1 Then...

Since you are not requiring a business address, does the conrol
cmbDefaultAddress even exist on the form and do you set its value at some
point? If not, or its value has never been 1, then perhaps it has simply been
bypassing the rest of the loop (if the Else was, indeed commented out as you
posted below)

If all the of the above is accurate, then it points to something in the
SQL-building line, and you might try using an explicit reference to your
table name instead of "C", just to clarify the SQL structure. You might also
try creating a query that generates the same SQL, with
[Form]![yourForm]![yourControl] as the criteria of the various fields. The
query builder will pinpoint SQL errors immediately, whereas they can be
difficult to find in VBA (with all those single & double quotes).

Also, although I know what Soundex is, I am not familiar with how it works
in Access; but if it is an add-in, are you sure that you have the add-in
installed?

The server is a linked backed end to my programme and the form is not bound
to the table . Form is populated via a local table on the front end.
The click function on the save button calls the fctCheckedSaved . I have
copied half of the code . If i knew what was not set in the error message it
would be a great hepl.
Public Function fctCheckSaved() As String
' This code checks that the required information is complete and then
' saves the record and flags the linked table for an update
Dim db As DAO.Database, rst As DAO.Recordset
Dim strErrMsg As String, strSQL As String
Dim blnNewRec As Boolean, blnSkipSave As Boolean
Dim lngID As Long

' Set an error trap
On Error GoTo errfctCheckSaved

' If no customer ID yet,
If IsNothing(Me.txtCustomerID) Then
' Set the new record flag
blnNewRec = True
Else
' Have a customer ID - save it
lngID = Me.txtCustomerID
blnNewRec = False
End If

' Make sure the composite Customer Name field is properly filled
Me.txtCustomerName = Me.txtCFirst & " " & Me.txtCLast

'If the current record already saved,
If blnSaved = True Then
' Confirm saved and exit
fctCheckSaved = "Saved"
Else
' Validate the customer data - returns ZLS if successful
strErrMsg = ValidateRecord("frmCustomers", "frmCustomers")
' Add the local validation on addresses and phone numbers
strErrMsg = strErrMsg & fctValidateCustomer()
' If the error string not empty,
If strErrMsg <> "" Then
' Set up the message to display
strErrMsg = "The record could not be saved " & _
"because the following fields are not complete: " & _
vbCrLf & strErrMsg & vbCrLf & "What would you like to do?"
' Display the message - if user clicked cancel,
If CustomError(strErrMsg, OkCancel, "Could not save record", _
Question) = "Cancel Changes" Then
' Return that edit was canceled
fctCheckSaved = "Cancel"
' Clear all data to a new screen
Call fctReLoadRecord
Else
' User clicked - OK, wants to work on it some more
' Return that edit failed, but still dirty
fctCheckSaved = "Ok" 'The user chose to keep working.
' Make sure record not marked saved
fctDirty
End If
Else
' No error messages, so save the record
' But if this is a new record,
If blnNewRec Then
' See if a potential duplicate
' Point to this database
Set db = DBEngine(0)(0)
MsgBox "sql select on fctChecksaved)"
' Open a recordset using Soundex to find duplicates
' and match on city name
' Set up the SQL depending on default address
'THE ERROR MESSAGE APPEARS WHEN I DELETE THE IF STATEMENT I
DONT REQUIRE A BUSINESS _
ADDRESS IN MY PROGRAMME AND THEREFORE WANT TO DELETE THE
COMBO BOX
If Me.cmbDefaultAddress = 1 Then
' Home address
strSQL = "SELECT C.CFirst, C.CLast, " & _
"C.HomeCity As City, C.HomeStateOrProvince As State "
& _
"FROM tblCustomers As C " & _
"WHERE Soundex(C.CLast) = '" & Soundex(Me.txtCLast) & _
"' AND C.HomeCity = '" & Me.txtHomeCity & _
"' AND C.HomeStateOrProvince = '" & _
Me.cmbHomeStateOrProvince & "'"
' Else
' Business address
' strSQL = "SELECT C.CFirst, C.CLast, " & _
' "C.BusinessCity As City, C.BusinessStateOrProvince
As State " & _
' "FROM tblCustomers As C " & _
' "WHERE Soundex(C.CLast) = '" & Soundex(Me.txtCLast)
& _
' "' AND C.BusinessCity = '" & Me.txtBusinessCity &
"'" '_
' "' AND C.BusinessStateOrProvince = '" & _'
' Me.cmbBusinessStateOrProvince & "'"
End If