Property Not Found error

[RDunlap]

I have a data entry form that worked, but I changed the backend to linked SQL
tables and now I get an error: Property Not Found when I try to create a new
record. I can edit existing records with the form just fine.

Here is what happens:

I have a combo box that the user selects an experiment that they want to
work with. Once they make a selection, the are presented with about 30
fields to populate and then they can press a button to save the record. When
they enter data into a field and then try to go to another field, an error
saying "Property Not Found" pops up. Below is the code that gets exeucted
when they make a selection in the combo box:

Private Sub ComboPenRepRoomExp_Click()
Dim strSQL As String

Set rstHarvest = New ADODB.Recordset
rstHarvest.CursorLocation = adUseClient

strSQL = "Select * from PenHarvest where PenKey = " &
ComboPenRepRoomExp.Value

rstHarvest.Open strSQL, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic

If rstHarvest.EOF And rstHarvest.BOF Then
rstHarvest.AddNew
cmdNext.Enabled = False
cmdPrev.Enabled = False
End If

Set Me.Recordset = rstHarvest

UnlockPenHarvestDetailTextBoxes
LotTattooNo.SetFocus

End Sub

UnlockPenHarvestDetail is just a routine that loops through all the text
boxes on the screen and enables them for data entry.

This used to work fine when using Access tables, but now I get the weird
Property Not Found error message when it is a linked SQL table.

I don't know what other info to post. There is no code on the fields for
validation, no events such as BeforeUpdate or AfterUpdate or anything like
that on any of the fields.

Can anyone give me an inkling of what might be the issue?

TIA for any help.

 

[RDunlap]

I forgot to mention that once the "Property Not Found" error message pops up,
all other fields except the one that data was entered into, contains #ERROR.
It does not matter which field you type data into.

 

[Klatuu]

On which line in your code does the error occur?

 

[RDunlap]

As you can probably tell, I am fairly new to Access programming. To answer
your question, I have no idea. As far as I can tell, there is no specific
line of code that is triggering this - it seems to me that it is something
happening behind the scenes that I cannot see? I set a breakpoint in the
code, it executes the line that sets the focus to the field LotTattooNo, I
type data into that field and then click on another field and that is when
the error pops up.

Is there something else I can do to try and trap where this is occurs?

 

[Klatuu]

Set a breakpoint on the first line of code. (put the cursor on the line and
press F9)
When the code executes, the VB editor will open and the line will be
highlighted. It will not execute. The highligh shows the next line to be
executed.
To execute the line press F8. It if executes successfully, the highlight
will move to the next line. When you get to the line that is having the
problem, it will show the error.

 

[RDunlap]

That is what I am trying to say that I did. I stepped through each line of
code that I posted. The last line sets the focus to LotTattooNo. I type
data into that field, click on a different field and I get the error without
control ever returning to the code.

 

[Klatuu]

Then the code posted is not the problem. Is it possible there is an event in
LotTattooNo that executes code, like the After Update, Lost Focus, or Exit
events?

 

[RDunlap]

That is the puzzling part to me. As I posted originally, there are no events
on any of the other fields. This is why I am at a loss on how to
troubleshoot something like this.

At first I thought maybe it was automatically creating a blank record in the
table, but in SQL all of the fields are set to not allow nulls. So, I put
some code in to default all of the fields to some value, but that did not
make any difference.

I am at my wits end trying to determine what the problem is.

 

[Klatuu]

I am wondering if it could be because you are using a client side cursor as a
record source for your form. I don't know if this is an issue or not, but
I'm guessing it could be.

I would try using the query you use to create the cursor as the record source.

 

[RDunlap]

Thanks for all your help, Dave... but this is where my ignorance comes in.
what do you mean by: using the query to create the cursor as the record
source?

 

[Klatuu]

This line:

rstHarvest.CursorLocation = adUseClient


means it is creating a cursor on the client machine. A cursor is like a
recordset in memory. I don't know that using a cursor for a recordset is
allowed. It seems to fail when you try to update anything in the record, for
example, the control you are entering the data into.

 

[RDunlap]

Thanks Dave. At least I have a direction to start on and see if I can come
up with some sort of resolution.

I appreciate your time.